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?)
[EMAIL PROTECTED] Quad/Tech International, Sussex, WI USA
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Orr, Steve
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
