RE: Collecting table statistics

From: Steve Orr <sorr_at_arzoo.com>
Date: Fri, 21 Jul 2000 12:33:34 -0700
Message-Id: <10565.112678@fatcity.com>

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-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Deshpande, Kirti
Sent: Friday, July 21, 2000 12:46 PM
To: Multiple recipients of list ORACLE-L Subject: RE: Collecting table statistics

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?

Received on Fri Jul 21 2000 - 14:33:34 CDT