ORACLE9i 表分析脚本
2024-08-29 13:49:39
供稿：网友
Oracle9i 表分析(包含INDEX)的分析脚本，在AIX5.2(ksh)上通过.####filename:get_ana_sql.sqlset 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 分析indexspool all_analyze.sqlselect '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.shuser=system
passwd=manager
server=OSSDB
proc_num=40
sleep_sec=10crt_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}##############################daterm -f all_analyze.sql
rm -f ./log/*.logcrt_ana_sqlatp_run_anadate##############################
