RE: Collecting table statistics

Have you subscribed to the Ixora Newsletter? This very topic was covered in the July issue. Look at Steve Adam's website:

Ixora News - http://www.ixora.com.au/newsletter/2000_07.htm

I'll take the liberty of quoting from Steve...

> "The modification counts are maintained in an efficient hash table is
> the SGA, and are updated without the protection of a latch (although
> the structure of the hash table itself is protected by the hash table
> modification latch). Even in heavy OLTP environments, the cost of
> maintaining the modification counts is likely to be less than 1% of
> additional CPU usage."

There is MUCH, MUCH more so I encourage you to visit Steve's site and subscribe and buy his book.

HTH,
Steve Orr

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Glenn Travis
Sent: Friday, July 21, 2000 2:52 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Collecting table statistics

I'd be interested to know the overhead associated with #2.

Specifically, the sentence;
"Oracle tracks statistical changes whenever any DML is executed."

anyone?

> -----Original Message-----
> From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Steve Orr
> Sent: Friday, July 21, 2000 4:48 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: Collecting table statistics
>
>
> I've automated it so I don't have to worry about stats. Here's my PR to
> damagement write-up:
> ------------------------------------------------------------------
> ---------
> 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. We take advantage of some of the latest Oracle
> features and packages to reduce the amount of resource used.
>
> Here are the steps:
> 1. First we gather complete statistics on the entire myzoo schema as
> follows:
>
> SQL> execute DBMS_STATS.GATHER_SCHEMA_STATS -
> ('MYZOO',null,null,null,null,null,TRUE);
>
>
> 2. Next we create dynamic SQL to alter all the tables to implement
> statistical monitoring. Example DDL for a single table:
>
> SQL> alter table myzoo.TBARZ_SESSION_LOGS monitoring;
>
>
> 3. Finally, on a weekly basis, we refresh the statistics. The first two
> steps above only need to be done once after database creation.
> This step is
> performed every week or as needed. Here’s the command to refresh the
> statistics:
>
> SQL> execute DBMS_STATS.GATHER_SCHEMA_STATS('MYZOO',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 the amount 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 a lot of computing resource for our 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 a shell script for crontab:
>
> # File: getstats.sh, Steve Orr, 5/9/00
> # Purpose: Oracle maintenance, recompute stats.
> # Usage: Executed as an Oracle crontab job. (Implement as DBMS_JOB?)
> . /usr/bin/dbaenv
> LOGFILE="$LOGDIR/stats.log"
> {
> echo "`date` -- Compute Stats."
> ORACLE_SID=dazu1;export ORACLE_SID
> sqlplus -s / << EOSQL
> execute DBMS_STATS.GATHER_SCHEMA_STATS('MYZOO',null,FALSE, -
> 'FOR ALL COLUMNS SIZE 1',null,'DEFAULT',TRUE,null,null,'GATHER STALE')'
> exit;
> EOSQL
> echo "`date` -- Done recomputing stats on $ORACLE_SID"
> } >>$LOGFILE
> ------------------------------------------------------------------
> ---------
>
> HTH... dah, well yeah... I just gave it away!
>
> Happy Friday!!!!!!
> Steve Orr
>
>
>
> -----Original Message-----
> Kirti
> Sent: Friday, July 21, 2000 12:46 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi,
> That would be the new 'monitoring' option (as in , alter table <tablename>
> monitoring, default is nomonitoring).
> I have not used it yet, but remember reading about it..
> - Kirti
>
> > -----Original Message-----
> > From: Chuck Hamilton [SMTP:chuck_hamilton_at_yahoo.com]
> > Sent: Friday, July 21, 2000 1:57 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: Collecting table statistics
> >
> > Some time ago I thought I saw something on the list about 8i having the
> > ability to automatically collecet statistics on tables with needing to
> > issue the ANALYZE TABLE command. Can someone tell me how this works or
> > point me to a document that describes it? Is this what the MONITORING
> > clause of ALTER/CREATE table is for?
> > also send the HELP command for other information (like subscribing).
>
> --
> Author: Steve Orr
> INET: sorr_at_arzoo.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).
>

--
Author: Glenn Travis
  INET: Glenn.Travis_at_wcom.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