Shulou(Shulou.com)06/01 Report--

See how to execute the plan

Explain Plan For SQL

If the SQL statement is not actually executed, the generated plan may not be the real execution plan.

There must be a plan_table.

It's a trick, but not the best SQLPLUS AUTOTRACE.

SQL is actually implemented except for set autotrace traceonly explain, but still not necessarily

It's a real plan.

There must be a plan_tableSQL TRACE.

Need to enable SQL_TRACE for 10046 commandments

Generally use tkprof to see more clearly, of course, 10046 also has its own implementation plan information V$SQL and V$SQL_PLAN

You can query the plan information for multiple sub-cursors, but it looks like it's hard to Enterprise Manager.

Execution plans can be graphically displayed, but not all environments have other third-party tools available for EM

Note that the execution plan seen by tools such as PL/SQL developer F5 may not be real

D:\ instantclient_12_2 > sqlplus scott/tiger@clonepdb_plug

SQL*Plus: Release 12.2.0.1.0 Production on Fri Jan 12 13:54:31 2018

Copyright (c) 1982, 2016, Oracle. All rights reserved.

Last Successful login time: Fri Jan 12 2018 11:18:59 + 08:00

Connected to:

Oracle Database 12c Enterprise Edition Release 12.2.0.1.0-64bit Production

SCOTT@clonepdb_plug > set linesize 180

SCOTT@clonepdb_plug > set pagesize 0

SCOTT@clonepdb_plug > alter session set STATISTICS_LEVEL = ALL

Session altered.

SCOTT@clonepdb_plug > select * from emp where ename='SMITH'

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20

SCOTT@clonepdb_plug > select * from table (dbms_xplan.display_cursor (null,null,'allstats'))

SQL_ID cgf95c3k5mszx, child number 0

Select * from emp where ename='SMITH'

Plan hash value: 3956160932

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | 0 | SELECT STATEMENT | | 1 | 1 | 00001 | 8 |

| | * 1 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 | 00Predicate Information 00.01 | 8 | Predicate Information (identified by operation id):

1-filter ("ENAME" = 'SMITH')

18 rows selected.

SCOTT@clonepdb_plug >

Authorization required

Grant select on v$session to scott

Grant select on v$sql to scott

Grant select on v_$sqlplan to scott

Grant select on v$sql_plan_statistics_all to scott

Basic fields (always available)

The identifier of each action (line) in the Id execution plan. If the number is preceded by an asterisk, it means that the predicate information contained in this line will be provided later.

Operation corresponds to the action performed. Also known as row source operation

Object name of the Name operation

two。 Query optimizer evaluation information

Number of records returned by Rows (E-Rows) estimation operation

The number of record bytes returned by the Bytes (E-Bytes) estimate operation

The TempSpc estimate operation uses the size of the temporary tablespace

Cost (% CPU) estimates the cost of the operation. The percentage of CPU overhead is listed in parentheses. Note that these values are calculated through the execution plan. In other words, the cost of the parent operation includes the cost of the child operation

Time estimates how long it will take to perform the operation (HH:MM:SS)

3. Partition (the following fields are visible only when accessing the partition table)

The first partition accessed by Pstart. If you don't know which partition to parse, set it to KEY,KEY (I), KEY (MC), KEY (OR), KEY (SQ).

The last partition accessed by Pstop. If you don't know which partition to parse, set it to KEY,KEY (I), KEY (MC), KEY (OR), KEY (SQ).

4. Parallel and distributed processing (the following fields are visible only when using parallel or distributed operations)

In a distributed operation, Inst refers to the name of the database link used by the operation

TQ A table queue used for communication between slave threads in parallel operations.

Relationships between IN-OUT parallel or distributed operations

PQ Distrib in parallel operations, the allocation made by producers to send data to consumers.

5. Runtime statistics (the following fields are visible when setting the parameter statistics_level to all or using the gather_plan_statistics prompt)

Starts specifies the number of times the operation is performed

The number of real records returned by the A-Rows operation

Real time that the A-Time operation was executed (HH:MM:SS.FF)

6.I/O statistics (the following fields are visible when setting the parameter statistics_level to all or using the gather_plan_statistics prompt)

Number of logical reads performed during Buffers execution

Number of physical reads performed during Reads execution

Number of physical writes performed during Writes execution

7. Memory usage statistics

Estimation of memory required for optimal execution of OMem

1Mem performs a pre-estimate of the required memory through (one-pass)

The number of times a multipass mode operation is performed at one pass / multiple passes.

The amount of memory used by the operation when Used-Mem was last executed

The amount of temporary space used by the operation when Used-Tmp was last executed. This field must be 1024 times larger to be consistent with other memory measurement fields (for example, 32k means 32MB)

The maximum temporary space size used by the Max-Tmp operation. This field must be 1024 times larger to be consistent with other memory measurement fields (for example, 32k means 32MB)

Example

SCOTT@clonepdb_plug > explain plan for

2 select * from emp eForce dept d

3 where e.deptno=d.deptno

4 and e.enameplate Smit

Explained.

SCOTT@clonepdb_plug > select from table (dbms_xplan.display (null,null,'advanced'))

Plan hash value: 3625962092-refers to the hash value converted from the execution plan, which has nothing to do with the execution efficiency, and is the only indication of the execution plan.

/ execution plan section * / | Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time | | 0 | SELECT STATEMENT | | 1 | 59 | 4 (0) | 00:00:01 |

| | 1 | NESTED LOOPS | | 1 | 59 | 4 (0) | 00:00:01 |

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

| | 3 | TABLE ACCESS FULL | EMP | 1 | 39 | 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 | Query Block Name / Object Alias (identified by operation id):-- this part shows the query block name and object alias |

3-SEL$1 / E@SEL$1-E@SEL$1, corresponding to the operation ID 3 in the execution plan, that is, the query on table E, E is an alias, the following class is the same as SEL$, which is the abbreviation of select, located in block 1, and corresponding to DEL$,INS$,UPD$, etc.

4-SEL$1 / D@SEL$1

5-SEL$1 / D@SEL$1

Outline Data-outline section, which graphically presents the execution plan in text form, that is, converts it to a prompt

/ +

BEGIN_OUTLINE_DATA

NLJ_BATCHING (@ "SEL$1"D" @ "SEL$1")-- instructs the optimizer to access the table in a nested loop association pre-extraction

USE_NL (@ "SEL$1"D" @ "SEL$1")-- use the USE_NL hint, that is, a nested loop

LEADING (@ "SEL$1"E" @ "SEL$1"D" @ "SEL$1")-- indicates the lead table

INDEX (@ "SEL$1"D" @ "SEL$1" ("DEPT". "DEPTNO"))-indicates that the access method on D is to use the index

FULL (@ "SEL$1"E" @ "SEL$1")-indicates that the access method on E is full table scan OUTLINE_LEAF (@ "SEL$1") br/ > OUTLINE_LEAF (@ "SEL$1")

DB_VERSION ('12.2.0.1')

OPTIMIZER_FEATURES_ENABLE ('12.2.0.1')

IGNORE_OPTIM_EMBEDDED_HINTS

END_OUTLINE_DATA

/

Predicate Information (identified by operation id):-- predicate information section where each line of ID with an asterisk corresponds to the following line in the execution plan

3-filter ("E". "ENAME" = 'SMITH')

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

Column Projection Information (identified by operation id):-- the columns returned by each step when executed, and different columns are returned by the following steps

1-(# keys=0) "E". "EMPNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10]

"E". "JOB" [VARCHAR2,9], "E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7]

"E". "SAL" [NUMBER,22], "E". "COMM" [NUMBER,22], "E". "DEPTNO" [NUMBER,22]

"D". "DEPTNO" [NUMBER,22], "D". "DNAME" [VARCHAR2,14], "D". "LOC" [VARCHAR2,13]

2-(# keys=0) "E". "EMPNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10]

"E". "JOB" [VARCHAR2,9], "E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7]

"E". "SAL" [NUMBER,22], "E". "COMM" [NUMBER,22], "E". "DEPTNO" [NUMBER,22]

"D" .ROWID [ROWID,10], "D". "DEPTNO" [NUMBER,22]

3-"E". "EMPNO" [NUMBER,22], "E". "ENAME" [VARCHAR2,10], "E". "JOB" [VARCHAR2,9]

"E". "MGR" [NUMBER,22], "E". "HIREDATE" [DATE,7], "E". "SAL" [NUMBER,22]

"E". "COMM" [NUMBER,22], "E". "DEPTNO" [NUMBER,22]

4-"D" .ROWID [ROWID,10], "D". "DEPTNO" [NUMBER,22]

5-"D". "DNAME" [VARCHAR2,14], "D". "LOC" [VARCHAR2,13]

In the Note-- comment and description section, the following description shows that this SQL statement uses the dynamic sampling function this is an adaptive plan

64 rows selected.

Example

SCOTT@clonepdb_plug > select / + gather_plan_statistics / * from emp eForce dept d

2 where e.deptno=d.deptno

3 and e.enameplate

Notice that the prompt gather_plan_statistics is added here and that the statement is executed equivalent to alter session set STATISTICS_LEVEL = ALL

7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH

DALLAS

SCOTT@clonepdb_plug > select * from table (dbms_xplan.display_cursor (null,null,'iostats last'));-- use display_cursor to get the actual execution plan

SQL_ID 0wb0wwb3621n2, child number 0-- this section gives the SQL_ID of the SQL statement, the subcursor, and the original SQL statement

Select / + gather_plan_statistics / * from emp eJane dept d

Where e.deptno=d.deptno and e.enameplate

Plan hash value: 3625962092

| | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | | 0 | SELECT STATEMENT | | 1 | 1 | 00001 | 9 |

| | 1 | NESTED LOOPS | | 1 | 1 | 1 | 00001 | 00.01 | 9 |

| | 2 | NESTED LOOPS | | 1 | 1 | 1 | 00001 | 00.01 | 8 |

| | 3 | TABLE ACCESS FULL | EMP | 1 | 1 | 1 | 00lv 00.01 | 7 |

| | 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 1 | 1 | 00lv 00.01 | 1 |

| 5 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 1 | 1 | 00.01 | Predicate Information (identified by operation id):

3-filter ("E". "ENAME" = 'SMITH')

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

Note

This is an adaptive plan

28 rows selected.

SCOTT@clonepdb_plug > select * from table (dbms_xplan.display_cursor ('6h2msdb5hj7s))

SQL_ID 6h2msdzb5hj7s, child number 0

Select / + gather_plan_statistics / * from emp eJane dept d

Where e.deptno=d.deptno and rownumIGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('12.2.0.1')

DB_VERSION ('12.2.0.1')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

LEADING (@ "SEL$1"E" @ "SEL$1"D" @ "SEL$1")

END_OUTLINE_DATA

/

Predicate Information (identified by operation id):

1-filter (ROWNUM

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.