Re: How to implement and use DBMS_STATS?

how nice of them, since they are pushing so hard the concept of dbms_stats.

Long live analyze :)

joe
"Lord, David - C&S" wrote:
>
> 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
> 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:
> 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)

-- 
Joe Testa  
Performing Remote DBA Services, need some backup DBA support?
For Sale: Oracle-dba.com domain, its not going cheap but feel free to
ask :)
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Joe Testa
  INET: teci_at_the-testas.net

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