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

Using the method in the DBMS_XPLAN package is another way to get the execution plan of the target SQL in the oracle database. For different application scenarios, you can choose one of the following four methods:

Select * from table (dbms_xplan.display)

Select * from table (dbms_xplan.display_cursor (null,null,'advenced'))

Select * from table (dbms_xplan.display_cursor ('sql_id/hash_value',child_cursor_number,'advanced'))

Select * from table (dbms_xplan.display_awr ('sql_id'))

Method 1 is to execute select * from table (dbms_xplan.display), which needs to be used with the explain plan command, which is used to view the execution plan obtained after using the explain plan command.

Method 2 is to execute select * from table (dbms_xplan.display_cursor (null,null,'advenced')) It is used to view the implementation plan of the sql that has just been executed, here the values of the first and second parameters passed in for dbms_xplan.display_cursor are null, the value of the third parameter is "advanced", and the value of the third input parameter can also be "all", but the display result after using "advanced" will be more detailed than the display result with "all".

SQL > conn scott/scott

Connected.

SQL > set linesize 800

SQL > set pagesize 900

SQL > col plan_table_output for A200

SQL > select empno,ename,dname from emp,dept where emp.deptno=dept.deptno

EMPNO ENAME DNAME

7782 CLARK ACCOUNTING

7839 KING ACCOUNTING14 rows selected.

SQL > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

-

SQL_ID 7ww0fhpbqqt0n, child number 0

-

Select empno,ename,dname from emp,dept where emp.deptno=dept.deptno

Plan hash value: 844388907

-

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

-

| | 0 | SELECT STATEMENT | 6 (100) | |

| | 1 | MERGE JOIN | | 14 | 364 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 182 | 4 (25) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 |

-

Query Block Name / Object Alias (identified by operation id):

1-SEL$1

2-SEL$1 / DEPT@SEL$1

3-SEL$1 / DEPT@SEL$1

5-SEL$1 / EMP@SEL$1

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')

DB_VERSION ('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX (@ "SEL$1"DEPT" @ "SEL$1" ("DEPT". "DEPTNO"))

FULL (@ "SEL$1"EMP" @ "SEL$1")

LEADING (@ "SEL$1"DEPT" @ "SEL$1"EMP" @ "SEL$1")

USE_MERGE (@ "SEL$1"EMP" @ "SEL$1")

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

4-access ("EMP". "DEPTNO" = "DEPT". "DEPTNO")

Filter ("EMP". "DEPTNO" = "DEPT". "DEPTNO")

Column Projection Information (identified by operation id):

1-"DNAME" [VARCHAR2,14], "EMPNO" [NUMBER,22], "ENAME" [VARCHAR2,10]

2-"DEPT". "DEPTNO" [NUMBER,22], "DNAME" [VARCHAR2,14]

3-"DEPT" .ROWID [ROWID,10], "DEPT". "DEPTNO" [NUMBER,22]

4-(# keys=1) "EMP". "DEPTNO" [NUMBER,22], "EMPNO" [NUMBER,22]

"ENAME" [VARCHAR2,10]

5-"EMPNO" [NUMBER,22], "ENAME" [VARCHAR2,10], "EMP". "DEPTNO" [NUMBER,22]

58 rows selected.

SQL > select * from table (dbms_xplan.display_cursor (null,null,'all'))

PLAN_TABLE_OUTPUT

-

SQL_ID 7ww0fhpbqqt0n, child number 0

-

Select empno,ename,dname from emp,dept where emp.deptno=dept.deptno

Plan hash value: 844388907

-

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

-

| | 0 | SELECT STATEMENT | 6 (100) | |

| | 1 | MERGE JOIN | | 14 | 364 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 182 | 4 (25) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 |

-

Query Block Name / Object Alias (identified by operation id):

1-SEL$1

2-SEL$1 / DEPT@SEL$1

3-SEL$1 / DEPT@SEL$1

5-SEL$1 / EMP@SEL$1

Predicate Information (identified by operation id):

4-access ("EMP". "DEPTNO" = "DEPT". "DEPTNO")

Filter ("EMP". "DEPTNO" = "DEPT". "DEPTNO")

Column Projection Information (identified by operation id):

1-"DNAME" [VARCHAR2,14], "EMPNO" [NUMBER,22], "ENAME" [VARCHAR2,10]

2-"DEPT". "DEPTNO" [NUMBER,22], "DNAME" [VARCHAR2,14]

3-"DEPT" .ROWID [ROWID,10], "DEPT". "DEPTNO" [NUMBER,22]

4-(# keys=1) "EMP". "DEPTNO" [NUMBER,22], "EMPNO" [NUMBER,22]

"ENAME" [VARCHAR2,10]

5-"EMPNO" [NUMBER,22], "ENAME" [VARCHAR2,10], "EMP". "DEPTNO" [NUMBER,22]

41 rows selected.

When the parameter is passed into all, the contents of the "Qutline Data" section of the result are displayed:

Method 3 is to execute select * from table (dbms_xplan.display_cursor ('sql_id/hash_value',child_cursor_number,'advanced')); it is used to view the execution plan of the specified sql. Here, the value of the first parameter passed to the method dbms_xplan.display_cursor is the sql_id or sql hash value of the specified sql, the second parameter depends on the child cursor number where the execution plan is located, and the third parameter is advcanced or all

SQL > select sql_text,sql_id,hash_value,child_number from v$sql where sql_text like 'select empno,ename,dname%'

SQL_TEXT

Select empno,ename,dname from emp,dept where emp.deptno=dept.deptno

SQL_ID HASH_VALUE CHILD_NUMBER

7ww0fhpbqqt0n 1466655764 0

As long as the child cursor in which the execution plan of the target sql is located has not been page out out of the shared pool, you can use method 3 to view the execution plan of the sql:

SQL > select * from table (dbms_xplan.display_cursor ('2qm0f3qgsqqyc)

PLAN_TABLE_OUTPUT

-

SQL_ID 2qm0f3qgsqqyc, child number 0

-

Select empno,ename,dname from scott.emp,scott.dept where

Scott.emp.deptno=scott.dept.deptno

Plan hash value: 844388907

-

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

-

| | 0 | SELECT STATEMENT | 6 (100) | |

| | 1 | MERGE JOIN | | 14 | 364 | 6 (17) | 00:00:01 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 4 | 52 | 2 (0) | 00:00:01 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 4 | | 1 (0) | 00:00:01 |

| | * 4 | SORT JOIN | | 14 | 182 | 4 (25) | 00:00:01 |

| | 5 | TABLE ACCESS FULL | EMP | 14 | 182 | 3 (0) | 00:00:01 |

-

Query Block Name / Object Alias (identified by operation id):

1-SEL$1

2-SEL$1 / DEPT@SEL$1

3-SEL$1 / DEPT@SEL$1

5-SEL$1 / EMP@SEL$1

Outline Data

-

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.4')

DB_VERSION ('11.2.0.4')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX (@ "SEL$1"DEPT" @ "SEL$1" ("DEPT". "DEPTNO"))

FULL (@ "SEL$1"EMP" @ "SEL$1")

LEADING (@ "SEL$1"DEPT" @ "SEL$1"EMP" @ "SEL$1")

USE_MERGE (@ "SEL$1"EMP" @ "SEL$1")

END_OUTLINE_DATA

, /

Predicate Information (identified by operation id):

4-access ("EMP". "DEPTNO" = "DEPT". "DEPTNO")

Filter ("EMP". "DEPTNO" = "DEPT". "DEPTNO")

Column Projection Information (identified by operation id):

1-"DNAME" [VARCHAR2,14], "EMPNO" [NUMBER,22], "ENAME" [VARCHAR2,10]

2-"DEPT". "DEPTNO" [NUMBER,22], "DNAME" [VARCHAR2,14]

3-"DEPT" .ROWID [ROWID,10], "DEPT". "DEPTNO" [NUMBER,22]

4-(# keys=1) "EMP". "DEPTNO" [NUMBER,22], "EMPNO" [NUMBER,22]

"ENAME" [VARCHAR2,10]

5-"EMPNO" [NUMBER,22], "ENAME" [VARCHAR2,10], "EMP". "DEPTNO" [NUMBER,22]

59 rows selected.

We can get all the historical execution plans of the sql through dbms_xplan.display_awr:

SQL > set lines 100

SQL > select * from table (dbms_xplan.display_awr ('cy097a90nu4fk'))

PLAN_TABLE_OUTPUT

SQL_ID cy097a90nu4fk

-

Plan hash value: 1999179007

-

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

Inst | IN-OUT |

-

| | 0 | SELECT STATEMENT | 11 (100) | | |

| | |

| | 1 | HASH GROUP BY | | 1 | 101 | 11 (19) | 00:00:01 |

| | |

| | 2 | VIEW | | 1 | 101 | 10 (10) | 00:00:01 |

| | |

| | 3 | HASH GROUP BY | | 1 | 261 | 10 (10) | 00:00:01 |

| | |

| | 4 | FILTER | | |

| | |

| | 5 | NESTED LOOPS | | |

| | |

| | 6 | NESTED LOOPS | | 1 | 261 | 9 (0) | 00:00:01 |

| | |

| | 7 | REMOTE | | 1 | 176 | 5 (0) | 00:00:01 |

DBLK_~ | R-> S |

| | 8 | INDEX RANGE SCAN | PRODUCTSPEC_PK | 1 | | 1 (0) | 00:00:01 |

| | |

| | 9 | TABLE ACCESS BY INDEX ROWID | PRODUCTSPEC | 1 | 41 | 2 (0) | 00:00:01 |

| | |

-

Remote SQL Information (identified by operation id):

7-SELECT "A1". "EVENTTIMEKEY", "A1". "EVENTID", "A1". "EVENTTIME", "A1". "FOLLOTID", "A1". PRODUCTSPECI

D "," A1 "

. "PROCESSFLOWID", "A1". "OLDOPERATIONID", "A1". "QUANTITY", "A1". "EQUIPMENTID", "A2". PROCESSFLOWID

"A2". "REWORK

TYPE "FROM" LOTHISTORY "A1", "PROCESSFLOW"A2" WHERE "A2". "REWORKTYPE" = 'Normal' AND

"A1". "PROCESSFLOWID" = "A2". "PROCESSFLOWID" AND "A1". "EVENTID" = 'TrackIn' AND "A1". "OLDOPERATION

ID "= 'BFOP001'

AND (SUBSTR ("A1". "FOLLOTID", 1p1) ='V'OR SUBSTR ("A1". "FOLLOTID", 1p1) ='P') AND

"A1". "EVENTTIME" > = TO_DATE ('20140918080001) AND

"A1". "EVENTTIME"

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.