完全掌握Oracle進階學習之查看執行計劃

這篇文章為大家帶來了關於Oracle的相關知識，其中主要介紹了查看執行計劃的相關問題，希望對大家有幫助。

推薦教學：《Oracle影片教學》

今天談談Oracle檢視執行計畫的方式，以及怎麼看執行計劃。

一、查看執行計劃的方式

1.1、設定autotrace

#autotrace指令如下

1SET AUTOTRACE OFF此為預設值，即關閉Autotrace2SET AUTOTRACE ON EXPLAIN

##序號	#指令
		##只顯示執行計劃
3	#SET AUTOTRACE ON STATISTICS	只顯示執行的統計資訊
4	#SET AUTOTRACE ON	##包含2,3兩項內容

####5############SET AUTOTRACE TRACEONLY###################################################################################### ##與ON相似，但不顯示語句的執行結果###############

1.2、使用第三方工具

例如PL/SQL Develop的explain視窗

1.3、EXPLAIN PLAN FOR

#據說在執行的SQL前加上EXPLAIN PLAN FOR 可以查看執行計劃，還沒搞明白，後續補上

舉例：

SQL> EXPLAIN PLAN FOR SELECT * FROM EMP;

已解釋。

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

或：

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

二、清除SGA快取

#因為在sql執行時，sql的執行計畫、從磁碟讀取的資料庫等資訊會在SGA的某些快取中保存一段時間，為了查看語句第一次執行的效果，就需要清空這些快取。

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

三、分析執行計畫

3.1、建立測試表

新兩張表cust_info、cst_tran（單純用來測試，沒有實際意義）

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

插入一些數據，CUST_INFO表1萬，CST_TRAN表100萬。

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、查看執行計劃

查看這兩個表關聯的執行計劃

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、執行計劃

首先我們來看看第一部分

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

執行計畫中欄位解釋：

• #       ID: 一個序號，但不是執行的先後順序。執行的先後根據縮進來判斷。
•        Operation： 目前作業的內容。
•        Rows： 目前操作的Cardinality，Oracle估計目前作業的回傳結果集。
•        Cost（CPU）：Oracle 計算出來的一個數值（代價），用來說明SQL執行的代價。
•        Time：Oracle 估計目前作業的時間。

說明：

1、Operation

記錄每一步的操作，依照縮排的程度判斷執行的先後順序。

在OLAP資料庫中，HASH JOIN連線較多，特別是回傳資料集大的時候，基本上都是HASH JOIN。

2、Rows

rows值表示CBO預期從一個行來源（row source）傳回的記錄數，這個行來源可能是一個表，一個索引，也可能是一個子查詢。  在Oracle 9i中的執行計畫中，Cardinality縮寫成Card。在10g中，Card值被rows取代。

rows值對於CBO做出正確的執行計畫至關重要。 如果CBO獲得的rows值不夠準確（通常是沒有做分析或分析資料過舊造成），在執行計劃成本計算上就會出現偏差，從而導致CBO錯誤的製定出執行計劃。

在多表關聯查詢或SQL中有子查詢時，每個關聯表或子查詢的rows的值對主查詢的影響都非常大，甚至可以說，CBO就是依賴各個關聯表或子查詢rows值計算出最後的執行計劃。

對於多表查詢，CBO使用每個關聯表傳回的行數（rows）決定用什麼樣的存取方式來做表格關聯（如Nested loops Join 或hash Join）

# 3、Cost（CPU）和Time是執行計畫的重要參考值

3.2.2、謂詞說明：

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

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

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

       Access: 表示這個謂詞條件的值將會影響資料的存取路勁（表還是索引）。

       Filter：表示謂詞條件的數值不會影響資料的存取路勁，只起過濾的功能。 （此例中沒有）

注意：在謂詞中主要注意access，要考慮謂詞的條件，使用的存取路徑是否正確。

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='chf1';

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='chf2';

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视频教程》

以上是完全掌握Oracle進階學習之查看執行計劃的詳細內容。更多資訊請關注PHP中文網其他相關文章！