Dbms_xplan Display View Execution plan

The Dbms_xplan package includes a series of functions, primarily for displaying the execution plan of the SQL statement, and using different functions to display in different situations, such as an estimated execution plan using

The display function, while the actual execution plan is using the Display_cursor function, the Display_awr function is used for the execution plan in the AWR, and the execution plan in the SQL tuning collection

It is done by Display_sqlset (the use of the display function of the Dbms_xplan from Leshami).

function Syntax:

Dbms_xplan. DISPLAY (

table_name in VARCHAR2 DEFAULT ' plan_table ',

statement_id in VARCHAR2 DEFAULT NULL,

Format in VARCHAR2 DEFAULT ' typical ',

Filter_preds in VARCHAR2 DEFAULT NULL);

Parameter description for display function

table_name

Specifies the name of the schedule and the default value is ' plan_table '.

statement_id

The ID number of the SQL statement is the value set with the set statement_id name when the execution plan is generated, the default value is null, and when the default value is used, the most recent

Insert execution plan in schedule (when the value of the Filter_preds parameter is empty)

Format

The content used to control the output of the display function. Its common value is basic,typical,serial,all,advanced. Where typical is the default value

In addition to the above several values, you can also match some additional modifiers to display different content. Such as:

Alias, bytes, cost, note, outline, parallel, paration, predicate, etc.

Examples of common value combination modifiers:

Basic +predicate, Basic +outline (requires a modifier to connect using the "+" symbol)

Typical-bytes, typical +alias-bytes-cost (does not require a modifier to connect using the "-" sign)

Note: the "+" and "-" numbers should be preceded by a space

Filter_preds

Filter predicates. Used to filter the records returned from the Plan_table table. When the value is null, the execution plan displays the execution plan that was recently inserted in the Schedule table.

such as:filter_preds=> ' plan_id = 223 '

Format parameter Common value description

Basic only shows the least amount of information. Objects that basically include operations and operations

Typical displays most of the information. It basically includes all information except aliases, outlines, and field projections, which are the default values.

Serial similar to typical, but does not show parallel operations

All displays all information except the outline

Advanced Show All information

Format parameter modifier

Alias controls the display part that contains the query block and alias

Bytes control the display of field bytes in the Execution plan table

Cost control the display of the field cost in the Execution plan table

Note controls the display part that contains the comment information

Outline controls the display part that contains outline information

Parallel control hints that contain parallel processing information

Partition control the display of information in parallel processing, especially in the Execution Schedule table in the fields TQ, In-out, PQ distrib

The Peeked_binds control contains the display of the bound variable snooping section. Binding variables are visible only when the execution plan is built

predicate control contains predicate filter and Access Display section

Projection controls the display part that contains the projection information

Remote controls the display of remotely executed SQL statements

Rows control the display of rows in the Execution plan table

Use Demo:

sql> EXPLAIN PLAN SET statement_id= ' test ' for SELECT * from EMP E, dept d WHERE E.deptno = D.deptno and E.ename = ' CLARK ';

explained.

Sql> SELECT * from TABLE (Dbms_xplan. DISPLAY (' plan_table ', ' Test ', ' BASIC ');

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

Plan Hash value:3625962092

------------------------------------------------

| Id | Operation | Name |

------------------------------------------------

| 0 |            SELECT STATEMENT | |

|  1 |            NESTED LOOPS | |

|   2 |            NESTED LOOPS | |

|    3 | TABLE ACCESS Full | EMP |

|    4 | INDEX UNIQUE SCAN | pk_dept |

|   5 | TABLE ACCESS by INDEX rowid| DEPT |

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

------------------------------------------------

Rows selected.

Sql> SELECT * from TABLE (Dbms_xplan. DISPLAY (' plan_table ', ' Test ', ' basic+predicate '));

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

Error:format ' basic+predicate ' not valid for Dbms_xplan. DISPLAY ()

Sql> SELECT * from TABLE (Dbms_xplan. DISPLAY (' plan_table ', ' Test ', ' BASIC +predicate ');

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

Plan Hash value:3625962092

------------------------------------------------

| Id | Operation | Name |

------------------------------------------------

| 0 |            SELECT STATEMENT | |

|  1 |            NESTED LOOPS | |

|   2 |            NESTED LOOPS | |

|* 3 | TABLE ACCESS Full | EMP |

|* 4 | INDEX UNIQUE SCAN | pk_dept |

|   5 | TABLE ACCESS by INDEX rowid| DEPT |

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

3-filter ("E". " ename "= ' CLARK ')

4-access ("E". " DEPTNO "=" D "." DEPTNO ")

Rows selected.

Sql> SELECT * from TABLE (Dbms_xplan. DISPLAY (' plan_table ', ' test ', ' typical '));

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

Plan Hash value:3625962092

----------------------------------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |

----------------------------------------------------------------------------------------

| 0 |            SELECT STATEMENT |     |    1 |     58 | 4 (0) | 00:00:01 |

|  1 |            NESTED LOOPS |       |       |     |              | |

|   2 |            NESTED LOOPS |     |    1 |     58 | 4 (0) | 00:00:01 |

|* 3 | TABLE ACCESS Full |     EMP |    1 |     38 | 3 (0) | 00:00:01 |

|* 4 | INDEX UNIQUE SCAN |     pk_dept |       1 |     | 0 (0) | 00:00:01 |

|   5 | TABLE ACCESS by INDEX rowid|     DEPT |    1 |     20 | 1 (0) | 00:00:01 |

Plan_table_output

--------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------

----------------------------------------------------------------------------------------

predicate information (identified by Operation ID):

---------------------------------------------------

3-filter ("E". " ename "= ' CLARK ')

4-access ("E". " DEPTNO "=" D "." DEPTNO ")

Rows selected.

Sql> select * FROM table (Dbms_xplan.display (Null,null, ', ' id=1 ')); --Added filter conditions

Plan_table_output

--------------------------------------------------------------------------------

Plan Hash value:3625962092

------------------------------

| Id | Operation | Name |

------------------------------

|  1 |      NESTED LOOPS | |

------------------------------

Note

-----

-rule based optimizer used (consider using CBO)

One by one rows selected.

Summarize

1. The display function is only for the estimated execution plan, not the actual execution plan

2. The display function shows the estimated execution plan and the display format is flexible and can be presented in different output formats.

3. Displays the execution plan for the last statement in the execution plan (default = plan_table) when all parameters are null

4, although the SQL statement can be queried plan_table to obtain the execution plan, it is recommended to use the display function directly, which is enough to explain all the problems

5. When a binding variable is used in an SQL statement, the execution plan obtained by explain plan is unreliable