We need to collect system statistics for our Oracle production databases.
As I have studied this, I see there are actually 3 categories of these statistics – dictionary statistics, fixed-object statistics, and system (hardware) statistics.
The first two are recommended for all applications, while the E-business Suite specifically does not want the system (hardware) statistics.
I don’t know what specific procedure or commands that our previous DBAs used to collect these statistics. When I read their workplans in our change-tracking application, all they bothered to say was “1) Save old statistics, and 2) collect new ones.” Sorry, that is just not specific enough for me. Nor I have never noticed that we already have an official script to collect these statistics.
So I wrote the following script, which makes sense to me.
It produces the output shown below, which is about as safe and transparent as would seem worthwhile.
At least, this is what works for me, here, now. No promises it will do anything for you. In fact this is probably WRONG for you in at least 1 way, probably more! Perhaps it will give you some ideas. If so, you are welcome to them.
Nevertheless, “You can’t beat something with nothing.”, and this is a more specific and more recoverable than whatever secret procedure we used in the past.
Let me know if I can talk about that more.
And best wishes to always have accurate statistics.
~the hard-working DBA
> update_system_stats.ksh -i ORACAT20
Thu Jul 21 12:56:54 MDT 2011: Starting.
SQL*Plus: Release 10.2.0.4.0 - Production on Thu Jul 21 12:56:56 2011
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
SQL> Connected.
SQL> SQL> exec dbms_stats.create_stat_table ('SYSTEM', 'ENC_DICT_STATS_20110721125654');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.create_stat_table ('SYSTEM', 'ENC_FIXED_STATS_20110721125654');
PL/SQL procedure successfully completed.
SQL> --
SQL> alter table ENC_DICT_STATS_20110721125654 move tablespace tools;
Table altered.
SQL> alter table ENC_FIXED_STATS_20110721125654 move tablespace tools;
Table altered.
SQL> --
SQL> alter index ENC_DICT_STATS_20110721125654 rebuild tablespace tools;
Index altered.
SQL> alter index ENC_FIXED_STATS_20110721125654 rebuild tablespace tools;
Index altered.
SQL> --
SQL> exec dbms_stats.export_dictionary_stats ( statown=>'SYSTEM', stattab=>'ENC_DICT_STATS_20110721125654');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.export_fixed_objects_stats ( statown=>'SYSTEM', stattab=>'ENC_FIXED_STATS_20110721125654');
PL/SQL procedure successfully completed.
SQL> --
SQL> exec dbms_stats.gather_dictionary_stats ();
PL/SQL procedure successfully completed.
SQL> --
SQL> exec dbms_stats.gather_fixed_objects_stats;
PL/SQL procedure successfully completed.
SQL>
SQL> exec dbms_stats.create_stat_table ('SYSTEM', 'ENC_SYS_STATS_20110721125654');
PL/SQL procedure successfully completed.
SQL> alter table ENC_SYS_STATS_20110721125654 move tablespace tools;
Table altered.
SQL> alter index ENC_SYS_STATS_20110721125654 rebuild tablespace tools;
Index altered.
SQL> exec dbms_stats.export_system_stats ( statown=>'SYSTEM', stattab=>'ENC_SYS_STATS_20110721125654');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_system_stats ( gathering_mode=>'INTERVAL', interval=>60);
PL/SQL procedure successfully completed.
SQL>
SQL> set echo off
The following Stats tables are available to restore from, if necessary.
TABLE_NAME
------------------------------
ENC_SYS_STATS_20110721125654
ENC_FIXED_STATS_20110721125654
ENC_DICT_STATS_20110721125654
3 rows selected.
Here are the system statistics in effect now.
SNAME PNAME PVAL1 PVAL2
------------- ---------- ---------- -------------------
SYSSTATS_INFO STATUS AUTOGATHERING
SYSSTATS_INFO DSTART 07-21-2011 13:08
SYSSTATS_INFO DSTOP 07-21-2011 14:08
SYSSTATS_INFO FLAGS 0
SYSSTATS_MAIN CPUSPEEDNW 312.295633
SYSSTATS_MAIN IOSEEKTIM 10
SYSSTATS_MAIN IOTFRSPEED 4096
SYSSTATS_MAIN SREADTIM
SYSSTATS_MAIN MREADTIM
SYSSTATS_MAIN CPUSPEED
SYSSTATS_MAIN MBRC
SYSSTATS_MAIN MAXTHR
SYSSTATS_MAIN SLAVETHR
SYSSTATS_TEMP SBLKRDS 35062065
SYSSTATS_TEMP SBLKRDTIM 16253810
SYSSTATS_TEMP MBLKRDS 13777560
SYSSTATS_TEMP MBLKRDTIM 12537540
SYSSTATS_TEMP CPUCYCLES 50114736
SYSSTATS_TEMP CPUTIM 162174037
SYSSTATS_TEMP JOB 1
SYSSTATS_TEMP CACHE_JOB 2
SYSSTATS_TEMP MBRTOTAL 218243782
22 rows selected.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Thu Jul 21 13:08:16 MDT 2011: Done.
> cat update_system_stats.ksh
#!/bin/ksh
#======== ========= ========= ========= ========= ========= ========= ==========
# Purpose: See Usage explanation.
#
# See Oracle Support Node ID: 368252.1
# That explains, it is "mandatory" to gather statistics for the SYS schema.
# With 10g, the gather_dictionary_stats procedure will do that.
#
# Statistics on the fixed objects (V$ views) are collected using the
# gather_fixed_objects procedure. That is better than using the
# $APPL_TOP/admin/adstats.sql script, because that script requires the
# database to be in restricted mode, while defeats the whole purpose, because
# you want to collect statistics on the v$views while the database is
# running a typical workload.
#
# Finally it says, "The use of dbms_stats.gather_system_stats to collect
# system stats is not recommended with Apps E-Business Suite 11i or R12."
#
# This script should be run on all production databases about once per quarter
# while the database has a representative workload, say midweek, during
# midmorning or midafternoon.
#
# History
# 20110721 SWC Wrote it.
#======== ========= ========= ========= ========= ========= ========= ==========
usage ()
{
echo "
Usage: update_system_stats.ksh -i ORACLE_SID [-m]
This script will run the following procedures:
dbms_stats.gather_dictionary_stats,
dbms_stats.gather_fixed_stats,
and IFF the instance is not running E-Business Suite, it will also run
dbms_status.gather_system_stats.
The previous statistics will be exported to tables with a date-time suffix,
so if you decide you need to restore the old statistics, you can retrieve
them from there.
The script will use the ORACLE_SID to identify a file containing the
other variables that need to be set for this script. That file is
expected to be found in ~/scripts/env/$ORACLE_SID.env
-m an optional argument to e-mail the results, which might be
useful if you want to schedule this as a cronjob. So far
I anticipate this will be run as needed in the foreground.
The required variables in the env file are:
ORACLE_SID
the name of the Oracle instance to monitor.
ORACLE_HOME
where to find the Oracle software for this SID
PATH
where to find utilities used by this script.
MAIL_READER
who to notify in case the threshold is exceeded.
NUM_DAYS_TO_SAVE_LOG_FILES
will delete old logs older than the specified number of days.
Routine output will be saved in a subdirectory called logs.
The log file will be named update_system_stats.\$SID.\$DATE.log
Best wishes."
exit 1
}
#======== ========= ========= ========= ========= ========= ========= ==========
export MAIL_IT=0
# Parse the command line parameters.
if [ $# -eq 0 ]; then
usage
fi
while getopts m:i:h option
do
case $option in
i) SID=$OPTARG
;;
i) MAIL_IT=1
;;
h) usage
;;
\?) usage
;;
esac
done
#======== ========= ========= ========= ========= ========= ========= ==========
HOME=~
ENV_FILE=$HOME/scripts/env/$SID.env
if [ ! -f $ENV_FILE ]; then
echo "Error: ENV file not found."
echo "Expected to find $ENV_FILE."
exit 1
fi
. $ENV_FILE
if [ "$ORACLE_SID." = "." ]; then
echo "Error: ORACLE_SID is not set."
exit 2
fi
if [ "$ORACLE_HOME." = "." ]; then
echo "Error: ORACLE_HOME is not set."
exit 3
fi
if [ ! -d $ORACLE_HOME ]; then
echo "Error: ORACLE_HOME is not found."
echo "Expected to find directory $ORACLE_HOME"
exit 4
fi
if [ "$NUM_DAYS_TO_SAVE_LOG_FILES." = "." ]; then
echo "Error: NUM_DAYS_TO_SAVE_LOG_FILES is not set."
exit 8
fi
#======== ========= ========= ========= ========= ========= ========= ==========
echo "`date`: Starting."
DIR=`dirname $0`
cd $DIR
DIR=`pwd`
BASE=`basename $0 .ksh`
if [ ! -d $DIR/logs ]; then
mkdir $DIR/logs
# If the logs subdirectory doesn't exist, then create it
else
# remove old logs
find ./logs -name '*.log' -mtime +$NUM_DAYS_TO_SAVE_LOG_FILES -exec rm {} \;
find ./logs -name '*.sql' -mtime +$NUM_DAYS_TO_SAVE_LOG_FILES -exec rm {} \;
fi
DATE=`date +%Y%m%d%H%M%S`
SQL_FILE=$DIR/logs/$BASE.$DATE.sql
LOG_FILE=$DIR/logs/$BASE.$DATE.log
sqlplus -s /nolog <<-EOF >$LOG_FILE
connect system/$SYSTEM_PASSWORD@$ORACLE_SID
EOF
if [ `grep -c ORA- $LOG_FILE` -gt 0 ]; then
echo "Error: Unable to connect to $ORACLE_SID as system."
exit 1
fi
DICTIONARY_STAT_TABLE="ENC_DICT_STATS_$DATE"
SYSTEM_STAT_TABLE="ENC_SYS_STATS_$DATE"
FIXED_STAT_TABLE="ENC_FIXED_STATS_$DATE"
#======== ========= ========= ========= ========= ========= ========= ==========
# Collect dictionary and fixed stats for all instance types.
echo "set echo on
exec dbms_stats.create_stat_table ('SYSTEM', '$DICTIONARY_STAT_TABLE');
exec dbms_stats.create_stat_table ('SYSTEM', '$FIXED_STAT_TABLE');
--
alter table $DICTIONARY_STAT_TABLE move tablespace tools;
alter table $FIXED_STAT_TABLE move tablespace tools;
--
alter index $DICTIONARY_STAT_TABLE rebuild tablespace tools;
alter index $FIXED_STAT_TABLE rebuild tablespace tools;
--
exec dbms_stats.export_dictionary_stats (\
statown=>'SYSTEM', \
stattab=>'$DICTIONARY_STAT_TABLE');
exec dbms_stats.export_fixed_objects_stats (\
statown=>'SYSTEM', \
stattab=>'$FIXED_STAT_TABLE');
--
exec dbms_stats.gather_dictionary_stats ();
--
exec dbms_stats.gather_fixed_objects_stats;
" > $SQL_FILE
#======== ========= ========= ========= ========= ========= ========= ==========
# If this instance is NOT E-Business suite, then collect system stats too.
sqlplus /nolog <<-EOF > $LOG_FILE
connect system/$SYSTEM_PASSWORD@$ORACLE_SID
set echo on
select 'EU FOUND'
from dual
where exists (select 1 from dba_users where username = 'EU');
EOF
if [ `grep -c 'EU FOUND' $LOG_FILE` -eq 0 ]; then
echo "exec dbms_stats.create_stat_table ('SYSTEM', '$SYSTEM_STAT_TABLE');
alter table $SYSTEM_STAT_TABLE move tablespace tools;
alter index $SYSTEM_STAT_TABLE rebuild tablespace tools;
exec dbms_stats.export_system_stats (\
statown=>'SYSTEM', \
stattab=>'$SYSTEM_STAT_TABLE');
exec dbms_stats.gather_system_stats (\
gathering_mode=>'INTERVAL', \
interval=>60);
" >> $SQL_FILE
fi
#======== ========= ========= ========= ========= ========= ========= ==========
# Let the user know where the old statistics are saved.
echo "set echo off
prompt "The following Stats tables are available to restore from, if necessary."
select table_name from dba_tables
where table_name like 'ENC_%_STATS_%'
;
prompt "Here are the system statistics in effect now."
column sname format a13
column pname format a10
column pval2 format a19
select * from sys.aux_stats$
;" >> $SQL_FILE
#======== ========= ========= ========= ========= ========= ========= ==========
# Finally run the SQL script.
sqlplus /nolog <<-EOF | tee $LOG_FILE
connect system/$SYSTEM_PASSWORD@$ORACLE_SID
@$SQL_FILE
EOF
if [ "$MAIL_IT" -eq 1 ]; then
for MAIL_READER in $MAIL_READERS; do
# Mail the results to the MAIL_READER
HOST=`hostname`
SUBJ="Info: $HOST: $ORACLE_SID: $BASE: Completed."
FROMMAIL="`whoami`@$HOST"
mailx -r $FROMMAIL -s "$SUBJ" $MAIL_READER < $LOG_FILE
done
fi
#======== ========= ========= ========= ========= ========= ========= ==========
echo "`date`: Done."
No comments:
Post a Comment