RE: Oracle 10g upgrade - performance issue

Gee now doesn't that sound familiar
Did you load test it in the test environment, how many users ran it in test and how many do you now have on the system, you will likely trace it back to lock or latch contention, never shows up in test because you only had one or two users testing and now you have 20 or 200. Have a look at the SQL advisor but also enable tracing the DBAsupport website has a good article to guide you in using client identifier trace with a logon trigger useful for products where a single user does all the connections.

Sadly SQL Advisor is likely an ongoing task, because as data changes you may need to do ongoing tuning and won't tell you other issues. Look at the SGA advisor, see what it wants to set the PGA_Aggregate at, all these are inputs to you decisions but your 10046 traces will pinpoint you time wasters explicitly.
The optimiser isn't making bad decisions it is making the best decision it can about bad code.

HTH Cheers

Peter McLarty
Technical Consultant
Service Delivery
+61733777542
TechnologyOne
-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Dixon Sent: Wednesday, 12 July 2006 3:43 AM
To: oracle-l_at_freelists.org
Subject: Oracle 10g upgrade - performance issue

Help!

Upgraded 8.1.7.4 to 10g rel 2 smoothly over the weekend. Batch works fine,
but OLTP is causing CPU and disk i/o to max out at 100%, i believe the optimiser is making bad decisions as my introducing rule hints the problem
has slightly improved. Any ideas as our SR with Oracle says "tune the code"
also we never got the issues in test.

Peter Dixon

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Jul 11 2006 - 22:14:51 CDT