Re: Oracle 11G Upgrade
- From: Alfredo Abate <alfredo.abate@xxxxxxxxx>
- To: Jack van Zanen <jack@xxxxxxxxxxxx>
- Date: Wed, 26 Mar 2014 21:19:17 -0500
Jack,
1. Most likely you are okay here.
2. If you haven't already, check v$sga_resize_ops to see if Oracle is
performing an excessive amount of resize operations. If it is you may want
to set minimums for the pools that don't have any set.
3. Do you still have one of the 9i environments around to see how
statistics differ on the problem tables involved?
Alfredo
On Wed, Mar 26, 2014 at 7:04 PM, Jack van Zanen <jack@xxxxxxxxxxxx> wrote:
> 1. Apart from the memory parameters and the parameters that are
> deprecated/changed (diagnostic dest etc) they are identical
> 2. Yes we turned on AMM and have kept some minimum pools (java & large)
> and let oracle sort out the rest
> 3. Yes there is a difference how statistics were gatherd 9i we had a
> scheduled job that would run gather_schema_stats with compute and cascade=Y
> Now we use the build in oracle statistics gathering. The tables
> involved in this get statistics refreshed daily in that process.
>
> 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
>
>
> On Thu, Mar 27, 2014 at 9:43 AM, Alfredo Abate <alfredo.abate@xxxxxxxxx>wrote:
>
>> Jack,
>>
>> Some things that I would go back and just double check.
>>
>> - Compare the init parameters from 9i to 11g. Does anything look
>> different?
>>
>> - What about AMM in 11g is it turned on? Have you set any minimums for
>> the different pools or left it for Oracle to determine?
>>
>> - Is there any difference in how statistics were gathered in 9i to 11g?
>>
>> Can you run a trace on one of the processes that executes the procedure
>> to get some additional information?
>>
>>
>> Alfredo Abate
>>
>>
>> On Wed, Mar 26, 2014 at 5:13 PM, Jack van Zanen <jack@xxxxxxxxxxxx>wrote:
>>
>>> 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
>>>
>>
>>
>
Other related posts:
