FreeLists / oracle-l / RE: Oracle 10g upgrade - performance issue

["Johnson, George" <GJohnson@xxxxxxx>]

        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
_optimizer_cost_based_transformation = OFF


        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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Peter Dixon
Sent: 11 Jul 2006 18:43
To: oracle-l@xxxxxxxxxxxxx
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