收集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