RE: Oracle 10g upgrade - performance issue

        My tuppence worth...

        After our upgrade from 9206 to 10201, we ended up with these params making the biggest difference to our slow query performance. The index_cost_adj figure was arrived at after about 2 days of testing various troublesome queries.

optimizer_secure_view_merging   = false      


_gby_hash_aggregation_enabled   = FALSE

optimizer_index_cost_adj        = 50
optimizer_index_caching         = 0

        Of course these were set after we had exhausted the list of badly performing queries and we were starting to get desperate. They were all advised by various Oracle consultants we spoke to, they were all tested in our Test and Dev copies before going up to Prod.

        We were told by one Oracle guy that if your DB is not a warehouse and it's used batch and OLTP, the bottom four parameters should be set in 10g, without question to ensure the Warehouse components do not affect OLTP type activity! The value for cost_adj needs to be adjusted slightly to get it right for your kit.

        Rgds

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Peter Dixon Sent: 11 Jul 2006 18:43
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



****************************************************************************
This message contains confidential information and is intended only 
for the individual or entity named.  If you are not the named addressee
you should not disseminate, distribute or copy this e-mail.  
Please notify the sender immediately by e-mail if you have received 
this e-mail by mistake and delete this e-mail from your system.
E-mail transmission cannot be guaranteed to be secure or error-free
as information could be intercepted, corrupted, lost, destroyed, arrive
late or incomplete, or contain viruses.  The sender therefore does not
accept liability for any errors or omissions in the contents of this 
message which arise as a result of e-mail transmission.  
If verification is required please request a hard-copy version.
This message is provided for informational purposes and should not
be construed as an invitation or offer to buy or sell any securities or
related financial instruments.
GAM operates in many jurisdictions and is 
regulated or licensed in those jurisdictions as required.
****************************************************************************
--
http://www.freelists.org/webpage/oracle-l