ORACLE9i 表分析脚本

Oracle9i 表分析(包含INDEX)的分析脚本，在AIX5.2(ksh)上通过. ####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 -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 ##############################