微課sql最佳化(11) 、如何檢視執行計劃

orastar發表於2020-03-04

1、執行計劃是什麼？

執行計劃描述了SQL引擎為執行SQL語句進行的操作。每當必須分析與SQL語句相關的效能問題或僅僅質疑查詢最佳化器的決定時，必須知道執行計劃。

2、父子關係

執行計劃是一個樹形結構，它不僅闡述了SQL引擎的執行操作的順序，也闡明瞭它們之間的關係。樹的每一個結點都代表一個操作，比如，表訪問、連線或排序。在各操作之間，存在你子關係。規則如下，

父有一個或多個子。
子只有一個父。
唯一沒有父的操作是樹的根。
子操作對父操作向右縮排。
父ID小於子ID，有多個父操作時，靠其最近的為父操作。

3、單獨型操作

最多有一個孩子的操作定義為單獨型操作。規則如下，

子操作在父操作前面執行
每個子操作只執行一次
每個子操作向父操作提供輸出。

SQL> set line 200

set heading off

alter session set statistics_level=all;

select count(1) from ht.c_cons where cons_no=101600;

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'allstats last'));SQL> SQL>

Session altered.

SQL_ID 8t8mnhwp921ym, child number 1

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

select count(1) from ht.c_cons where cons_no=101600

Plan hash value: 1250298410

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

| Id | Operation ???????| Name ???| Starts | E-Rows | A-Rows | A-Time | Buffers |

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

| 0 | SELECT STATEMENT ??| ??????| 1 ?| ???| 1 ??|00:00:00.01 | 2 |

| 1 | SORT AGGREGATE ??| ??????| 1 ?| 1 ???| 1 ??|00:00:00.01 | 2 |

|* 2 | INDEX UNIQUE SCAN? | SYS_C0011125 | 1? | 1 ???| 1 ??|00:00:00.01 | 2 |

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

4、非相關聯合型操作

有多個相互獨立孩子的操作定義為非相關聯合型操作，操作型別如，

AND-EQUAL、BITMAP AND、BITMAP OR、BITMAP MINUS、CONCATENATION、CONNECT BY WITHOUT FILTERING、HASH JOIN、INTERSECTION、MERGE JOIN、MINUS、MULTI-TABLE INSERT、SQL MODEL、TEMP TABLE TRANSFORMATION 和UNION-ALL。規則如下，

子操作在父操作前面執行。
子操作按順序執行，從ID最小的到ID最大的。在開始下一個子操作前，當前子操作必須全部完成。
每個子操作至多執行一次，並且與其它子操作相互獨立。
每個子向其父操作提供輸出

set heading off

alter session set statistics_level=all;

select /*+ use_hash(c,a)*/c.org_name,sum(a.amt) from ht.c_cons c,ht.a_amt a

where c.cons_no=a.cons_no

and a.amt_ym='201701' and c.cons_name='wang1706'

group by c.org_name;

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'allstats last'));

Plan hash value: 1779151266

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

| Id | Operation ??????????| Name ?????| Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

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

| 0 | SELECT STATEMENT ?????| ???????| 1 | ??| 1 |00:00:00.02 | 250 | | | |

| 1 | HASH GROUP BY ??????| ???????| 1 | 1 | 1 |00:00:00.02 | 250 | 1126K| 1126K| 500K (0)|

|* 2 | HASH JOIN ????????| ??????? | 1 | 3 | 1 |00:00:00.02 | 250 | 1483K| 1483K| 591K (0)|

| 3 | ?TABLE ACCESS BY INDEX ROWID | C_CONS ??? | 1 | 2 | 1 |00:00:00.01 | 3 | | | |

|* 4 | ?INDEX RANGE SCAN ????| IDX_CONS_NAME | 1 | 2 | 1 |00:00:00.01 | 2 | | | |

|* 5 | TABLE ACCESS FULL ????| A_AMT ??? | 1 | 10364 | 9998 |00:00:00.01 | 247 | | | |

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

5、相關聯合型

有多個子操作，其中一個子操作控制其它子操作執行定義為相關聯合型操作。操作型別如，NESTED LOOPS、UPDATE、FILTER、CONNECT BY WITH FILTERING和BITMAP KEY ITERATION。

規則如下，

子操作在父操作前面執行。
最小ID的子操作控制其它子操作。
從最小ID的子操作開始，到最大ID的子操作結束。
只有一個子操作最多執行一次，其它子操作可能執行多次或不執行。
不是每個子操作都向父操作提供資料，有些子操作是用來應用約束。

set heading off

alter session set statistics_level=all;

select /*+ use_nl(c,a) */c.org_name,sum(a.amt) from ht.c_cons c,ht.a_amt a

where c.cons_no=a.cons_no

and a.amt_ym='201701' and c.cons_name='wang1706'

group by c.org_name;

select * from table(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'allstats last'));

Plan hash value: 722682717

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

| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |

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

| 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 11 | | | |

| 1 | HASH GROUP BY | | 1 | 1 | 1 |00:00:00.01 | 11 | 1126K| 1126K| 513K (0)|

| 2 | NESTED LOOPS | | 1 | 3 | 1 |00:00:00.01 | 11 | | | |

| 3 | NESTED LOOPS | | 1 | 12 | 6 |00:00:00.01 | 5 | | | |

| 4 | TABLE ACCESS BY INDEX ROWID| C_CONS | 1 | 2 | 1 |00:00:00.01 | 3 | | | |

|* 5 | INDEX RANGE SCAN | IDX_CONS_NAME | 1 | 2 | 1 |00:00:00.01 | 2 | | | |

|* 6 | INDEX RANGE SCAN | IDX_A_AMT_CONS_NO | 1 | 6 | 6 |00:00:00.01 | 2 | | | |

|* 7 | TABLE ACCESS BY INDEX ROWID | A_AMT | 6 | 2 | 1 |00:00:00.01 | 6 | | | |

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

6、如何讀懂執行計劃

1、從ID=0開始，移動游標檢視是否存在同級操作。

2、所有操作無同級操作，類別為：單獨型，從最大ID子操作開始執行。

3、如果存在同級操作，繼續從小ID的子操作開始，查詢同級操作，依次迴圈。

7、練習

請用畫圖法描述該SQL語句的執行順序

select cons_name from ht.c_cons where cons_no=101600

select cons_name from ht.c_cons where cons_no=101601

select cons_name from ht.c_cons where cons_no=101602

select cons_name from ht.c_cons where cons_no=101603;

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/31442014/viewspace-2678267/，如需轉載，請註明出處，否則將追究法律責任。

相關文章

微課sql最佳化(9)、如何獲取執行計劃
2020-03-04
SQL
如何檢視SQL的執行計劃
2022-01-07
SQL
檢視sql執行計劃
2009-11-17
SQL
檢視SQL的執行計劃方法
2017-08-23
SQL
使用PL/SQL檢視執行計劃
2015-07-23
SQL
Oracle 檢視SQL的執行計劃
2010-11-04
OracleSQL
檢視sql執行計劃--set autotrace
2006-08-14
SQL
oracle如何檢視執行計劃
2008-09-04
Oracle
檢視sql執行計劃方法彙總
2015-08-26
SQL
MySQL 5.7 檢視理解SQL執行計劃
2016-06-01
MySql
【Explain Plan】檢視SQL的執行計劃
2009-10-07
AISQL
檢視執行計劃
2012-02-03
【Oracle】如何檢視sql 執行計劃的歷史變更
2017-02-14
OracleSQL
根據SQL_ID檢視執行計劃
2014-10-27
SQL
多種方法檢視Oracle SQL執行計劃
2012-06-20
OracleSQL
檢視sql執行計劃--set autotrace [final]
2012-03-22
SQL
oracle10g 檢視SQL執行計劃
2009-06-30
OracleSQL
透過查詢檢視sql執行計劃
2009-07-28
SQL
Oracle檢視正在執行的SQL以及執行計劃分析
2014-05-04
OracleSQL
檢視執行計劃（一）
2014-02-19
檢視執行計劃（二）
2014-02-20
檢視Oracle SQL執行計劃方法比較、分析
2017-01-22
OracleSQL
SQLPLUS檢視oracle sql執行計劃命令
2014-03-06
SQLOracle
檢視sql 執行計劃的歷史變更
2017-07-31
SQL
【最佳化】檢視歷史的執行計劃之檢視AWR報告
2016-10-16
Oracle如何檢視真實執行計劃（一）
2024-04-15
Oracle
【檢視】使用V$SQL_PLAN檢視獲取曾經執行過的SQL語句執行計劃
2014-09-17
SQL
Oracle檢視執行計劃（五）
2017-05-22
Oracle
Oracle檢視執行計劃（六）
2017-05-23
Oracle
Oracle檢視執行計劃（一）
2017-05-16
Oracle
Oracle檢視執行計劃（二）
2017-05-16
Oracle
Oracle檢視執行計劃（三）
2017-05-17
Oracle
Oracle檢視執行計劃（四）
2017-05-20
Oracle
檢視歷史執行計劃
2016-04-21
ORACLE執行計劃的檢視
2016-05-05
Oracle
檢視oracle執行計劃 - 轉
2010-10-19
Oracle
檢視執行計劃的方法
2010-07-02
檢視 OceanBase 執行計劃
2024-03-10