RE: Oracle 10g upgrade - performance issue
- From: "Johnson, George" <GJohnson@xxxxxxx>
- To: <peterdixon001@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 12 Jul 2006 14:06:08 +0100
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
--
//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.
****************************************************************************
--
//www.freelists.org/webpage/oracle-l
Other related posts:
