ORACLE9i 表分析脚本

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]${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]${user}/${passwd}@${server} >>./log/ana.log &

echo ${line} |nohup sqlplus {user}/${passwd}@${server]${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

##############################