View Explain plan using dbms_xplan.display_cursor

We can use dbms_xplan.display_cursor to help us diagnose and tune sql.

Run a sql statement

SQL> select name, object_id, type from v$fixed_table;

Now find the sql_id for the statement just executed in the SHARED POOL
using v$sql.

SQL> select sql_id, sql_text from v$sql
where sql_text like '%ct name, object_id, type from v$fix%';

SQL_ID SQL_TEXT
------------- --------------------------------------------------
00qkmkwq7n8h8 select name, object_id, type from v$fixed_table
8dq81c42m93jd select sql_id, sql_text from v$sql where sql_text
like '%ct name, object_id, type from v$fix%'

Notice there are 2 statements shown in the shared pool, the original
and the query ran to find the original.

Using the the sql_id of the original we can call dbms_xplan.display_cursor.

SQL> select plan_table_output from table(dbms_xplan.display_cursor('00qkmkwq7n8h8'));

SQL_ID 00qkmkwq7n8h8, child number 0
-------------------------------------
select name, object_id, type from v$fixed_table

Plan hash value: 351235649

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)|
| 1 | VIEW | GV$FIXED_TABLE | 10 | 470 | 0 (0)|
| 2 | UNION-ALL | | | | |
|* 3 | FIXED TABLE FULL| X$KQFTA | 8 | 344 | 0 (0)|
|* 4 | FIXED TABLE FULL| X$KQFVI | 1 | 43 | 0 (0)|
|* 5 | FIXED TABLE FULL| X$KQFDT | 1 | 43 | 0 (0)|
--------------------------------------------------------------------------

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

3 - filter("INST_ID"=USERENV('INSTANCE'))
4 - filter("INST_ID"=USERENV('INSTANCE'))
5 - filter("INST_ID"=USERENV('INSTANCE'))


24 rows selected.

******************************************
keywords: explain plan dbms_xplan
******************************************
rdbms version: 11g
******************************************