微課sql最佳化(9)、如何獲取執行計劃

orastar發表於2020-03-04
SQL

一、獲取執行計劃方法總結

1. explain plan for獲取；
2. set autotrace on ； 
3. statistics_level=all;
4. 透過(dbms_xplan.display_cursor/dbms_xplan.display_awr)輸入sql_id引數直接獲取
5. awrsqrpt.sql
6. 10046/10053 trace跟蹤
7. v$sql_plan_statistics_all

二、獲取執行計劃示例

  • 1、explain plan for
  1. SQL >   set   line 200
  2. SQL >  explain plan  for
  3.   2  select  c . org_name , sum ( a . amt )   from  ht . c_cons c , ht . a_amt a
  4. where  c . cons_no = a . cons_no
  5. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  6. group   by  c . org_name ;  3 4 5 

  8. Explained .
  9. SQL >   select   *   from   table ( dbms_xplan . display ) ;
  10. SQL >   /


  13. PLAN_TABLE_OUTPUT
  14. ----------------------------------------------------------------------------------------------------------------
  15. Plan hash  value :  1779151266


  18. -----------------------------------------------------------------------------------------------
  19. |  Id  |  Operation  |  Name  |   Rows   |  Bytes  |  Cost  ( % CPU ) |  Time  |
  20. -----------------------------------------------------------------------------------------------
  21. |  0  |   SELECT  STATEMENT  |   |  1  |  39  |  72  ( 2 ) |  00 : 00 : 01  |
  22. |  1  |  HASH  GROUP   BY   |   |  1  |  39  |  72  ( 2 ) |  00 : 00 : 01  |
  23. | *  2  |  HASH JOIN  |   |  3  |  117  |  71  ( 0 ) |  00 : 00 : 01  |
  24. |  3  |   TABLE   ACCESS   BY   INDEX   ROWID |  C_CONS  |  2  |  44  |  3  ( 0 ) |  00 : 00 : 01  |
  25. | *  4  |   INDEX  RANGE SCAN  |  IDX_CONS_NAME  |  2  |   |  1  ( 0 ) |  00 : 00 : 01  |
  26. | *  5  |   TABLE   ACCESS  FULL  |  A_AMT  |  10364  |  172K |  68  ( 0 ) |  00 : 00 : 01  |


  29. PLAN_TABLE_OUTPUT
  30. -----------------------------------------------------------------------------------------------------------------


  33. Predicate Information  ( identified   by  operation id ) :
  34. ---------------------------------------------------


  37.    2  -   access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
  38.    4  -   access ( "C" . "CONS_NAME" = 'wang1706' )
  39.    5  -  filter ( "A" . "AMT_YM" = '201701' )
  40. 19  rows  selected .
僅使用資料字典評估執行計劃，SQL語句未執行。
  • 結果解釋
欄位     描述
ID    執行計劃中每一個操作(行)的識別符號。如果數字前面帶有*號，意味著將在隨後提供這行包含的謂詞資訊
Operation    執行的操作，也叫做行源操作。
Name    操作的物件
查詢最佳化器評 估
Rows(E-Rows)    評估中操作返回的記錄條數
Bytes(E-Bytes)    評估中操作返回的記錄位元組數
TempSpc    評估中操作使用的臨時空間大小
Cost(%CPU)    評估中操作的開銷。在括號中列出了CPU開銷的百分比。注意這些值是透過執行計劃 計算出來的。即，你操作的開銷包含子操作的開銷
Time    評估中執行操作需要的時間(HH:MM:SS)
分 區
Pstart    訪問的第一個分割槽
Pstop    訪問的最後一個分割槽
並行和分散式處 理
Inst    在分散式操作中，指操作使用的資料庫鏈名稱
TQ    在並行操作中，用於屬執行緒間通訊的表佇列
IN-OUT    並行或分散式操作間的關係
PQ Distrib    在並行操作中，生產者為傳送資料給消費者進行的分配
執行進統 計
Starts    指定操作執行的次數
A-Rows    操作返回的真實記錄數
A-Time    操作執行的真實時間
I/O統 計
Buffers    執行期間進行的邏輯讀運算元量
Reads    執行期間進行的物理讀運算元量
Writes    執行期間進行的物理寫運算元量
記憶體使用統 計
0Mem    最優執行所需記憶體的評估值
1Mem    一次透過(one-pass：使用最少的磁碟操作，大部分操作在記憶體中執行)執行所需記憶體的評估值
0/1/M    最優/一次透過/多次透過(multipass: 工作區域設定太小，存在大量磁碟操作)模式操作執行次數
Used-Mem    最後一次執行時操作使用的記憶體量
Used-Tmp    最後一次執行時操作使用的臨時空間大小。
Max-Tmp    操作使用的最大臨時空間大小
  • 2. set autotrace on
  1. select   c . org_name , sum ( a . amt )   from   ht . c_cons c , ht . a_amt a
  2. where  c . cons_no = a . cons_no
  3. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  4. group   by  c . org_name ;
  5. SQL >   set  autotrace  on
  6. SQL >   select  c . org_name , sum ( a . amt )   from  ht . c_cons c , ht . a_amt a
  7. where  c . cons_no = a . cons_no
  8. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  9. group   by  c . org_name ;  2 3 4 

  11. ORG_NAME
  12. ------------------------------------------------------
  13. guangdong
  14.       9986

  16. Execution Plan
  17. ----------------------------------------------------------
  18. Plan hash  value :  1779151266


  21. -----------------------------------------------------------------------------------------------
  22. |  Id  |  Operation  |  Name  |   Rows   |  Bytes  |  Cost  ( % CPU ) |  Time  |
  23. -----------------------------------------------------------------------------------------------
  24. |  0  |   SELECT  STATEMENT  |   |  1  |  39  |  72  ( 2 ) |  00 : 00 : 01  |
  25. |  1  |  HASH  GROUP   BY   |   |  1  |  39  |  72  ( 2 ) |  00 : 00 : 01  |
  26. | *  2  |  HASH JOIN  |   |  3  |  117  |  71  ( 0 ) |  00 : 00 : 01  |
  27. |  3  |   TABLE   ACCESS   BY   INDEX   ROWID |  C_CONS  |  2  |  44  |  3  ( 0 ) |  00 : 00 : 01  |
  28. | *  4  |   INDEX  RANGE SCAN  |  IDX_CONS_NAME  |  2  |   |  1  ( 0 ) |  00 : 00 : 01  |
  29. | *  5  |   TABLE   ACCESS  FULL  |  A_AMT  |  10364  |  172K |  68  ( 0 ) |  00 : 00 : 01  |
  30. -----------------------------------------------------------------------------------------------


  33. Predicate Information  ( identified   by  operation id ) :
  34. ---------------------------------------------------


  37.    2  -   access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
  38.    4  -   access ( "C" . "CONS_NAME" = 'wang1706' )
  39.    5  -  filter ( "A" . "AMT_YM" = '201701' )




  44. Statistics
  45. ----------------------------------------------------------
  46.    1 recursive calls
  47.    0 db block gets
  48.  250 consistent gets
  49.    0 physical reads
  50.    0 redo  size
  51.  609 bytes sent via SQL * Net  to  client
  52.  523 bytes received via SQL * Net  from  client
  53.    2 SQL * Net roundtrips  to / from  client
  54.    0 sorts  ( memory )
  55.    0 sorts  ( disk )
  56.    1  rows  processed
語法解釋：
SQL> set autot
用法: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]
方括號內的字母都可以省略。
set autot on ----執行SQL 並且顯示執行計劃和統計資訊
set autot trace ----執行SQL 但不顯示執行結果，顯示執行計劃和統計資訊
set autot trace exp ----如果SELECT 就不執行SQL(dml 執行)，只顯示執行計劃
set autot trace stat ----執行SQL，只顯示統計資訊
結果解釋
1、DB Block Gets（當前請求的塊數目）
2、Consistent Gets（資料請求總數在回滾段Buffer中的資料一致性讀所需要的資料塊）
3、physical reads 物理讀——執行SQL的過程中，從硬碟上讀取的資料塊個數
4、redo size      重做數——執行SQL的過程中，產生的重做日誌的大小
5、bytes set via sql*net to client  透過sql*net傳送給客戶端的位元組數
6、bytes received via sql*net from client  透過sql*net接受客戶端的位元組數
7、sorts(memory)  在記憶體中發生的排序
8、sorts(disk)    不能在記憶體中發生的排序，需要硬碟來協助
9、rows processed 結果的記錄數 
  • 3. statistics_level=all
  1. set   line 200
  2. set  heading  off
  3. alter   session   set  statistics_level = all ;
  4. select  c . org_name , sum ( a . amt )   from  ht . c_cons c , ht . a_amt a
  5. where  c . cons_no = a . cons_no
  6. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  7. group   by  c . org_name ;
  8. select   *   from   table ( DBMS_XPLAN . DISPLAY_CURSOR ( null , null , 'allstats last' ) ) ;
  9. Plan hash  value :  1779151266


  12. ------------------------------------------------------------------------------------------------------------------------------------
  13. |  Id  |  Operation  ? ? ? ? ? ? ? ? ? ? ? |  Name  |  Starts  |  E - Rows   |  A - Rows   |  A - Time  |  Buffers  |  OMem  |  1Mem  |  Used - Mem  |
  14. ------------------------------------------------------------------------------------------------------------------------------------
  15. |  0  |   SELECT  STATEMENT  ? ? ? ? ? ? |   |  1  |   |  1  | 00 : 00 : 00 . 01  |  250  |   |   |   |
  16. |  1  |  HASH  GROUP   BY   ? ? ? ? ? ? ? |   |  1  |  1  |  1  | 00 : 00 : 00 . 01  |  250  |  1126K |  1126K |  499K  ( 0 ) |
  17. | *  2  |  HASH JOIN  ? ? ? ? ? ? ? ? ? |   |  1  |  3  |  1  | 00 : 00 : 00 . 01  |  250  |  1483K |  1483K |  679K  ( 0 ) |
  18. |  3  |   TABLE   ACCESS   BY   INDEX   ROWID ? |  C_CONS  |  1  |  2  |  1  | 00 : 00 : 00 . 01  |  3  |   |   |   |
  19. | *  4  |   INDEX  RANGE SCAN  ? ? ? ? |  IDX_CONS_NAME  |  1  |  2  |  1  | 00 : 00 : 00 . 01  |  2  |   |   |   |
  20. | *  5  |   TABLE   ACCESS  FULL  ? ? ? ? |  A_AMT  |  1  |  10364  |  9998  | 00 : 00 : 00 . 01  |  247  |   |   |   |
  21. ------------------------------------------------------------------------------------------------------------------------------------


  24. Predicate Information  ( identified   by  operation id ) :
  25. ---------------------------------------------------


  28.    2  -   access ( "C" . "CONS_NO" = "A" . "CONS_NO" )
  29.    4  -   access ( "C" . "CONS_NAME" = 'wang1706' )
  30.    5  -  filter ( "A" . "AMT_YM" = '201701' )
結果解釋：
Starts表示這個操作執行了幾次
E-Rows表示最佳化器估算的行數
A-Rows 表示實際的行數
A-Time 表示這個操作執行的時間(累加的)
Buffers 表示邏輯度(累加的)
  • 4. 透過(dbms_xplan.display_cursor/dbms_xplan.display_awr)輸入sql_id引數直接獲取
select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));
  • 5、awrsqrpt.sql
SQL> @?/rdbms/admin/awrsqrpt.sql
Enter value for begin_snap: 110
Begin Snapshot Id specified: 110
Enter value for end_snap: 112
End   Snapshot Id specified: 112
Specify the SQL Id
~~~~~~~~~~~~~~~~~~
Enter value for sql_id: 
  • 6. 10046/10053 trace跟蹤
  1. oradebug setmypid
  2. oradebug unlimit
  3. select  c . org_name , sum ( a . amt )   from  ht . c_cons c , ht . a_amt a
  4. where  c . cons_no = a . cons_no
  5. and  a . amt_ym = '201701'   and  c . cons_name = 'wang1706'
  6. group   by  c . org_name ;
  7. oradebug session_event 10046 trace name context forever  , level  12
  8. oradebug event 10046 trace name context  off
  9. oradebug tracefile_name
  10. [ oracle @ sndb  ~ ] $  tkprof  / u01 / app / oracle / diag / rdbms / sndb1 / sndb1 / trace / sndb1_ora_29017 . trc  / home / oracle / sndb1_ora_29017 . trc
  11. call  count  cpu elapsed disk query  current   rows
  12. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  13. Parse 1 0 . 00 0 . 00 0 0 0 0
  14. Execute  1 0 . 00 0 . 00 0 0 0 0
  15. Fetch 2 0 . 02 0 . 02 0 250 0 1
  16. ------- ------ -------- ---------- ---------- ---------- ---------- ----------
  17. total 4 0 . 02 0 . 02 0 250 0 1


  20. Misses  in  library cache during parse :  0
  21. Optimizer  mode :  ALL_ROWS
  22. Parsing  user  id :  SYS
  23. Number   of  plan statistics captured :  1


  26. Rows   ( 1st )   Rows   ( avg )   Rows   ( max )   Row  Source Operation
  27. ---------- ---------- ---------- ---------------------------------------------------
  28.          1 1 1 HASH  GROUP   BY   ( cr = 250 pr = 0 pw = 0 time = 24310 us cost = 72  size = 39 card = 1 )
  29.          1 1 1 HASH JOIN  ( cr = 250 pr = 0 pw = 0 time = 23827 us cost = 71  size = 117 card = 3 )
  30.          1 1 1  TABLE   ACCESS   BY   INDEX   ROWID  C_CONS  ( cr = 3 pr = 0 pw = 0 time = 113 us cost = 3  size = 44 card = 2 )
  31.          1 1 1  INDEX  RANGE SCAN IDX_CONS_NAME  ( cr = 2 pr = 0 pw = 0 time = 91 us cost = 1  size = 0 card = 2 ) ( object  id 87459 )
  32.       9998 9998 9998  TABLE   ACCESS  FULL A_AMT  ( cr = 247 pr = 0 pw = 0 time = 11812 us cost = 68  size = 176188 card = 10364 )




  37. Elapsed times include waiting  on  following events :
  38.   Event waited  on  Times  Max .  Wait Total Waited
  39.    ---------------------------------------- Waited ---------- ------------
  40.   SQL * Net message  to  client 2 0 . 00 0 . 00
  41.   SQL * Net message  from  client 2 0 . 00 0 . 00
  • 7. v$sql_plan_statistics_all
  1. select   '| Operation |Object Name | Rows | Bytes| Cost |'
  2. as   "Explain Plan in library cache:"   from  dual
  3. union   all
  4. select   rpad ( '| ' | | substr ( lpad ( ' ' , 1 * ( depth - 1 ) ) | | operation | |
  5.         decode ( options ,   null , '' , ' ' | | options ) ,  1 ,  35 ) ,  36 ,   ' ' ) | | '|' | |
  6.         rpad ( decode ( id ,  0 ,   '----------------------------' ,
  7.         substr ( decode ( substr ( object_name ,  1 ,  7 ) ,   'SYS_LE_' ,   null ,  object_name )
  8.         | | ' ' , 1 ,  30 ) ) ,  31 ,   ' ' ) | | '|' | |   lpad ( decode ( cardinality , null , ' ' ,
  9.         decode ( sign ( cardinality - 1000 ) ,   - 1 ,  cardinality | | ' ' ,
  10.         decode ( sign ( cardinality - 1000000 ) ,   - 1 ,   trunc ( cardinality / 1000 ) | | 'K' ,
  11.         decode ( sign ( cardinality - 1000000000 ) ,   - 1 ,   trunc ( cardinality / 1000000 ) | | 'M' ,
  12.         trunc ( cardinality / 1000000000 ) | | 'G' ) ) ) ) ,  7 ,   ' ' )   | |   '|'   | |
  13.         lpad ( decode ( bytes , null , ' ' ,
  14.         decode ( sign ( bytes - 1024 ) ,   - 1 ,  bytes | | ' ' ,
  15.         decode ( sign ( bytes - 1048576 ) ,   - 1 ,   trunc ( bytes / 1024 ) | | 'K' ,
  16.         decode ( sign ( bytes - 1073741824 ) ,   - 1 ,   trunc ( bytes / 1048576 ) | | 'M' ,
  17.         trunc ( bytes / 1073741824 ) | | 'G' ) ) ) ) ,  6 ,   ' ' )   | |   '|'   | |
  18.         lpad ( decode ( cost , null , ' ' ,   decode ( sign ( cost - 10000000 ) ,   - 1 ,  cost | | ' ' ,
  19.         decode ( sign ( cost - 1000000000 ) ,   - 1 ,   trunc ( cost / 1000000 ) | | 'M' ,
  20.         trunc ( cost / 1000000000 ) | | 'G' ) ) ) ,  8 ,   ' ' )   | |   '|'   as   "Explain plan"
  21.    from  v$sql_plan_statistics_all sp
  22.   where  sp . hash_value = & hash_value ;

3、適用場景

1、sql執行時間較短：statistics_level=all;
2、sql執行時間較長：explain plan for獲取
3、獲取實際執行的執行計劃：透過(dbms_xplan.display_cursor/dbms_xplan.display_awr)輸入sql_id引數直接獲取
4、獲取一段時間內實際執行情況：awrsqrpt.sql


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

相關文章