View the actual execution plan of ORACLE and the Execution Plan of oracle
ORACLE's execution plans are divided into estimated execution plans and actual execution plans. The execution plans you obtain using Toad, PL/SQL Developer, SQL Developer, EXPLAIN PLAN FOR, or SET ATUOTRACE TRACEONLY are all estimated execution plans. Sometimes the estimated execution plan is very different from the actual execution plan, so sometimes, the actual execution plan and the estimated execution plan need to be compared during optimization, it cannot be fooled by an estimated execution plan. So how can we view the actual execution plan?
Method 1: query the actual execution plan in the v $ SQL _plan view.
1: execute the following SQL statement in the window
SELECT ENAME, SAL FROM SCOTT.EMP, SCOTT.DEPT
WHERE EMP.DEPTNO =DEPT.DEPTNO
AND DEPT.LOC='CHICAGO';
2: view the SQL _ID of the executed SQL statement
SELECT SQL_ID, SQL_TEXT FROM V$SQL WHERE SQL_TEXT LIKE '%SELECT ENAME, SAL FROM SCOTT.EMP, SCOTT.DEPT%'
3: view the v $ SQL _plan View
SELECT * FROM v$sql_plan WHERE SQL_ID='7m5qbhn98j1xw';
Viewing the actual execution plan through the v $ SQL _plan view is not realistic because of reading difficulties and poor readability, especially complex SQL statements.
Method 2: set autotrace on to view the actual execution plan
In fact, this method has a lot of restrictions. For example, if SQL outputs a large number of records, the screen will be refreshed in SQL Plus, and the wait time is long. In addition, this method cannot be used for DML operations. In fact, it is not practical, but it is listed as a method.
Method 3: DBMS_XPLAN.DISPLAY_CURSOR
The DBMS_XPLAN package was first introduced in ORACLE 9i R2 to view the execution Plan generated by the Explain Plan. DBMS_XPLAN features have increased in ORACLE 10g and ORACLE 11g.
ORACLE 10 Gb provides the following four functions.
· DISPLAY-to format and display the contents of a plan table.
· DISPLAY_CURSOR-to format and display the contents of the execution plan of any loaded cursor.
· DISPLAY_AWR-to format and display the contents of the execution plan of a stored SQL statement in the AWR.
· DISPLAY_SQLSET-to format and display the contents of the execution plan of statements stored in a SQL tuning set.
The following five functions are provided in ORACLE 11g.
· DISPLAY-to format and display the contents of a plan table.
· DISPLAY_AWR-to format and display the contents of the execution plan of a stored SQL statement in the AWR.
· DISPLAY_CURSOR-to format and display the contents of the execution plan of any loaded cursor.
· DISPLAY_ SQL _PLAN_BASELINE-to display one or more execution plans for the SQL statement identified by SQL handle
· DISPLAY_SQLSET-to format and display the contents of the execution plan of statements stored in a SQL tuning set.
We do not intend to detail the functions of DBMS_XPLAN functions here. For details, refer to the official documentation. Only the DISPLAY_CURSOR function is described here. It displays the actual execution plan stored in the library cache. Of course, you need to query the actual execution plan of an SQL statement, the premise is that the SQL Execution Plan is still in the database cache. If it has been cached by the database, it will not be able to obtain its actual execution plan.
DISPLAY_CURSOR parameters are described as follows:
FUNCTION DISPLAY_CURSOR RETURNS DBMS_XPLAN_TYPE_TABLE
Argument Name Type In/Out Default?
------------------------------ ----------------------- ------ --------
SQL_ID VARCHAR2 IN DEFAULT
CURSOR_CHILD_NO NUMBER(38) IN DEFAULT
FORMAT VARCHAR2 IN DEFAULT
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.
CURSOR_CHILD_NO
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, the execution plan of all child game targets under the parent cursor indicated by SQL _id 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.
· BASIC: Displays the minimum information in the plan-the operation ID, the operation name and its option.
BASIC: displays only the minimum information. Basically, only the operation ID, operation name, and operation object are included.
· TYPICAL: This is the default. displays the most relevant information in the plan (operation id, name and option, # rows, # bytes and optimizer cost ). pruning, parallel and predicate information are only displayed when applicable. excludes onlyPROJECTION, ALIAS and remote SQL information (see below ).
TYPICAL: This is the default value. It displays most of the information (operation id, name and option, # rows, # bytes and optimizer cost) in the execution plan, parallel, and predicate information, except for aliases, outline and field projection.
· SERIAL: Like TYPICAL before t that the parallel information is not displayed, even if the plan executes in parallel.
SERIAL: similar to TYPICAL, except that the parallel operation information is not displayed, even if the execution plan has parallel processing.
· ALL: Maximum user level. Includes information displayed with theTYPICAL level with additional information (PROJECTION, ALIASand information about remote SQL if the operation is distributed ).
ALL: displays ALL information.
Next let's take a look. First, we will execute the following SQL statement
SQL> SELECT ENAME, SAL
2 FROM SCOTT.EMP E, SCOTT.DEPT D
3 WHERE E.DEPTNO =D.DEPTNO
4 AND D.LOC='CHICAGO';
ENAME SAL
---------- ----------
ALLEN 1600
WARD 1250
MARTIN 1250
BLAKE 2850
TURNER 1500
JAMES 950
6 rows selected.
Query the v $ SQL view and find the SQL _id of the statement. The SQL statement may not be in the Share Pool. This indicates that the SQL statement has been kicked out of the Share Pool.
SQL> SELECT SQL_ID,CHILD_NUMBER
2 FROM V$SQL
3 WHERE SQL_TEXT LIKE '%SELECT ENAME, SAL FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO =D.DEPTNO AND D.LOC=''CHICAGO''%';
SQL_ID CHILD_NUMBER
------------- ------------
7wga0v6nhkjug 0
SQL>
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('7wga0v6nhkjug',0));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 7wga0v6nhkjug, child number 0
-------------------------------------
SELECT ENAME, SAL FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO
=D.DEPTNO AND D.LOC='CHICAGO'
Plan hash value: 615168685
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 5 (100)| |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
|* 1 | HASH JOIN | | 5 | 120 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| DEPT | 1 | 11 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| EMP | 14 | 182 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("E"."DEPTNO"="D"."DEPTNO")
2 - filter("D"."LOC"='CHICAGO')
22 rows selected.
SQL>
If no parameter is passed to the display_cursor function, the execution plan of the last SQL statement of the current session is displayed. For example, I run the following SQL statement in SQL * Plus.
SQL> SELECT * FROM SCOTT.DEPT;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 4db4txmchwqqh, child number 0
-------------------------------------
SELECT * FROM SCOTT.DEPT
Plan hash value: 3383998547
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | TABLE ACCESS FULL| DEPT | 4 | 80 | 2 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
--------------------------------------------------------------------------
13 rows selected.
References:
Http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xplan.htm#CACFJGHG
Https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_xplan.htm