Use of the display_cursor function of dbms_xplan

The display_cursor function in the dbms_xplan package is different from the display function. display_cursor is used to display the real execution plan of SQL statements. In most cases
The Execution Plan helps to better analyze the whole process of the SQL statement, especially the Real-Time I/O overhead of running this SQL statement. Compare the estimated I/O with the actual I/O overhead to determine whether the SQL statement is stored
When the problem arises, such as the lack of statistical information, the number of SQL statement executions, and the appropriate connection method based on the actual intermediate result set size. This article only describes how to use the display_cursor Function
.

For descriptions of each field module in the execution plan, see descriptions of each field module in the execution plan.
For more information about how to obtain execution plans using SQL statements, see:
Use explain plan to obtain the SQL statement execution plan
For how to use autotrace to obtain an execution plan, see:
Enable autotrace
For the display function of dbms_xplan, see:
Use of the display function of dbms_xplan

I. display_cursor function usage
1. display_cursor function syntax
Dbms_xplan.display_cursor (<br/> SQL _id in varchar2 default null, <br/> child_number in number default null, <br/> Format in varchar2 default 'typical'); <br/>2. display_cursor function parameter description
SQL _id
Specifies the parent cursor of the SQL statement in the database cache execution plan. The default value is null. When the default value is used, the execution plan of the last SQL statement of the current session will be returned.
You can obtain the SQL _id of an SQL statement by querying the SQL _id column of V $ SQL or V $ sqlarea.
Child_number
Specify the sequence number of the Child cursor under the parent cursor. That is, the subcursor of the SQL statement returned for execution plan. The default value is 0. If it is null, SQL _id indicates all child cursors under the parent cursor
Will be returned.
Format
Control the output part of the SQL statement execution plan, that is, which can be displayed or not displayed. The format parameter and modifier of the display function are also applicable here.
In addition, when statistics_level = All is enabled or gather_plan_statistics is used, the system prompts that real-time statistics in the execution plan can be obtained.
For detailed format description, see the format parameter description in the use of the display function of dbms_xplan.

The modifier added by format when statistics are enabled is shown below
Display of iostats control I/O statistics
By default, last displays all statistics that have been computed. If this value is specified, only the statistics of the last execution are displayed.
Memstats controls the display of PGA-related statistics
Allstats: a shortcut for iostats memstats, that is, allstats includes iostats and memstats.
Run_stats_last is equivalent to iostats last. It can only be used for Oracle 10g r1
Run_stats_tot is equivalent to iostats. It can only be used for Oracle 10g r1

Ii. demonstrate how to use the display_cursor function to obtain the execution plan
1. Load the current database version and execution plan to the database cache
SQL> select * from V $ version where rownum <2; </P> <p> banner <br/> alias <br/> Oracle Database 10g Release 10.2.0.3.0-64bit production </P> <p> SQL> select ename, dname, loc <br/> 2 from emp e, DEPT d <br/> 3 where E. deptno = D. deptno <br/> 4 and E. empno = 7788; </P> <p> ename dname loc <br/> ---------- -------------- ------------- <br/> Scott research Dallas <br/>2. view the real execution plan/* ---------------- Do not pass any parameters to the display_cursor function, display the execution plan of the last SQL statement of the current session ------------- */<br/> /********************** * **************************/<br/>/* Author: robinson Cheng */<br/>/* blog: http://blog.csdn.net/robinson_0612 */<br/>/* MSN: robinson_0612@hotmail.com */<br/>/* QQ: 645746311 */<br/> /******************************** * ****************/<br/> SQL> select * from table (dbms_xplan.display_cursor (null, null); </P> <p> plan_table_output <br/> limit <br/> SQL _id a67wqmkfb9j65, Child number 0 <br/> --------------------------------- <br/> select ename, dname, LOC from emp e, DEPT d Where E. deptno = D. deptno and <br/> E. empno = 7788 </P> <p> plan hash value: 2385808155 </P> <p> bytes <br/> | ID | operation | Name | rows | bytes | cost (% CPU) | time | <br/> latency <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | nested loops | 1 | 63 | 3 (0) | 00:00:01 | <br/> | 2 | table access by index rowid | EMP | 1 | 33 | 2 (0) | 00:00:01 | <br/> | * 3 | index unique scan | pk_emp | 1 | 1 (0) | 00:00:01 | <br/> | 4 | table access by index rowid | dept | 409 | 12270 | 1 (0) | 00:00:01 | <br/> | * 5 | index unique scan | pk_dept | 1 | 0 (0) | <br/> identifier </P> <p> predicate information (identified by Operation ID ): <br/> ------------------------------------------------- </P> <p> 3-access ("e ". "empno" = 7788) <br/> 5-access ("e ". "deptno" = "D ". "deptno") </P> <p>/* ----------------- obtain the SQL _id of the SQL statement, it can be seen that this SQL _id is consistent with the SQL _id in the preceding execution plan ---------- */<br/> SQL> select SQL _id, address, plan_hash_value, hash_value, child_number from V $ SQL <br/> 2 where SQL _text like '% select ename %' and SQL _text not like '% from V $ SQL % '; </P> <p> SQL _id address plan_hash_value hash_value child_number <br/> ------------- ---------------- Jun ---------- ------------ <br/> Jun 2385808155 2629092549 0 </P> <p> /*-------------- pass the SQL _id and format parameters, use the modifier to control the execution plan output ---------------------- */<br/> SQL> select * from table (dbms_xplan.display_cursor ('a67wqmkfb9j65', null, 'typical-Predicate-rows '); </P> <p> plan_table_output <br/> latency <br/> SQL _id a67wqmkfb9j65, child number 0 <br/> ----------------------------------- <br/> select ename, dname, LOC from emp e, DEPT d Where E. deptno = D. deptno <br/> and E. empno = 7788 </P> <p> plan hash value: 2385808155 </P> <p> ------------------------------------------------------------------------------ <br/> | ID | operation | Name | bytes | cost (% CPU) | time | <br/> -------------------------------------------------------------------------------- <br/> | 0 | SELECT statement | 3 (100) | <br/> | 1 | nested loops | 63 | 3 (0) | 00:00:01 | <br/> | 2 | table access by index rowid | EMP | 33 | 2 (0) | 00:00:01 | <br/> | 3 | index unique scan | pk_emp | 1 (0) | 00:00:01 | <br/> | 4 | table access by index rowid | dept | 12270 | 1 (0) | 00:00:01 | <br/> | 5 | index unique scan | pk_dept | 0 (0) | <br/> average <br/>3. view the real execution plan and obtain statistical information
Prerequisites
Set the statistics_level parameter to all, which can be based on the session level and instance level.
Or enable the gather_plan_statistics prompt./* ------------- View the value of the Instance parameter statistics_level, set it to all --------- */<br/> SQL> show parameter statistics_le </P> <p> name type value <br/> ------------- ------------------------------ <br /> statistics_level string all </P> <p> SQL> alter session set statistics_level = all; </P> <p> session altered. </P> <p> SQL> select e. ename, E. sal, S. grade <br/> 2 from EMP e <br/> 3 join salgrade S <br/> 4 on E. sal between losal and hisal <br/> 5 and E. deptno = 20; </P> <p> ename Sal Grade <br/> ---------- <br/> Scott 3000 4 <br/> Ford 3000 4 <br/> Jones 2975 4 <br /> Adams 1100 1 <br/> Smith 800 1 </P> <p>/* ------- execute the preceding SQL statement to obtain the actual execution plan, iostats last-Predicate-note modifier is used to control the output ----- */<br/> SQL> set pagesize 0 <br/> SQL> select * from table (dbms_xplan.display_cursor (null, null, 'iostats last-Predicate-note'); <br/> SQL _id 243b0tpjxj6wv, Child number 0 <br/> ----------------------------------- <br/> select e. ename, E. sal, S. grade from EMP e join salgrade s on E. sal between losal and <br/> hisal and E. deptno = 20 </P> <p> plan hash value: 4204027666 </P> <p> buffers <br/> | ID | operation | Name | starts | E-rows | A-time | buffers | <br/> commit <br/> | 1 | merge join | 1 | 1 | 5 | 00:00:00. 01 | 14 | <br/> | 2 | sort join | 1 | 5 | 5 | 00:00:00. 01 | 7 | <br/> | 3 | table access full | EMP | 1 | 5 | 5 | 00:00:00. 01 | 7 | <br/> | 4 | filter | 5 | 5 | 00:00:00. 01 | 7 | <br/> | 5 | sort join | 5 | 5 | 14 | 00:00:00. 01 | 7 | <br/> | 6 | table access full | salgrade | 1 | 5 | 5 | 00:00:00. 01 | 7 | <br/> modify </P> <p>/* -------------- modify the statistics_level parameter of the session level to typical and verify the Modification result -------------- */<br/> SQL> alter session set statistics_level = typical; </P> <p> SQL> Col name format A40 <br/> SQL> Col value format A25 <br/> SQL> Col display_value format A25 <br/> SQL> select name, value, display_value, isses_modifiable <br/> 2 from V $ parameter <br/> 3 where isses_modifiable = 'true' <br/> 4 and name like '% & input_name % '; <br/> enter value for input_name: statistics_level <br/> old 4: And name like '% & input_name %' <br/> NEW 4: and name like '% statistics_level %' </P> <p> name value display_value isses <br/> commandid ----------------------- ----- <br/> statistics_level typical true </P> <p>/* -------- the usage prompt gather_plan_statistics, and obtain the actual execution plan, use the allstats-rows modifier to control the output --- */<br/> SQL> set pagesize 180 <br/> SQL> select/* + gather_plan_statistics */ename, dname, loc <br/> 2 from emp e, DEPT d <br/> 3 where E. deptno = D. deptno <br/> 4 and D. deptno = 20 order by 1, 2, 3; </P> <p> ename dname loc <br/> ---------- -------------- ------------- <br/> Adams research Dallas <br/> Ford Research Dallas <br/> Jones research Dallas <br /> Scott research Dallas <br/> Smith research Dallas </P> <p> SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats-rows '); </P> <p> plan_table_output <br/> rows <br/> SQL _id d2hh42yzqqjz7, child number 0 <br/> ----------------------------------- <br/> select/* + gather_plan_statistics */ename, dname, LOC from emp e, DEPT d Where E. deptno = D. deptno and <br/> D. deptno = 20 order by 1, 2, 3 </P> <p> plan hash value: 3339094711 </P> <p> logs <br/> | ID | operation | Name | starts | A-rows | A-time | buffers | omem | 1mem | o/1/ M | <br/> limit <br/> | 1 | sort order by | 1 | 5 | 00:00:00. 01 | 9 | 2048 | 2048 | 1/0/0 | <br/> | 2 | nested loops | 1 | 5 | 00:00:00. 01 | 9 | <br/> | 3 | table access by index rowid | dept | 1 | 1 | 00:00:00. 01 | 2 | <br/> | * 4 | index unique scan | pk_dept | 1 | 1 | 00:00:00. 01 | 1 | <br/> | * 5 | table access full | EMP | 1 | 5 | 00:00:00. 01 | 7 | <br/> identifier </P> <p> predicate information (identified by Operation ID ): <br/> ------------------------------------------------- </P> <p> 4-access ("D ". "deptno" = 20) <br/> 5-filter ("e ". "deptno" = 20) </P> <p> note <br/> ----- <br/>-dynamic sampling used for this statement <br/>Iii. Summary
1. Unlike the display function, display_cursor displays a real execution plan.
2. For the format parameter, each value of the display function is also applicable to the display_cursor function.
3. When the statistics_level is all or the gather_plan_statistics prompt is used to obtain statistics for execution.
4. Based on the actual and estimated statistical information, you can preliminarily determine the cause of low SQL efficiency, such as the accuracy of the statistical information and the main overhead lies in those steps.