RE: How long are statistics good for
John My initial tests indicated that not all tables which had any DMLs were getting into the 'stale category'. I checked the ratio A/B where: A is the sum of inserts+deletes+updates from user_tab_modifications B is num_rows from user_tables (or user_tab_partitions). I observed that if this ratio exceeded 10%, package dbms_stats analyzed the objects with 'GATHER STALE' option. (But I get error for the partitioned tables with this option). Anand >>> [EMAIL PROTECTED] 10/23/01 05:20PM >>>Anand,Just curious : Is there some test or other observation that you can sharewith the list about dbms_stats using 10% as a boundary for staleness?John KanagarajWhich version of Oracle are you using. In 8i you can set 'monitoring on' forthe tables and use dbms_stats to analyze stale. (Though, I am getting errorwhile using dbms_stats for the partitioned tables. So I have made a homemade version to analyze stale). As per my calculations, package dbms_statsconsiders statistics stale if all DMLs affect more than 10% of number ofrows.-- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: John Kanagaraj INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: How long are statistics good for
Anand, Just curious : Is there some test or other observation that you can share with the list about dbms_stats using 10% as a boundary for staleness? John Kanagaraj Which version of Oracle are you using. In 8i you can set 'monitoring on' for the tables and use dbms_stats to analyze stale. (Though, I am getting error while using dbms_stats for the partitioned tables. So I have made a home made version to analyze stale). As per my calculations, package dbms_stats considers statistics stale if all DMLs affect more than 10% of number of rows. -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: John Kanagaraj INET: [EMAIL PROTECTED] 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: [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).
Re: How long are statistics good for
Which version of Oracle are you using. In 8i you can set 'monitoring on' for the tables and use dbms_stats to analyze stale. (Though, I am getting error while using dbms_stats for the partitioned tables. So I have made a home made version to analyze stale). As per my calculations, package dbms_stats considers statistics stale if all DMLs affect more than 10% of number of rows. Anand Prakash >>> [EMAIL PROTECTED] 10/23/01 11:00AM >>>We have a data-warehouse that is a combination of Snapshots and table-buildsbased on the snapshots.The table builds run at 4:30 am, scripts are setup to start the snapshots at7:00am and end at 9:00pm. At 6:30 am a script performs an analyze on ALL (except sys andsystem) tables inthe database.If the snapshots have been running all day, should I run an analyze before Ido the table builds?as opposed to after ?At what point do the statistics on a table become no good ? when a new rowis added ?ThanksDarren--Darren Browett P.Eng This messagewas transmittedSystems Analyst - Information Systems using 100%recycled electrons City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com-- Author: Browett, Darren INET: [EMAIL PROTECTED]Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing ListsTo REMOVE yourself from this mailing list, send an E-Mail messageto: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).
RE: How long are statistics good for
Statistics become old after a single change is made to the object. Granted adding a single row to a 1 Million row table isn't going to effect much. But adding 100,000 rows will. So where do you draw the line? I really don't know. But 5%-10% is generally a good measure. Sometimes more sometimes less depending on the size. "Do not criticize someone until you walked a mile in their shoes, that way when you criticize them, you are a mile a way and have their shoes." Christopher R. Spence Oracle DBA Phone: (978) 322-5744 Fax:(707) 885-2275 Fuelspot 73 Princeton Street North, Chelmsford 01863 -Original Message- Sent: Tuesday, October 23, 2001 2:00 PM To: Multiple recipients of list ORACLE-L We have a data-warehouse that is a combination of Snapshots and table-builds based on the snapshots. The table builds run at 4:30 am, scripts are setup to start the snapshots at 7:00am and end at 9:00pm. At 6:30 am a script performs an analyze on ALL (except sys and system) tables in the database. If the snapshots have been running all day, should I run an analyze before I do the table builds? as opposed to after ? At what point do the statistics on a table become no good ? when a new row is added ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Systems Analyst - Information Systems using 100% recycled electrons City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] 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: [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.com -- Author: Christopher Spence INET: [EMAIL PROTECTED] 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: [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).
How long are statistics good for
We have a data-warehouse that is a combination of Snapshots and table-builds based on the snapshots. The table builds run at 4:30 am, scripts are setup to start the snapshots at 7:00am and end at 9:00pm. At 6:30 am a script performs an analyze on ALL (except sys and system) tables in the database. If the snapshots have been running all day, should I run an analyze before I do the table builds? as opposed to after ? At what point do the statistics on a table become no good ? when a new row is added ? Thanks Darren -- Darren Browett P.EngThis message was transmitted Systems Analyst - Information Systems using 100% recycled electrons City of Coquitlam P:(604)927 - 3614 E:[EMAIL PROTECTED] --- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Browett, Darren INET: [EMAIL PROTECTED] 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: [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).