Re: Oracle 11G Upgrade
From: Lyall personal <lyallbarbour_at_sanfranmail.com>
Date: Wed, 26 Mar 2014 20:03:18 -0400
Message-ID: <20140327000318.5234827.19915.6102_at_sanfranmail.com>
--
http://www.freelists.org/webpage/oracle-l
Received on Thu Mar 27 2014 - 01:03:18 CET
Date: Wed, 26 Mar 2014 20:03:18 -0400
Message-ID: <20140327000318.5234827.19915.6102_at_sanfranmail.com>
I don't see any commits. Are there sessions blocking each other? Is that why the program grinds to a halt?
Can you monitor blocking sessions while the program is running?
Sent from my BlackBerry 10 smartphone.
| From: Jack van Zanen Sent: Wednesday, March 26, 2014 6:17 PM To: oracle-l_at_freelists.org Reply To: jack_at_vanzanen.com 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
-------------------------
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