How to view Oracle execution plan

What is an execution plan?

SQL is a fool-like language. Each condition is a requirement. Different access orders form different execution plans. Oracle must make a choice and can only have one access path at a time. The execution plan is a description of the execution process or access path of a query statement in Oracle.

Selection of execution plan:

Usually a SQL has multiple execution plans, so how do we choose? The one with lower execution overhead means better performance and faster speed. We will choose which one. This process is called Oracle's parsing process, and then Oracle will put the better execution plan into the Shared Pool of SGA. To execute the same SQL later, you only need to obtain it from the Shared Pool, and there is no need to analyze it again.

Execution plan selection basis:

Select an execution plan based on statistical information.

Statistical information:

What is statistical information: number of records, number of blocks, etc., please view dba_tables / dba_indexes

Dynamic sampling:

Oracle normally collects statistical information at a certain time every day. How does Oracle collect statistical information for newly created tables? Use dynamic sampling.

set autotrace on
set linesize 1000
--Execute SQL statement
--The dynamic sampling used for this statement(level=2) key will appear

Six execution plans

Oracle provides 6 execution plan acquisition methods, each with different emphasis:

Generally follow the following rules when selecting:

1. If the sql execution takes a long time to produce results or cannot return results, use method 1: explain plan for

2. The easiest way to track a certain sql is method 1: explain plan for, followed by method 2: set autotrace on

3. If you want to check multiple execution plans of a certain sql, you can only use method 4: dbms_xplan.display_cursor or method 6: awrsqrpt.sql

4. If the sql contains a function, and the function contains sql, that is, there are multiple layers of calls. If you want to accurately analyze, you can only use method 5: 10046 tracking

5. Idea to see the real execution plan , you cannot use method 1: explain plan for and method 2: set autotrace on

6. If you want to get the number of times the table has been accessed, you can only use method 3: statistics_level = all

How Oracle collects statistics:

1. Oracle will choose to collect table and index statistics in a specific time period (default Monday to Friday: 22:00, Saturday and Sunday: 06:00), users can adjust by themselves, mainly to avoid peak periods;

2. There is a threshold limit for the analysis of tables and indexes, and analysis will only be performed automatically when the threshold is exceeded. . If the data changes are not large, Oracle will not analyze it;

3. The collection method is flexible. It can be done for a certain partition of the partition table, and a parallel mechanism can be used to collect table and index information;

How to collect statistical information:

--Collect table statistical information

exec dbms_stats.gather_table_stats(ownname => 'AAA', tabname => 'TEST02',estimate_percent =>
10,method_opt => 'for all indexed columns');

--Collect index statistics

exec dbms_stats.gather_index_stats(ownname => 'AAA',indname => 'ID_IDX',estimate_percent =>
10,degree => '4');

--Collect table and index statistics

exec dbms_stats.gather_table_stats(ownname => 'AAA',tabname => 'TEST02',estimate_percent =>
10,method_opt => 'for all indexed columns',cascade => true);

(1) explain plan for

SQL> show user
     USER 为 "HR"
SQL> set linesize 1000
SQL> set pagesize 2000
SQL> explain plan for
2 select *
3 from employees,jobs
4 where employees.job_id=jobs.job_id
5 and employees.department_id=50;
已解释。

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
已选择19行。

Advantages: No need to actually execute, Fast and convenient;

Disadvantages:

1. No relevant statistical information is output, such as how many logical reads, how many physical reads, and how many recursive calls are generated;

2. It is impossible to determine how many rows have been processed;

3. It is impossible to determine how many times the table has been executed

(2) set autotrace on

Usage:

Command function:

SET AUTOT[RACE] OFF stops AutoTrace
SET AUTOT[RACE] ON turns on AutoTrace, displays AUTOTRACE information and SQL execution results
SET AUTOT[RACE] TRACEONLY turns on AutoTrace , only display AUTOTRACE information
SET AUTOT[RACE] ON EXPLAIN Turn on AutoTrace, display only AUTOTRACE EXPLAIN information
SET AUTOT[RACE] ON STATISTICS Turn on AutoTrace, display only AUTOTRACE STATISTICS information

SQL> set autotrace on
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果（略）
-- ...
已选择45行。

执行计划
----------------------------------------------------------
Plan hash value: 303035560
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 45 | 4590 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN | | 45 | 4590 | 6 (17)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 19 | 627 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 19 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 45 | 3105 | 4 (25)| 00:00:01 |
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 45 | 3105 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
统计信息
----------------------------------------------------------
0 recursive calls
0 db block gets
13 consistent gets
0 physical reads
0 redo size
5040 bytes sent via SQL*Net to client
433 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
45 rows processed

Advantages:

1. Can output relevant statistical information at runtime (how many logical reads are generated, how many recursive calls, how many physical reads, etc.);

2. Although it has to wait The execution plan can only be output after the statement is executed, but the traceonly switch can be used to control the return results without printing the screen output;

Disadvantages:

1. You must wait for the SQL statement to be executed before the results are output;

2. It is impossible to see how many times the table has been accessed;

(3) statistics_level=all

Step 1: ALTER SESSION SET STATISTICS_LEVEL=ALL;

Step 2: Execute the SQL to be analyzed

Step 3: select * from table(dbms_xplan.display_cursor('sql_id/hash_value',null,'allstats last'));

SQL> alter session set statistics_level=all;
SQL> select * from employees,jobs where employees.job_id=jobs.job_id and employees.department_id=50;
--输出结果
--...
已选择45行。

SQL> set linesize 1000
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----------
SQL_ID d8jzhcdwmd9ut, child number 0
-------------------------------------
select * from employees,jobs where employees.job_id=jobs.job_id and
employees.department_id=50
Plan hash value: 303035560
------------------------------------------------------------------------------------------------------------------------
----------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem |
1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
----------------
| 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 13 | 8 | |
| |
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-------------
| 1 | MERGE JOIN | | 1 | 45 | 45 |00:00:00.01 | 13 | 8 | |
| |
| 2 | TABLE ACCESS BY INDEX ROWID| JOBS | 1 | 19 | 19 |00:00:00.01 | 6 | 2 | |
| |
| 3 | INDEX FULL SCAN | JOB_ID_PK | 1 | 19 | 19 |00:00:00.01 | 3 | 1 | |
| |
|* 4 | SORT JOIN | | 19 | 45 | 45 |00:00:00.01 | 7 | 6 | 6144 |
6144 | 6144 (0)|
|* 5 | TABLE ACCESS FULL | EMPLOYEES | 1 | 45 | 45 |00:00:00.01 | 7 | 6 | |
| |
------------------------------------------------------------------------------------------------------------------------
----------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
-----
filter("EMPLOYEES"."JOB_ID"="JOBS"."JOB_ID")
5 - filter("EMPLOYEES"."DEPARTMENT_ID"=50)
已选择25行。

Keyword interpretation:

1, starts: the number of SQL executions;

2, E-Rows: the number of rows expected to be returned by the execution plan;

3, R-Rows : The number of rows actually returned by the execution plan;

4. A-Time: The execution time of each step (HH:MM:SS.FF). Based on this row, you can know where the SQL time is spent;

5. Buffers: logical read or consistent read actually executed in each step;

6. Reads: physical read;

Advantages:

1. Can be clear Get how many times the table has been accessed from starts;

2、可以从E-Rows和A-Rows得到预测的行数和真实的行数，从而可以准确判断Oracle评估是否准确；

3、虽然没有准确的输出运行时的相关统计信息，但是执行计划中的Buffers就是真实的逻辑读的数值；

缺点：

1、必须要等执行完后才能输出结果；

2、无法控制结果打屏输出，不像autotrace可以设置traceonly保证不输出结果；

3、看不出递归调用，看不出物理读的数值

（4）dbms_xplan.display_cursor获取

步骤1：select * from table( dbms_xplan.display_cursor('&sql_id') ); --该方法是从共享池得到

注释：

1、还有1种方法，select * from table( dbms_xplan.display_awr('&sql_id') ); --该方法是从awr性能视图里面获取

2、如果有多个执行计划，可用以下方法查出：

select * from table(dbms_xplan.display_cursor('&sql_id',0));
select * from table(dbms_xplan.display_cursor('&sql_id',1));
*/
SQL> select * from table(dbms_xplan.display_cursor('5hkd01f03y43d'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID 5hkd01f03y43d, child number 0
-------------------------------------
select * from test where table_name = 'LOG$'
Plan hash value: 2408911181
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)|
| 1 | TABLE ACCESS BY INDEX ROWID| TEST | 1 | 241 | 2 (0)|
|* 2 | INDEX RANGE SCAN | IDX_TEST_1 | 1 | | 1 (0)|
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("TABLE_NAME"='LOG$')
19 rows selected

注释：如何查看1个sql语句的sql_id，可直接查看v$sql

优点：

1、知道sql_id即可得到执行计划，与explain plan for一样无需执行；

2、可得到真实的执行计划

缺点：

1、没有输出运行的统计相关信息；

2、无法判断处理了多少行；

3、无法判断表被访问了多少次；

（5）事件10046 trace跟踪

步骤1：alter session set events '10046 trace name context forever,level 12'; --开启追踪

步骤2：执行sql语句；

步骤3：alter session set events '10046 trace name context off'; --关闭追踪

步骤4：找到跟踪后产生的文件（开启10046前先用‘ls -lrt’看一下文件，执行结束后再看哪个是多出来的文件即可）

步骤5：tkprof trc文件 目标文件 sys=no sort=prsela,exeela,fchela --格式化命令

优点：

1、可以看出sql语句对应的等待事件；

2、如果函数中有sql调用，函数中有包含sql，将会被列出，无处遁形；

3、可以方便的看处理的行数，产生的逻辑物理读；

4、可以方便的看解析时间和执行时间；

5、可以跟踪整个程序包

缺点：

1、步骤繁琐；

2、无法判断表被访问了多少次；

3、执行计划中的谓词部分不能清晰的展现出来

推荐：Oracle数据库学习教程

The above is the detailed content of How to view Oracle execution plan. For more information, please follow other related articles on the PHP Chinese website!