Re: Oracle 11G Upgrade

Thanks for all the different things to llok at.

By not have access to source I meant that we can not change the source code.
The business may be able to but w/o extensive testing that woulld not be an option at the moment.

The data comes from meters that send a period (i believe a month) worth of data to this process as a result only the daily new reads will be insert and the majority will be updates.
When we ran this on an empty test database the first run was fast ( majority inserts) and than performance went south again for subsequent runs (majority updates)

This also means that the data has not changed in The way it is presented.

It leads me to think that somehow the error handling is the bottleneck.

There is only one index/contraint on this table. And any insert/update will trigger another insert into an archive table that keeps historic data.

This whole process could be made so much better by using external tables for the presented data and merge to do the insert/update

Jack

On Saturday, March 29, 2014, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> You mentioned you don't have the source, but then you show us the source...
>
>
>
> I'm not sure how many unique constraints you have on the underlying table.
>
>
>
> IF there is just one, you might well improve concurrency by injecting a
> select count(*) on just the unique key (so it only has to hit the unique
> index protecting the constraint) and only invoke the insert on zero found,
> making the update on greater than 0 found, so that neither of the actions
> invokes the error processing exception.
>
>
>
> IF you have multiple unique constraints to check, you start down a
> slippery slope where the violation detection may be cheaper than checking
> each independently unique possible key set.
>
>
>
> The problem cropping up from a move from 9i to 11G is suggestive that it
> is related to concurrency improvements that may burn cpu to achieve a
> higher top throughput rate on well-designed systems and that you have many
> partial transaction bits slamming against the same row in the update part,
> exacerbated by the error processing loop part.
>
>
>
> This further suggests that the select count(*) where unique_key = practice
> will be effective for you. Your vendor interaction is beyond the scope of
> this technical list, but since that procedure is not wrapped you can
> certainly substitute your own. Of course that becomes a customization, so
> you should try to get your vendor to swallow the improvement (if it is) and
> give it back to you.
>
>
>
> As you have noted, maximum parallelism is not the same as maximum
> throughput. A binary search of number of threads that delivers maximum
> throughput would probably be useful, and if you can dither out the input so
> that separate threads operate on disjoint sets of input data with regard to
> the unique key, that will probably be almost magical in effect.
>
>
>
> Okay, so I buried the lead: If you can feed disjoint sets of input data to
> the multiple threads you are running, that will probably be almost magical
> in effect.
>
>
>
> (IF the input data is sourced from some select that involved a change from
> or to hash aggregation versus sort aggregation, that could explain a huge
> uptick in collisions. If you slap some views in front of each of the
> threads you're running with some modulo functions matching the number of
> threads, so that they automatically operate on disjoint data you might
> avoid collisions completely.)
>
>
>
> mwf
>
>
>
> *From:* oracle-l-bounce_at_freelists.org<javascript:_e(%7B%7D,'cvml','oracle-l-bounce_at_freelists.org');>[mailto:
> oracle-l-bounce_at_freelists.org<javascript:_e(%7B%7D,'cvml','oracle-l-bounce_at_freelists.org');>]
> *On Behalf Of *Jack van Zanen
> *Sent:* Wednesday, March 26, 2014 6:13 PM
> *To:* oracle-l_at_freelists.org<javascript:_e(%7B%7D,'cvml','oracle-l_at_freelists.org');>
> *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
>

-- 
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

--
http://www.freelists.org/webpage/oracle-l