| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |INOUT |PQ Distrib | As shown in the following example, parallel information is reported only if the query executes in parallel. Hence, information related to the parallelization of the plan is not reported. In Displaying a Plan Table Using DBMS_XPLAN.DISPLAY, the query does not execute in parallel. Instead of issuing two queries, one to the get the sql_id and child_number pair and one to display the plan, you can combine these in a single query:ĭisplay the execution plan of all cursors matching the string 'TOTO': SELECT t.*įROM v$sql s, DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number) t WHERE sql_text LIKE '%TOTO%' īy default, only relevant information is reported by the display and display_cursor table functions. Plan hash value: 3693697075, SQL ID: gwp663cqh5qbf, child number: 0 Get sql_id and child_number for the preceding statement:ĭisplay the execution plan for the cursor: SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0) Run a query with a distinctive comment: SELECT /* TOTO */ ename, dname

This includes the SQL ID of the statement and optionally the child number. In that case, you must supply a reference to the child cursor to the table function. You can also use the table function DISPLAY_CURSOR to display the execution plan for any loaded cursor stored in the cursor cache. SELECT ename FROM emp e, dept d WHERE e.deptno = d.deptno This query produces the following output: Plan hash value: 3693697075, SQL hash value: 2096952573, child number: 0 SELECT * FROM DBMS_XPLAN.DISPLAY_CURSOR()

To display the execution plan of the last executed statement for that session: SET PAGESIZE 0 Predicate Information (identified by operation id):īy default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | This query produces the following output: Plan hash value: 3693697075 The profile_name parameter specifies the name ofĪdvisor_object (task_name, execution_name, sql_id, plan_id)Įxecute an explain plan command on a SELECT statement:ĭisplay the plan using the DBMS_XPLAN.DISPLAY table function Plan_name-The name of the SQL plan baseline Sql_handle-The SQL handle of plans protected by

Sqlset_name-The name of the SQL tuning set Sqlset_owner-The owner of the SQL tuning set

Sqlset_object (sqlset_owner, sqlset_name, sql_id, plan_hash_value) Plan_hash_value-The hash value of the plan Plan_table_name-The name of the plan tableĬursor_cache_object(sql_id, child_number)Ĭhild_number-The child number of the plan in theĪwr_object(sql_id, dbid, con_dbid, plan_hash_value) Plan_table_object(owner, plan_table_name, statement_id, plan_id)

Xplan for all