Re: Massive MERGE statement causing massive locks

What kind of locks? What is the wait on? Do you have a SQL Monitoring report you can share?

Sent from my iPhone

> On Oct 12, 2016, at 6:19 PM, David Ramírez Reyes <dramirezr_at_gmail.com> wrote:
>
> Hello everyone,
>
> This is the environment:
> Solaris 5.11, 2 processors, 2 cores each
> Oracle DB 11g R2 (11.2.0.4.0), 140 GB size DB
>
> There are performance problems because of locks; the locks are caused by a MERGE statement on an SP that is executed more than 200 times by hour using two tables of 8 million and 5 million records each.
>
> I haven't used MERGE in detail before, excepting for a very special case when moving data from one old table to a newer, but until I know, the usage of MERGE should not be for this cases (they are using it as an easy way to stora data into a table without validating if it exists or not, according to the logic).
>
> I have access to the SP (actually, there are 5 sp's using MERGE, but only one is executed massively) so that I could change the logic of it, but don't have access to the code of the app, if you were thinking about modifying it.
>
> Do any of you have experience using MERGE?, how is its performance?, wouldn't it be more effective to create another SP that would make the same process but manually?
>
> Any comments and suggestions are welcomed.
>
> Tks
>
>
> David Ramírez Reyes
> Profesión: Padre de Familia y DBA en mis ratos libres
> Profession: Parent and DBA in my spare time
>

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