#!/usr/bin/ksh
#////////////////////////////////////////////////////////////////////////////////
#// gatherStats.sh
#//
#// run DBMS_STATS.GATHER_TABLE_STATS on desired tables
#//
#// input : void
#// output : void
#// history
#// 130122 newly created by in0de
logPath="/DBA/LOG/IMP_GatherStats"
logFile="IMP_GatherStats_$(date +"%y%m%d")"
oracleSID="SID"
oracleUser="scott"
oraclePasswd="tiger"
statsOption="estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''FOR ALL INDEXED COLUMNS SIZE AUTO'', degree=>8, cascade=>TRUE, no_invalidate=>FALSE"
statsPredicate="owner='SCHEMA_OWNER'"
strTime=$(date +"%Y-%m-%d %H:%M:%S")
statsTables=$(export ORACLE_SID=$oracleSID;sqlplus -s $oracleUser/$oraclePasswd <<EOF
SET PAGESIZE 50000 LINES 32767 FEEDBACK OFF VERIFY OFF HEADING OFF ECHO OFF
select 'exec DBMS_STATS.GATHER_TABLE_STATS('''||owner||''','''|| table_name|| ''', $statsOption);' from dba_tables;'
EXIT;
EOF
)
if [ -z $statsTables ]; then
echo "No target tables are found." >> $logPath/$logFile
exit 0
else
IFS=; #-- set the delimiter to a semicolon
print "Started : $(date +"%Y-%m-%d %H:%M:%S")\t\c" >> $logPath/$logFile
for statsTableCommand in $statsTables
do
print "$statsTableCommand" >> $logPath/$logFile
retElapsed=$(time $ORACLE_HOME/bin/sqlplus -s /nolog << EOF
CONNECT $oracleUser/$oraclePasswd
SET PAGESIZE 50000 LINES 32767 FEEDBACK OFF ECHO OFF VERIFY OFF HEADING OFF TIME ON TIMING ON
$statsTableCommand
EXIT;
EOF
)
print "$retElapsed\t$statsTableCommand\c" >> $logPath/$logFile
done
print "\nFinished : $(date +"%Y-%m-%d %H:%M:%S")\n" >> $logPath/$logFile
print >> $logPath/$logFile
fi
이거를 실행하면 예상하는 결과는
Started : 2013-01-22 15:00:14
Elapsed: 00:00:18.15 exec DBMS_STATS.GATHER_TABLE_STATS(...);
Elapsed: 00:00:05.16 exec DBMS_STATS.GATHER_TABLE_STATS(...);
Elapsed: 00:00:01.79 exec DBMS_STATS.GATHER_TABLE_STATS(...);
Finished : 2013-01-22 15:00:39
이런 식이어야 하는데
Started : 2013-01-22 16:16:46 Elapsed: 00:00:19.40
Elapsed: 00:00:08.52
Elapsed: 00:00:04.02
exec DBMS_STATS.GATHER_TABLE_STATS(...);
exec DBMS_STATS.GATHER_TABLE_STATS(...);
exec DBMS_STATS.GATHER_TABLE_STATS(...);
Finished : 2013-01-22 16:17:19
이렇게 루프 안의 순서대로 찍히지가 않고
뭉텅이로 찍힘;;;
실행 당시에 뭉텅이로 실행된 건 아닌게, 각기 다른 수행시간들이 찍히고 있음
근데 왜 아웃풋이 뭉쳐서 나오는 지를 모르겠네효
S/W | 1725명이 읽었어요. 18.188.152.162
