; Oracle9i 表分析(包含INDEX)的分析脚本，在AIX5.2(ksh)上通过.
####filename:get_ana_sql.sql
set feedback; off
set echo;;;off
set headingoff
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
whereowner='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 -efgrep ${OSNAME}grep sqlplusgrep ${user}grep -v grepwc -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
##############################
