Home  >  Article  >  Database  >  Completely master Oracle advanced learning to view execution plan

Completely master Oracle advanced learning to view execution plan

WBOY
WBOYforward
2022-02-24 18:02:142837browse

This article brings you relevant knowledge about Oracle, which mainly introduces the related issues of viewing execution plans. I hope it will be helpful to everyone.

Completely master Oracle advanced learning to view execution plan

Recommended tutorial: "Oracle Video Tutorial"

Today we will talk about the way Oracle views execution plans and how to view execution plan.

1. How to view the execution plan

1.1. Set autotrace

The autotrace command is as follows

##3SET AUTOTRACE ON STATISTICSDisplay execution only Statistics for ##4##5

Serial number

Command

##Explanation

1

SET AUTOTRACE OFF

This is the default value, which means Autotrace is turned off

2

SET AUTOTRACE ON EXPLAIN

Display only execution plan

SET AUTOTRACE ON

contains 2 and 3 items

##SET AUTOTRACE TRACEONLY

Similar to ON, but does not display the execution results of the statement

1.2. Use third-party tools

such as the explain window of PL/SQL Develop

##1.3. EXPLAIN PLAN FOR

It is said that adding EXPLAIN PLAN FOR before the executed SQL can check the execution plan. I haven’t figured it out yet. I will add

for example:

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;

has been explained.

SQL> SELECT plan_table_output FROM TABLE(DBMS_XPLAN.DISPLAY('PLAN_TABLE'));

Or:

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

2. Clear the SGA cache

Because when sql is executed, the sql execution plan, the database read from the disk and other information will be in the SGA Some caches are stored for a period of time. In order to see the effect of the first execution of the statement, these caches need to be cleared.

ALTER SYSTEM FLUSH SHARED_POOL;
ALTER SYSTEM FLUSH BUFFER_CACHE;
ALTER SYSTEM FLUSH GLOBAL CONTEXT;

3. Analyze execution plan

3.1. Create test table

Create two new tables cust_info and cst_tran (only for testing, no practical significance)

CREATE TABLE CUST_INFO
(CST_NO NUMBER,
CST_NAME VARCHAR2(50),
AGE SMALLINT);

CREATE TABLE CST_TRAN
(
CST_NO NUMBER,
TRAN_DATE VARCHAR2(8),
TRAN_AMT NUMBER(19,3)
);

Insert some data, CUST_INFO table 10,000, CST_TRAN table 1 million.

INSERT INTO CUST_INFO
SELECT 100000+LEVEL,
       'test'||LEVEL,
       ROUND(DBMS_RANDOM.VALUE(1,100))
FROM DUAL
CONNECT BY LEVEL<=10000;


INSERT INTO CST_TRAN
WITH AA AS
(SELECT LEVEL FROM DUAL CONNECT BY LEVEL<=100)
SELECT T.CST_NO,
       TO_CHAR(SYSDATE - DBMS_RANDOM.VALUE(1,1000),&#39;yyyymmdd&#39;),
       ROUND(DBMS_RANDOM.VALUE(1,999999999),3)
FROM CUST_INFO T
INNER JOIN AA
ON 1=1;

3.2. View the execution plan

View the execution plan associated with these two tables

SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CUST_INFO T INNER JOIN CST_TRAN G ON G.CST_NO = T.CST_NO;

1000000 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 2290587575

--------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |   996K|    68M|  1079     (2)| 00:00:13 |
|*  1 |  HASH JOIN                   |                     |   996K|    68M|  1079     (2)| 00:00:13 |
|   2 |   TABLE ACCESS FULL | CUST_INFO | 10000 |   390K|    11     (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | CST_TRAN  |  1065K|    32M|  1064     (1)| 00:00:13 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("G"."CST_NO"="T"."CST_NO")

Note
-----
   - dynamic sampling used for this statement (level=2)


Statistics
----------------------------------------------------------
    561  recursive calls
      0  db block gets
      70483  consistent gets
       4389  physical reads
      0  redo size
   45078003  bytes sent via SQL*Net to client
     733845  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
     10  sorts (memory)
      0  sorts (disk)
    1000000  rows processed

3.2.1. Execution plan

First let’s take a look Part 1

--------------------------------------------------------------------------------
| Id  | Operation                        | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                     |   996K|    68M|  1079     (2)| 00:00:13 |
|*  1 |  HASH JOIN                   |                     |   996K|    68M|  1079     (2)| 00:00:13 |
|   2 |   TABLE ACCESS FULL | CUST_INFO | 10000 |   390K|    11     (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL | CST_TRAN  |  1065K|    32M|  1064     (1)| 00:00:13 |
--------------------------------------------------------------------------------

Explanation of the fields in the execution plan:

## ID: A serial number, but not the order of execution. The order of execution is judged based on indentation.
  • Operation: The content of the current operation.
  • Rows: Cardinality of the current operation, Oracle estimates the return result set of the current operation.
  • Cost (CPU): A numerical value (cost) calculated by Oracle to illustrate the cost of SQL execution.
  • Time: Oracle estimates the time of the current operation.
Instructions:

1. Operation

Record the operations of each step, and judge the order of execution according to the degree of indentation.

In OLAP databases, there are many HASH JOIN connections, especially when the returned data set is large, they are basically HASH JOIN.

2. Rows

The rows value indicates the number of records that CBO expects to return from a row source. This row source may be a table, an index, or a subquery. . In the execution plan in Oracle 9i, Cardinality is abbreviated as Card. In 10g, Card values ​​are replaced by rows. The

rows

value is crucial for CBO to make a correct execution plan. If the rows value obtained by CBO is not accurate enough (usually due to lack of analysis or outdated analysis data), there will be deviations in the execution plan cost calculation, which will lead to CBO incorrectly formulating an execution plan. When there is a multi-table related query or a subquery in SQL, the rows value of each related table or subquery has a great impact on the main query. It can even be said that CBO depends on each related table. Or the subquery rows value calculates the final execution plan.

For multi-table queries, CBO uses the number of rows (rows) returned by each associated table to determine what access method to use for table association (such as Nested loops Join or hash Join)

3. Cost (CPU) and Time are important reference values ​​for the execution plan

3.2.2. Predicate description:

Predicate Information (identified by operation id):

------------------------------------------------ ---


1 - access("G"."CST_NO"="T"."CST_NO")

Note

-----

- dynamic sampling used for this statement (level=2)

Access: Indicates that the value of this predicate condition will affect the access path of the data (table or index).

Filter: Indicates that the value of the predicate condition will not affect the access path of the data and only plays a filtering role. (Not in this example)

Note: Pay attention to

access in the predicate. You must consider the conditions of the predicate and whether the access path used is correct.

3.2.3、统计信息

Statistics
----------------------------------------------------------
    561  recursive calls
      0  db block gets
      70483  consistent gets
       4389  physical reads
      0  redo size
   45078003  bytes sent via SQL*Net to client
     733845  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
     10  sorts (memory)
      0  sorts (disk)
    1000000  rows processed

参数说明:

  • recursive calls   :递归调用。一般原因:dictionary cache未命中;动态存储扩展;PL/SQL语句
  • db block gets    :bufer中读取的block数量,用于insert,update,delete,selectfor update
  • consistent gets   :这里是一致读次数(一个block可能会被读多次),bufer中读取的用于查询(除掉select forupdate)的block数量。                
  • physical reads    :从磁盘上读取的block数量,敬请关注每周五晚免费网络公开课。
  • redo size      :bytes,写到redo logs的数据量
  • bytes sent via SQL*Net to client :发送给客户端的字节数
  • bytes received via SQL*Net from client :从客户端接收的字节数
  • SQL*Net roundtrips to/from client :与客户端的交互次数(个人理解接收一条SQL语句,执行结果分多次发送给客户端,如有问题请指正
  • sorts (memory)    :内存排序次数
  • sorts (disk)     :磁盘排序次数;与sort_area_size有关 
  • rows processed :执行完SQL后返回结果集的行数

四、部分信息解释

4.1、SQL*Net roundtrips to/from client的计算方式

这个指标的计算方式和一个参数息息相关,arraysize

arraysize是什么呢?

请查阅大牛博文:Oracle arraysize 和 fetch size 参数 与 性能优化 说明

arraysize定义了一次返回到客户端的行数,取值范围【1-5000】,默认15。

使用命令在数据库中查看arraysize的值。

show arraysize

还可以修改这个值

set arraysize 5000;

明白了arraysize这个参数就可以计算SQL*Net roundtrips to/from client的值了。上例中,返回客户端结果集的行数是1000000,默认arraysize值是15,1000000/15向上取整等于66667。

为啥要向上取整?

举个栗子,如果有10个苹果,一个只能拿3个,几次可以拿完,3次可以拿9个,还剩1个,所以还需要再拿一次,共4次。

统计分析中的值是66668,为什么我们计算的值是66667?

就要看这个指标本身了,再粘贴一次:SQL*Net roundtrips to/from client  重点看from,意思是我们还要接受一次客户端发来的SQL语句,因此是:66667+1,本问题纯属个人臆断,无真凭实据,受限于本人的知识水平,如有误,请指出。

将arraysize的值修改为5000后,再观察SQL*Net roundtrips to/from client的变化,结果为201。

前面提到 arraysize的取值范围是【1-5000】,我们可以试一下改为不在这个区间的值,比如改为0,结果报错了

SQL> set arraysize 0;
SP2-0267: arraysize option 0 out of range (1 through 5000)

4.2、consistent gets

译为中文就是:一致性读, 好抽象的一个指标,啥叫一致性读,心中无数羊驼驼在大海中狂奔。

官网对consistent gets 的解释:

consistent gets:Number of times a consistent read wasrequested for a block.

通常我们执行SQL查询时涉及的每一block都是Consistent Read, 只是有些CR(Consistent Read)需要使用undo 来进行构造, 大部分CR(Consistent Read)并不涉及到undo block的读.

还有就是每次读这个block都是一次CR(可能每个block上有多个数据row), 也就是如果某个block被读了10次, 系统会记录10个Consistent Read.

如果想深入学习,请参考大佬博文:Oracle 有关 Consistent gets 的测试 -- cnDBA.cn_中国DBA社区

接来下测试下, consistent gets是从哪来的,需要使用有sysdba权限的用户,因为oradebug工具需要sysdba权限。

oradebug工具介绍:oracle实用工具:oradebug

使用10046对同一条数据跟踪两次,注意观察 consistent gets的不同

为了不影响测试结果,首先清空缓存

SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> ALTER SYSTEM FLUSH BUFFER_CACHE;
System altered.
SQL> ALTER SYSTEM FLUSH GLOBAL CONTEXT;
System altered.

第一次执行

SQL> set tim on timing on
00:42:30 SQL> set autot trace stat
00:42:36 SQL> oradebug setmypid
Statement processed.
00:42:42 SQL> alter session set tracefile_identifier=&#39;chf1&#39;;

Session altered.

Elapsed: 00:00:00.01
00:42:50 SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
00:42:57 SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CHF.CUST_INFO T INNER JOIN CHF.CST_TRAN G ON G.CST_NO = T.CST_NO;

1000000 rows selected.

Elapsed: 00:00:22.71

Statistics
----------------------------------------------------------
    547  recursive calls
      0  db block gets
      70368  consistent gets
       3898  physical reads
      0  redo size
   45078003  bytes sent via SQL*Net to client
     733845  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
     10  sorts (memory)
      0  sorts (disk)
    1000000  rows processed

00:44:24 SQL> oradebug event 10046 trace name context off;
Statement processed.
00:45:54 SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf1.trc

第二次执行

00:46:04 SQL> alter session set tracefile_identifier=&#39;chf2&#39;;

Session altered.

Elapsed: 00:00:00.00
00:46:35 SQL> oradebug event 10046 trace name context forever,level 12;
Statement processed.
00:46:43 SQL> SELECT T.CST_NO, T.CST_NAME, G.TRAN_DATE, G.TRAN_AMT FROM CHF.CUST_INFO T INNER JOIN CHF.CST_TRAN G ON G.CST_NO = T.CST_NO;

1000000 rows selected.

Elapsed: 00:00:21.62

Statistics
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      70301  consistent gets
       3850  physical reads
      0  redo size
   45078003  bytes sent via SQL*Net to client
     733845  bytes received via SQL*Net from client
      66668  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
    1000000  rows processed

00:47:11 SQL> oradebug event 10046 trace name context off;
Statement processed.
00:49:03 SQL> oradebug tracefile_name
/u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf2.trc

通过对比两次执行,发现consistent gets、physical reads、sorts (memory)都有变化,这是因为SGA中已经缓存了部分数据块。

再对比下我们刚才生产的两个跟踪日志,为方便查看,先将其格式转换以下

[oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf1.trc /u01/chf1.trc

TKPROF: Release 11.2.0.1.0 - Development on Wed Dec 8 00:53:37 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


[oracle@localhost ~]$ tkprof /u01/app/oracle/diag/rdbms/orcl/bpas/trace/bpas_ora_7715_chf2.trc /u01/chf2.trc

TKPROF: Release 11.2.0.1.0 - Development on Wed Dec 8 00:53:48 2021

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

打开 /u01/chf1.trc,下面贴出部分重要信息

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.03       0.03          8         67          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    66668      0.76       3.24       3890      70301          0     1000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    66670      0.79       3.28       3898      70368          0     1000000

Misses in library cache during parse: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   66670        0.01          0.14
  SQL*Net message from client                 66670       64.54         79.11
  db file sequential read                         5        0.00          0.00
  Disk file operations I/O                        1        0.00          0.00
  db file scattered read                          5        0.00          0.00
  asynch descriptor resize                        4        0.00          0.00
  direct path read                               69        0.00          0.02


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       12      0.00       0.00          0          0          0           0
Execute     24      0.01       0.01          0          0          0           0
Fetch       30      0.00       0.00          8         67          0          18
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       66      0.02       0.02          8         67          0          18

打开 /u01/chf2.trc,下面贴出部分重要信息

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch    66668      1.57       3.73       3850      70301          0     1000000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total    66670      1.57       3.73       3850      70301          0     1000000
Misses in library cache during parse: 0
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                   66670        0.00          0.10
  SQL*Net message from client                 66670        6.83         19.93
  asynch descriptor resize                        4        0.00          0.00
  direct path read                               69        0.00          0.01
OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      0      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        0      0.00       0.00          0          0          0           0

比较发现,第一次执行解析SQL语句,生产执行计划时,consistent gets发生67次,执行SQL语句时发生70301。第一次执行解析SQL语句,生产执行计划时,因已经有缓存,所以consistent gets发生0次,执行SQL语句时发生70301。

推荐教程:《Oracle视频教程

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

Statement:
This article is reproduced at:csdn.net. If there is any infringement, please contact admin@php.cn delete