How to collect Oracle system statistics.

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."

I'm relieved to report that I have passed the DBA development test 70-433.  Here are some of the preparations I made.   If these ideas would be helpful to you, you are welcome to them, and I will wish you well.

Microsoft’s website for this exam has a long list of training materials.

For me, Step 1 was to read the book that Microsoft calls the Self-Paced Training Kit.   That book is definitely mandatory. I read that at least twice.   And just as important, I did ALL the practices too.

But when I took the assessment test on the disk included with that book, it was like I didn't know anything. There are 200 sample questions. I would try, say 10 questions, and get 1 right.  The book simply did not cover anywhere near the amount of material referenced by those questions.

Step 2.  I happened to have a copy of the SQL Server 2008 Internals book by Delaney et.al, which covers a lot of these same topics. That helped fill-in a number of gaps. Sometimes that book went WAY further into the internals than anyone needs to know for this test.   When that happened it was easy-enough to turn the page until it related to me again.   After that I could get 3 out of 10 questions right.

Step 3.   I read The Real MCTS SQL Server 2008 Exam 70-433 Prep Kit: Database Design, edited by Horniger.  I’m willing to say that was helpful. If you are going to take this test, I think it is definitely worth reading. I have to admit it really irritated me that some chapters seemed to have a misspelled word or grammatical error on every page. If we have all the time in the world, I have to think a team of us can write a better book than they did. But it was helpful. After that I could get 5 out of 10 right.

Step 4.  I read DeBetta's free Introduction to SQL Server 2008.  You can’t read the same thing over and over again.   Every additional author gives you a new way of thinking about things.   He has provided a great service to our community by providing a readable, good quality explanation full of good ideas.   It may be free, but it’s a keeper on my bookshelf.    

Step 5.   After all that, it was still not enough.   So basically I decided I had to write, or at least assemble, my own book. I mean, I would read a question, and say to myself, "Wow, I just do NOT know enough about that yet."  So I would do a Google Search, and find some article or blog about it, and paste that into a Word document for the things I needed to know more about myself. I assembled over 180 pages of such notes.

Step 6.   After that, the only thing left I could do was build my test-taking muscles. The book(s) will tell you what you CAN do. But the practice questions will help you see the LIMITs.

For example, I knew you can insert into view (with restrictions), and you can use the OUTPUT clause to return changed data back to the screen, or insert it to a table, or into a table variable, but OUTPUT cannot insert into a view. I can only remember such factoids if a practice question gives me a reason to.

Or, do you know that SET SHOWPLAN ON will show you the ACTUAL execution plan, while SET SHOWPLAN_XML ON will show you the ESTIMATED execution plan. I cringe to think about what my brain has to do to remember such details!

Because, practice does not make perfect. PERFECT PRACTICE makes perfect. If I didn't know the answer to a practice question, there is no point in guessing.  Nor is there any point to simply learning the answer to 1 question.  The best way to meet the challenge is to go study that on the spot.

Hopefully some of those ideas are helpful to you.
Best wishes.
~the hard-working DBA