v$sql_plan中存放已经执行过但仍在库中的sql执行计划。
查库中全表扫描的sql:
select SQL_FULLTEXT from v$sqlarea where SQL_ID in (
select distinct sql_id from v$sql_plan where peration='TABLE ACCESS' and ptions='FULL');
查库中前500个大表中全表扫描的sql:
select SQL_FULLTEXT
from v$sqlarea
where SQL_ID in
(select distinct sql_id
from v$sql_plan
where peration = 'TABLE ACCESS'
and ptions = 'FULL'
and object_name in (select *
from (select TABLE_NAME
from dba_tables
where owner in ('USER1', 'USER2')
order by blocks DESC)
where rownum <= 500));
SQL> desc v$sql_plan
Name Type Nullable Default Comments
----------------- -------------- -------- ------- --------
ADDRESS RAW(8) Y
HASH_VALUE NUMBER Y
SQL_ID VARCHAR2(13) Y
PLAN_HASH_VALUE NUMBER Y
CHILD_ADDRESS RAW(8) Y
CHILD_NUMBER NUMBER Y
TIMESTAMP DATE Y
OPERATION VARCHAR2(30) Y
OPTIONS VARCHAR2(30) Y
OBJECT_NODE VARCHAR2(40) Y
OBJECT# NUMBER Y
OBJECT_OWNER VARCHAR2(30) Y
OBJECT_NAME VARCHAR2(30) Y
OBJECT_ALIAS VARCHAR2(65) Y
OBJECT_TYPE VARCHAR2(20) Y
OPTIMIZER VARCHAR2(20) Y
ID NUMBER Y
PARENT_ID NUMBER Y
DEPTH NUMBER Y
POSITION NUMBER Y
SEARCH_COLUMNS NUMBER Y
COST NUMBER Y
CARDINALITY NUMBER Y
BYTES NUMBER Y
OTHER_TAG VARCHAR2(35) Y
PARTITION_START VARCHAR2(5) Y
PARTITION_STOP VARCHAR2(5) Y
PARTITION_ID NUMBER Y
OTHER VARCHAR2(4000) Y
DISTRIBUTION VARCHAR2(20) Y
CPU_COST NUMBER Y
IO_COST NUMBER Y
TEMP_SPACE NUMBER Y
ACCESS_PREDICATES VARCHAR2(4000) Y
FILTER_PREDICATES VARCHAR2(4000) Y
PROJECTION VARCHAR2(4000) Y
TIME NUMBER Y
QBLOCK_NAME VARCHAR2(30) Y
REMARKS VARCHAR2(4000) Y
OTHER_XML CLOB Y
http://hi.baidu.com/dba_gongchang/item/01363c1037dbf48999ce333a