【转】收集全表扫描语句

1、收集full table / index scan sql

1. 子程序

#!/bin/ksh

sqlplus -S /nolog <  connect sys/xxxxxx as sysdba;
 col sql_text format a81
 set lines 1000
 set pages 1000
 set verify off
select t.* from v\$sqlarea t, v\$sql_plan p
 where t.hash_value=p.hash_value and p.operation='TABLE ACCESS'
   and p.options='FULL';
  
select sql_text from v\$sqlarea t, v\$sql_plan p
 where t.hash_value=p.hash_value and p.operation='INDEX'
   and p.options='FULL SCAN';  

exit;
EOF

 

2. monitor

#!/usr/bin/ksh
echo "begin seach full table scan" > ./fullscansql.log
while [ 1 -lt 2 ]
do

sleep 300
./getfullscansql.sh >> ./fullscansql.log

done

还可以对sql_plan的timestamp和object_owner加入限制条件

p.TIMESTAMP > to_date('2009-03-18 16:00:00','yyyy-mm-dd hh24:mi:ss')
p.object_owner in ('schema_names...')

http://blog.sina.com.cn/s/blog_538040b70100eect.html

2、查库中全表扫描的sql

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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/438414/viewspace-764811/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/438414/viewspace-764811/