Steve Adams says it's probably less than 1% on heavy OLTP systems. Check out
his excellent explanation at: http://www.ixora.com.au/newsletter/2000_07.htm
Steve
-----Original Message-----
Sent: Friday, July 06, 2001 2:21 PM
To: Multiple recipients of list ORACLE-L
Steve,
Good stuff !
What's the overhead incurred in "monitoring" the tables ? Is that
quantifiable ?
The reason I ask is because I am currently gathering statistics weekly on
all tables, but would much rather use the monitoring/stale route. I assume
that the overhead would be less than gathering stats for all tables.
TIA
Srini Chavali
Oracle DBA
Cummins Inc
"Orr, Steve" <[EMAIL PROTECTED]>@fatcity.com on 07/06/2001 02:42:03 PM
Please respond to [EMAIL PROTECTED]
Sent by: [EMAIL PROTECTED]
To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
cc:
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:
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: 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).
