全索引扫描的SQL语句

以下查找出全索引扫描的SQL语句
select t.address,
t.hash_value,
t.sql_id,
p.child_number,
t.piece,
sql_text,
p.object_name,
p.operation,
p.options ,
p.object_owner
from v$sqltext t, v$sql_plan p
where t.hash_value = p.hash_value
and p.operation = 'INDEX'
and p.options = 'FULL SCAN'
order by t.address, t.hash_value, t.sql_id, p.child_number, t.piece;


select t.*,p.* 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;

 

以下查找出全表扫描的SQL语句
select t.address,
t.hash_value,
t.sql_id,
p.child_number,
t.piece,
sql_text,
p.object_name,
p.operation,
p.options ,
p.object_owner
from v$sqltext t, v$sql_plan p
where t.hash_value = p.hash_value
and p.operation = 'TABLE ACCESS'
and p.options = 'FULL'

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


--以下通过等待事件查询执行计划
--输入 waitevent 的值: enq: TX - row lock contention
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;


--所有会话的当前等待事件可以通过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'));

通过以下脚本可以查询库缓存中已经运行过的SQL的执行计划 （
输入 sql_id 的值:? a08vqym1n4k5n
）

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';