| 9i Performance on NT4 [message #64925] |
Thu, 04 March 2004 08:57  |
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  |
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 |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
