RE: How to implement and use DBMS

From: Hillman, Alex <Alex.Hillman_at_usmint.treas.gov>
Date: Fri, 06 Jul 2001 13:12:21 -0700
Message-ID: <F001.003438F4.20010706132616@fatcity.com>

I did the same thing using directly views dba_tab_modifications and dba_tables. This way you can device your own algotithm to decide for which objects you want to gather statistics. Also I use these views to rebuild indexes - when sum of inserts and deletes > then X% of number of records in dba_tables.

Alex Hillman

-----Original Message-----
Sent: Friday, July 06, 2001 3:42 PM
To: Multiple recipients of list ORACLE-L

OK, here's an excerpt from our "DBA Cookbook"

Optimizer Statistics
We are using the cost-based optimizer for our implementation so it is important that we keep optimizer statistics up-to-date. But computing these statistics can be very resource intensive requiring lots of CPU and a tablespace for temporary sorts of up to 1.25 times the size of the largest table being analyzed. To minimize resource usage we use the new DBMS_STATS Oracle supplied package.

Here are the steps:
1. First we gather complete statistics on a schema:

SQL> execute DBMS_STATS.GATHER_SCHEMA_STATS('<SCHEMA>', - null,null,null,null,null,TRUE);

2. Next we alter the tables to implement statistical monitoring:

SQL> alter table <SCHEMANAME>.<TBLNAME> monitoring;

3. Finally, on a periodic basis, we refresh the statistics. The first two steps above only need to be done once after database/schema creation. The following step should be performed periodically or as needed:

SQL> execute DBMS_STATS.GATHER_SCHEMA_STATS('<SCHEMA>', - null,FALSE, 'FOR ALL COLUMNS SIZE 1',null,'DEFAULT', - TRUE,null,null,'GATHER STALE');

By putting the tables in "monitor" mode, Oracle tracks statistical changes whenever any DML is executed. If data changes are such that it could affect optimization, Oracle marks the table or index as "stale." When we perform step 3 above, we are only recomputing statistics on the objects that need it. This saves computing resource in a 24X7 environment. For more information see the "Automated Statistics Gathering" section of the Oracle Tuning manual. Also, reference the Oracle packages documentation for information on DBMS_STATS.

Here's some sample contents of a cron job script:

#!/usr/bin/ksh
# File:    getstats.sh, Steve Orr, 5/21/01
# Purpose: Oracle maintenance, recompute stats.
# Usage:   getstats.sh SCHEMA_OWNER_NAME

. /usr/bin/dbaenv
LOGFILE="$LOGDIR/stats.log"

if [ "$1" ]
then DASCHEMA=$1
else echo "You must supply an argument for the schema." exit 1
fi

echo "Calculating statistics, please wait..." getstats()
{

echo "-----------------------------------------------------------"

echo "`date` -- Compute Stats."
ORACLE_SID=WHATEVER;export ORACLE_SID
sqlplus -s <internal/oracle or whatever user/pw> << EOSQL execute DBMS_STATS.GATHER_SCHEMA_STATS( -

        ownname    => '$DASCHEMA' , -
        method_opt => 'FOR ALL INDEXED COLUMNS SIZE 64' , -
        cascade    => TRUE , -
        options    => 'GATHER STALE' ) ;

exit;
EOSQL
echo "`date` -- Done recomputing stats on $DASCHEMA" }
getstats | tee $LOGFILE

Well, that should get you started,
Steve Orr

-----Original Message-----
Sent: Friday, July 06, 2001 10:21 AM
To: Multiple recipients of list ORACLE-L

OK, enough is enough. For a month now, I've been researching on how one is supposed to implement and use the DBMS_STATS package. The FMs I R'd, Oracle 8.1.7's "Designing and Tuning for Performance" and "Supplied PL/SQL Packages Reference", give the syntax and some very weak and incomplete examples, but fail to define how a DBA is actually supposed to USE the package in day-to-day operation.

And Oracle Support just keeps pointing me back to inane and unrelated articles in MetaClink.

Can anyone point out a website or a good book with a chapter on using DBMS_STATS for CBO? Specifically, some good examples, some definitions (when are stats considered "stale"?), what is the scope of GATHER_DATABASE_STATS (does it stat SYS???), under what circumstances GATHER_TABLE, GATHER_SCHEMA, and GATHER_DATABASE are to be used, and generally how the hell one goes about implementing this. I've now got less than two weeks to figure it out!

And if you know this, where did you learn it? Even my Oracle Perf Tuning Class student guide mentions the package, says to use it, but then points to the syntax-only Oracle docs for more info. <sigh>

Frustratedly yours,

Rich Jesse                          System/Database Administrator (wannabe?)
Rich.Jesse_at_qtiworld.com             Quad/Tech International, Sussex, WI USA

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Orr, Steve
INET: sorr_at_rightnow.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Hillman, Alex
INET: Alex.Hillman_at_usmint.treas.gov

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

RE: How to implement and use DBMS_STATS?