网上找了一个SQL挺好用的特此记录如下:
---------------------------------------------------------
set linesize 132
break on hash_value skip 1 dup
col child_number format 9999 heading 'CHILD'
col operation format a55
col cost format 99999
col kbytes format 999999
col object format a25
select hash_value,
child_number,
lpad(' ',2*depth)||operation||' '||options||decode(id, 0, substr(optimizer,1,6)||' Cost='||to_char(cost)) operation,
object_name object,
cost,
cardinality,
round(bytes / 1024) kbytes
from v$sql_plan
where hash_value in (select a.sql_hash_value
from v$session a, v$session_wait b
where a.sid = b.sid
and b.event = '&waitevent')
order by hash_value, child_number, id;
这样我们可以根据等待事件来获得具体SQl的执行计划,在runtime的时候用比较方便。
以下是我生产环境里的一个输出计划:
HASH_VALUE CHILD OPERATION OBJECT COST CARDINALITY KBYTES
---------- ----- ------------------------------------------------------- ------------------------- ------ ----------- -------
3267427106 0 UPDATE STATEMENT ALL_RO Cost=7231 7231
3267427106 0 UPDATE TS_FLPLNCMPNT
3267427106 0 TABLE ACCESS FULL TS_FLPLNCMPNT 7231 1 0
那么如果我想要查一下当前全表扫描的表都有那些,怎么查呢?
select distinct object_name,object_owner from v$sql_plan p
where p.operation='TABLE ACCESS' and p.options='FULL'
and object_owner='&schema_owner';
这样我们可以通过查询v$sql_plan指定schema_owner来获得那些进行过full table scan的表
如何获得全索引扫描的对象呢?
select distinct object_name,object_owner from v$sql_plan p
where p.operation='INDEX' and p.options='FULL SCAN'
and object_owner='&schema_owner';
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/12361284/viewspace-150441/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/12361284/viewspace-150441/