通过等待事件来获得查询SQl的执行计划

网上找了一个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';