Re: RE: how to keep statistics up to date for CBO

does monitoring have any real overhead in a high transaction system?
>
> From: "Mercadante, Thomas F" <NDATFM_at_labor.state.ny.us>
> Date: 2003/10/09 Thu AM 08:59:33 EDT
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: bmetelsky_at_cps92.com
>
> 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: 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: Mercadante, Thomas F
> INET: NDATFM_at_labor.state.ny.us
>
> 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: 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).
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: <rgaffuri_at_cox.net
  INET: rgaffuri_at_cox.net

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: 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).