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博客

收集full table / index scan sql

最新文章