Tuesday, January 29, 2013

Explain Plan for any SQL ID


set echo on
set lines 300 pages 0
set trimspool on
--spool explain_plan.out
select t.*  
  from v$sql s, 
       table(dbms_xplan.display_cursor(s.sql_id, 
             s.child_number, 'TYPICAL ALLSTATS LAST')) t 
where s.sql_id = '&sql_id';

EXAMPLE OUTPUT
==============
SQL_ID  1fd4dhwcxj9c4, child number 1
-------------------------------------
select shadow from eisaudittrail where id = :1

Plan hash value: 3922317436

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |        |       |     3 (100)|          |
|   1 |  TABLE ACCESS BY INDEX ROWID| EISAUDITTRAIL |      1 |    93 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | PK_AUDITTRAIL |      1 |       |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"=:1)

No comments:

Post a Comment