What is an “SP?”
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Thomas Roach
Sent: Wednesday, October 12, 2016 8:36 PM
To: dramirezr@xxxxxxxxx
Cc: 'oracle-l@xxxxxxxxxxxxx' (oracle-l@xxxxxxxxxxxxx)
Subject: 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@xxxxxxxxx> 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
