Oracle

Oracle Explain plan Performance tuning-16

Advertisements

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.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: