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 -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 ##############################
|