View the actual execution plan of ORACLE and the Execution Plan of oracle

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