How to get SQL execution Plan in Oracle 04/01 Update SLTechnology News&Howtos

How to get SQL execution Plan in Oracle

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

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

This article will explain in detail how to obtain the SQL implementation plan in Oracle. The content of the article is of high quality, so the editor shares it for you as a reference. I hope you will have a certain understanding of the relevant knowledge after reading this article.

Oracle acquires SQL execution plan method

Method 1: DBMS_XPLAN.DISPLAY_CURSOR

Method 2: explain plan for.

Method 3: Tuning SQL*Plus (autotrace)

Method 4: * + gather_plan_statistics *

Method 5: 10046

Method 6: 10053

Method 7: awrsqrpt.sql

Method 8: statistics_level=all

Method 1: DBMS_XPLAN.DISPLAY_CURSOR

(1) obtain from the shared pool

SELECT * FROM TABLE (dbms_xplan.display_cursor ('SQL_ID'))

By default, the table function DISPLAY_CURSOR formats the execution plan for the last SQL statement executed by the session.

DISPLAY_CURSOR Function

This table function displays the explain plan of any cursor loaded in the cursor cache.

In addition to the explain plan, various plan statistics (such as. I got O, memory and timing) can be reported (based on the V$SQL_PLAN_STATISTICS_ALL VIEWS).

Syntax

DBMS_XPLAN.DISPLAY_CURSOR (

Sql_id IN VARCHAR2 DEFAULT NULL

Cursor_child_no IN NUMBER DEFAULT 0

Format IN VARCHAR2 DEFAULT 'TYPICAL')

Example:

SYS@orcl > SELECT / * TOTO * / ename, dname FROM scott.dept d join scott.emp e USING (deptno)

14 rows selected.

SYS@orcl > SELECT sql_id, child_number FROM v$sql WHERE sql_text LIKE'% TOTO%'

-- 9dpnjdgna1k40

SYS@orcl > SELECT * FROM table (DBMS_XPLAN.DISPLAY_CURSOR ('9dpnjdgna1k40))

SQL_ID 9dpnjdgna1k40, child number 0

SELECT / * TOTO * / ename, dname FROM scott.dept d join scott.emp e

USING (deptno)

Plan hash value: 844388907

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

| | 1 | MERGE JOIN | | 14 | 308 | 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 | 126 | 4 (25) | 00:00:01 |

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

Predicate Information (identified by operation id):

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

Filter ("D". "DEPTNO" = "E". "DEPTNO")

24 rows selected.

Or inquire directly:

SELECT t.* FROM v$sql s, table (DBMS_XPLAN.DISPLAY_CURSOR (s.sql_id, s.child_number)) t WHERE sql_text LIKE'% TOTO%'

Advantages:

(1) if you know sql_id, you can get the execution plan, just like explain plan for, you don't need to execute it.

(2) the real execution plan can be obtained.

Disadvantages:

(1) there is no output of runtime-related statistics (for example, logical reads, recursive calls, physical reads, etc.)

(2) it is impossible to judge how much to deal with.

(3) it is impossible to determine how many times the table has been accessed

Applicable:

One SQL corresponds to multiple execution plans

Method 2: explain plan for acquisition

(1) EXPLAIN PLAN FOR XXX

(2) select * from table (DBMS_XPLAN.DISPLAY)

(3) obtain it from the awr performance view

SELECT * FROM TABLE (dbms_xplan.display_awr ('SQL_ID'))

DISPLAY Function

This table function displays the contents of the plan table.

In addition, you can use this table function to display any plan (with or without statistics) stored in a table as long as the columns of this table are named the same as columns of the plan table (or V$SQL_PLAN_STATISTICS_ALL if statistics are included).

You can apply a predicate on the specified table to select rows of the plan to display.

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)

Example:

(1) EXPLAIN PLAN FOR XXX

(2) select * from table (DBMS_XPLAN.DISPLAY)

SYS@orcl >

EXPLAIN PLAN FOR

SELECT *

FROM scott.emp e, scott.dept d

WHERE e.deptno = d.deptno

AND e.ename = 'CLARK'

Explained.

SYS@orcl > SET LINESIZE 130

SYS@orcl > SET PAGESIZE 0

SYS@orcl > SELECT * FROM table (DBMS_XPLAN.DISPLAY)

Plan hash value: 3625962092

| | 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 |

Predicate Information (identified by operation id):

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

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

18 rows selected.

-if a 'PLAN_TABLE' is old version prompt appears under the execution plan, the plan_table needs to be regenerated

SQL > drop table PLAN_TABLE

SQL > @? / rdbms/admin/utlxplan.sql

Advantages:

(1) it is fast and convenient without real execution.

Disadvantages:

(1) the relevant statistics of runtime are not output.

(how many logical reads, how many recursive calls, how many physical reads, etc.)

(2) it is impossible to judge how many lines to process

(3) it is impossible to determine how many times the table has been accessed

Applicable to:

It takes a long time for SQL to produce a result or cannot return a result

Method 3: Tuning SQL*Plus (autotrace)

Tracing Statements

You can automatically get a report on the execution path used by the SQL optimizer and the statement execution statistics. The report is generated after successful SQL DML (that is, SELECT, DELETE, UPDATE and INSERT) statements. It is useful for monitoring and tuning the performance of these statements.

SQL*Plus report output may differ for DML if dynamic sampling is in effect.

Controlling the Autotrace Report

You can control the report by setting the AUTOTRACE system variable.

(1) SET AUTOTRACE OFF:

No AUTOTRACE report is generated. This is the default.

(2) SET AUTOTRACE ON EXPLAIN:

The AUTOTRACE report shows only the optimizer execution path

(3) SET AUTOTRACE ON STATISTICS:

The AUTOTRACE report shows only the SQL statement execution statistics.

(4) SET AUTOTRACE ON:

The AUTOTRACE report includes both the optimizer execution path and the SQL statement execution statistics.

(5) SET AUTOTRACE TRACEONLY:

Like SET AUTOTRACE ON, but suppresses the printing of the user's query output, if any.

If STATISTICS is enabled, query data is still fetched, but not printed.

To use this feature, you must create a PLAN_TABLE table in your schema and then have the PLUSTRACE role granted to you.

DBA privileges are required to grant the PLUSTRACE role.

(1) Creating a PLAN_TABLE

CONNECT HR

@ $ORACLE_HOME/rdbms/admin/utlxplan.sql

(2) Creating the PLUSTRACE Role

CONNECT / AS SYSDBA

@ $ORACLE_HOME/sqlplus/admin/plustrce.sql

(3) Granting the PLUSTRACE Role

CONNECT / AS SYSDBA

GRANT PLUSTRACE TO HR

Example:

SYS@orcl > set autotrace on

SYS@orcl >

SELECT / * TOTO * / ename, dname FROM scott.dept d join scott.emp e USING (deptno)

14 rows selected.

Execution Plan

Plan hash value: 844388907

| | 0 | SELECT STATEMENT | | 14 | 308 | 6 (17) | 00:00:01 |

| | 1 | MERGE JOIN | | 14 | 308 | 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 | 126 | 4 (25) | 00:00:01 |

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

Predicate Information (identified by operation id):

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

Filter ("D". "DEPTNO" = "E". "DEPTNO") Statistics

0 recursive calls

0 db block gets

7 consistent gets

0 physical reads

0 redo size

819 bytes sent via SQL*Net to client

524 bytes received via SQL*Net from client

2 SQL*Net roundtrips to/from client

1 sorts (memory)

0 sorts (disk)

14 rows processed

Error SP2-0618 requires plustrce.sql

SQL > conn scott/tiger

SQL > set autotrace on

SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

SP2-0611: Error enabling STATISTICS report

SQL > @? / sqlplus/admin/plustrce.sql

SQL > grant plustrace to scott

Advantages:

(1) you can output relevant statistics of the runtime (how many logical reads, how many recursive calls, how many physical reads)

(2) traceonly can control whether the returned result is output.

Disadvantages:

(1) the result can not be produced until the statement is really executed.

(2) cannot see how many times the table has been accessed

Method 4: * + gather_plan_statistics *

SYS@orcl > set linesize 130

SYS@orcl > show parameter statistics_level

NAME TYPE VALUE

Statistics_level string TYPICAL

SYS@orcl > SELECT / * + gather_plan_statistics * /

Ename, dname

FROM scott.dept d

Join scott.emp e

USING (deptno)

14 rows selected.

SYS@orcl > select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

PLAN_TABLE_OUTPUT

SQL_ID 4y8f4mhhn471d, child number 0

SELECT / * + gather_plan_statistics * / ename, dname FROM scott.dept d

Join scott.emp e USING (deptno)

Plan hash value: 844388907

| | 0 | SELECT STATEMENT | | 1 | | 14 | 0000 SELECT STATEMENT 00.01 | 7 | PLAN_TABLE_OUTPUT |

| | 1 | MERGE JOIN | | 1 | 14 | 14 | 00001 | 00.01 | 7 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 4 | 4 | 00lv 00.01 | 4 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 | 00lv 00.01 | 2 |

| | * 4 | SORT JOIN | | 4 | 14 | 14 | 00lv 00.01 | 3 | 2048 | 2048 | 2048 (0) |

| | 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 | 00lv 00.01 | 3 |

Predicate Information (identified by operation id):

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

Filter ("D". "DEPTNO" = "E". "DEPTNO")

24 rows selected.

(1) the number of times starts executes for the SQL

(2) the number of rows expected by E-Rows for the execution plan

(3) A-Rows is the actual number of rows returned. By comparing A-Rows with E-Rows, you can determine which step of the execution plan went wrong.

(4) A-Time is the actual execution time of each step (HH:MM:SS.FF). According to this line, you can know where the SQL is spent.

(5) buffers is the logical or consistent read actually performed at each step

(6) Reads is a physical read

Advantages:

(1) it is clear how many times the table has been accessed from STARTS.

(2) the predicted number of rows and the real number of rows can be clearly obtained from E-ROWS and A-ROWS, so that the accuracy of Oracle evaluation can be accurately judged.

(3) BUFFER shows the real number of logical reads

Disadvantages:

(1) the result can not be produced until the statement is really executed.

(2) it is impossible to control the recording without screen output, and it does not have the function of traceonly like autotrace.

(3) can not see the number of recursive calls, can not see the physical read

Applicable:

Get the number of times the table has been accessed

Method 5: 10046

(1)

Set the tracking identifier

Alter session set tracefile_identifier='10046'

Turn on tracking

Alter session set events' 10046 trace name context forever, level 12'

Execute statement

Select count (*) from all_objects

Turn off 10046 tracking

Alter session set events' 10046 trace name context off'

(2)

Oradebug setmypid

Oradebug unlimit

Oradebug event 10046 trace name context forever, level 12

SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno)

Oradebug tracefile_name

Oradebug event 10046 trace name context off

Example 1:

SYS@orcl > alter session set tracefile_identifier='10046'

SYS@orcl > alter session set events' 10046 trace name context forever, level 12'

SYS@orcl > SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno)

SYS@orcl > alter session set events' 10046 trace name context off'

[oracle@uf2010813 trace] $pwd

/ opt/oracle/app/diag/rdbms/orcl/orcl/trace

[oracle@uf2010813 trace] $ll-rth * 10046 *

-rw-r-. 1 oracle oinstall 135 Jul 31 21:55 orcl_ora_29418_10046.trm

-rw-r-. 1 oracle oinstall 3.8K Jul 31 21:55 orcl_ora_29418_10046.trc

[oracle@uf2010813 trace] $tkprof orcl_ora_29418_10046.trc 10046.log sys=no

Example 2:

SYS@orcl > oradebug setmypid

SYS@orcl > oradebug unlimit

SYS@orcl > oradebug event 10046 trace name context forever, level 12

SYS@orcl > SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno)

SYS@orcl > oradebug tracefile_name

/ opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_31388.trc

SYS@orcl > Oradebug event 10046 trace name context off

[oracle@uf2010813 trace] $tkprof orcl_ora_31388.trc 10046.log1 sys=no

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

SQL*Net message to client 4 0.00 0.00

SQL*Net message from client 4 6.17 17.25

Disk file operations I/O 2 0.00 0.00

Db file sequential read 4 0.02 0.04

Elapsed times include waiting on following events:

Event waited on Times Max. Wait Total Waited

-- Waited--

Disk file operations I/O 1 0.00 0.00

Db file sequential read 23 0.01 0.12

Asynch descriptor resize 1 0.00 0.00

1 user SQL statements in session.

66 internal SQL statements in session.

67 SQL statements in session.

Trace file: orcl_ora_31388.trc

Trace file compatibility: 11.1.0.7

Sort options: default

1 session in tracefile.

1 user SQL statements in trace file.

66 internal SQL statements in trace file.

67 SQL statements in trace file.

13 unique SQL statements in trace file.

1390 lines in trace file.

0 elapsed seconds in trace file.

Advantages:

(1) you can see the wait event corresponding to the SQL statement.

(2) if there is a function call in the SQL statement and there is SQL in the function, it will be listed

(3) it is convenient to see the number of rows processed, physical reading and logical reading.

(4) it is convenient to see the parsing time and execution plan.

(5) you can track the entire package

Disadvantages:

(1) tedious steps

(2) it is impossible to determine how many times the table has been accessed

(3) the predicate part of the implementation plan can not be clearly shown.

Applicable: if SQL contains functions, functions include SQL, etc., that is, there are multi-layer calls, and methods can only be used for accurate analysis.

Method 6: 10053

(1)

Alter session set tracefile_identifier='10053'

ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'

Select count (*) from dba_users

ALTER SESSION SET EVENTS '10053 trace name context off'

(2)

Oradebug setmypid

Oradebug unlimit

Oradebug event 10053 trace name context forever, level 1

SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno)

Oradebug tracefile_name

Oradebug event 10053 trace name context off

Example 1:

SYS@orcl > alter session set tracefile_identifier='10053'

SYS@orcl > ALTER SESSION SET EVENTS='10053 trace name context forever, level 1'

SYS@orcl > select count (*) from dba_users

SYS@orcl > ALTER SESSION SET EVENTS '10053 trace name context off'

[oracle@uf2010813 trace] $ls-l * 10053 *

-rw-r-. 1 oracle oinstall 566260 Aug 100: 27 orcl_ora_13125_10053.trc

-rw-r-. 1 oracle oinstall 119517 Aug 100: 27 orcl_ora_13125_10053.trm

Example 2:

SYS@orcl > oradebug setmypid

SYS@orcl > oradebug unlimit

SYS@orcl > oradebug event 10053 trace name context forever, level 1

SYS@orcl > SELECT ename, dname FROM scott.dept d join scott.emp e USING (deptno)

SYS@orcl > oradebug tracefile_name

/ opt/oracle/app/diag/rdbms/orcl/orcl/trace/orcl_ora_11907_10053.trc

SYS@orcl > Oradebug event 10053 trace name context off

Method 7: awrsqrpt.sql

(1) @? / rdbms/admin/awrsqrpt.sql

Type Specified: html

Enter value for num_days: 1

Enter value for begin_snap: 21034

Enter value for end_snap: 21035

Enter value for sql_id: 3ag4uwm6ypra1

Enter value for report_name: awrsqlrpt_0731.html

[oracle@uf2010813 ~] $ls-l awrsqlrpt_0731.html

-rw-r--r--. 1 oracle oinstall 13773 Aug 1 03:03 awrsqlrpt_0731.html

-Exec dbms_workload_repository.create_snapshot

Method 8: statistics_level=all

(1) alter session set statistics_level=all

(2) execute SQL

(3) View the implementation plan

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

Example:

SYS@orcl > show parameter statistics_level

NAME TYPE VALUE

Statistics_level string TYPICAL

SYS@orcl > alter session set statistics_level=all

SYS@orcl > set linesize 130

SYS@orcl > SELECT / * TOTO * / ename, dname FROM scott.dept d join scott.emp e USING (deptno)

SYS@orcl > select * from table (dbms_xplan.display_cursor (null,null,'allstats last'))

PLAN_TABLE_OUTPUT

SQL_ID 72zuupmcd922s, child number 0

SELECT / * TOTO * / ename, dname FROM scott.dept d join scott.emp e

USING (deptno)

Plan hash value: 844388907

| | 0 | SELECT STATEMENT | | 1 | | 14 | 00001 | 00.01 | 7 |

PLAN_TABLE_OUTPUT

| | 1 | MERGE JOIN | | 1 | 14 | 14 | 00001 | 00.01 | 7 |

| | 2 | TABLE ACCESS BY INDEX ROWID | DEPT | 1 | 4 | 4 | 00lv 00.01 | 4 |

| | 3 | INDEX FULL SCAN | PK_DEPT | 1 | 4 | 4 | 00lv 00.01 | 2 |

| | * 4 | SORT JOIN | | 4 | 14 | 14 | 00lv 00.01 | 3 | 2048 | 2048 | 2048 (0) |

| | 5 | TABLE ACCESS FULL | EMP | 1 | 14 | 14 | 00lv 00.01 | 3 |

Predicate Information (identified by operation id):

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

PLAN_TABLE_OUTPUT

Filter ("D". "DEPTNO" = "E". "DEPTNO") 24 rows selected. Reference to: "follow the rules: quickly understand the routines and tools of SQL implementation plan"-Liang Jingbin

For more information, see the official documentation:

Http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_xplan.htm#ARPLS378

DBMS_XPLAN

The DBMS_XPLAN package provides an easy way to display the output of the EXPLAIN PLAN command in several, predefined formats. You can also use the DBMS_XPLAN package to display the plan of a statement stored in the Automatic Workload Repository (AWR) or stored in a SQL tuning set. It further provides a way to display the SQL execution plan and SQL execution runtime statistics for cached SQL cursors based on the information stored in the V$SQL_PLAN and V$SQL_PLAN_STATISTICS_ALL fixed views. Finally, it displays plans from a SQL plan baseline.

Overview

The DBMS_XPLAN package supplies five table functions:

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.

Http://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037231

Tuning SQL*Plus

SQL*Plus User's Guide and Reference

Statistics

The statistics are recorded by the server when your statement executes and indicate the system resources required to execute your statement. The results include the following statistics.

(1) recursive calls

Number of recursive calls generated at both the user and system level. Oracle Database maintains tables used for internal processing. When Oracle Database needs to make a change to these tables, it internally generates an internal SQL statement, which in turn generates a recursive call.

(2) db block gets

Number of times a CURRENT block was requested.

(3) consistent gets

Number of times a consistent read was requested for a block

(4) physical reads

Total number of data blocks read from disk. This number equals the value of "physical reads direct" plus all reads into buffer cache.

(5) redo size

Total amount of redo generated in bytes

(6) bytes sent through SQL*Net to client

Total number of bytes sent to the client from the foreground processes.

(7) bytes received through SQL*Net from client

Total number of bytes received from the client over Oracle Net.

(8) SQL*Net round-trips to/from client

Total number of Oracle Net messages sent to and received from the client

(9) sorts (memory)

Number of sort operations that were performed completely in memory and did not require any disk writes

(10) sorts (disk)

Number of sort operations that required at least one disk write

(11) rows processed

Number of rows processed during the operationSTATISTICS_LEVEL = {ALL | TYPICAL | BASIC}

STATISTICS_LEVEL specifies the level of collection for database and operating system statistics. The Oracle Database collects these statistics for a variety of purposes, including making self-management decisions.

The default setting of TYPICAL ensures collection of all major statistics required for database self-management functionality and provides best overall performance. The default value should be adequate for most environments.

When the STATISTICS_LEVEL parameter is set to ALL, additional statistics are added to the set of statistics collected with the TYPICAL setting. The additional statistics are timed OS statistics and plan execution statistics.

Setting the STATISTICS_LEVEL parameter to BASIC disables the collection of many of the important statistics required by Oracle Database features and functionality, including:

(1) Automatic Workload Repository (AWR) Snapshots

(2) Automatic Database Diagnostic Monitor (ADDM)

(3) All server-generated alerts

(4) Automatic SGA Memory Management

(5) Automatic optimizer statistics collection

(6) Object level statistics

(7) End to End Application Tracing (V$CLIENT_STATS)

(8) Database time distribution statistics (V$SESS_TIME_MODEL and V$SYS_TIME_MODEL)

(9) Service level statistics

(10) Buffer cache advisory

(11) MTTR advisory

(12) Shared pool sizing advisory

(13) Segment level statistics

(14) PGA Target advisory

(15) Timed statistics

(16) Monitoring of statistics

On how to get the SQL implementation plan in Oracle to share here, I hope the above content can be of some help to you, can learn more knowledge. If you think the article is good, you can share it for more people to see.

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.