| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: How to implement and use DBMS_STATS?
A word of warning. I just came across a really dumb bug (1407738) in dbms_stats on 8i. If you use gather_schema_stats with the gather stale or empty options on a schema with a table name longer than 28 characters, you get an ora-6502. Turns out that dbms_stats uses varchar2(30) variables to store the table name, but then goes and puts quotes round it to guard against odd characters. I was irritated to hear from OWS that its fixed in 9i but that they aren't going to backport it to 8.1.7 <grr>.
David Lord
-----Original Message-----
Sent: 06 July 2001 21:21
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" <sorr_at_rightnow.com>@fatcity.com on 07/06/2001 02:42:03 PM
Please respond to ORACLE-L_at_fatcity.com
Sent by: root_at_fatcity.com
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> 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
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."
ownname => '$DASCHEMA' , -
method_opt => 'FOR ALL INDEXED COLUMNS SIZE 64' , -
cascade => TRUE , -
options => 'GATHER STALE' ) ;
exit;
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-LReceived on Mon Jul 09 2001 - 04:02:54 CDT
(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: Srini.Chavali_at_Cummins.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: Lord, David - C&S INET: David.Lord_at_hayscsg.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).
 |
 |