| GENERAL DISCUSSION: 8i vs 9i query performance [message #57118] |
Wed, 21 May 2003 09:01  |
Mark Rahal
Messages: 7
Registered: May 2003
|
Junior Member |
|
|
Hello again all,
Figured I'd start a general discussion on 8i vs. 9i query performance.
Here we've been upgrading various applications from 8.1.6 to 9.2.0, and some queries have become unworkably slow. (e.g. 1 minute up to 2 hours+failure).
For those of you going through a similar transition, here's a quick resolution: in 9i, set the init parameter "optimizer_features_enable" to the version of Oracle you're coming from (e.g. 8.1.6). BAM! Instant query performance fix.
This is not a dynamic param, so you have to bounce your instance, and it's not "standard" either, you have to add it to your init.
Having done that, I'm still looking to determine what exactly is slowing down the queries with the 9i CBO.
Any ideas so far?
I am going to be experimenting with the following parameter changes:
- sort_area_size
- hash_join_enable
- hash_area_size (??)
Any other ideas? optimizer_index_cost_adj and optimizer_max_permutations haven't helped.
The particular queries I'm dealing with are convoluted, complex, multi-join (11+ tables) monsters (that, as the DBA, I can't at this time ask development to change).
Thoughts?
-- Mark K.
|
|
|
|
| Re: GENERAL DISCUSSION: 8i vs 9i query performance [message #57127 is a reply to message #57118] |
Wed, 21 May 2003 23:14  |
Prasad
Messages: 104
Registered: October 2000
|
Senior Member |
|
|
Hi Mark
optimizer_features_enable should be set to 9.0.2 for
9i or 8.1.6 for 9i Database. I am facing similar
problem please suggest me.
My database is also 9i which was migrated last week
from 8.1.7 So what should be the optimizer_features_enable value set to be
is it 8.1.6 or 9.0.2.
Thanks in Advance.
Prasad
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
