Generally speaking, you tune a merge just like any other statement where you're
joining multiple tables. I'm assuming you are doing a basic UPDATE else INSERT
type operation. The key, in my experience, is to have your joins set up
properly, using indexed columns when possible.
Now, when you're saying massive locks, when and where are these occurring? On
the source table(s)? On the target table? Is it for the duration of the
entire merge statement, or do they come and go? Are you talking about row
level locks, or is one of the entire tables being locked?
Could you share info on the merge statement and execution plan?
Thanks,
Scott
On Oct 12, 2016, at 18:20, David Ramírez Reyes
<dramirezr@xxxxxxxxx<mailto: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
Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged,
confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or
entity to
which it is addressed. If you are not the intended recipient of this E-mail,
you are
hereby notified that any dissemination, distribution, copying, or action taken
in
relation to the contents of and attachments to this E-mail is strictly
prohibited
and may be unlawful. If you have received this E-mail in error, please notify
the
sender immediately and permanently delete the original and any copy of this
E-mail
and any printout. Thank You.**
