RE: Oracle 11G Upgrade
- From: "Cunningham, Mike" <mcunningham@xxxxxxxxxxxxxx>
- To: "jack@xxxxxxxxxxxx" <jack@xxxxxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Mar 2014 16:19:16 -0700
Hi Jack, now that I have read the entire email I realize my previous post was
wrong since you have already checked what I incorrectly mentioned.
I have never had any performance issues with exception handling in 11g
(11.2.0.3), but that doesn’t mean they don’t exist.
I’ve seen something like this when there are a lot of uncommitted inserts, and
an update (or select) comes along which requires an index read on the index
with all those inserts. It seems to take a lot longer to perform the index
read than once the transactions are committed. I don’t know why this is, but
I’m sure interested in someone with more knowledge chiming in.
Out of curiosity, how is the performance for a separate select on those columns
while the 30 processes are running?
Michael Cunningham
Senior Database Administrator
The Doctors' Company
707.226.0221 - desk
707.337.0184 - cell
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Jack van Zanen
Sent: Wednesday, March 26, 2014 3:13 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: Oracle 11G Upgrade
Hi All,
We have a database where we are upgraded to 11G from 9i. We have gone through
the cycle of upgrading test first and doing testing before upgrading production
and everything appeared fine.
Generally speaking the business is happy with performance except for one
process that has slowed down quite badly.
We are on windows 32 bit oracle 11.2.0.3
The process that slowed down is below procedure which gets executed a lot of
times.
We have no access to the source to change this so changing to merge is not an
option.
Files are loaded that have a lot of history in it so the exception handler
kicks in more often than not.
When we do an initial load it is fast (just insert), but when the exception
kicks in because of the duplicate value the performance goes south.
If I check the explain plan it uses the index to find the record to update so I
do not see how to make that any faster. I am now thinking that it may be a
difference in the way oracle 9i and 11G handle the exception that is causing it
to take more time than it should.
I should say when we run 30 processes at the same time it comes to a grinding
halt and when we run 4-5 at least it finishes.
PROCEDURE DBO.insert_in_r( id_mp IN NUMBER,
id_rt IN NUMBER, ts IN DATE, r_val IN FLOAT, stat IN
NUMBER )
IS
BEGIN
INSERT INTO DBO.results VALUES ( id_mp, id_rt, ts, r_val, stat );
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
UPDATE dbo.Results
SET ResultValue = r_val, Status = stat
WHERE
ID_MeasurementPlace = id_mp AND
ID_ResultType = id_rt AND
ResultTimeStamp = ts;
END insert_in_r;
/
Anybody have any idea where to look for a solution?
Jack van Zanen
-------------------------
This e-mail and any attachments may contain confidential material for the sole
use of the intended recipient. If you are not the intended recipient, please be
aware that any disclosure, copying, distribution or use of this e-mail or any
attachment is prohibited. If you have received this e-mail in error, please
contact the sender and delete all copies.
Thank you for your cooperation
Confidentiality Notice: This message and any attachments hereto may contain
confidential and privileged communications or information and/or attorney
client communications or work-product protected by law. The information
contained herein is transmitted for the sole use of the intended recipient(s).
If you are not the intended recipient or designated agent of the recipient of
such information, you are hereby notified that any use, dissemination, copying
or retention of this e-mail or the information contained herein is strictly
prohibited and may subject you to penalties under federal and/or state law. If
you received this e-mail in error, please notify the sender immediately and
permanently delete this e-mail.
Other related posts:
