How to view the execution plan method of SQL in oracle 03/01 Update SLTechnology News&Howtos

How to view the execution plan method of SQL in oracle

2025-03-01 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article mainly introduces how to view the implementation plan of SQL in oracle, which has a certain reference value, interested friends can refer to, I hope you can learn a lot after reading this article, the following let the editor take you to understand it.

Oracle looks at how SQL executes the plan

Getting the oracle sql execution plan and viewing the execution plan is the basic skill to master and judge the performance of the database. The following example describes several ways to view the sql execution plan:

There are basically the following ways:

1. Initialize parameters through sql_trace

2. Through Autotrace

3. Through explain plan

4. Through dbms_xplan.display_cursor

5. Through dbms_xplan.display_awr

6. Through the 10046 incident

1. Through the explain plan tool

12:24:00 SCOTT@ prod > explain plan for

12:24:06 2 select empno,ename,sal,deptno from emp where empno=7788

Explained.

Elapsed: 00:00:00.22

12:24:16 SCOTT@ prod > select * from table (dbms_xplan.display)

PLAN_TABLE_OUTPUT

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | | 1 | 46 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 46 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7788)

14 rows selected.

Elapsed: 00:00:01.14

2. Check through DBMS_XPLAN.display_cursor

12:52:37 SCOTT@ prod > desc dbms_xplan

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

If scott user access requires authorization:

12:31:44 SYS@ prod > select * from dict where upper (table_name) = 'video session'

TABLE_NAME COMMENTS

V$SESSION Synonym for V_$SESSION

Elapsed: 00:00:00.09

12:31:09 SYS@ prod > grant select on V_$SESSION to scott

Grant succeeded.

Elapsed: 00:00:00.10

12:43:15 SCOTT@ prod > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

User has no SELECT privilege on V$SQL_PLAN

Resolve insufficient permissions:

12:42:33 SYS@ prod > grant select any table to scott

Grant succeeded.

12:43:46 SYS@ prod > show parameter o7

NAME TYPE VALUE

O7_DICTIONARY_ACCESSIBILITY boolean TRUE

12:44:54 SYS@ prod >

Case: dbms_xplan.display_cursor

12:42:45 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7788

EMPNO ENAME SAL DEPTNO

7788 SCOTT 3000 20

Elapsed: 00:00:00.08

12:43:15 SCOTT@ prod > select * from table (dbms_xplan.display_cursor (null,null,'all'))

PLAN_TABLE_OUTPUT

SQL_ID bqz9ujgnn4jzu, child number 0

Select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | 2 (100) | |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 46 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

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

PLAN_TABLE_OUTPUT

1-SEL$1 / EMP@SEL$1

2-SEL$1 / EMP@SEL$1

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7788)

Column Projection Information (identified by operation id):

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

"DEPTNO" [NUMBER,22]

2-"EMP" .ROWID [ROWID,10], "EMPNO" [NUMBER,22]

32 rows selected.

Elapsed: 00:00:00.05

Case study:

12:49:10 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7788

EMPNO ENAME SAL DEPTNO

7788 SCOTT 3000 20

Elapsed: 00:00:00.00

12:50:06 SCOTT@ prod > select * from table (dbms_xplan.display_cursor (null,null,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID bqz9ujgnn4jzu, child number 0

Select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | 2 (100) | |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 46 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

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

PLAN_TABLE_OUTPUT

1-SEL$1 / EMP@SEL$1

2-SEL$1 / EMP@SEL$1

Outline Data

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.1')

DB_VERSION ('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX_RS_ASC (@ "SEL$1"EMP" @ "SEL$1" ("EMP". "EMPNO"))

END_OUTLINE_DATA

, /

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7788)

Column Projection Information (identified by operation id):

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

"DEPTNO" [NUMBER,22]

2-"EMP" .ROWID [ROWID,10], "EMPNO" [NUMBER,22]

46 rows selected.

Elapsed: 00:00:00.06

12:50:21 SCOTT@ prod >

This method looks at the execution plan of the SQLSQLSQL that has just been executed in SQLPLUS.

-- the first two parameter values passed in by dbms_xplan.display_cursor are both null,null, and the third parameter is "advanced". The third parameter can also be the display result obtained by "all", missing the content of "Outline data".

Sql > select sql_text,sql_id,hash_value,child_number from v$sql

2 * where sql_text like 'select empno,ename,sal%'

SQL_TEXT SQL_ID HASH_VALUE CHILD_NUMBER

Select empno,ename,sal,deptno from emp where empno bqz9ujgnn4jzu 3913435130 0

= 7788

Elapsed: 00:00:00.04

13:00:25 SCOTT@ prod > select * from table (dbms_xplan.display_cursor ('bqz9ujgnn4jzu',0,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID bqz9ujgnn4jzu, child number 0

Select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | 2 (100) | |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 46 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

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

PLAN_TABLE_OUTPUT

1-SEL$1 / EMP@SEL$1

2-SEL$1 / EMP@SEL$1

Outline Data

/ * +

BEGIN_OUTLINE_DATA

IGNORE_OPTIM_EMBEDDED_HINTS

OPTIMIZER_FEATURES_ENABLE ('11.2.0.1')

DB_VERSION ('11.2.0.1')

ALL_ROWS

OUTLINE_LEAF (@ "SEL$1")

INDEX_RS_ASC (@ "SEL$1"EMP" @ "SEL$1" ("EMP". "EMPNO"))

END_OUTLINE_DATA

, /

PLAN_TABLE_OUTPUT

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7788)

Column Projection Information (identified by operation id):

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

"DEPTNO" [NUMBER,22]

2-"EMP" .ROWID [ROWID,10], "EMPNO" [NUMBER,22]

46 rows selected.

Elapsed: 00:00:00.14

3. Through DBMS_XPLAN.display_awr

The prerequisite for obtaining the sql execution plan using the method dbms_xplan.display_cursor is that the SQL is still in the shared pool, and if the prerequisite for the execution plan is that the SQL is still in the shared pool, and if the prerequisite for the execution plan is that the SQL is still in the shared pool, and if the execution plan of the SQLSQLSQL has been flushed out of the shared pool, then as long as the execution plan of the SQL is collected into the AWR Repository by ORACLE, you can use this method to view it.

12:24:00 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7788

13:10:56 SYS@ prod > exec dbms_workload_repository.create_snapshot

PL/SQL procedure successfully completed.

13:11:37 SYS@ prod > alter system flush shared_pool

System altered.

Elapsed: 00:00:00.31

13:16:28 SYS@ prod > select * from table (dbms_xplan.display_cursor ('bqz9ujgnn4jzu',0,'advanced'))

PLAN_TABLE_OUTPUT

SQL_ID: bqz9ujgnn4jzu, child number: 0 cannot be found

13:21:53 SYS@ prod > desc dbms_xplan

FUNCTION DISPLAY_AWR RETURNS DBMS_XPLAN_TYPE_TABLE

Argument Name Type In/Out Default?

SQL_ID VARCHAR2 IN

PLAN_HASH_VALUE NUMBER (38) IN DEFAULT

DB_ID NUMBER (38) IN DEFAULT

FORMAT VARCHAR2 IN DEFAULT

13:30:15 SCOTT@ prod > select * from table (dbms_xplan.display_awr ('bqz9ujgnn4jzu'))

PLAN_TABLE_OUTPUT

SQL_ID bqz9ujgnn4jzu

Select empno,ename,sal,deptno from emp where empno=7788

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | 2 (100) | |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 46 | 2 (0) | 00:00:01 |

| | 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

14 rows selected.

Elapsed: 00:00:00.30

4. View through the 10046 event

1) View the current session:

13:29:52 SYS@ prod > grant alter session to scott

Grant succeeded.

13:44:31 SCOTT@ prod > alter session set events' 10046 trace name context forever,level 12'

Session altered.

13:44:53 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7788

EMPNO ENAME SAL DEPTNO

7788 SCOTT 3000 20

Elapsed: 00:00:00.10

13:45:51 SCOTT@ prod > alter session set events' 10046 trace name context off'

Session altered.

Elapsed: 00:00:00.03

[oracle@rh7 ~] $ls-lt / u01/app/oracle/diag/rdbms/prod/prod/trace/ | more

Total 1256

-rw-r- 1 oracle oinstall 27801 May 16 13:46 prod_ora_4995.trc

-rw-r- 1 oracle oinstall 177May 16 13:46 prod_ora_4995.trm

-rw-r- 1 oracle oinstall 1122 May 16 13:34 prod_j000_5188.trc

-rw-r- 1 oracle oinstall 59 May 16 13:34 prod_j000_5188.trm

[oracle@rh7 ~] $tkprof / u01/app/oracle/diag/rdbms/prod/prod/trace/prod_ora_4995.trc / home/oracle/emp_0416.txt sys=no

TKPROF: Release 11.2.0.1.0-Development on Fri May 16 13:47:41 2014

[oracle@rh7 ~] $cat emp_0416.txt

SQL ID: bqz9ujgnn4jzu

Plan Hash: 2949544139

Select empno,ename,sal,deptno

From

Emp where empno=7788

Call count cpu elapsed disk query current rows

Parse 1 0.07 0.09 1 66 0 0

Execute 1 0.00 0.00 00 00

Fetch 2 0.00 0.00 0 2 0 1

Total 4 0.07 0.09 1 68 0 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Rows Row Source Operation

1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=46 card=1)

1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1) (object id 73202)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

Db file sequential read 1 0.00 0.00

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 2 19.13 19.13

2) View other session:

14:12:23 SYS@ prod > select sid,serial#,username from v$session where username is not null

SID SERIAL# USERNAME

1 5 SYS

42 9 SCOTT

6 rows selected.

Elapsed: 00:00:00.10

14:12:47 SYS@ prod > desc dbms_monitor

PROCEDURE SESSION_TRACE_DISABLE

Argument Name Type In/Out Default?

SESSION_ID BINARY_INTEGER IN DEFAULT

SERIAL_NUM BINARY_INTEGER IN DEFAULT

PROCEDURE SESSION_TRACE_ENABLE

Argument Name Type In/Out Default?

SESSION_ID BINARY_INTEGER IN DEFAULT

SERIAL_NUM BINARY_INTEGER IN DEFAULT

WAITS BOOLEAN IN DEFAULT

BINDS BOOLEAN IN DEFAULT

PLAN_STAT VARCHAR2 IN DEFAULT

14:13:11 SCOTT@ prod > select sid from v$mystat where rownum=1

SID

forty-two

14:13:41 SYS@ prod > exec dbms_monitor.SESSION_TRACE_ENABLE (42pcm9) waits = > true,binds= > true)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.10

14:13:25 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7369

EMPNO ENAME SAL DEPTNO

7369 SMITH 800 20

Elapsed: 00:00:00.03

14:14:29 SYS@ prod > exec dbms_monitor.SESSION_TRACE_disable (42 and 9)

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.02

[oracle@rh7 ~] $cat emp_0416.txt

SQL ID: fyydvbdw2uq6q

Plan Hash: 2949544139

Select empno,ename,sal,deptno

From

Emp where empno=7369

Call count cpu elapsed disk query current rows

Parse 1 0.00 0.00 00 00

Execute 1 0.00 0.00 00 00

Fetch 2 0.00 0.00 0 2 0 1

Total 4 0.00 0.00 0 2 0 1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 84

Rows Row Source Operation

1 TABLE ACCESS BY INDEX ROWID EMP (cr=2 pr=0 pw=0 time=0 us cost=2 size=46 card=1)

1 INDEX UNIQUE SCAN PK_EMP (cr=1 pr=0 pw=0 time=0 us cost=1 size=0 card=1) (object id 73202)

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

SQL*Net message to client 2 0.00 0.00

SQL*Net message from client 1 0.00 0.00

5. Check through autotrace

SET AUTOTRACE ON

SET AUTOTRACE TRACEONLY

SET AUTOTRACE TRACEONLY EXPLAIN

When using SET AUTOTRACE ON and SET AUTOTRACE TRACEONLY, the target SQL has already been executed, so the target SQL can be seen in the case of SET AUTOTRACE ON and SET AUTOTRACE TRACEONLY

The actual consumption.

When using SET AUTOTRACE TRACEONLY EXPLAIN, if the SELECT statement is executed, the SQL is not executed, but if the DML statement is executed, the situation is different. In this case, the DML statement will

Executed by ORACLE.

-- it is important to note that although the execution plan obtained by using the SET AUTOTRACE command may be inaccurate, the execution plan displayed by the SET AUTOTRACE command is derived from the explain plan command.

Case study:

15:32:11 SYS@ prod > conn scott/tiger

Connected.

15:32:17 SCOTT@ prod > set autotrace on

15:32:21 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7788

EMPNO ENAME SAL DEPTNO

7788 SCOTT 3000 20

Elapsed: 00:00:00.03

Execution Plan

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | | 1 | 46 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 46 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7788)

Statistics

168 recursive calls

0 db block gets

38 consistent gets

0 physical reads

0 redo size

736 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

5 sorts (memory)

0 sorts (disk)

1 rows processed

15:32:26 SCOTT@ prod > set autotrace trace

15:32:49 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7788

Elapsed: 00:00:00.02

Execution Plan

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | | 1 | 46 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 46 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7788)

Statistics

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

736 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

15:34:59 SCOTT@ prod > set autotrace on exp

15:35:04 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7788

EMPNO ENAME SAL DEPTNO

7788 SCOTT 3000 20

Elapsed: 00:00:00.02

Execution Plan

Plan hash value: 2949544139

| | 0 | SELECT STATEMENT | | 1 | 46 | 2 (0) | 00:00:01 |

| | 1 | TABLE ACCESS BY INDEX ROWID | EMP | 1 | 46 | 2 (0) | 00:00:01 |

| | * 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 1 (0) | 00:00:01 |

Predicate Information (identified by operation id):

2-access ("EMPNO" = 7788)

15:35:12 SCOTT@ prod > set autotrace on statis

15:35:20 SCOTT@ prod > select empno,ename,sal,deptno from emp where empno=7788

EMPNO ENAME SAL DEPTNO

7788 SCOTT 3000 20

Elapsed: 00:00:00.03

Statistics

0 recursive calls

0 db block gets

2 consistent gets

0 physical reads

0 redo size

736 bytes sent via SQL*Net to client

523 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

0 sorts (memory)

0 sorts (disk)

1 rows processed

15:35:26 SCOTT@ prod >

Thank you for reading this article carefully. I hope the article "how to View the implementation Plan of SQL in oracle" shared by the editor will be helpful to you. At the same time, I also hope that you will support and follow the industry information channel. More related knowledge is waiting for you to learn!

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.

*The comments in the above article only represent the author's personal views and do not represent the views and positions of this website. If you have more insights, please feel free to contribute and share.