SQL性能的度量 - 通過v$sql_plan查詢執行計劃

在分析SQL語句性能時，視圖v$sql_plan很有用，以下分幾點舉例說明。

通過以下腳本可以查詢庫緩存中已經運行過的SQL的執行計劃

col "Explain Plan" for a100

select ' ' as "No.", '| Operation                         |Object Name                    |  Rows | Bytes|   Cost |'

as "Explain Plan" from dual

union all

select to_char(id, '999'), rpad('| ' || substr(lpad(' ', 1*(depth-1)) || operation ||

       decode(options, null, ' ' || substr(optimizer, 1, 7), ' ' || options), 1, 35), 36, ' ') || '|' ||

       rpad(decode(id, 0, '                            ',

       substr(decode(substr(object_name, 1, 7), 'SYS_LE_', null, object_name)

       || ' ', 1, 30)), 31, ' ') || '|' || lpad(decode(cardinality, null, '  ',

       decode(sign(cardinality - 1000), -1, cardinality || ' ',

       decode(sign(cardinality - 1000000), -1, trunc(cardinality / 1000) || 'K',

       decode(sign(cardinality - 1000000000), -1, trunc(cardinality / 1000000) || 'M',

       trunc(cardinality / 1000000000) || 'G')))), 7, ' ') || '|' ||

       lpad(decode(bytes, null, ' ',

       decode(sign(bytes - 1024), -1, bytes || ' ',

       decode(sign(bytes - 1048576), -1, trunc(bytes / 1024) || 'K',

       decode(sign(bytes - 1073741824), -1, trunc(bytes / 1048576) || 'M',

       trunc(bytes / 1073741824) || 'G')))), 6, ' ') || '|' ||

       lpad(decode(cost, null, ' ', decode(sign(cost - 10000000), -1, cost || ' ',

       decode(sign(cost - 1000000000), -1, trunc(cost / 1000000) || 'M',

       trunc(cost / 1000000000) || 'G'))), 8, ' ') || '|'

  from v$sql_plan sp

 where sp.sql_id = '&sql_id';

輸入 sql_id 的值:  a08vqym1n4k5n

No.  Explain Plan

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

     | Operation                         |Object Name                    |  Rows | Bytes|   Cost |

   0 | SELECT STATEMENT ALL_ROW          |                               |       |      |      2 |

   1 | NESTED LOOPS                      |                               |     1 |  821 |      2 |

   2 |  NESTED LOOPS                     |                               |     1 |  808 |      2 |

   3 |   HASH JOIN                       |                               |     1 |  782 |      2 |

   4 |    NESTED LOOPS                   |                               |     1 |  736 |      1 |

   5 |     NESTED LOOPS                  |                               |     1 |  718 |      0 |

   6 |      FIXED TABLE FULL             |X$KSUSE                        |     1 |  182 |      0 |

   7 |      FIXED TABLE FIXED INDEX      |X$KGLCURSOR_CHILD_SQLID (ind:2 |     1 |  536 |      0 |

   8 |     INDEX RANGE SCAN              |I_AUDIT_ACTIONS                |     1 |   18 |      1 |

   9 |    FIXED TABLE FULL               |X$KSUPR                        |     1 |   46 |      0 |

  10 |   FIXED TABLE FIXED INDEX         |X$KSLWT (ind:1)                |     1 |   26 |      0 |

  11 |  FIXED TABLE FIXED INDEX          |X$KSLED (ind:2)                |     1 |   13 |      0 |

結合v$sqltext可以快速找到庫緩存中執行全表掃描或全索引掃描的SQL語句

以下查找出全表掃描的SQL語句

select t.address,

       t.hash_value,

       t.sql_id,

       p.child_number,

       t.piece,

       sql_text,

       p.object_name

  from v$sqltext t, v$sql_plan p

 where t.hash_value = p.hash_value

   and p.operation = 'TABLE ACCESS'

   and p.options = 'FULL'

   and p.object_owner = 'RMES'

 order by t.address, t.hash_value, t.sql_id, p.child_number, t.piece;

ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER      PIECE SQL_TEXT                                                         OBJECT_NAME

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

000007FF5567C198  580882336 cwmfwqcj9z3x0            0          0 DELETE RMES.R_WIP_AGV_T T WHERE T.LINE_ID = :B1 AND T.WIP_AGV_ID R_WIP_STATION_T

000007FF5567C198  580882336 cwmfwqcj9z3x0            0          1  NOT IN (SELECT T1.WIP_AGV_ID FROM RMES.R_WIP_STATION_T T1 WHERE R_WIP_STATION_T

000007FF5567C198  580882336 cwmfwqcj9z3x0            0          2  T1.WIP_AGV_ID IS NOT NULL AND T1.WIP_AGV_ID <> 0 AND T1.LINE_ID R_WIP_STATION_T

000007FF5567C198  580882336 cwmfwqcj9z3x0            0          3  = :B1 )                                                         R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56            2          0 UPDATE RMES.R_WIP_STATION_T SET GROUP_ID = :B7 , STATION_ID = :B R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56            2          1 6 , WIP_EMP_ID = :B5 , TEAM_ID = :B4 , SHIFT_ID = :B3 , ERROR_FL R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56            2          2 AG = '0', IN_LINE_TIME = :B2 , OUT_LINE_TIME = :B2 , IN_STATION_ R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56            2          3 TIME = SYSDATE, OUTLINE_FLAG = '1', HAD_OUTLINED = '1' WHERE WIP R_WIP_STATION_T

000007FF5590F710 2960126118 dtsrgu6s6zw56            2          4 _TRACKING_ID = :B1                                               R_WIP_STATION_T

以下查找出全索引掃描的SQL語句

select t.address,

       t.hash_value,

       t.sql_id,

       p.child_number,

       t.piece,

       sql_text,

       p.object_name

  from v$sqltext t, v$sql_plan p

 where t.hash_value = p.hash_value

   and p.operation = 'INDEX'

   and p.options = 'FULL SCAN'

   and p.object_owner = 'RMES'

 order by t.address, t.hash_value, t.sql_id, p.child_number, t.piece;

ADDRESS          HASH_VALUE SQL_ID        CHILD_NUMBER      PIECE SQL_TEXT                                                         OBJECT_NAME

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

000007FF55FC55E8 1305393110 b4ysx616wxdyq            0          0 select pd.force,pd.position from rmes.r_press_t p,rmes.r_press_d IDX_PK_PRESS_DETAIL_ID

000007FF55FC55E8 1305393110 b4ysx616wxdyq            0          1 etail_t pd where p.press_id=pd.press_id and p.press_id = -1 ORDE IDX_PK_PRESS_DETAIL_ID

000007FF55FC55E8 1305393110 b4ysx616wxdyq            0          2 R BY PD.PRESS_DETAIL_ID ASC                                      IDX_PK_PRESS_DETAIL_ID

以下通過等待事件查詢執行計劃

col operation for a50

select hash_value,

       child_number,

       lpad(' ', 2 * depth) || operation || ' ' || options ||

       decode(id, 0, substr(optimizer, 1, 6) || ' Cost = ' || to_char(cost)) operation,

       object_name,

       cost,

       round(bytes / 1024) kbytes

  from v$sql_plan

 where hash_value in

       (select sql_hash_value from v$session where event = '&waitevent')

 order by hash_value, child_number, id;

輸入 waitevent 的值:  enq: TX - row lock contention

HASH_VALUE CHILD_NUMBER OPERATION                                          OBJECT_NAME                    COST       KBYTES

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

2186674966            0 SELECT STATEMENT ALL_RO Cost = 3                                                   3

2186674966            0   FOR UPDATE

2186674966            0     BUFFER SORT

2186674966            0       TABLE ACCESS FULL                            C_LINE_T                        3          1

而所有會話的當前等待事件可以通過v$session_wait查詢

col username for a10

col event for a60

select sw.sid, s.serial#, s.username, sw.event, sw.wait_time, sw.state from v$session s, v$session_wait sw where sw.event not like 'rdbms%' and sw.sid = s.sid;

最后可以通過等待事件找到對應的SQL語句

select sql_text

  from v$sql

 where sql_id =

       (select sql_id

          from v$session

         where sid =

               (select sid from v$session_wait where event = '&waitwvent'));

輸入 waitwvent 的值:  enq: TX - row lock contention

SQL_TEXT

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

select * from cmes.c_line_t for update