RE: 8.1.5 much slower than 7.3.4

From: Gait, Christopher <cgait_at_condor.nrl.navy.mil>
Date: Thu, 18 May 2000 23:03:27 -0400
Message-Id: <10501.106040@fatcity.com>

The sad truth of the matter is that some of the carefully tuned SQL you wrote for RULE-based optimization in 7.x will not work well in 8i, even if the tables are analyzed. There isn't any easy answer, though you could determine if this is the case on a test database by dropping statistics on your tables, setting the instance to RULE in the init.ora and running for a while to see if performance improves. Improvement would be an indicator that you need to do two things:

1. Find the most resource intensive SQL that requires RULE to run well and use the /*+ RULE */ hint to run it short term while you are doing :
2. Rewrite queries that run poorly under COST-based, using all the various tricks and techniques available under 8i (and there are a lot!).

This certainly isn't a cheap process, but in the long run it's inevitable if the queries themselves are at issue because Oracle has stated that they won't necessarily support RULE optimization forever (just for long enough for it no longer to be used by SYS <g>) and by using RULE you can't take advantage of hints, bitmap indexes and other goodies.

-----Original Message-----
From: Louis BROUILLETTE [mailto:Louis_Brouillette_at_UQTR.UQuebec.CA] Sent: Thursday, May 18, 2000 2:03 PM
To: ORACLE-L_at_fatcity.com
Cc: oracledba_at_QuickDOC.co.uk
Subject: 8.1.5 much slower than 7.3.4

Here is a summary of what I got from all of you: 1) if in cost base optimizer, did I analyze the tables
--> Yes

2) the changes I made to the init.ora are:

• remove obsolete parameters
• set compatible to 8.1.5
• set shared_pool_size and db_block_buffers to higher values
• add DB_DOMAIN='.world'
• set java_pool_size to '1000K'
  --> should not do any harm
  3) Did you use a different location for the 8.1.5 db?
  --> the 8.1.5 database is on the same disks than the 7.3.4 database
  4) I understand there's some bug when timed_statistics=true in 8.1.5
  --> I switched it to false with no results
  

Thanks for your input. I guess there is nothing obvious so I'll look at it more closely and tell you what I find.

Louis Brouillette
Analyste en informatique (DBA)
Universite du Quebec a Trois-Rivieres
Tel: (819) 376-5011 ext. 2435
Email: Louis_Brouillette_at_uqtr.uquebec.ca

---

If you're bored, then visit the list's website: http://www.lazydba.com (updated daily)
to unsubscribe, send a blank email to oracledba-unsubscribe_at_quickdoc.co.uk to subscribe send a blank email to oracledba-subscribe_at_quickdoc.co.uk Received on Thu May 18 2000 - 22:03:27 CDT