Laura, Keep in mind that analyzing tables/indexes will invalidate related SQL in the shared pool. If you have Statspack snapshots at that time, you will see that both latching (for shared pool/library cache) as well as waits for 'library cache pin/locks/loads' was high at that time. You may have observed that logins freeze up, SQL processing literally stops and nothing gets done. This is why you should *always* analyze during off hours (or at least light load times). The CBO in 8.0.5 (lower than 8.1.7.3 anyway) had a number of issues so I would wait until 8.1.7 in any case. Also I wouldn't roll back the Stats...
CBO _is_ the way to go, but the path from Rule to Cost is strewn with hidden mines. Tim Gorman's paper at 'http://www.evdbt.com' and mine at 'http://www.geocities.com/john_sharmila/links.htm' will help you avoiding these mines! For a more in-depth look, you can look at Jonathan Lewis and Wolfgang Breitling's sites. Also keep in mind that when the CBO processes an SQL where at least _one_ object is analyzed and some aren't, it assumes ridiculously low default values for these objects and that will result in horrendous performance as it will make wrong decisions. The key is this: Analyze all or Analyze none. Another caveat is that the CBO will default when certain type of objects or operations are attempted (even if the mode is RULE). I would also suggest using DBMS_STATS rather than the ANALYZE command in 8.1.7+. You can read all about it in the paper... John Kanagaraj -----Original Message----- Sent: Monday, August 25, 2003 1:49 PM To: Multiple recipients of list ORACLE-L We currently have an application we are trying to speed up. In researching rule/cost based optimizers, I read that the cost based optimizer was the way to go (although rule had its moments) because that is where Oracle would be focusing any upgrades, enhancements, etc. So I analyzed all tables and indexes. It brought our application to a stand still!! I then deleted the statistics and the application ran like before...slow. I know that I must have missed something although it seemed so straight forward. I verified that all tables were analyzed because I read that this would cause an extra step if all the tables were not analyzed. The database is Oracle 8.0.5. This weekend I will be upgrading to 8.1.7. The operating system is NT 4.0. Does anyone know something that could point me in the right direction? Thank you for your help. Laura -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Burton, Laura 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).
