None that I've noticed & the database in question is processing something like 1B transactions per day.
Dick Goulet Senior Oracle DBA Oracle Certified 8i DBA -----Original Message----- Sent: Thursday, October 09, 2003 9:14 AM To: Multiple recipients of list ORACLE-L does monitoring have any real overhead in a high transaction system? > > From: "Mercadante, Thomas F" <[EMAIL PROTECTED]> > Date: 2003/10/09 Thu AM 08:59:33 EDT > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]> > Subject: RE: how to keep statistics up to date for CBO > > Bob, > > I do the following: > > First, alter all tables turning monitoring on: alter table {table name} > monitoring; > > Monitoring says: > "Specify MONITORING if you want Oracle to collect modification statistics on > table. These statistics are estimates of the number of rows affected by DML > statements over a particular period of time. They are available for use by > the optimizer or for analysis by the user." > > Then use the following. It recalculates stats for those tables that have > been changed enough to warrant stats. The User_Tab_Modifications table will > hold a record if 10% of the table was changed. I've been using this for a > while now, and it seems to be working fine. As you can see, I have a > database table that I insert a record into so I can see how much work is > done. I'm happy with it. And I'm not gathering stats for tables that I > don't need to. I run this job daily. > > Hope this helps. > > PROCEDURE WTWDBA.Wtw_Gather_Statistics IS > /* > Procedure Name : Wtw_Gather_Statistics > Author : Tom Mercadante > Mercadante Systems Design > June 14, 2001 > Purpose : > This Package will use the System DBMS_STATS package to gather statistics > for both tables and indexes. > > */ > > loc_table_name USER_TABLES.TABLE_NAME%TYPE; > loc_index_name USER_INDEXES.INDEX_NAME%TYPE; > tbl_count NUMBER := 0; > indx_count NUMBER := 0; > loc_start_time DATE; > > CURSOR c1 IS > SELECT ut.table_name FROM USER_TABLES UT, USER_TAB_MODIFICATIONS UTM > WHERE UT.TABLE_NAME = UTM.table_name; > > CURSOR c2 IS > SELECT index_name FROM USER_INDEXES > WHERE table_name = loc_table_name; > > > BEGIN > > loc_start_time := SYSDATE; > > -- Gather statistics on tables > > OPEN c1; > LOOP > FETCH c1 INTO loc_table_name; > EXIT WHEN c1%NOTFOUND; > dbms_stats.gather_table_stats('WTWDBA',loc_table_name); > tbl_count := tbl_count + 1; > > -- Gather statistics on indexes > OPEN c2; > LOOP > FETCH c2 INTO loc_index_name; > EXIT WHEN c2%NOTFOUND; > dbms_stats.gather_index_stats('WTWDBA',loc_index_name); > indx_count := indx_count + 1; > END LOOP; > CLOSE c2; > > END LOOP; > CLOSE c1; > > -- insert a record into the job log > INSERT INTO WTW_JOB_LOG(JOB_NAME, RUN_DATE, START_TIME, > END_TIME, MSG_TXT) > > VALUES('Wtw_Gather_Statistics',SYSDATE,loc_start_time, > SYSDATE,INITCAP('SCHEMA Stats Complete') || > CHR(10) || > tbl_count || INITCAP(' TABLES Analyzed ') || > CHR(10) || > indx_count || INITCAP(' INDEXES Analyzed')); > COMMIT; > > END; > > > Tom Mercadante > Oracle Certified Professional > > > -----Original Message----- > Sent: Wednesday, October 08, 2003 4:54 PM > To: Multiple recipients of list ORACLE-L > > > How does one keep CBO statistics for an applications base tables up to > date? > > We are about to implement the CBO any must read documents. > > Many thanks > bob > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Bob Metelsky > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.net > -- > Author: Mercadante, Thomas F > INET: [EMAIL PROTECTED] > > Fat City Network Services -- 858-538-5051 http://www.fatcity.com > San Diego, California -- Mailing list and web hosting services > --------------------------------------------------------------------- > To REMOVE yourself from this mailing list, send an E-Mail message > to: [EMAIL PROTECTED] (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). > -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: <[EMAIL PROTECTED] INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Goulet, Dick INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (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).