SQL性能的度量 - 通过v$sql_plan查询执行计划

2025-04-13 技术教程

在分析SQL语句性能时，视图v$sql_plan很有用，以下分几点举例说明。

通过以下脚本可以查询库缓存中已经运行过的SQL的执行计划

col"Explain Plan" for a100

as "ExplainPlan" 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 WHERET.LINE_ID = :B1 AND T.WIP_AGV_ID R_WIP_STATION_T

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

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

000007FF5567C198 580882336 cwmfwqcj9z3x0 0 3= :B1 ) R_WIP_STATION_T

000007FF5590F7102960126118 dtsrgu6s6zw562 0 UPDATERMES.R_WIP_STATION_T SET GROUP_ID = :B7 , STATION_ID = :B R_WIP_STATION_T

000007FF5590F7102960126118 dtsrgu6s6zw562 1 6 , WIP_EMP_ID = :B5, TEAM_ID = :B4 , SHIFT_ID = :B3 , ERROR_FL R_WIP_STATION_T

000007FF5590F7102960126118 dtsrgu6s6zw562 2 AG = '0',IN_LINE_TIME = :B2 , OUT_LINE_TIME = :B2 , IN_STATION_ R_WIP_STATION_T

000007FF5590F7102960126118 dtsrgu6s6zw562 3 TIME = SYSDATE,OUTLINE_FLAG = '1', HAD_OUTLINED = '1' WHERE WIP R_WIP_STATION_T

000007FF5590F7102960126118 dtsrgu6s6zw562 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

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

000007FF55FC55E81305393110 b4ysx616wxdyq0 0 selectpd.force,pd.position from rmes.r_press_t p,rmes.r_press_dIDX_PK_PRESS_DETAIL_ID

000007FF55FC55E81305393110 b4ysx616wxdyq0 1 etail_t pd wherep.press_id=pd.press_id and p.press_id = -1 ORDE IDX_PK_PRESS_DETAIL_ID

000007FF55FC55E81305393110 b4ysx616wxdyq0 2 R BYPD.PRESS_DETAIL_ID ASC IDX_PK_PRESS_DETAIL_ID

以下通过等待事件查询执行计划

col operationfor 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$sessionwhere event = '&waitevent')

order by hash_value, child_number, id;

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

HASH_VALUECHILD_NUMBER OPERATIONOBJECT_NAME COST KBYTES

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

2186674966 0 SELECT STATEMENT ALL_RO Cost = 33

2186674966 0FOR UPDATE

2186674966 0 BUFFER SORT

2186674966 0 TABLE ACCESS FULL C_LINE_T 3 1

而所有会话的当前等待事件可以通过v$session_wait查询

col usernamefor a10

col event fora60

selectsw.sid, s.serial#, s.username, sw.event, sw.wait_time,sw.state from v$session s, v$session_wait sw wheresw.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_waitwhere event = '&waitwvent'));

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

SQL_TEXT

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

select * fromcmes.c_line_t for update