10g New Feature - get yer execution plan!

I was doing some SQL troubleshooting today, and had occasion to use one of my most loved 10g New Features, so I thought I'd share it with you here. Don't worry, an 11g new feature is just around the corner.

Most of you are aware of dbms_xplan I'm sure. In 9i you could use it to format the contents of the PLAN_TABLE after running an explain plan command. Also in 9i, a new view was introduced, V$SQL_PLAN. This view provides you with the actual execution plan of any SQL statement currently in the shared pool.

In 9i there was no real simple way to query this view (Tom Kyte has a marvy view and SQL statement that makes it much easier on ASK TOM).

Oracle 10g makes it very easy to query V$SQL_PLAN and find out what really happened to that SQL statement (remember, the explain plan command can be wrong!!). Let's look at an example:

-- Our query
SQL> select a.empid, a.ename, b.dept_desc
2 from emp a, dept b
3 where a.deptid=b.deptid;

EMPID ENAME DEPT_DESC
---------- ------------------------------ -------------
10 Freeman HARDWARE
20 Dutch SOFTWARE
30 Jose NOWARE


-- Get the SQL ID
SQL> select sql_id, sql_text from v$sqlarea where sql_text like '%empid%';

SQL_ID
-------------
SQL_TEXT
--------------------------------------------------------------------------------
dymt4gchudyk9
select a.empid, a.ename, b.dept_desc from emp a, dept b where a.deptid=b.deptid

dm0a3c8v9awb1
select sql_id, sql_text from v$sqlarea where sql_text like '%empid%'


-- Using the SQL_ID, get the execution plan:
SQL> select * from table(dbms_xplan.display_cursor('dymt4gchudyk9',0));

SQL_ID dymt4gchudyk9, child number 0
-------------------------------------
select a.empid, a.ename, b.dept_desc from emp a, dept b where
a.deptid=b.deptid

Plan hash value: 844388907

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 6 (100)| |
| 1 | MERGE JOIN | | 3 | 72 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 33 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_DEPT | 3 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 3 | 39 | 4 (25)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 3 | 39 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

4 - access("A"."DEPTID"="B"."DEPTID")
filter("A"."DEPTID"="B"."DEPTID")

Other options provide more or less detail as you desire.