Hi Mitchell, The most efficient way to keep updated stats is to use monitoring and use 'GATHER STALE' option. Bear in mind that just because there is an entry in ALL_TAB_MODIFICATIONS does not mean that the next analyze using 'GATHER STALE' will analyze that table. It is based upon whether the changes add up to more than 10% change.
The way I do it now (after my very public fiasco with dbms_stats over the last couple of weeks) is I wrote a perl script that reads all_tab_modifications and fires the appropriate analyze command. If you'd like to see it, let me know. It's a quick hack job but it works. Lisa Koivu Oracle Database Stressmonkey Fairfield Resorts, Inc. 5259 Coconut Creek Parkway Ft. Lauderdale, FL, USA 33063 Office: 954-935-4117 Fax: 954-935-3639 Cell: 954-683-4459 -----Original Message----- Sent: Thursday, June 12, 2003 4:10 PM To: Multiple recipients of list ORACLE-L Hi All May I know what is best interval to exec dbms_stats.gather_schema_stats on a instance with 300 tables that updated everyday. By the way, Anybody has any detailed command reference for dbms_stats. I am not sure for the following such as degree? execute dbms_stats.gather_schema_stats ( ownname ='FNSS', degree => 4, cascade => true ); Thanks in advance Mitchell -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mitchell 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). "The sender believes that this E-Mail and any attachments were free of any virus, worm, Trojan horse, and/or malicious code when sent. This message and its attachments could have been infected during transmission. By reading the message and opening any attachments, the recipient accepts full responsibility for taking proactive and remedial action about viruses and other defects. The sender's business entity is not liable for any loss or damage arising in any way from this message or its attachments." -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Koivu, Lisa 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).
