ORACLE9i 表分析脚本
####filename:get_ana_sql.sql
set feedback off
set echo off
set heading off
set pagesize 0
set linesize 200
set sqlprompt ""
--oracle 默认比例: ESTIMATE_PERCENT=>dbms_stats.auto_sample_size
--一般情况 20-40 即可: ESTIMATE_PERCENT=>20
--同时分析指定表的索引: cascade=>true
--也可以使用 dbms_stats.gether_index_stats 分析index
spool all_analyze.sql
select 'exec dbms_stats.GATHER_TABLE_STATS('||''''||owner||''''
||','||''''||table_name||''''
||',ESTIMATE_PERCENT=>dbms_stats.auto_sample_size'
||',cascade=>true);'
from dba_tables
where owner='OSS'
--and PARTITIONED='YES'
--and (NUM_ROWS<1000 or last_analyzed is null)
--and rownum<10
--and 1=2
/
spool off
####filename:run_analyze.sh
user=system
passwd=manager
server=OSSDB
proc_num=40
sleep_sec=10
crt_ana_sql()
{
sqlplus ${user}/${passwd}@${server} <<!
@get_ana_sql.sql
!
}
atp_run_ana()
{
OSNAME=`whoami`
cat all_analyze.sql |while read line
do
#echo ${line}
echo ${line}|awk -F"'" '{print $4}'|read table_name
echo ${table_name}
NEXT=1
while [ ${NEXT} -eq 1 ]
do
COUNT=`ps -ef|grep ${OSNAME}|grep sqlplus|grep ${user}|grep -v grep|wc -l`
if [ ${COUNT} -gt ${proc_num} ]
then
echo "sleep" ${sleep_sec} "seconds ..."
sleep ${sleep_sec}
else
#echo ${line} |nohup sqlplus ${user}/${passwd}@${server} >>./log/ana.log &
echo ${line} |nohup sqlplus ${user}/${passwd}@${server} >./log/${table_name}.log &
NEXT=0
fi
done
done
}
##############################
date
rm -f all_analyze.sql
rm -f ./log/*.log
crt_ana_sql
atp_run_ana
date
##############################
