| Oracle 9i optimizer [message #64871] |
Mon, 23 February 2004 09:49  |
NDU
Messages: 2
Registered: February 2004
|
Junior Member |
|
|
hello All:
I recently upgraded my db's from 8.1.7 to 9.2.0.1 Ever since then, my queries have been running VERY VERY slow. (Even after analyzing the tables and indexes). But, if I put
Optimizer_features_enabled=8.1.7
parameter in the init.ora and then run the queries, then they behave like before. What am I missing? Why wouldn't the 9i optimizer rn these queries just like the 8.1.7 optimizer?
Any help, appreciated
Thanks
|
|
|
|
| Re: Oracle 9i optimizer [message #64878 is a reply to message #64871] |
Tue, 24 February 2004 06:12  |
Thiru
Messages: 1089
Registered: May 2002
|
Senior Member |
|
|
thats a tough one! One of the things that changed with the 9i optimizer is the number of optimizer permutations.It used to 80,000 by default in 8i and is reduced to 2000 , in 9i. Try increasing OPTIMIZER_MAX_PERMUTATIONS to say 10,000 and see if that makes any difference.
Also were there any other parameters that changed ,during the migration ? Statistics collection?
-Thiru
|
|
|
|
| Re: Oracle 9i optimizer [message #64896 is a reply to message #64878] |
Fri, 27 February 2004 10:33 |
NDU
Messages: 2
Registered: February 2004
|
Junior Member |
|
|
Hello Thiru:
Yes. I tried with the permutation param. No difference. I ran the statistics again...I even upgraded the DB from 9.2.0.1 to 9.2.0.4, and still the same issue. If I don't put OPTIMIZER_COMPATIBLE parameter set to 8.1.7, the queries do FTS and dont use indexes at all. I also played with optimizer_index costs and still no help. At a loss what else to do. Opened TAR and oracle folks are sitting on it.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
