Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: monitor not working ....

RE: monitor not working ....

From: MacGregor, Ian A. <ian_at_SLAC.Stanford.EDU>
Date: Sat, 22 Jul 2000 08:38:56 -0700
Message-Id: <10566.112710@fatcity.com> 





Take a look at bug 1192012 and see if it applies.  Also be aware that
running dbms_stats does not immediately update your statistics. 



Ian Macgregor


Stanford Linear Accelerator Center


ian_at_slac.stanford.edu



-----Original Message-----



From: Thapliyal, Deepak [mailto:DThapliyal_at_ea.com]
Sent: Friday, July 21, 2000 4:56 PM


To: Multiple recipients of list ORACLE-L
Subject: RE: monitor not working ....




Steve,



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





both are not 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-----



Sent: Friday, July 21, 2000 1:48 PM


To: Multiple recipients of list ORACLE-L




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: Thapliyal, Deepak
  INET: DThapliyal_at_ea.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 Sat Jul 22 2000 - 10:38:56 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US