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

透過以下指令碼可以查詢庫快取中已經執行過的SQL的執行計劃

col "Explain Plan" for a100

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

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

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

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

相關文章