Re: Should we stop analyzing?
Dunno how he does it. But I'd settle for my replies from my ISP to make it here... Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - How do you know they're nodding if they call you on the phone? Distinct rattling sound? :-) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
RE: Should we stop analyzing?
My explanation is. How is network connectivity priced? By bandwidth or latency. Niall -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Wolfgang Breitling Sent: 12 January 2004 21:35 To: Multiple recipients of list ORACLE-L Subject: RE: Should we stop analyzing? My explanation for that would be that it is all driven by beans. If manager learns that a resource is underutilized he/she immediately starts to plan to switch it for a smaller (i.e. cheaper) resource. Unless you can express performance in terms of beans it doesn't mean beans (so to speak) to them. At 02:19 PM 1/12/2004, you wrote: P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. Author: Niall Litchfield INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Niall Litchfield 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).
RE: Should we stop analyzing?
We have the occasional network issue from Perth to Port Hedland (both in Western Australia, with Port Hedland being a couple of thousand km's north of Perth). When management phone up, I always reply with: Have you looked at a map? See how far north Port Hedland is...that's all uphill you know! Never fails to amaze me how many will nod in agreement... Cheers Connor --- Wolfgang Breitling [EMAIL PROTECTED] wrote: My explanation for that would be that it is all driven by beans. If manager learns that a resource is underutilized he/she immediately starts to plan to switch it for a smaller (i.e. cheaper) resource. Unless you can express performance in terms of beans it doesn't mean beans (so to speak) to them. At 02:19 PM 1/12/2004, you wrote: P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. Author: Niall Litchfield INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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).
Re: Should we stop analyzing?
How do you know they're nodding if they call you on the phone? Distinct rattling sound? :-) - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, January 13, 2004 12:59 PM We have the occasional network issue from Perth to Port Hedland (both in Western Australia, with Port Hedland being a couple of thousand km's north of Perth). When management phone up, I always reply with: Have you looked at a map? See how far north Port Hedland is...that's all uphill you know! Never fails to amaze me how many will nod in agreement... Cheers Connor --- Wolfgang Breitling [EMAIL PROTECTED] wrote: My explanation for that would be that it is all driven by beans. If manager learns that a resource is underutilized he/she immediately starts to plan to switch it for a smaller (i.e. cheaper) resource. Unless you can express performance in terms of beans it doesn't mean beans (so to speak) to them. At 02:19 PM 1/12/2004, you wrote: P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. Author: Niall Litchfield INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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). = Connor McDonald web: http://www.oracledba.co.uk web: http://www.oaktable.net email: [EMAIL PROTECTED] GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day Yahoo! Messenger - Communicate instantly...Ping your friends today! Download Messenger Now http://uk.messenger.yahoo.com/download/index.html -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?iso-8859-1?q?Connor=20McDonald?= 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: Gudmundur Josepsson 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).
Re: Should we stop analyzing?
And all the +RULE queries you listed, where data dictionary queries anyway (which is designed for RBO). Tanel. [TG]: I can't even spell 10g, so I'll take your word for it... The OraApps 11i assertion did not sound right, so to verify I queried both the V$SQLAREA view as well as the STATSPACK repository (i.e. STATS$SQLTEXT) on a rather busy OraApps 11.5.8 system running Financials, ERP, HR/Payroll, Order Entry, and Inventory. The STATSPACK repository is only holding 14 days worth of data; I keep it purged pretty tight to keep it below 1Gb in size... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
Re: Should we stop analyzing?
Don, Comments inline... Yes! IME, there ARE still problems in the CBO, especially with complex subqueries. I have more than a dozen systems where management insists on staying with the RBO! [TG]: With all due respect, what does management know about this stuff anyway? They do not work with it, they do not research it, and they do not understand the issues if technical people do not research, understand, and inform them. Management makes decisions based on information provided. That is their job. Bad information, bad decisions. Hi Tim, Went to a management meeting the other day to discuss the statuses of a number of projects. At the meeting I asked the assembled managers Hey guys, what are your opinions on what type of Oracle optimizer we should use ? They kinda looked at me with a glazed look in their eyes and one of them was brave enough to ask What's an optimizer ?. OK, it's not entirely true but I were (stupid enough) to ask the question, I'm sure it's the reaction I would receive, if not a lot worse. Can't say I've (yet) worked in an organisation where management decides how to tune the databases !! Cheers Richard -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Richard Foote 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).
RE: Should we stop analyzing?
Hi Richard Strangely, I've also never been to a management meeting where the reason for my attendance was to enquire as to how the instance efficiency statistics were this month. On the other hand when management reports take 3 days not 3 hours they're the first to complain. I wonder since we know all management is damagement and that DBAs know best if you could advise me how to explain efficiency ratios to them. Niall P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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).
RE: Should we stop analyzing?
My explanation for that would be that it is all driven by beans. If manager learns that a resource is underutilized he/she immediately starts to plan to switch it for a smaller (i.e. cheaper) resource. Unless you can express performance in terms of beans it doesn't mean beans (so to speak) to them. At 02:19 PM 1/12/2004, you wrote: P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. Author: Niall Litchfield INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
Re: Should we stop analyzing?
I'm sure that buffer cache hit ratio is still a big hit with the damagement. You should also compile dictionary cache hit ratio (v$rowcache) and library cache hit ratio. Damagement usually loves statistics, the more meaningless it is, the more they love it. On 01/12/2004 04:19:34 PM, Niall Litchfield wrote: Hi Richard Strangely, I've also never been to a management meeting where the reason for my attendance was to enquire as to how the instance efficiency statistics were this month. On the other hand when management reports take 3 days not 3 hours they're the first to complain. I wonder since we know all management is damagement and that DBAs know best if you could advise me how to explain efficiency ratios to them. Niall P.S. whilst the above is fictitious they do care about %utilisation of bandwidth but not response time from remote sites, God that irritates me. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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). -- Mladen Gogala Oracle DBA -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Mladen Gogala 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).
Re: Should we stop analyzing?
Don, Comments inline... Yes! IME, there ARE still problems in the CBO, especially with complex subqueries. I have more than a dozen systems where management insists on staying with the RBO! [TG]: With all due respect, what does management know about this stuff anyway? They do not work with it, they do not research it, and they do not understand the issues if technical people do not research, understand, and inform them. Management makes decisions based on information provided. That is their job. Bad information, bad decisions. Every time we collect deep stats and histogram and switch optimizer_mode, hundreds of statements generate poor plans. [TG]: Please, let's talk specific examples, not generalities. This list resolves specific examples almost every week, and never (in my recollection) has a resolution involved going to RBO. Someone please correct me if I've mis-spoken. To verify, some enterprising soul may choose to review the list archives going back over two years, which are available on http://www.orafaq.com;. First of all, besides statistics, there are some init.ora parameters (besides OPTIMIZER_MODE) to be set appropriately, such as OPTIMIZER_INDEX_CACHING. It would cost these clients many thousands of dollars to have adjusted these plans, and management says If it ain't broke, why fix it. [TG]: No doubt any application transitioning from RBO to CBO needs to be tested thoroughly. But how about the success stories of the CBO? How about all of the queries that were impossible to fix under the RBO but now magically performed well after implementing CBO, and how about the dozens of options for fixing bad situations using the myriad options available with the CBO? Function-based indexes? Materialized views and query rewrite? Etc, etc, etc... Personally, I can't understand why anyone would continue to bleed money away using the RBO. Certainly, legacy software that requires RBO should continue to use it until end-of-life. But advocating a return to the RBO for new applications is not rational. Again, please let's discuss specifics... We need look no further than Oracle Applications to see this issue. Oracle made a big-deal about going to the CBO in 11i, yet when we look at the SQL, a significant number of statement employ the rule hint! Connect-the-dots and you can guess why the RBO IS NOT being removed from Oracle10g. . . . [TG]: I can't even spell 10g, so I'll take your word for it... The OraApps 11i assertion did not sound right, so to verify I queried both the V$SQLAREA view as well as the STATSPACK repository (i.e. STATS$SQLTEXT) on a rather busy OraApps 11.5.8 system running Financials, ERP, HR/Payroll, Order Entry, and Inventory. The STATSPACK repository is only holding 14 days worth of data; I keep it purged pretty tight to keep it below 1Gb in size... In both V$SQLAREA and STATS$SQLTEXT, I found only nine (9) and eight (8) SQL statements, respectivley, using the RULE hint, all of which were querying the data dictionary objects only. 8-9 is not what I would call a significant number, not when V$SQLAREA has over 50,000 distinct SQL statements and STATS$SQLTEXT has almost 6,400 distinct SQL statements. Here is the query and results from the STATSPACK repository: SQL break on hash_value SQL select hash_value, sql_text from stats$sqltext 2 where upper(text_subset) like '%/*+%RULE%*/%' 3 order by hash_value, piece; HASH_VALUE SQL_TEXT -- 296554613 Select /*+ RULE */ * FROM SYS.ALL_SYNONYMS WHERE ((OWNER = :own) OR (TABLE_OWNER = :own and OWNER = 'PUBLIC')) and db_link is nu ll and TABLE_NAME = 'OE_SOLD_TO_ORGS_V' ORDER BY SYNONYM_NAME 476032654 SELECT /*+ rule */O.SUBNAME PART_NAME,O.OBJ# OBJ_NUM FROM SYS .USER$ U,SYS.OBJ$ O WHERE U.NAME = :b1 AND O.OWNER# = U.USER# AND O.NAME = :b2 AND O.TYPE# = 19 ORDER BY PART_NAME 529775420 SELECT /*+ rule */C.NAME COL_NAME,C.TYPE# COL_TYPE,C.CHARSETFOR M COL_CSF,C.DEFAULT$ COL_DEF,C.PROPERTY COL_PROP,C.COL# COL_UNUM ,C.INTCOL# COL_INUM FROM SYS.USER$ U,SYS.OBJ$ O,SYS.COL$ C WH ERE U.NAME = :b1 AND O.OWNER# = U.USER# AND O.TYPE# = 2 AND O .NAME = :b2 AND O.OBJ# = C.OBJ# 531307833 Select /*+ RULE */ t.*, o.status validity from SYS.ALL_TRIGGERS t, SYS.ALL_OBJECTS o where t.owner = o.owner and t.trigger_name = o.object_name and o.object_type = 'TRIGGER' and o. OWNER = :ow n AND ( t.table_name = 'OE_SOLD_TO_ORGS_V' OR o.ob ject_name = 'OE_SOLD_TO_ORGS_V' ) ORDER BY t.TRIGGER_NAME 787810128 select /*+ rule */ bucket_cnt, row_cnt, cache_cnt, null_cnt, tim estamp#, sample_size, minimum, maximum, distcnt, lowval, hival, density, col#, spare1, spare2, avgcln from hist_head$ where obj# =:1 and intcol#=:2 2014200833 select /*+ RULE */
RE: Should we stop analyzing?
exactly the same with steve trying to log a bug about x$ksmlru frits -Original Message- Sent: donderdag 8 januari 2004 20:59 To: Multiple recipients of list ORACLE-L Comment in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors Several years ago I raised an issue with Oracle support where something was clearly going wrong - can't remember what, too long ago - and got told that I couldn't get the issue logged as a bug because the code was performing to specification. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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). This e-mail and any attachment is for authorised use by the intended recipient(s) only. It may contain proprietary material, confidential information and/or be subject to legal privilege. It should not be copied, disclosed to, retained or used by, any other party. If you are not an intended recipient then please promptly delete this e-mail and any attachment and all copies and inform the sender. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Frits Hoogland 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).
Re: Should we stop analyzing?
- Original Message - Wouldn't it be nice if dbms_stats could do an incremental refresh, tracking ONLY stats changes that might make a difference to execution plan: I'd settle for a flag I could turn on and off, saying: do/do not change stats for this object. I know which of them need to be analyzed and which don't. Better than Oracle will ever, deltas or no deltas, workload managers or not. a) Allow for dbms_stats to collect, store and compare changes to historical execution plans, using historical SQL from STATSPACK (or new 10g workload views) Sadly, this workload feature of 10g if I know anything about how Oracle works, will evolve into another monster elephant gun. Completely forgetting the problem out there is in most cases mosquito-size and can be addressed with a simple fly-swat. Yes, there is such a thing as over-engineering a solution. This will be one of them. And like anything that is over-engineered, it will be buggy - sorry Pete, feature-reluctant. Or perheaps document-challenged? And it will create a bad name for itself while the developers evolve it until Oracle 12r2... b) Allow the DBA control about whether to implement the new statistics That, sadly, is totally outside of Oracle's plans for the traditional production DBA role in future. It would cost these clients many thousands of dollars to have adjusted these plans, and management says If it ain't broke, why fix it. My problem too. Try and convince a damager that something that is working fine should have thousands of buckeroos spent on it to become compatible with new CBO! Like Heck it's gonna happen... Cripes, I know quite a few sites here that are STILL running Prime computers with Prime Information (for those who don't know, look-up Pick in google), 13 years after the company vanished! And no plans whatsoever to update. Why? Heck, it WORKS! Talk about TCO, eh? Oracle made a big-deal about going to the CBO in 11i, yet when we look at the SQL, a significant number of statement employ the rule hint! Connect-the-dots and you can guess why the RBO IS NOT being removed from Oracle10g. . . . Bingo!... Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: Should we stop analyzing?
Yahwoll, mein herr! Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 09, 2004 6:09 AM Waddya mean, propaganda sheets? We never release propaganda - everything always works the way we say it does! :) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: Should we stop analyzing?
Note in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 09, 2004 1:19 PM - Original Message - Wouldn't it be nice if dbms_stats could do an incremental refresh, tracking ONLY stats changes that might make a difference to execution plan: I'd settle for a flag I could turn on and off, saying: do/do not change stats for this object. I know which of them need to be analyzed and which don't. Better than Oracle will ever, deltas or no deltas, workload managers or not. Available in Oracle 10g - lock stats. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Should we stop analyzing?
Thanks. Sounds SUPER! Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - I'd settle for a flag I could turn on and off, saying: do/do not change stats for this object. snip Available in Oracle 10g - lock stats. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
RE: Should we stop analyzing?
Nice. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jonathan Lewis Sent: 09 January 2004 14:04 To: Multiple recipients of list ORACLE-L Subject: Re: Should we stop analyzing? Note in-line. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Friday, January 09, 2004 1:19 PM - Original Message - Wouldn't it be nice if dbms_stats could do an incremental refresh, tracking ONLY stats changes that might make a difference to execution plan: I'd settle for a flag I could turn on and off, saying: do/do not change stats for this object. I know which of them need to be analyzed and which don't. Better than Oracle will ever, deltas or no deltas, workload managers or not. Available in Oracle 10g - lock stats. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Niall Litchfield 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).
Re: Should we stop analyzing?
Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 1:14 AM When we consider that re-analyzing stats can cause huge changes to data access patterns I'm continuously amazed at the number of shops that re-analyze on a schedule and have the Monday Morning syndrome. The issue here is that very few people understand how the CBO works, or what the statistics do, or how to use them properly. If someone came to me and said: I'd like to inject some random numbers into the database every Monday morning I'd insist on proof of concept and rigid change control for evey set of random numbers Is some came to me and said: I need to keep the meta-data synchronised with the data, and install a routine to adjust certain components of the meta-data that the database cannot derive automatically I'd ask for one proof of concept, and a one-off change control. I have worked for shops where they must certify every change, no matter how trivial. Mostly banks and medical systems. So they have a difficult choice to make when the data changes sufficiently to make the out of date statistics a disaster and NEED to correct the statistics. Do they clone the production database, change the statistics, prove that the system can complete it's batch job in 8 hours, then install ? I doubt it. These certified shops are stuck. On one hand, they are obligated to follow the best-practices of their vendor, yet obligated not to make any untested changes in production. Even Oracle is schizophrenic on the issue; my contacts in the real-world performance group are zealously in favor of the take one deep sample approach, while the 10g developers are pissed that the CBO has been getting a bum-rap because of crappy statistics. Can you ask them what their approach is towards monotonic increasing values in columns, and the side-effects of the low/high basis for selectivity ? Personally, I love the automatic histogram generation skewonly and the auto option in dbms_stats, and use it for all my 9ir2 clients. However, I remain skeptical about the benefits of dynamic sampling and workload analysis automation tools for most shops. These two statements aren't entirely compatible. the skewonly and auto options are driven by built-in dynamic sampling and workload analysis automation tools. There's no very good reason why Oracle can build the only such tools that make sense - and in fact, it is arguable that a 3rd party may have a more general view of how these types of tools need to work because Oracle Corp tends to focus at two extremes - the very tiny (lab experiment) or the huge (big companies and TPC). In my experience, the vast majority of shops DO NOT benefit from re-analysis, and I've got shops where re-analysis NEVER results in CBO changes. But sometimes the re-analysis NEVER results in CBO changes because failure to re-analyze WOULD result in a detrimental CBO change. (Actually, re-analysis almost always results in CBO changes if the data has changed, but hardly anyone looks at the actual stats stored in user_table, user_indexes, user_tab_columns etc.) Regards, Donald K. Burleson www.dba-oracle.com www.remote-dba.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Should we stop analyzing?
- Original Message - The issue here is that very few people understand how the CBO works, or what the statistics do, or how to use them properly. And a seriously *big* component of that problem is that Oracle keeps changing/patching/modifying the CBO and how it reacts to certain combinations of information, on EVERY SINGLE point version! It's virtually impossible for a typical DBA to find the time to fully test all the combinations and find out what works where and how. Hence why this type of thread becomes so useful. Do they clone the production database, change the statistics, prove that the system can complete it's batch job in 8 hours, then install ? I doubt it. Exactly my point above. Not feasible. So, what's the alternative given that: 1- the information in Oracle's own doco is far from complete or covering all bases? 2- the CBO like any other piece of code, is sometimes buggy? 3- You, Don, SA, and so many others who have the time to investigate this may in turn not have the time to produce the detailed doco that is needed to understand how the CBO works in all situations? Is dynamic sampling the solution? I don't think so: if anything, it will enhance/increase the problems caused by 1 and 2. approach, while the 10g developers are pissed that the CBO has been getting a bum-rap because of crappy statistics. coughitwouldhavehelpediftheyhadfixedthecodebackin7/8/9insteadofdelivering aseriesofpatchesasnewreleasesthattotallyconfusedeverybodyaboutitsoperationcough Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: Should we stop analyzing?
Hi Nuno, Do they clone the production database, change the statistics, prove that the system can complete it's batch job in 8 hours, then install ? I doubt it. Exactly my point above. Not feasible. So, what's the alternative? You hit the Nail on the head here, Nuno. The central questions about stats changes are: 1- How can I list the changes to execution plans after re-analysis, A Priori? 2 - How do I justify the risk (and server expense) of re-analyzing? Some alternatives might be: 1 - Enhance the dbms_stats auto option (monitoring) to make it more intelligent. Wouldn't it be nice if dbms_stats could do an incremental refresh, tracking ONLY stats changes that might make a difference to execution plan: a) Changes to clustering_factor b) Changes to column skew. Only create histograms when column is skewed AND SQL uses the column. The 10g workload tool claims to do some of this. c) Changes to highest-lowest values of key indexes, etc. 2- Devise a method where new stats can be collected, stored and compared against historical SQL (from stats$sql_summary) a) Allow for dbms_stats to collect, store and compare changes to historical execution plans, using historical SQL from STATSPACK (or new 10g workload views) b) Allow the DBA control about whether to implement the new statistics coughitwouldhavehelpediftheyhadfixedthecodebackin7/8/9insteadofdelivering aseriesofpatchesasnewreleasesthattotallyconfusedeverybodyaboutitsoperationc ough Yes! IME, there ARE still problems in the CBO, especially with complex subqueries. I have more than a dozen systems where management insists on staying with the RBO! Every time we collect deep stats and histogram and switch optimizer_mode, hundreds of statements generate poor plans. It would cost these clients many thousands of dollars to have adjusted these plans, and management says If it ain't broke, why fix it. We need look no further than Oracle Applications to see this issue. Oracle made a big-deal about going to the CBO in 11i, yet when we look at the SQL, a significant number of statement employ the rule hint! Connect-the-dots and you can guess why the RBO IS NOT being removed from Oracle10g. . . . JMHO. . . . Regards, Donald K. Burleson www.dba-oracle.com www.remote-dba.net - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Thursday, January 08, 2004 5:19 AM - Original Message - The issue here is that very few people understand how the CBO works, or what the statistics do, or how to use them properly. And a seriously *big* component of that problem is that Oracle keeps changing/patching/modifying the CBO and how it reacts to certain combinations of information, on EVERY SINGLE point version! It's virtually impossible for a typical DBA to find the time to fully test all the combinations and find out what works where and how. Hence why this type of thread becomes so useful. Do they clone the production database, change the statistics, prove that the system can complete it's batch job in 8 hours, then install ? I doubt it. Exactly my point above. Not feasible. So, what's the alternative given that: 1- the information in Oracle's own doco is far from complete or covering all bases? 2- the CBO like any other piece of code, is sometimes buggy? 3- You, Don, SA, and so many others who have the time to investigate this may in turn not have the time to produce the detailed doco that is needed to understand how the CBO works in all situations? Is dynamic sampling the solution? I don't think so: if anything, it will enhance/increase the problems caused by 1 and 2. approach, while the 10g developers are pissed that the CBO has been getting a bum-rap because of crappy statistics. coughitwouldhavehelpediftheyhadfixedthecodebackin7/8/9insteadofdelivering aseriesofpatchesasnewreleasesthattotallyconfusedeverybodyaboutitsoperationc ough Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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: Don Burleson INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051 http://www.fatcity.com San Diego, California-- Mailing list and web hosting services
Re: Should we stop analyzing?
2- the CBO like any other piece of code, is sometimes buggy? Always, not sometimes Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
RE: Should we stop analyzing?
Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. Still it tries to make a better judgment .. Hey it is a whale lot better than those RDBMS where there are no hints to use in case optimizer goes crazy. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, January 08, 2004 11:29 AM To: Multiple recipients of list ORACLE-L 2- the CBO like any other piece of code, is sometimes buggy? Always, not sometimes Tanel. ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Should we stop analyzing?
Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. At this point it would seem beneficial to differentiate between a bug and a logic error. Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors A logic error would be found in code that does exactly what the developer intended, but what the developer intended is the wrong thing to do. This could be expanded to include the inability of CBO to properly identify a usage pattern. just my opinion. Jared On Thu, 2004-01-08 at 08:49, Jamadagni, Rajendra wrote: Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. Still it tries to make a better judgment .. Hey it is a whale lot better than those RDBMS where there are no hints to use in case optimizer goes crazy. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, January 08, 2004 11:29 AM To: Multiple recipients of list ORACLE-L 2- the CBO like any other piece of code, is sometimes buggy? Always, not sometimes Tanel. ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Jared Still 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).
RE: Should we stop analyzing?
Right Jared, But this is where the new 'learning CBO' comes into picture isn't it? in 10g CBO looks at the history and then modified the execution plans. This is all from Oracle 10g propaganda sheets, I'll agree when I see it in action. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, January 08, 2004 1:40 PM To: Multiple recipients of list ORACLE-L Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. At this point it would seem beneficial to differentiate between a bug and a logic error. Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors A logic error would be found in code that does exactly what the developer intended, but what the developer intended is the wrong thing to do. This could be expanded to include the inability of CBO to properly identify a usage pattern. just my opinion. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Should we stop analyzing?
Waddya mean, propaganda sheets? We never release propaganda - everything always works the way we say it does! :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Friday, 9 January 2004 5:49 AM To: Multiple recipients of list ORACLE-L Right Jared, But this is where the new 'learning CBO' comes into picture isn't it? in 10g CBO looks at the history and then modified the execution plans. This is all from Oracle 10g propaganda sheets, I'll agree when I see it in action. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, January 08, 2004 1:40 PM To: Multiple recipients of list ORACLE-L Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. At this point it would seem beneficial to differentiate between a bug and a logic error. Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors A logic error would be found in code that does exactly what the developer intended, but what the developer intended is the wrong thing to do. This could be expanded to include the inability of CBO to properly identify a usage pattern. just my opinion. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Pete Sharman 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).
RE: Should we stop analyzing?
Yeah, copped a copy of 10g new features from somewhere, but it was sorely lacking in detail. I seem to have missed the CBO bit. Jared On Thu, 2004-01-08 at 10:49, Jamadagni, Rajendra wrote: Right Jared, But this is where the new 'learning CBO' comes into picture isn't it? in 10g CBO looks at the history and then modified the execution plans. This is all from Oracle 10g propaganda sheets, I'll agree when I see it in action. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, January 08, 2004 1:40 PM To: Multiple recipients of list ORACLE-L Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. At this point it would seem beneficial to differentiate between a bug and a logic error. Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors A logic error would be found in code that does exactly what the developer intended, but what the developer intended is the wrong thing to do. This could be expanded to include the inability of CBO to properly identify a usage pattern. just my opinion. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Jared Still 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).
RE: Should we stop analyzing?
And if it doesn't it's a documentation error. ;-) At 12:09 PM 1/8/2004, you wrote: Waddya mean, propaganda sheets? We never release propaganda - everything always works the way we say it does! :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -- Author: Pete Sharman INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
RE: Should we stop analyzing?
Of course! Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Friday, 9 January 2004 6:29 AM To: Multiple recipients of list ORACLE-L And if it doesn't it's a documentation error. ;-) At 12:09 PM 1/8/2004, you wrote: Waddya mean, propaganda sheets? We never release propaganda - everything always works the way we say it does! :) Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -- Author: Pete Sharman INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Pete Sharman 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).
RE: Should we stop analyzing?
At 11:09 8-1-04 -0800, you wrote: 'Waddya mean, propaganda sheets? We never release propaganda - everything always works the way we say it does! ' Says Pat -Al-Shahaf- Sherman, so it must be the truth, the whole truth and nothing but the truth Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === Pete Controlling developers is like herding cats. Kevin Loney, Oracle DBA Handbook Oh no, it's not. It's much harder than that! Bruce Pihlamae, long-term Oracle DBA -Original Message- Sent: Friday, 9 January 2004 5:49 AM To: Multiple recipients of list ORACLE-L Right Jared, But this is where the new 'learning CBO' comes into picture isn't it? in 10g CBO looks at the history and then modified the execution plans. This is all from Oracle 10g propaganda sheets, I'll agree when I see it in action. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Thursday, January 08, 2004 1:40 PM To: Multiple recipients of list ORACLE-L Me thinks CBO is probably never going to be bug free. What works for you, won't work for me unless we run identical systems, it is a general purpose system, CBO doesn't know your system or data usage. At this point it would seem beneficial to differentiate between a bug and a logic error. Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors A logic error would be found in code that does exactly what the developer intended, but what the developer intended is the wrong thing to do. This could be expanded to include the inability of CBO to properly identify a usage pattern. just my opinion. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Pete Sharman 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: Carel-Jan Engel 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).
Re: Should we stop analyzing?
Comment in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors Several years ago I raised an issue with Oracle support where something was clearly going wrong - can't remember what, too long ago - and got told that I couldn't get the issue logged as a bug because the code was performing to specification. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
RE: Should we stop analyzing?
Then ask for the bug to be filed against the spec! ;-) Mark J. Bobak Oracle DBA ProQuest Company Ann Arbor, MI Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is. --Unknown -Original Message- Sent: Thursday, January 08, 2004 2:59 PM To: Multiple recipients of list ORACLE-L Comment in-line Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr Next public appearance2: March 2004 Hotsos Symposium - Keynote March 2004 Charlotte NC - OUG Tutorial April 2004 Iceland One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___February The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors Several years ago I raised an issue with Oracle support where something was clearly going wrong - can't remember what, too long ago - and got told that I couldn't get the issue logged as a bug because the code was performing to specification. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Bobak, Mark 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).
Re: Should we stop analyzing?
This opens a whole new can of worms. design bug specification bug 'get it out the door, now!' bug 'had a few too many porters when I wrote that bit' bug .. Jared On Thu, 2004-01-08 at 11:59, Jonathan Lewis wrote: - Original Message - Roughly, a bug would seem to be code that falls into one of two categories: * code that doesn't do what the developer intended * code that generates errors Several years ago I raised an issue with Oracle support where something was clearly going wrong - can't remember what, too long ago - and got told that I couldn't get the issue logged as a bug because the code was performing to specification. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: Jared Still 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).
Re: Should we stop analyzing?
Jonathan, Good point about the "change-control" issue. When we consider that re-analyzing stats can cause huge changes to data access patterns I’m continuously amazed at the number of shops that re-analyze on a schedule and have the “Monday Morning” syndrome. I have worked for shops where they must “certify” every change, no matter how trivial. Mostly banks and medical systems. These “certified” shops are stuck. On one hand, they are obligated to follow the best-practices of their vendor, yet obligated not to make any untested changes in production. Even Oracle is schizophrenic on the issue; my contacts in the real-world performance group are zealously in favor of the“take one deep sample” approach, while the 10g developers are pissed that the CBO has been getting a bum-rap because of crappy statistics. Personally, I love the automatic histogram generation “skewonly” and the “auto” option in dbms_stats, and use it for all my 9ir2 clients. However, I remain skeptical about the benefits of “dynamic sampling” and “workload analysis” automation tools for most shops. In my experience, the vast majority of shops DO NOT benefit from re-analysis, and I’ve got shops where re-analysis NEVER results in CBO changes. Regards, Donald K. Burlesonwww.dba-oracle.comwww.remote-dba.net - Original Message - From: "Jonathan Lewis" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 1:34 AM Subject: Re: Should we stop analyzing? This makes Oracle's position with 10g interesting, given that the default behaviour is to collect statistics all over the place automatically. If it's built in by the supplier, does it count as a change ? Jared's point is valid - in theory, if you keep statistics up to date, then the CBO should produce the optimum plan; if you fail to keep statistics up to date, the CBO plans can cease to be optimal, or may change to become sub-optimal. Moreover, in theory, if a plan changes on a change of statistics, it will be a better, or at worst equal cost, plan with at worst no change in performance. Of course, in the real world, we know that there are various reasons why things go wrong at the boundary points between plans, which is why we like to stick the statistics down well within our preferred boundary. Of course, following your argument about change control to its logical conclusion, since a change in the data may change execution plans, which may introduce untested portions of Oracle code, any data change should also be subject to change control. Despite any whimsical arguments, though, your basic premise is the important one. You need to know the application to do the job correctly. If you know how the data evolves, you will know how to get the minimum amount of work done that allows the optimizer to do its job well. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 11:44 PM At 03:29 PM 12/30/2003, you wrote: But then again, if re-collecting statistics causes your database performance to suddenly become very bad, it seems at first cut there are only two conclusions you can come to. 1) CBO is broke if fresh statistics result in poor performance That a plan changes due to changes in the statistics doesn't mean that the CBO is broke. That's the whole name of the game. The optimizer uses statistics - together with initialization parameters, heuristics and rules - to develop the anticipated best access path. If you change any of these, statistics by analyzing, initialization parameters by changes to the init.ora, or heuristics and rule by upgrading to a new version or applying a patch. I regard any of these changes as serious changes to the database which should go through a test and acceptance cycle. And that includes refreshing statistics. I am constantly amazed how nonchalantly most shops schedule daily, weekly, or whatever analyze jobs even if they batten down the hatches against changes to the application (Don Burleson alluded to that as well). Most of the time the changed statistics do not cause a change in access plans ( which immediately begs the question why do it then ), but ever so often the changed statistics cross a threshold to make a different plan appears to be better. It may be better, or it may turn out to be horrible. My point is: shouldn't that be tested first?Wolfgan
RE: Should we stop analyzing?
Jared, I think your conclusions must have assumptions: 1) The SQL was written correctly. 2) The data structures wrer designed and layed out properly. Here, we can AssUMe neither. :) Food for thought... Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Tuesday, December 30, 2003 4:29 PM To: Multiple recipients of list ORACLE-L Mogens, Quite a controversy you started here. As always. ;) I must admit this is the first time I've heard this come up. As Jonathan stated, it does seem somewhat like rebuilding indexes. But then again, if re-collecting statistics causes your database performance to suddenly become very bad, it seems at first cut there are only two conclusions you can come to. 1) CBO is broke if fresh statistics result in poor performance 2) statistics were collected at a time when temporary conditions created different statistics than what would normally appear. ie. at night when batch jobs are being run with lots of DML. No. 2 seems the likely candidate, unless of course, it's both 1 and 2. If just 2, then from a users perspective, it would seem most appropriate to have statistics collected during the day, when people are banging away on the OLTP stuff. But then, might that play havoc with the batch jobs? How about 2 sets of statistics. Import the OLTP stats in the morning for the users, and the BATCH stats at night for the batch jobs. I'm not sure if I should laugh at that, or not. Jared -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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).
Re: Should we stop analyzing?
Wolfgang, First off, sorry for mangling your name in the previous post. I too will make notes inline. On Tue, 2003-12-30 at 22:14, Wolfgang Breitling wrote: Note inline At 10:29 PM 12/30/2003, you wrote: If my data changes, and I analyze it, CBO should still find reasonable execution paths for the current data. If the CBO were infallable we wouldn't have this discussion. There are many reasons why even the most up-to-date statistics can lead to less than optimal access plans. My point is not necessarily with the frequency of statistics gathering but with the untested activation of new statistics, which is the hallmark of scheduled analyze jobs, as it carries the same risk as any untested change. If my data does not change, and I analyze it, CBO should have the same set of statistics as it did previously. If your data didn't change, or didn't change enough to make a difference in access plans, wouldn't you agree that the exercise of gathering statistics was futile and useless. I didn't dispute that. My point is, it shouldn't matter. One thing that may help to see another perspective is to consider the lone DBA in a medium sized company that doesn't have the luxury of spending much time trying to determine if stats should be run. That DBA may also be involved in development projects, maintenance and monitoring of the databases in 3 sites, maintaining licenses, running change control, and a few other goodies. (yes, that would be me) I automate as much of this as possible. No, stats don't need to be run frequently, but it shouldn't really matter if they are run periodically. I administer several SAP databases and use brconnect to manage the statistics. It does a fair job of only running dbms_stats when needed. ie. though there are 22k tables it may only cause 30 of them to be analyzed, as it did this week. brconnect is setup to run weekly, I check the logs occasionaly, one less thing to worry about. In a previous job we had a large team of DBA's and it was possible ( I had more time) to exercise more control over things like this. Other non-SAP database have automated jobs to run collect the stats weekly. Hasn't caused a problem so far, while on one of those databases, going for some time without an analyze does seem to cause some problems, IIRC. There are ideal ways to do things, but sometimes compromises are necessary. In a situation like this I will either automate stats gathering, or it likely will never be done. And then there's the fleet of RX-7's in my garage that demand attention, and of late I much prefer working on them to running maintenance Oracle jobs at work. :) Jared Is that not true, or is there some other piece missing here? If the current statistics produce access plans that render the required data in the time stipulated by your SLAs, why the urge to change something. You are getting dangerously close to symptoms of CTD. If, on the other hand, there are performance problems, they should be analyzed case by case and at that time the possibility that newer statistics will change the access plan and improve the performance should be explored. Jared -- Author: Jared Still INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Jared Still 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).
Re: Should we stop analyzing?
I didn't even notice. As for the rest of your rebuttal. I am not a religious fanatic. If it works for you, great. Just be aware of the risk involved and backup the statistics before analyzing them so that you can restore them in case things go sour after the analyze. I had one case for example where a developer had problems with a new sql. I wasn't at the office that day and the dba they called noticed that the statistics were several years old and decided that that must be the cause of the performance problem. Of course it wasn't (or else I wouldn't be using it, it actually turned out to be an Oracle bug that caused the session to terminate) but all the newly gathered statistics caused performance problems all over the place. Fortunately it was only a development database. I could have just copied the statistics from production, but I also have regular backups of the statistics (even though most don't change at all) and could easily restore the prior state. At 12:09 AM 12/31/2003, you wrote: Wolfgang, First off, sorry for mangling your name in the previous post. Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
RE: Should we stop analyzing?
-Original Message-From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]Sent: Tuesday, December 30, 2003 5:29 PMTo: Multiple recipients of list ORACLE-LSubject: Re: Should we stop analyzing? If just 2, then from a users perspective, it would seem most appropriate to have statistics collected during the day, when people are banging away on the OLTP stuff. But then, might that play havoc with the batch jobs? [Shrek] no might about it boss man, trust me. been there done that, got the T-shirt, the sweat shirt AND the leather jacket.;-) we eventually went to two sets of init filesafteri had a long fight with damagement.;-)How about 2 sets of statistics. Import the OLTP stats in the morning for the users, and the BATCH stats at night for the batch jobs. [Shrek] NOW he tells me.;-) could i have done that in 7.3 and 8i?;-) -- Bill "Shrek" Thater ORACLE DBA "I'm going to work my ticket if I can..." -- Gilwell song [EMAIL PROTECTED] The value of a program is proportional to the weight of its output.
RE: Should we stop analyzing?
Wolfgang, I don't have 9i available at the moment so I can't test this. Just wondering if a 10053 trace shows you if the statistics it is using are gathered from dynamic sampling. Henry -Original Message- Wolfgang Breitling Sent: Tuesday, December 30, 2003 6:24 PM To: Multiple recipients of list ORACLE-L The CBO will do dynamic sampling automatically provided the conditions are met. The conditions that need to be met depend on the dynamic_sampling initialization parameter in effect for the session. The default is 1 which practically disables dynamic sampling. 0 will totally disable it but IMHO the conditions for dynamic_sampling=1 are so rare (in practice) that one can regard it as off. BTW, even if the CBO goes to dynamic sampling that does not guarantee that it will use the statistics it did gather this way. At 03:24 PM 12/30/2003, you wrote: Tanel, I know the values, you are missing my question ... let me re-phrase it ... 1. To have CBO use dynamic sampling do you have to specify the hint? or 2. CBO will do that automatically? Just to let you know, Oracle 9ir2 docs main page is my home page on Mozilla firebird browser and Metalink is my homepage on IE. Raj --- - Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling parameter, you'll see descriptions for it's different values there. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Poras, Henry R. 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).
RE: Should we stop analyzing?
Jared, One problem is that the CBO sometimes CAN'T come up with the optimal execution plan. This could happen because it doesn't have all of the necessary data (i.e. histograms). There are also some types of data distribution that it ignores (see Wolfgang's paper at http://www.centrexcc.com/ Fallacies of the Cost Based Optimizer). For example, if two fields within a table, or across two tables are dependent, the optimizer won't know or use this information. What the CBO thinks is the best path based on estimated cardinalities can be way off. By accident, an inefficient execution plan (as seen by the CBO) might actually be more efficient than the CBO's optimal choice. Analyzing can change these plans even if nothing is broken. Henry -Original Message- Jared Still Sent: Wednesday, December 31, 2003 12:29 AM To: Multiple recipients of list ORACLE-L Wolgang, What I had in mind was simple DML, no patches, etc. Whether statistic are refreshed monthly, weekly, daily or every 2 hours, it doesn't make sense that this would create statistics that would be detrimental to performance, unless the data at the time the statistics are gathered is substantially different than at the time of usage. eg. stats gathered on a table when it has a few blocks allocated during a batch job, but later grows to few hundred thousand blocks prior to users hitting the system. Granted, it may be unnecessary continually analyze: as I stated earlier, this is the first time I've seen this discussed, so maybe I'm missing some important point about it that hasn't sunk in yet. MetaLink document 44961.1 supports infrequent analyzing, though it conveniently fail to define 'frequent'. If my data changes, and I analyze it, CBO should still find reasonable execution paths for the current data. If my data does not change, and I analyze it, CBO should have the same set of statistics as it did previously. Is that not true, or is there some other piece missing here? Jared On Tue, 2003-12-30 at 15:44, Wolfgang Breitling wrote: At 03:29 PM 12/30/2003, you wrote: But then again, if re-collecting statistics causes your database performance to suddenly become very bad, it seems at first cut there are only two conclusions you can come to. 1) CBO is broke if fresh statistics result in poor performance That a plan changes due to changes in the statistics doesn't mean that the CBO is broke. That's the whole name of the game. The optimizer uses statistics - together with initialization parameters, heuristics and rules - to develop the anticipated best access path. If you change any of these, statistics by analyzing, initialization parameters by changes to the init.ora, or heuristics and rule by upgrading to a new version or applying a patch. I regard any of these changes as serious changes to the database which should go through a test and acceptance cycle. And that includes refreshing statistics. I am constantly amazed how nonchalantly most shops schedule daily, weekly, or whatever analyze jobs even if they batten down the hatches against changes to the application (Don Burleson alluded to that as well). Most of the time the changed statistics do not cause a change in access plans ( which immediately begs the question why do it then ), but ever so often the changed statistics cross a threshold to make a different plan appears to be better. It may be better, or it may turn out to be horrible. My point is: shouldn't that be tested first? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Jared Still 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:
Re: Should we stop analyzing?
Yes, it does say when dynamic sampling is evaluated or executed. 1st example is where dyn sampling is used: - SINGLE TABLE ACCESS PATH *** 2003-12-31 17:25:07.521 ** Performing dynamic sampling initial checks. ** ** Dynamic sampling initial checks returning TRUE (level = 4). *** 2003-12-31 17:25:07.581 ** Generated dynamic sampling query: query text : SELECT /*+ ALL_ROWS IGNORE_WHERE_CLAUSE */ NVL(SUM(C1),0), NVL(SUM(C2),0) FROM (SELECT /*+ NOPARALLEL(T) */ 1 AS C1, 1 AS C2 FROM T T) SAMPLESUB *** 2003-12-31 17:25:07.631 ** Executed dynamic sampling query: level : 4 sample pct. : 100.00 actual sample size : 1 filtered sample card. : 1 orig. card. : 409 block cnt. : 5 max. sample block cnt. : 32 sample block cnt. : 5 min. sel. est. : -1. ** Using dynamic sampling card. : 1 TABLE: T ORIG CDN: 1 ROUNDED CDN: 1 CMPTD CDN: 1 Access path: tsc Resc: 3 Resp: 3 BEST_CST: 4.00 PATH: 2 Degree: 1 - Second example is where dynamic sampling is considered, but eventually not used - ** Performing dynamic sampling initial checks. ** ** Dynamic sampling initial checks returning TRUE (level = 1). ** Not using dynamic sampling:max. blk. (1) count too low. - When dyn sampling isn't even considered, then there'll be no lines about dynamic sampling in 10053 trace (except few parameter values which which are always written to trace). Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 4:59 PM Wolfgang, I don't have 9i available at the moment so I can't test this. Just wondering if a 10053 trace shows you if the statistics it is using are gathered from dynamic sampling. Henry -Original Message- Wolfgang Breitling Sent: Tuesday, December 30, 2003 6:24 PM To: Multiple recipients of list ORACLE-L The CBO will do dynamic sampling automatically provided the conditions are met. The conditions that need to be met depend on the dynamic_sampling initialization parameter in effect for the session. The default is 1 which practically disables dynamic sampling. 0 will totally disable it but IMHO the conditions for dynamic_sampling=1 are so rare (in practice) that one can regard it as off. BTW, even if the CBO goes to dynamic sampling that does not guarantee that it will use the statistics it did gather this way. At 03:24 PM 12/30/2003, you wrote: Tanel, I know the values, you are missing my question ... let me re-phrase it .. 1. To have CBO use dynamic sampling do you have to specify the hint? or 2. CBO will do that automatically? Just to let you know, Oracle 9ir2 docs main page is my home page on Mozilla firebird browser and Metalink is my homepage on IE. Raj --- - Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling parameter, you'll see descriptions for it's different values there. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Poras, Henry R. INET: [EMAIL PROTECTED] Fat City Network Services-- 858-538-5051
RE: Should we stop analyzing?
Yes, it does. extract from 10053 trace: ** Executed dynamic sampling query: level : 2 sample pct. : 11.151079 actual sample size : 2601 filtered sample card. : 2601 orig. card. : 11321 block cnt. : 278 max. sample block cnt. : 32 sample block cnt. : 31 ndv C3 : 12 scaled : 12.00 min. sel. est. : -1. ** Using dynamic sampling NDV estimates. Scaled NDVs using cardinality = 23325. ** Using dynamic sampling card. : 23325 It also tells you if it does NOT use the dynamic sampling results. Couldn't find an example right now. At 07:59 AM 12/31/2003, you wrote: Wolfgang, I don't have 9i available at the moment so I can't test this. Just wondering if a 10053 trace shows you if the statistics it is using are gathered from dynamic sampling. Henry -Original Message- Wolfgang Breitling Sent: Tuesday, December 30, 2003 6:24 PM To: Multiple recipients of list ORACLE-L The CBO will do dynamic sampling automatically provided the conditions are met. The conditions that need to be met depend on the dynamic_sampling initialization parameter in effect for the session. The default is 1 which practically disables dynamic sampling. 0 will totally disable it but IMHO the conditions for dynamic_sampling=1 are so rare (in practice) that one can regard it as off. BTW, even if the CBO goes to dynamic sampling that does not guarantee that it will use the statistics it did gather this way. At 03:24 PM 12/30/2003, you wrote: Tanel, I know the values, you are missing my question ... let me re-phrase it ... 1. To have CBO use dynamic sampling do you have to specify the hint? or 2. CBO will do that automatically? Just to let you know, Oracle 9ir2 docs main page is my home page on Mozilla firebird browser and Metalink is my homepage on IE. Raj - -- - Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling parameter, you'll see descriptions for it's different values there. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Poras, Henry R. 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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
RE: Should we stop analyzing?
Mogens Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: I've always told people that using the 'monitoring' option (alter table X monitoring in 8i, plus alter index I monitoring in 9i) was a good thing, because they would make sure that after a certain amound of data changes you got fresh stats (after, of course, using dbms_stats.gather_stale_statistics, etc. on the collected objects). We can always discuss whether the 10% threshold that gather_stale_statistics is based on is sound or not, but it can be as good as any other number. Except 42 :). But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. I too listened to Dan Fink at UKOUG and he made what I think is a really important distinction. You don't want statistics to be up-to-date, you want them to be accurate. In other words the frequency of analysis is not what we should care about, but how well the statistics reflect the data. The quote about the US_STATE table reflects this, when was the last time the US modified the states that make it up (the UK does the equivalent about every 10 years BTW). I don't think that your summary of what Dave Ensor said contradicts either this, or your recommendations. The stats become inaccurate when the data changes 'A LOT'. My gut feeling is that saying 'A LOT' = 10% is better than 'A LOT' = 'every 7 days', but probably not much. For one of our apps 'A LOT' turned out (predictably, but unpredicted :( ) to mean 13 rows in a single table, for the same app 250,000 rows in another table is irrelevant. 13 10% and 250k 10%. Obviously the people who had added the 13 rows 'haven't made any changes, what did you do to the database'. 'A LOT' is in my view almost certainly application and data dependent. Niall ( who admits that we still gather stats on the schema each week, but thinks this might be a bad risk :( ) -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Niall Litchfield 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).
Should we stop analyzing?
Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: I've always told people that using the 'monitoring' option (alter table X monitoring in 8i, plus alter index I monitoring in 9i) was a good thing, because they would make sure that after a certain amound of data changes you got fresh stats (after, of course, using dbms_stats.gather_stale_statistics, etc. on the collected objects). We can always discuss whether the 10% threshold that gather_stale_statistics is based on is sound or not, but it can be as good as any other number. Except 42 :). But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. It made terrific sense in one respect to let the stats stay the same, thus letting the optimizer have access to the same information, thus choosing the same execution plan instead of changing it constantly. On the other hand it was irritating, because I had always beleived (and said) the opposite. Even more frustrating was Anjo's grin afterwards and his Yeah, of course you shouldn't analyze all the time remark. Hrmf. So everybody else knew but me. Typical. Looking back, I can recall several places where they analyzed every weekend, and on Monday the system could very well behave differently. Makes sense if the optimizer has some new/different information to consider. On the other hand, it feels so intuitively right to constantly have up-to-date stats, doesn't it? I'd like to know what practical and philosofical ideas you guys have on this topic. Best regards - and Happy New Year, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
Re: Should we stop analyzing?
Analyzing over and over again might make your system unstable, because the optimizer each time might choose a different approach. But. If you never update/delete/your data after the initial load including initial analyze, performance will be consistent, and no surprises will hurt you. Instead of stopping analyzing alone, I would suggest to stop changing the contents of your database at all ;-). Is analyzing over and over again not one of the symptoms of CTD? I would not analyze weekly, or because x % of the data has changed. I would analyze when response times degrade. Then you can search for the cause (trace the SQL involved) and do some analyzing, when appeared necessary. I agree with Dave, except for the fact that an initial load might give a non-common distribution of the data compared to the more-or-less stabilized situation after a period of using/changing the contents. Most tables will stabilize after some time. You should reanalyze then, just the first time after initial load is not enough. Regards, Carel-Jan === If you think education is expensive, try ignorance. (Derek Bok) === At 02:34 30-12-03 -0800, you wrote: Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: I've always told people that using the 'monitoring' option (alter table X monitoring in 8i, plus alter index I monitoring in 9i) was a good thing, because they would make sure that after a certain amound of data changes you got fresh stats (after, of course, using dbms_stats.gather_stale_statistics, etc. on the collected objects). We can always discuss whether the 10% threshold that gather_stale_statistics is based on is sound or not, but it can be as good as any other number. Except 42 :). But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. It made terrific sense in one respect to let the stats stay the same, thus letting the optimizer have access to the same information, thus choosing the same execution plan instead of changing it constantly. On the other hand it was irritating, because I had always beleived (and said) the opposite. Even more frustrating was Anjo's grin afterwards and his Yeah, of course you shouldn't analyze all the time remark. Hrmf. So everybody else knew but me. Typical. Looking back, I can recall several places where they analyzed every weekend, and on Monday the system could very well behave differently. Makes sense if the optimizer has some new/different information to consider. On the other hand, it feels so intuitively right to constantly have up-to-date stats, doesn't it? I'd like to know what practical and philosofical ideas you guys have on this topic. Best regards - and Happy New Year, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
Re: Should we stop analyzing?
Hi Mogens, Ok, fun topic! Here is my take: 1 - Frequency of re-analyze - It astonishes me how many shops prohibit any un-approved production changes and get re-analyze schema stats weekly, acting surprised when things change! - I agree, most shops do not have to do this, and I agree with Dave; One very-deep sample (with histograms) is usually sufficient. - The only exception that I have seen are highly-dynamic (e.g. lab research) systems where a table is huge one-day and small the next. The 10g dynamic sampling feature may address this issue! - For my clients, I use the monitoring option and also method_opt=repeat, after I;'m confident that all histograms are in-place. 2 - Saving and re-using stats - I like the 9ir2 features for export and import of statistics, especially the ability to collect the external cup_cost and io_cost figures. - I have a client that got huge benefits from using two sets of stats, one for OLTP (daytime), and another for batch (evening jobs). - I also export production stats into the development instances so that execution plan more closely resemble production. 3 - Getting top-quality stats - I think that the CBO has gotten a bad reputation solely because the DBA does not give the CBO good statistics. - In 9ir2, the CBO almost always makes a good decision when given good schema information. - Because 9i stats work best with external system load, I like to schedule a valid sample (method_opt=auto_sample_size) during regular working hours. 4 - My pet peeves - I see a lot of shops that do not use method_opt=skewonly and suffer from poor execution plans on skewed column access. - Many DBAs forget that the CBO must have foreign-key histograms in order to determine the optimal table join order (i.e. the ORDERED hint). - Whenever I see a sub-optimal order for table joins, I resist the temptation to add the ORDERED hint, and instead create histograms on the foreign keys of the join. - I'm playing with the 10g automatic histogram collection mechanism that interrogates v$sql_plan to see where the foreign keys are and generate histograms when appropriate. Very cool! BTW, what's the deal with Dave Ensor? He told be that he was retiring from BMC to become a Barrister! Regards, Donald K. Burleson www.dba-oracle.com www.remote-dba.net - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:34 AM Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: I've always told people that using the 'monitoring' option (alter table X monitoring in 8i, plus alter index I monitoring in 9i) was a good thing, because they would make sure that after a certain amound of data changes you got fresh stats (after, of course, using dbms_stats.gather_stale_statistics, etc. on the collected objects). We can always discuss whether the 10% threshold that gather_stale_statistics is based on is sound or not, but it can be as good as any other number. Except 42 :). But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. It made terrific sense in one respect to let the stats stay the same, thus letting the optimizer have access to the same information, thus choosing the same execution plan instead of changing it constantly. On the other hand it was irritating, because I had always beleived (and said) the opposite. Even more frustrating was Anjo's grin afterwards and his Yeah, of course you shouldn't analyze all the time remark. Hrmf. So everybody else knew but me. Typical. Looking back, I can recall several places where they analyzed every weekend, and on Monday the system could very well behave differently. Makes sense if the optimizer has some new/different information to consider. On the other hand, it feels so intuitively right to constantly have up-to-date stats, doesn't it? I'd like to know what practical and philosofical ideas you guys have on this topic. Best regards - and Happy New Year, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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
RE: Should we stop analyzing?
Mogens, We've been in the same situation here where analyzing was turned off to stop problems occurring (partly because of Oracle 7 and the fact that histograms were created at a second stage so if the analyze failed part way through the histograms were lost). Although the data does not change significantly in character once a database is a bit older we have the problem then of how to manage any new tables or indexes which may take a bit of time to reach a more stable character. For this reason as we've just moved to 9i I'd like to see us going back to analyzing every week but only stale statistics. If the database only had application changes once a year or so then I'd think not analyzing was a more sensible option but I believe I'd seen someone claim (not sure with which version of the optimizer) that the table size etc were also looked at rather than just purely stats so changes in execution plan could still occur. I suppose all that I'm saying above boils down to IMHO it depends but it is certainly an arguable point, though less so in 9i than previous version. Iain Nicoll -Original Message- Mogens Nørgaard Sent: 30 December 2003 10:34 To: Multiple recipients of list ORACLE-L Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: I've always told people that using the 'monitoring' option (alter table X monitoring in 8i, plus alter index I monitoring in 9i) was a good thing, because they would make sure that after a certain amound of data changes you got fresh stats (after, of course, using dbms_stats.gather_stale_statistics, etc. on the collected objects). We can always discuss whether the 10% threshold that gather_stale_statistics is based on is sound or not, but it can be as good as any other number. Except 42 :). But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. It made terrific sense in one respect to let the stats stay the same, thus letting the optimizer have access to the same information, thus choosing the same execution plan instead of changing it constantly. On the other hand it was irritating, because I had always beleived (and said) the opposite. Even more frustrating was Anjo's grin afterwards and his Yeah, of course you shouldn't analyze all the time remark. Hrmf. So everybody else knew but me. Typical. Looking back, I can recall several places where they analyzed every weekend, and on Monday the system could very well behave differently. Makes sense if the optimizer has some new/different information to consider. On the other hand, it feels so intuitively right to constantly have up-to-date stats, doesn't it? I'd like to know what practical and philosofical ideas you guys have on this topic. Best regards - and Happy New Year, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Nicoll, Iain 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).
Re: Should we stop analyzing?
- Original Message - I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? As a regular thing, yes. Unless there is a clear case for doing it often: highly variable tables. And even then, I want to know WHEN to analyze: when they are empty or when they are full? Looking back, I can recall several places where they analyzed every weekend, and on Monday the system could very well behave differently. and usually for the worse. I recall a particular PS site where every time we analyzed, we got into trouble... That was with 8.0 and there was nothing we could do other than stop analyzing. Which we did and the problems went away (on that particular table). Makes sense if the optimizer has some new/different information to consider. Yeah, but the $64K question is: HOW do you know that the optimizer has something different to consider? There is nothing (other than seat-of-the-pants feeling or prior knowledge of behaviour of app) that will tell you that. On the other hand, it feels so intuitively right to constantly have up-to-date stats, doesn't it? No, not at all. I'm really against this tune-every-minute approach. From my point of view, I want to get the darn thing running OK and then LOCK IT IN so it doesn't blow in my face unexpectedly. I'll gladly trade the last 10% of performance I might (and I stress the might) get for a system that behaves reasonably well and STAYS that way all the time! Makes for quiet nights, full of sleep. And at my age I like those more and more... I'd like to know what practical and philosofical ideas you guys have on this topic. Well, my approach has always been: get the thing to perform within 10-20% of optimal and lock it in so it won't suddenly go South. It's much more important for me not to spring surprises on users than to give them a system that's only tuned optimally for 5 minutes before I next run the stats. And the last thing I want when all hell breaks loose and I've got the site manager breathing down my neck is to overload the system even more with a full analyze... But that may be just me. Best regards - and Happy New Year, To you too. Cheers Nuno Souto [EMAIL PROTECTED] -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
Re: Should we stop analyzing?
It's just like index rebuilding. Too many people do it too aggressively, too often and waste their time and the machine resources doing it for very little benefit. But if you have the time and resources, then it doesn't often do too much damage. However, there are cases where you really do need to get some statistics up to date - particularly for columns like timestamps or sequences that are always increasing in value. NOTE - up to date= correct for the current moment in time fresh= recently acquired To quote one of the people at the UKOUG conference: The statistics on the US_STATES table aren't fresh, because I gathered them 5 years ago but they are up to date, because the number, names, and abbreviations for the states haven't changed recently. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 10:34 AM Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
RE: Should we stop analyzing?
Mogens, if you are looking for a poster boy ... We analyze 9 production databases ... *every day*. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Should we stop analyzing?
On one of our OLTP databases (designed in the dark ages, made-for-rbo database design), we have seen time and again that if we skip statistics collection for a day, queries go to the town. So, reluctantly we have to analyze (a 10% keeps the developer/CBO/Query trio happy). Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Should we stop analyzing?
I have you beat one schema in one of our databases (9.2.0.2) is analyzed every 4 hours. Not mine, and I *will* be talking to the DBA about his reasoning... however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is estimate at least. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Mogens, if you are looking for a poster boy ... We analyze 9 production databases ... *every day*. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
RE: Should we stop analyzing?
Mogens Nørgaard scribbled on the wall in glitter crayon: I'd like to know what practical and philosofical ideas you guys have on this topic. i think a lot of this depends on the optimizer. i know the cost biased one has improved dramatically since it was introduced. and i thought that the purpose of gathering statistics was to enable the optimizer to pick the best and fastest execution path for each query at the time the query is being run. didn't the creation of outlines provide for those times that you wanted a set execution path for a query to persist? but then again i'm just a simple grunt DBA and may have all of this stuff wrong. Best regards - and Happy New Year, and to you and the rest of the list as well. may you all have a year filled with good times, good friends and an open heart. -- Bill Shrek Thater ORACLE DBA I'm going to work my ticket if I can... -- Gilwell song [EMAIL PROTECTED] The best way to predict your future is to create it. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Thater, William 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).
Re: Should we stop analyzing?
however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is estimate at least. Or update HIVAL and DISTCNT and ROWCNT statistics using dbms_stats regularly... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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).
Re: Should we stop analyzing?
we are using dbms_stats, gather auto, for all indexed columns and estimate 15% Now if my other DBA would just show up for work, I can ask him about this. Sometimes being the early bird has disadvantages. I do know that when the analyze is not done, we have performance problems. Or at least the end-users complain about performance. Of course part of the problem is that they can do any sort of requests through the system, which turn into ad-hoc queries which end up returning enormous amounts of data. so they moan the database is too slow. --- Tanel Poder [EMAIL PROTECTED] wrote: however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is estimate at least. Or update HIVAL and DISTCNT and ROWCNT statistics using dbms_stats regularly... Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Tanel Poder 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
RE: Should we stop analyzing?
I strongly suspect I'm missing something here, but I don't see a problem with gathering stale many times a day, every hour say. If your tables aren't subject to much DML activity then they won't be analysed anyway. On Tue, 2003-12-30 at 12:59, Rachel Carmichael wrote: I have you beat one schema in one of our databases (9.2.0.2) is analyzed every 4 hours. Not mine, and I *will* be talking to the DBA about his reasoning... however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is estimate at least. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Mogens, if you are looking for a poster boy ... We analyze 9 production databases ... *every day*. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Austin Hackett 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).
Re: Should we stop analyzing?
Hehehe! You rat! :D Cheers Nuno Souto [EMAIL PROTECTED] - Original Message - Or update HIVAL and DISTCNT and ROWCNT statistics using dbms_stats regularly... -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Nuno Souto 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).
RE: Should we stop analyzing?
Makes sense, BUT... If the data changes A LOT you should of course re-analyze. is assuming you know when that happens. You are assuming communication between users, developers, and DBAs. Communication is my New Year's Resolution. I would at least suggest exporting stats before changing them. Then there are all the extra problems such as retaining histograms with 8i, applications which constantly insert/delete from temporary tables (not Oracle temp tables), ... Henry -Original Message- But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Poras, Henry R. 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).
RE: Should we stop analyzing?
This is slightly OT ... Talking about exporting stats ... I do that and about 30 seconds ago finished writing a SQL that looks at a history of exported stats and displays a 7 day pattern of 1. rowcount changes 2. average row length change 3. allocated blocks changes basic treand analysis should be possible from exported stats and monitoring info ... right? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 10:24 AM To: Multiple recipients of list ORACLE-L Makes sense, BUT... If the data changes A LOT you should of course re-analyze. is assuming you know when that happens. You are assuming communication between users, developers, and DBAs. Communication is my New Year's Resolution. I would at least suggest exporting stats before changing them. Then there are all the extra problems such as retaining histograms with 8i, applications which constantly insert/delete from temporary tables (not Oracle temp tables), ... Henry ** This e-mail message is confidential, intended only for the named recipient(s) above and may contain information that is privileged, attorney work product or exempt from disclosure under applicable law. If you have received this message in error, or are not the named recipient(s), please immediately notify corporate MIS at (860) 766-2000 and delete this e-mail message from your computer, Thank you. **4 -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Should we stop analyzing?
Title: RE: Should we stop analyzing? I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say the table is full but actually is empty takes a little longer when CBO says use indexes; however, if CBO thinks the table is empty and does a FTS when there's actually a million records, well let's just say it takes a while. Hints work sometimes; however, analyzing these table after they are populated and letting CBO do it's job usually works best. Two points. First this particular database had not been analyzed for over a year when I got there and this database gets larger daily. They added indexes but it didn't make much difference. After analyzing, most things were much faster; however, the 'work' tables started acting up depending on their state when analyzed. We now analyze twice a month. Second when it comes to these 'work' tables, I wasn't there and it wasn't my idea! Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Rachel Carmichael [SMTP:[EMAIL PROTECTED] I have you beat one schema in one of our databases (9.2.0.2) is analyzed every 4 hours. Not mine, and I *will* be talking to the DBA about his reasoning... however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is estimate at least. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Mogens, if you are looking for a poster boy ... We analyze 9 production databases ... *every day*. Raj
Re: Should we stop analyzing?
Title: RE: Should we stop analyzing? In 9i you could use optimizer_dynamic_sampling for such "work" tables Tanel. - Original Message - From: Whittle Jerome Contr NCI To: Multiple recipients of list ORACLE-L Sent: Tuesday, December 30, 2003 6:09 PM Subject: RE: Should we stop analyzing? I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say the table is full but actually is empty takes a little longer when CBO says use indexes; however, if CBO thinks the table is empty and does a FTS when there's actually a million records, well let's just say it takes a while. Hints work sometimes; however, analyzing these table after they are populated and letting CBO do it's job usually works best. Two points. First this particular database had not been analyzed for over a year when I got there and this database gets larger daily. They added indexes but it didn't make much difference. After analyzing, most things were much faster; however, the 'work' tables started acting up depending on their state when analyzed. We now analyze twice a month. Second when it comes to these 'work' tables, I wasn't there and it wasn't my idea! Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Rachel Carmichael [SMTP:[EMAIL PROTECTED] I have you beat one schema in one of our databases (9.2.0.2) is analyzed every 4 hours. Not mine, and I *will* be talking to the DBA about his reasoning... however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is "estimate" at least. --- "Jamadagni, Rajendra" [EMAIL PROTECTED] wrote: Mogens, if you are looking for a poster boy ... We analyze 9 production databases ... *every day*. Raj
RE: Should we stop analyzing?
I fold :) --- Whittle Jerome Contr NCI [EMAIL PROTECTED] wrote: I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say the table is full but actually is empty takes a little longer when CBO says use indexes; however, if CBO thinks the table is empty and does a FTS when there's actually a million records, well let's just say it takes a while. Hints work sometimes; however, analyzing these table after they are populated and letting CBO do it's job usually works best. Two points. First this particular database had not been analyzed for over a year when I got there and this database gets larger daily. They added indexes but it didn't make much difference. After analyzing, most things were much faster; however, the 'work' tables started acting up depending on their state when analyzed. We now analyze twice a month. Second when it comes to these 'work' tables, I wasn't there and it wasn't my idea! Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Rachel Carmichael [SMTP:[EMAIL PROTECTED] I have you beat one schema in one of our databases (9.2.0.2) is analyzed every 4 hours. Not mine, and I *will* be talking to the DBA about his reasoning... however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is estimate at least. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Mogens, if you are looking for a poster boy ... We analyze 9 production databases ... *every day*. Raj __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael 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).
Re: Should we stop analyzing?
That's (partly) what the 9i dynamic sampling feature is for. And such tables are, of course, going to be GTTs. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:09 PM I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say the table is full but actually is empty takes a little longer when CBO says use indexes; however, if CBO thinks the table is empty and does a FTS when there's actually a million records, well let's just say it takes a while. Hints work sometimes; however, analyzing these table after they are populated and letting CBO do it's job usually works best. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
Re: Should we stop analyzing?
Now there's a thread from my heart. I have been saying and practicing (where I'm allowed to as a outside contractor) that for years. I am dead against regularly scheduled analyze jobs - it must be Sunday because the analyze is running - but it is sometimes hard to convince the resident DBAs of the futility and even outright danger of the practice. In one system for which I was the DBA for several years most of the tables have not been analyzed sine May 2001 when the system was upgraded to 8i. Even the yearly partitions are not re-analyzed when they are split off the maxvalue partition. I just copy the statistics from a prior year partition. There are some tables where the histograms on certain columns need to be re-calculated every night because of an update that changes the data distribution completely ( the column values are ever increasing and the new most frequently occurring value is larger than the previous maximum value ). For me the bottom line is you need to know your system(s) and what is required, but don't just blindly analyze on a schedule for the sole purpose of keeping the stats up-to-date. If you analyze, there must be a (documented) reason for it and that reason must be tied to improving or preserving the response time of the application or parts of the application and not because it is the weekend and I have the time and resources to do it. At 03:34 AM 12/30/2003, you wrote: Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: I've always told people that using the 'monitoring' option (alter table X monitoring in 8i, plus alter index I monitoring in 9i) was a good thing, because they would make sure that after a certain amound of data changes you got fresh stats (after, of course, using dbms_stats.gather_stale_statistics, etc. on the collected objects). We can always discuss whether the 10% threshold that gather_stale_statistics is based on is sound or not, but it can be as good as any other number. Except 42 :). But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. It made terrific sense in one respect to let the stats stay the same, thus letting the optimizer have access to the same information, thus choosing the same execution plan instead of changing it constantly. On the other hand it was irritating, because I had always beleived (and said) the opposite. Even more frustrating was Anjo's grin afterwards and his Yeah, of course you shouldn't analyze all the time remark. Hrmf. So everybody else knew but me. Typical. Looking back, I can recall several places where they analyzed every weekend, and on Monday the system could very well behave differently. Makes sense if the optimizer has some new/different information to consider. On the other hand, it feels so intuitively right to constantly have up-to-date stats, doesn't it? I'd like to know what practical and philosofical ideas you guys have on this topic. Best regards - and Happy New Year, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
RE: Should we stop analyzing?
to get dynamic sampling one must specify that as a hint .. right? can cbo use dynamic sampling automatically on GTTs? (Hey, it's new year time and some wishful thinking is in order). Happy New Year. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 11:44 AM To: Multiple recipients of list ORACLE-L That's (partly) what the 9i dynamic sampling feature is for. And such tables are, of course, going to be GTTs. Regards Jonathan Lewis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Should we stop analyzing?
I am surprised no one raised the issue of invalidations in the shared pool caused by Stats gathering, and the parsing/reloading load that is caused _after_ the extra I/O and changed plans due to ANALYZEs I have this 250Gb Apps database that is analyzed once a month and we have not suffered due to incorrect or stale statistics. Projects in the new year include revisting the Stats gathering schedules of all our 90+ databases, some of which are analyzed daily :( Have a happy, blessed new year all! John Kanagaraj DB Soft Inc Phone: 408-970-7002 (W) Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com ** The opinions and facts contained in this message are entirely mine and do not reflect those of my employer or customers ** -Original Message- From: Jonathan Lewis [mailto:[EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 8:44 AM To: Multiple recipients of list ORACLE-L Subject: Re: Should we stop analyzing? That's (partly) what the 9i dynamic sampling feature is for. And such tables are, of course, going to be GTTs. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 4:09 PM I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say the table is full but actually is empty takes a little longer when CBO says use indexes; however, if CBO thinks the table is empty and does a FTS when there's actually a million records, well let's just say it takes a while. Hints work sometimes; however, analyzing these table after they are populated and letting CBO do it's job usually works best. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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: John Kanagaraj 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).
Re: Should we stop analyzing?
there's also an optimizer_dynamic_sampling init parameter (in addition to hint) Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 7:14 PM to get dynamic sampling one must specify that as a hint .. right? can cbo use dynamic sampling automatically on GTTs? (Hey, it's new year time and some wishful thinking is in order). Happy New Year. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 11:44 AM To: Multiple recipients of list ORACLE-L That's (partly) what the 9i dynamic sampling feature is for. And such tables are, of course, going to be GTTs. Regards Jonathan Lewis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Tanel Poder 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).
Re: Should we stop analyzing?
There is a hint, and there is a parameter. optimizer_dynamic_sampling = 2 is probably a good way of making sure that all queries involving GTTs get a dynamic sample of 32 blocks on the GTT Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 5:14 PM to get dynamic sampling one must specify that as a hint .. right? can cbo use dynamic sampling automatically on GTTs? (Hey, it's new year time and some wishful thinking is in order). Happy New Year. Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
RE: Should we stop analyzing?
Is there an easy way to track the rate of change in a particular table? -Original Message- Sent: Tuesday, December 30, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Makes sense, BUT... If the data changes A LOT you should of course re-analyze. is assuming you know when that happens. You are assuming communication between users, developers, and DBAs. Communication is my New Year's Resolution. I would at least suggest exporting stats before changing them. Then there are all the extra problems such as retaining histograms with 8i, applications which constantly insert/delete from temporary tables (not Oracle temp tables), ... Henry -Original Message- But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Poras, Henry R. 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: Josh Collier 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).
RE: Should we stop analyzing?
select count(*) on the PK each day and store the results for tracking. monitor the extent usage for the table. audit the table. [EMAIL PROTECTED] 12/30/2003 12:49:33 PM Is there an easy way to track the rate of change in a particular table? -Original Message- Sent: Tuesday, December 30, 2003 7:24 AM To: Multiple recipients of list ORACLE-L Makes sense, BUT... If the data changes A LOT you should of course re-analyze. is assuming you know when that happens. You are assuming communication between users, developers, and DBAs. Communication is my New Year's Resolution. I would at least suggest exporting stats before changing them. Then there are all the extra problems such as retaining histograms with 8i, applications which constantly insert/delete from temporary tables (not Oracle temp tables), ... Henry -Original Message- But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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: Poras, Henry R. 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: Josh Collier 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: Ron Rogers 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).
RE: Should we stop analyzing?
Thanks Jonathan,Tanel Some more clarification ... is dynamic sampling automatically used or one must specify the hint? Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 12:30 PM To: Multiple recipients of list ORACLE-L There is a hint, and there is a parameter. optimizer_dynamic_sampling = 2 is probably a good way of making sure that all queries involving GTTs get a dynamic sample of 32 blocks on the GTT Regards Jonathan Lewis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
RE: Should we stop analyzing?
Interesting! Could this account for LOADS1 on pinned objects? Damn. Almost got thru the rest of the year without learning anything new. :) Rich Rich Jesse System/Database Administrator [EMAIL PROTECTED] Quad/Tech Inc, Sussex, WI USA -Original Message- Sent: Tuesday, December 30, 2003 11:19 AM To: Multiple recipients of list ORACLE-L I am surprised no one raised the issue of invalidations in the shared pool caused by Stats gathering, and the parsing/reloading load that is caused _after_ the extra I/O and changed plans due to ANALYZEs I have this 250Gb Apps database that is analyzed once a month and we have not suffered due to incorrect or stale statistics. Projects in the new year include revisting the Stats gathering schedules of all our 90+ databases, some of which are analyzed daily :( Have a happy, blessed new year all! John Kanagaraj -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jesse, Rich 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).
Re: Should we stop analyzing?
Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling parameter, you'll see descriptions for it's different values there. Tanel. - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 8:59 PM Thanks Jonathan,Tanel Some more clarification ... is dynamic sampling automatically used or one must specify the hint? Raj -- -- Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 12:30 PM To: Multiple recipients of list ORACLE-L There is a hint, and there is a parameter. optimizer_dynamic_sampling = 2 is probably a good way of making sure that all queries involving GTTs get a dynamic sample of 32 blocks on the GTT Regards Jonathan Lewis -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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: Tanel Poder 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).
RE: Should we stop analyzing?
Tanel, I know the values, you are missing my question ... let me re-phrase it ... 1. To have CBO use dynamic sampling do you have to specify the hint? or 2. CBO will do that automatically? Just to let you know, Oracle 9ir2 docs main page is my home page on Mozilla firebird browser and Metalink is my homepage on IE. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling parameter, you'll see descriptions for it's different values there. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
Re: Should we stop analyzing?
Mogens, Quite a controversy you started here. As always. ;) I must admit this is the first time I've heard this come up. As Jonathan stated, it does seem somewhat like rebuilding indexes. But then again, if re-collecting statistics causes your database performance to suddenly become very bad, it seems at first cut there are only two conclusions you can come to. 1) CBO is broke if fresh statistics result in poor performance 2) statistics were collected at a time when temporary conditions created different statistics than what would normally appear. ie. at night when batch jobs are being run with lots of DML. No. 2 seems the likely candidate, unless of course, it's both 1 and 2. If just 2, then from a users perspective, it would seem most appropriate to have statistics collected during the day, when people are banging away on the OLTP stuff. But then, might that play havoc with the batch jobs? How about 2 sets of statistics. Import the OLTP stats in the morning for the users, and the BATCH stats at night for the batch jobs. I'm not sure if I should laugh at that, or not. Jared Mogens Nørgaard [EMAIL PROTECTED] Sent by: [EMAIL PROTECTED] 12/30/2003 02:34 AM Please respond to ORACLE-L To:Multiple recipients of list ORACLE-L [EMAIL PROTECTED] cc: Subject:Should we stop analyzing? Friends, I'd like to start a debate, which perhaps has already taken place, but if so I don't recall it: Should we stop analyzing tables and indexes? Let me clarify: I've always told people that using the 'monitoring' option (alter table X monitoring in 8i, plus alter index I monitoring in 9i) was a good thing, because they would make sure that after a certain amound of data changes you got fresh stats (after, of course, using dbms_stats.gather_stale_statistics, etc. on the collected objects). We can always discuss whether the 10% threshold that gather_stale_statistics is based on is sound or not, but it can be as good as any other number. Except 42 :). But then I listened to Dave Ensor at the UKOUG conference, and he said roughly this: * Stop analyzing after the first analyze. It's the new stats that cause the optimizer to change execution plans. * I know that big tables tend to stay big. Small tables stay small. Unique indexes stay unique and non-unique indexes stay non-unique... * If the data changes A LOT you should of course re-analyze. It made terrific sense in one respect to let the stats stay the same, thus letting the optimizer have access to the same information, thus choosing the same execution plan instead of changing it constantly. On the other hand it was irritating, because I had always beleived (and said) the opposite. Even more frustrating was Anjo's grin afterwards and his Yeah, of course you shouldn't analyze all the time remark. Hrmf. So everybody else knew but me. Typical. Looking back, I can recall several places where they analyzed every weekend, and on Monday the system could very well behave differently. Makes sense if the optimizer has some new/different information to consider. On the other hand, it feels so intuitively right to constantly have up-to-date stats, doesn't it? I'd like to know what practical and philosofical ideas you guys have on this topic. Best regards - and Happy New Year, Mogens -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: =?ISO-8859-1?Q?Mogens_N=F8rgaard?= 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).
RE: Should we stop analyzing?
I had a similar situation once working in a data warehouse environment. One example is a job that recreated a large dimension table each night: The dimension table was truncated and reconstructed in phases - this was by far the most efficient approach. It was necessary to analyze the table part way through the building phase though. Doing analyze at this particular point provided a massive performance increase. We could have probably worked around it with hints but it was easier to just analyze the table at particular points within the build script. A quick estimate was all that was required and the couple of seconds spend analyzing could shave maybe 30 minutes off the execution time. A final estimate at the end of the batch meant that the stats were then valid until the next time the table was rebuilt. Whittle Jerome Contr NCITo: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] [EMAIL PROTECTED]cc: ott.af.mil Subject: RE: Should we stop analyzing? Sent by: [EMAIL PROTECTED] com 31/12/2003 03:09 Please respond to ORACLE-L I'll see your 'analyzed every 4 hours' and raise you one. We have some tables that are analyzed every time they are used! They are 'work' tables that are sometimes empty, very full, or somewhere in between. Running something when the statistics say the table is full but actually is empty takes a little longer when CBO says use indexes; however, if CBO thinks the table is empty and does a FTS when there's actually a million records, well let's just say it takes a while. Hints work sometimes; however, analyzing these table after they are populated and letting CBO do it's job usually works best. Two points. First this particular database had not been analyzed for over a year when I got there and this database gets larger daily. They added indexes but it didn't make much difference. After analyzing, most things were much faster; however, the 'work' tables started acting up depending on their state when analyzed. We now analyze twice a month. Second when it comes to these 'work' tables, I wasn't there and it wasn't my idea! Jerry Whittle ASIFICS DBA NCI Information Systems Inc. [EMAIL PROTECTED] 618-622-4145 -Original Message- From: Rachel Carmichael [SMTP:[EMAIL PROTECTED] I have you beat one schema in one of our databases (9.2.0.2) is analyzed every 4 hours. Not mine, and I *will* be talking to the DBA about his reasoning... however Jonathan's point may well be the reason. This is an ever-growing database, frequent insert and updates, and sequences are used throughout. Analyze is estimate at least. --- Jamadagni, Rajendra [EMAIL PROTECTED] wrote: Mogens, if you are looking for a poster boy ... We analyze 9 production databases ... *every day*. Raj Privileged/Confidential information may be contained in this message. If you are not the addressee indicated in this message (or responsible for delivery of the message to such person), you may not copy or deliver this message to anyone. In such a case, you should destroy this message and kindly notify
RE: Should we stop analyzing?
The CBO will do dynamic sampling automatically provided the conditions are met. The conditions that need to be met depend on the dynamic_sampling initialization parameter in effect for the session. The default is 1 which practically disables dynamic sampling. 0 will totally disable it but IMHO the conditions for dynamic_sampling=1 are so rare (in practice) that one can regard it as off. BTW, even if the CBO goes to dynamic sampling that does not guarantee that it will use the statistics it did gather this way. At 03:24 PM 12/30/2003, you wrote: Tanel, I know the values, you are missing my question ... let me re-phrase it ... 1. To have CBO use dynamic sampling do you have to specify the hint? or 2. CBO will do that automatically? Just to let you know, Oracle 9ir2 docs main page is my home page on Mozilla firebird browser and Metalink is my homepage on IE. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling parameter, you'll see descriptions for it's different values there. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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). Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
Re: Should we stop analyzing?
actually with this parameter description I pointed you to, there were pointers to perf tuning guide: quote about optimizer_dynamic_sampling parameter You control dynamic sampling with the OPTIMIZER_DYNAMIC_SAMPLING parameter, which can be set to a value from 0 to 10. A value of 0 means dynamic sampling will not be done. A value of 1 (the default) means dynamic sampling will be performed if all of the following conditions are true: There is more than one table in the query. Some table has not been analyzed and has no indexes. The optimizer determines that a relatively expensive table scan would be required for this unanalyzed table. Increasing the value of the parameter results in more aggressive application of dynamic sampling, in terms of both the type of tables sampled (analyzed or unanalyzed) and the amount of I/O spent on sampling. /quote I haven't tested what happens when you already have statistics and specify dynamic sampling hint, then which stats are used after all... but it's very easy to check out with 10053 trace, it has a lines about dynamic sampling in it when Oracle uses or tries to use dynamic sampling. Tanel. - Original Message - From: "Jamadagni, Rajendra" [EMAIL PROTECTED] To: "Multiple recipients of list ORACLE-L" [EMAIL PROTECTED] Sent: Wednesday, December 31, 2003 12:24 AM Subject: RE: Should we stop analyzing? Tanel, I know the values, you are missing my question ... let me re-phrase it ... 1. To have CBO use dynamic sampling do you have to specify the hint? or 2. CBO will do that automatically? Just to let you know, Oracle 9ir2 docs main page is my home page on Mozilla firebird browser and Metalink is my homepage on IE. Raj Rajendra dot Jamadagni at nospamespn dot com All Views expressed in this email are strictly personal. QOTD: Any clod can have facts, having an opinion is an art ! -Original Message- Sent: Tuesday, December 30, 2003 2:44 PM To: Multiple recipients of list ORACLE-L Go to tahiti.oracle.com and search for the optimizer_dynamic_sampling parameter, you'll see descriptions for it's different values there. Tanel. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jamadagni, Rajendra 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).
Re: Should we stop analyzing?
At 03:29 PM 12/30/2003, you wrote: But then again, if re-collecting statistics causes your database performance to suddenly become very bad, it seems at first cut there are only two conclusions you can come to. 1) CBO is broke if fresh statistics result in poor performance That a plan changes due to changes in the statistics doesn't mean that the CBO is broke. That's the whole name of the game. The optimizer uses statistics - together with initialization parameters, heuristics and rules - to develop the anticipated best access path. If you change any of these, statistics by analyzing, initialization parameters by changes to the init.ora, or heuristics and rule by upgrading to a new version or applying a patch. I regard any of these changes as serious changes to the database which should go through a test and acceptance cycle. And that includes refreshing statistics. I am constantly amazed how nonchalantly most shops schedule daily, weekly, or whatever analyze jobs even if they batten down the hatches against changes to the application (Don Burleson alluded to that as well). Most of the time the changed statistics do not cause a change in access plans ( which immediately begs the question why do it then ), but ever so often the changed statistics cross a threshold to make a different plan appears to be better. It may be better, or it may turn out to be horrible. My point is: shouldn't that be tested first? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
Re: Should we stop analyzing?
Wolgang, What I had in mind was simple DML, no patches, etc. Whether statistic are refreshed monthly, weekly, daily or every 2 hours, it doesn't make sense that this would create statistics that would be detrimental to performance, unless the data at the time the statistics are gathered is substantially different than at the time of usage. eg. stats gathered on a table when it has a few blocks allocated during a batch job, but later grows to few hundred thousand blocks prior to users hitting the system. Granted, it may be unnecessary continually analyze: as I stated earlier, this is the first time I've seen this discussed, so maybe I'm missing some important point about it that hasn't sunk in yet. MetaLink document 44961.1 supports infrequent analyzing, though it conveniently fail to define 'frequent'. If my data changes, and I analyze it, CBO should still find reasonable execution paths for the current data. If my data does not change, and I analyze it, CBO should have the same set of statistics as it did previously. Is that not true, or is there some other piece missing here? Jared On Tue, 2003-12-30 at 15:44, Wolfgang Breitling wrote: At 03:29 PM 12/30/2003, you wrote: But then again, if re-collecting statistics causes your database performance to suddenly become very bad, it seems at first cut there are only two conclusions you can come to. 1) CBO is broke if fresh statistics result in poor performance That a plan changes due to changes in the statistics doesn't mean that the CBO is broke. That's the whole name of the game. The optimizer uses statistics - together with initialization parameters, heuristics and rules - to develop the anticipated best access path. If you change any of these, statistics by analyzing, initialization parameters by changes to the init.ora, or heuristics and rule by upgrading to a new version or applying a patch. I regard any of these changes as serious changes to the database which should go through a test and acceptance cycle. And that includes refreshing statistics. I am constantly amazed how nonchalantly most shops schedule daily, weekly, or whatever analyze jobs even if they batten down the hatches against changes to the application (Don Burleson alluded to that as well). Most of the time the changed statistics do not cause a change in access plans ( which immediately begs the question why do it then ), but ever so often the changed statistics cross a threshold to make a different plan appears to be better. It may be better, or it may turn out to be horrible. My point is: shouldn't that be tested first? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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: Jared Still 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).
Re: Should we stop analyzing?
Note inline At 10:29 PM 12/30/2003, you wrote: If my data changes, and I analyze it, CBO should still find reasonable execution paths for the current data. If the CBO were infallable we wouldn't have this discussion. There are many reasons why even the most up-to-date statistics can lead to less than optimal access plans. My point is not necessarily with the frequency of statistics gathering but with the untested activation of new statistics, which is the hallmark of scheduled analyze jobs, as it carries the same risk as any untested change. If my data does not change, and I analyze it, CBO should have the same set of statistics as it did previously. If your data didn't change, or didn't change enough to make a difference in access plans, wouldn't you agree that the exercise of gathering statistics was futile and useless. Is that not true, or is there some other piece missing here? If the current statistics produce access plans that render the required data in the time stipulated by your SLAs, why the urge to change something. You are getting dangerously close to symptoms of CTD. If, on the other hand, there are performance problems, they should be analyzed case by case and at that time the possibility that newer statistics will change the access plan and improve the performance should be explored. Jared -- Author: Jared Still INET: [EMAIL PROTECTED] Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Wolfgang Breitling 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).
Re: Should we stop analyzing?
This makes Oracle's position with 10g interesting, given that the default behaviour is to collect statistics all over the place automatically. If it's built in by the supplier, does it count as a change ? Jared's point is valid - in theory, if you keep statistics up to date, then the CBO should produce the optimum plan; if you fail to keep statistics up to date, the CBO plans can cease to be optimal, or may change to become sub-optimal. Moreover, in theory, if a plan changes on a change of statistics, it will be a better, or at worst equal cost, plan with at worst no change in performance. Of course, in the real world, we know that there are various reasons why things go wrong at the boundary points between plans, which is why we like to stick the statistics down well within our preferred boundary. Of course, following your argument about change control to its logical conclusion, since a change in the data may change execution plans, which may introduce untested portions of Oracle code, any data change should also be subject to change control. Despite any whimsical arguments, though, your basic premise is the important one. You need to know the application to do the job correctly. If you know how the data evolves, you will know how to get the minimum amount of work done that allows the optimizer to do its job well. Regards Jonathan Lewis http://www.jlcomp.demon.co.uk The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr One-day tutorials: http://www.jlcomp.demon.co.uk/tutorial.html Three-day seminar: see http://www.jlcomp.demon.co.uk/seminar.html UK___November The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html - Original Message - To: Multiple recipients of list ORACLE-L [EMAIL PROTECTED] Sent: Tuesday, December 30, 2003 11:44 PM At 03:29 PM 12/30/2003, you wrote: But then again, if re-collecting statistics causes your database performance to suddenly become very bad, it seems at first cut there are only two conclusions you can come to. 1) CBO is broke if fresh statistics result in poor performance That a plan changes due to changes in the statistics doesn't mean that the CBO is broke. That's the whole name of the game. The optimizer uses statistics - together with initialization parameters, heuristics and rules - to develop the anticipated best access path. If you change any of these, statistics by analyzing, initialization parameters by changes to the init.ora, or heuristics and rule by upgrading to a new version or applying a patch. I regard any of these changes as serious changes to the database which should go through a test and acceptance cycle. And that includes refreshing statistics. I am constantly amazed how nonchalantly most shops schedule daily, weekly, or whatever analyze jobs even if they batten down the hatches against changes to the application (Don Burleson alluded to that as well). Most of the time the changed statistics do not cause a change in access plans ( which immediately begs the question why do it then ), but ever so often the changed statistics cross a threshold to make a different plan appears to be better. It may be better, or it may turn out to be horrible. My point is: shouldn't that be tested first? Wolfgang Breitling Oracle7, 8, 8i, 9i OCP DBA Centrex Consulting Corporation http://www.centrexcc.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jonathan Lewis 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).
