ORACLE9i 表分析脚本

; Oracle9i 表分析(包含INDEX)的分析脚本，在AIX5.2(ksh)上通过.####filename:get_ana_sql.sqlset feedback; off set echo;;;off set headingoffset pagesize; 0 set linesize; 200set 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_tableswhereowner='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=systempasswd=managerserver=OSSDBproc_num=40sleep_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 donedone}##############################daterm -f all_analyze.sqlrm -f ./log/*.logcrt_ana_sqlatp_run_anadate##############################