Home » RDBMS Server » Performance Tuning » 9i Performance on NT4
9i Performance on NT4 [message #64925] Thu, 04 March 2004 08:57 Go to next message
deni
Messages: 6
Registered: March 2004
Junior Member
We recently upgraded an NT4 server to oracle 9i (9.0.1.1.1)

from 8.1.7.

There's high cpu usage (98-100%) and extremely slow

performance.

Simple select and count(*) queries take forever.

There were no performance issues with 8.1.7

anybody else encounter a similar situation?...any clues?

thanks

 

 

 
Re: 9i Performance on NT4 [message #64926 is a reply to message #64925] Thu, 04 March 2004 10:27 Go to previous messageGo to next message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
Hi
what are your optimizer* settings ?
did you analyze the schema to update statistics ?
Did you look into the exeuction plans of the queries ?
Did anything else change during the migration?
Did you check Metalink for bugs associated with this particular release.

I havent personally encountered a situation like that.

Somebody else reported(in this forum) a similar issue and had to use optimizer_features_enable=8.1.7 to overcome the performance issue.

-Thiru
Re: 9i Performance on NT4 [message #64927 is a reply to message #64925] Thu, 04 March 2004 14:33 Go to previous messageGo to next message
Vijay
Messages: 116
Registered: September 1999
Senior Member
Deni,

We upgraded from 8.1.6 to 9.2.0.4 on NT and some of our queries were running slow. We did the following.

1) Updated all the statistics.( used the same analyze script we used in 8.1.6)

2) Went to the URL described below ( found thru google) which gave us the optimizer parameter differences between oracle 8.1.7 and 9.2. for e.g the always_anti_join was defaulted to nested_loops in 8.1.7, but in 9.0.1 it is defaulted to choose, So we added/changed the always_anti_join = nested_loops in our init.ora , bounced the db and checked the differences. We kept experimenting with all the values(total of about 15 ) and finally when we made _PUSH_JOIN_UNION_VIEW=FALSE, almost all of our queries started running faster. I feel this method is faster since it took me a total of about 2hrs to figure this out.

3)instead of all these steps , you can change the OPTIMIZER_FEATURES_ENABLE=8.1.7 and the queries may run faster, but i have seen some queries run slow, even after changing these parameter.

URL mentioned in step 2 : http://www.centrexcc.com/What%20is%20new%20in%20the%20Oracle%209i%20CBO.pdf
cut and paste this url in your browser. It looks wierd with the special characters but this is the right url.

-Vijay
Re: 9i Performance on NT4 [message #64928 is a reply to message #64927] Sat, 06 March 2004 03:18 Go to previous message
Thiru
Messages: 1089
Registered: May 2002
Senior Member
You're right ,a bunch of parameters changed between 8i and 9i and spotting which one would make the difference is more of a trial and error as it depends on the application(ie sql ) and other instance settings.
On one occasion,just bumping up optimizer_max_permutations was enough to get the right execution plan , as in 9i, its defaulted to 2000.

You could also set event 10053 to see whats going on with the CBO.

-Thiru
Previous Topic: create index on date fields
Next Topic: Covering Index - Execution plan
Goto Forum:
  


Current Time: Tue Sep 29 10:29:47 CDT 2020