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_ROCost = 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查询执行计划
链接分享：http://pwwzsj.com/article/iispjj.html
