To retrieve the execution plan for a specific sql request or session, you need to install the PLAN_TABLE table in your database.
Oracle provide the utlxplan.sql script located under ${ORACLE_HOME}/rdbms/admin/ to perform its installation.
SQL>@?/rdbms/admin/utlxplan;Calculate plan for a specific request
To populate the PLAN_TABLE table for a specific request :
SQL>truncate table PLAN_TABLE; SQL>explain plan for select * from user_tables;Display plan for the previous request
SQL>set linesize 400 SQL>@?/rdbms/admin/utlxpls PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- Plan hash value: 4164660786 --------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1831 | 4173K| 515 (1)| 00:00:07 | |* 1 | HASH JOIN RIGHT OUTER | | 1831 | 4173K| 515 (1)| 00:00:07 | | 2 | INDEX FAST FULL SCAN | I_OBJ2 | 12815 | 300K| 33 (0)| 00:00:01 | |* 3 | HASH JOIN RIGHT OUTER | | 1831 | 4130K| 482 (2)| 00:00:06 | | 4 | TABLE ACCESS FULL | USER$ | 93 | 1581 | 3 (0)| 00:00:01 | |* 5 | HASH JOIN RIGHT OUTER | | 1831 | 4100K| 478 (1)| 00:00:06 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- | 6 | INDEX FAST FULL SCAN | I_OBJ1 | 12815 | 100K| 11 (0)| 00:00:01 | |* 7 | HASH JOIN | | 1831 | 4085K| 466 (1)| 00:00:06 | | 8 | TABLE ACCESS FULL | TS$ | 5 | 95 | 3 (0)| 00:00:01 | |* 9 | HASH JOIN RIGHT OUTER | | 1831 | 4051K| 463 (1)| 00:00:06 | | 10 | TABLE ACCESS FULL | SEG$ | 5235 | 230K| 43 (0)| 00:00:01 | |* 11 | HASH JOIN RIGHT OUTER | | 1831 | 3971K| 419 (1)| 00:00:06 | | 12 | TABLE ACCESS FULL | DEFERRED_STG$ | 2783 | 64009 | 7 (0)| 00:00:01 | |* 13 | HASH JOIN | | 1831 | 3930K| 412 (1)| 00:00:05 | |* 14 | TABLE ACCESS FULL | TAB$ | 2729 | 261K| 367 (0)| 00:00:05 | | 15 | MERGE JOIN CARTESIAN| | 1831 | 3754K| 44 (3)| 00:00:01 | |* 16 | HASH JOIN | | 1 | 2070 | 1 (100)| 00:00:01 | PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- |* 17 | FIXED TABLE FULL | X$KSPPI | 1 | 55 | 0 (0)| 00:00:01 | | 18 | FIXED TABLE FULL | X$KSPPCV | 100 | 196K| 0 (0)| 00:00:01 | | 19 | BUFFER SORT | | 1831 | 54930 | 44 (3)| 00:00:01 | |* 20 | TABLE ACCESS FULL | OBJ$ | 1831 | 54930 | 43 (0)| 00:00:01 | --------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - access("T"."BOBJ#"="CO"."OBJ#"(+)) 3 - access("CX"."OWNER#"="CU"."USER#"(+)) PLAN_TABLE_OUTPUT ---------------------------------------------------------------------------------------------------- 5 - access("T"."DATAOBJ#"="CX"."OBJ#"(+)) 7 - access("T"."TS#"="TS"."TS#") 9 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND "T"."TS#"="S"."TS#"(+)) 11 - access("T"."OBJ#"="DS"."OBJ#"(+)) 13 - access("O"."OBJ#"="T"."OBJ#") 14 - filter(BITAND("T"."PROPERTY",1)=0) 16 - access("KSPPI"."INDX"="KSPPCV"."INDX") 17 - filter("KSPPI"."KSPPINM"='_dml_monitoring_enabled') 20 - filter("O"."OWNER#"=USERENV('SCHEMAID') AND BITAND("O"."FLAGS",128)=0) 43 rows selected.Remark : An other way to display a plan under sqlplus is :
SQL> set autotrace on;To trace only the plan and associated statistics:
SQL> set autotrace traceonly explain statistics;Note that you may need to enable the PLUSTRACE role for the user that activates the autotrace feature. ($ORACLE_HOME/sqlplus/admin/plustrce.sql)
If you have an error then give the 'select any dictionary' role to the user.
Remark : Using the dbms_xplan package:
- To display the plan from the plan table
SQL> select * from table( dbms_xplan.display);
- To display the plan from the shared pool
SQL> select * from table( dbms_xplan.display_cursor);
Displaying current plan with dbms_xplan
- Retrieve the sql_id associated to the current request in the v$sql view
- Display the plan like this:
SQL> select * from table( dbms_xplan.display_cursor('<sql_id>', NULL));
To known if a sql request is sensitive to bind values and adaptive cursor sharing:
SELECT sql_id, child_number, is_bind_sensitive, is_bind_aware FROM v$sql WHERE sql_text = '...'; SQL_ID CHILD_NUMBER I I ------------- ------------ - - 9bmm6cmwa8saf 0 Y N 9bmm6cmwa8saf 1 Y Y 2 rows selected.