1. First we need to turn serveroutput off
2. Set the linesize wide enoungh
3. Add the gather_plan_statistics hint to query
4. execute statement
4. Immediately after, exec dbms_xplan.display_cursor with options
set serveroutput off
set linesize 220
set pagesize 100
set timing on
SELECT /*+ gather_plan_statistics */ colA, colB, colC FROM tableA WHERE colA = 'ABC';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS PARTITION LAST NOTE'));
set linesize 220
set pagesize 100
set timing on
SELECT /*+ gather_plan_statistics */ colA, colB, colC FROM tableA WHERE colA = 'ABC';
select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS PARTITION LAST NOTE'));
The options for the call to dbms_xplan.display_cursor will show the following when relevant:
Starts - number of times the particular step is executed
E-Rows - Estimated rows returned by optimizer
A-Rows - Actual rows returned
A-Time - Actual time
Buffers - consistent gets + current gets
Reads - physical reads
Other format options:
ROWS - if relevant, shows the number of rows estimated by the optimizer
BYTES - if relevant, shows the number of bytes estimated by the optimizer
COST - if relevant, shows optimizer cost information
PARTITION - if relevant, shows partition pruning information
PARALLEL - if relevant, shows PX information (distribution method and table queue information)
PREDICATE - if relevant, shows the predicate section
PROJECTION -if relevant, shows the projection section
ALIAS - if relevant, shows the "Query Block Name / Object Alias" section
REMOTE - if relevant, shows the information for distributed query (for example, remote from serial distribution and remote SQL)
NOTE - if relevant, shows the note section of the explain plan
IOSTATS - assuming that basic plan statistics are collected when SQL statements are executed (either by using the gather_plan_statistics hint or by setting the parameter statistics_level to ALL), this format shows IO statistics for ALL (or only for the LAST as shown below) executions of the cursor.
MEMSTATS - Assuming that PGA memory management is enabled (that is, pga_aggregate_target parameter is set to a non 0 value), this format allows to display memory management statistics (for example, execution mode of the operator, how much memory was used, number of bytes spilled to disk, and so on). These statistics only apply to memory intensive operations like hash-joins, sort or some bitmap operators.
ALLSTATS - A shortcut for 'IOSTATS MEMSTATS'
LAST - By default, plan statistics are shown for all executions of the cursor. The keyword LAST can be specified to see only the statistics for the last execution.
******************************************
keywords: dbms_xplan.display_cursor dbms xplan explain plan
******************************************
rdbms version: 11g
******************************************
No comments:
Post a Comment