Finding the actual execution plan for a SQL Statement.

Issuing Explain plan does just that - doesn't actually give you what actually happened.

Can we get better  ?


set long 32000
set linesize 32000
set autotrace on
 select cust_first_name,cust_last_name from customers where cust_id=103379;
quit;

CUST_FIRST_NAME      CUST_LAST_NAME
-------------------- ----------------------------------------
Alana                Yoon


Execution Plan
----------------------------------------------------------
Plan hash value: 4238351645

--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    20 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

   2 - access("CUST_ID"=103379)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        617  bytes sent via SQL*Net to client
        524  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

This is just autotrace.  Lets move up the stack a bit. 

Using DBMS_XPLAN 

... 

If we don't suppply any parameters to DISPLAY_CURSOR then " in which case the plan of the last cursor executed by the session is displayed." 

( see http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_xplan.htm#i996786) 

set long 32000

set linesize 32000

set pagesize 0

-- set autotrace on

select cust_first_name,cust_last_name from customers where cust_id=103379;

-- set autotrace off

select * from table(dbms_xplan.display_cursor);

quit;

Alana                Yoon

SQL_ID  dh88td3czp639, child number 0

-------------------------------------

select cust_first_name,cust_last_name from customers where

cust_id=103379

Plan hash value: 4238351645

--------------------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |       |       |     2 (100)|          |

|   1 |  TABLE ACCESS BY INDEX ROWID| CUSTOMERS    |     1 |    20 |     2   (0)| 00:00:01 |

|*  2 |   INDEX UNIQUE SCAN         | CUSTOMERS_PK |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("CUST_ID"=103379)

20 rows selected.

This is very useful because it takes the work out of guessing with Autotrace etc.