monitor not working ....

From: Thapliyal, Deepak <DThapliyal_at_ea.com>
Date: Fri, 21 Jul 2000 15:25:21 -0700
Message-Id: <10565.112695@fatcity.com>

Steve,

after i have gathered stats i would assume that couple of things happen:

stuff from user_tab_modifications would be analysed and be removed from that view
dba_tables would reflect new values

both are no happening .. please have a look at the output.

thx in advance
deepak

PS : my optimizer is choose ...

SQL> select table_name, num_rows, blocks, avg_row_len, monitoring from user_tables where table_name='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN MON


------------------------------ ---------- ---------- ----------- ---

TEST                               401000        989          16 YES

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED from user_tab_modifications;

TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP

TRU
------------------------------ ---------- ---------- ---------- ---------
---

TEST                              1986103          0          0 21-JUL-00

YES SQL> begin
2 DBMS_STATS.GATHER_SCHEMA_STATS ('OPS$ORACLE',NULL,FALSE,'FOR ALL COLUMNS SIZE 1',NULL,'DEFAULT',TRUE,NULL,NULL,'GATHER STALE'); end;
/

PL/SQL procedure successfully completed.

SQL> select table_name, num_rows, blocks, avg_row_len, monitoring from user_tables where table_name='TEST';

TABLE_NAME                       NUM_ROWS     BLOCKS AVG_ROW_LEN MON


------------------------------ ---------- ---------- ----------- ---

TEST                               401000        989          16 YES

SQL> select TABLE_NAME,INSERTS,UPDATES,DELETES,TIMESTAMP,TRUNCATED from user_tab_modifications;

TABLE_NAME                        INSERTS    UPDATES    DELETES TIMESTAMP

TRU
------------------------------ ---------- ---------- ---------- ---------
---

TEST                              1986103          0          0 21-JUL-00

YES SQL>
-----Original Message-----

From: Steve Orr [mailto:sorr_at_arzoo.com]
Sent: Friday, July 21, 2000 1: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

Received on Fri Jul 21 2000 - 17:25:21 CDT