Explain plan
Analyze to gather statistics in Oracle 8i and above, Use DBMS_STATS.
ANALYZE TABLE <table> VALIDATE STRUCTURE; — Validate table structure.
dbms_stats.set_table_prefs – From 11g onwards. Designed to allow the DBA more control over the freshness of their statistics.
DBMS_XPLAN.xxx
DISPLAY – to format and display the contents of a plan table.
DISPLAY_AWR – to format and display the contents of the execution plan of a stored SQL statement in the AWR.
DISPLAY_CURSOR – to format and display the contents of the execution plan of any loaded cursor.
DISPLAY_SQL_PLAN_BASELINE – to display one or more execution plans for the SQL statement identified by SQL handle
DISPLAY_SQLSET – to format and display the contents of the execution plan of statements stored in a SQL tuning set.
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘plan_table’,null,’basic +predicate +cost’));
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘plan_table’,null,’typical -cost -bytes’));
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY(‘plan_table’,null,’basic +note’)); — NOTE is dynamic stats sampling
SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,’TYPICAL’));
SELECT /*+ GATHER_PLAN_STATISTICS */ n FROM T1_PERF where n1=’a’ or n2=’z’ or n=1; — GATHER_PLAN_STATISTICS hint has an impact on the execution time, it will show the estimated and actual rows
SELECT plan_table_output FROM table(DBMS_XPLAN.DISPLAY_CURSOR (FORMAT=>’ALLSTATS LAST’));
Display Execution plan:
@?/rdbms/admin/utlxplan.sql; –- It will create a plan table.
explain plan for select * from cf_test where owner=’BOWIE’; — It will save the data in plan_table
@?/rdbms/admin/utlxpls.sql;
select * from table (dbms_xplan.display);
How the data access will be for the COB
Clustering Factor of the index – how well ordered the rows in the table are in relation to the index is more important to fetch data from less block.
Full table scan – Reads all rows from a table.
Table access by ROWID – row-by-row access either index scan of one or more of the table’s indexes.
INDEX FULL SCAN — Oracle is reading all rows from the index, and may be accessing these rows in the underlying table.
INDEX FAST FULL SCAN — Oracle is reading all rows from the index, and is not accessing these rows in the underlying table. i.e. The index contains all columns required to resolve the query without having to lookup the table.
INDEX UNIQUE SCAN — Oracle is reading 0 or 1 rows from the index.
INDEX RANGE SCAN — Oracle is reading 0 or more contiguous rows from the index. (MIN/MAX)
INDEX SKIP SCAN — Oracle is reading 0 or more rows from different parts of the index, and may be accessing these rows in the underlying table.
Bitmap Index –- A bitmap index uses a set of bits for each key values and a mapping function that converts each bit position to a rowid. Oracle can efficiently merge bitmap indexes that correspond to several predicates in a WHERE clause, using Boolean operations to resolve AND and OR conditions.
