Home » RDBMS Server » Performance Tuning » Oracle 9i optimizer
Oracle 9i optimizer [message #64871] Mon, 23 February 2004 09:49 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Oracle 9i optimizer [message #64898 is a reply to message #64896] Sun, 29 February 2004 03:53 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi,
What are your optimizer* and db_file_multiblock_Read_count settings ?

-Thiru
Previous Topic: automate spurge.sql job
Next Topic: Stored Procedure - Bind Variables - Performance
Goto Forum:
  


Current Time: Tue Sep 29 06:49:12 CDT 2020