#!/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
ÀÌ·¸°Ô ·çÇÁ ¾ÈÀÇ ¼ø¼´ë·Î ÂïÈ÷Áö°¡ ¾Ê°í
¹¶ÅÖÀÌ·Î ÂïÈû;;;
½ÇÇà ´ç½Ã¿¡ ¹¶ÅÖÀÌ·Î ½ÇÇàµÈ °Ç ¾Æ´Ñ°Ô, °¢±â ´Ù¸¥ ¼öÇà½Ã°£µéÀÌ ÂïÈ÷°í ÀÖÀ½
±Ùµ¥ ¿Ö ¾Æ¿ôDzÀÌ ¹¶Ãļ ³ª¿À´Â Áö¸¦ ¸ð¸£°Ú³×È¿
S/W | 1739¸íÀÌ Àоú¾î¿ä. 18.226.248.252