在分析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
