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 P=
R 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 Oracl=
e
> features and packages to reduce the amount of resource used.
>
> Here are the steps:
> 1.=09First 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.=09Next we create dynamic SQL to alter all the tables to implemen=
t
> statistical monitoring. Example DDL for a single table:
>
> SQL> alter table myzoo.TBARZ_SESSION_LOGS monitoring;
>
>
> 3.=09Finally, on a weekly basis, we refresh the statistics. The fir=
st two
> steps above only need to be done once after database creation.
> This step is
> performed every week or as needed. Here=92s 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 STAL=
E');
>
>
> By putting the tables in "monitor" mode, Oracle tracks statistical =
changes
> whenever any DML is executed. If the amount data changes are such t=
hat 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 Gat=
hering"
> section of the Oracle Tuning manual. Also, reference the Oracle pac=
kages
> 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=3D"$LOGDIR/stats.log"
> {
> echo "`date` -- Compute Stats."
> ORACLE_SID=3Ddazu1;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 STAL=
E')'
> 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 <tab=
lename>
> monitoring, default is nomonitoring).
> I have not used it yet, but remember reading about it..
> - Kirti
>
> > -----Original Message-----
> > From:=09Chuck Hamilton [SMTP:chuck_hamilton_at_yahoo.com]
> > Sent:=09Friday, July 21, 2000 1:57 PM
> > To:=09Multiple recipients of list ORACLE-L
> > Subject:=09Collecting table statistics
> >
> > Some time ago I thought I saw something on the list about 8i havi=
ng the
> > ability to automatically collecet statistics on tables with needi=
ng to
> > issue the ANALYZE TABLE command. Can someone tell me how this wor=
ks or
> > point me to a document that describes it? Is this what the MONITO=
RING
> > clause of ALTER/CREATE table is for?
> > also send the HELP command for other information (like subscribin=
g).
>
> --
> 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 Li=
sts
> -------------------------------------------------------------------=
-
> 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
Received on Fri Jul 21 2000 - 15:21:33 CDT