收集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...')

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

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

转载于:http://blog.itpub.net/90618/viewspace-670384/