Tuesday 25 October 2011

View EXPLAIN PLAN for a specific SQL ID

The following will display memory stats and estimated number of rows.

select *
from 
table(dbms_xplan.display_cursor('fz8911kvm8mw8'
                                ,null
                                ,'ALLSTATS COST ROWS LAST'))
/
The output in SQL Plus will be in the following format;
-------------------------------------------------------------------
|Id | Operation        |Name|E-Rows|Cost (%CPU)|OMem|1Mem|Used-Mem|
-------------------------------------------------------------------
|  0| SELECT STATEMENT |    |      |  452K(100)|    |    |        |

There are many formats one can pass into DBMS_XPLA.DISPLAY_CURSOR, namely;
ROWS
BYTES
COST
PARTITION
PARALLEL
PREDICATE
PROJECTION
ALIAS
REMOTE
NOTE
IOSTATS
MEMSTATS
ALLSTATS
LAST