Re: Massive MERGE statement causing massive locks

Yes, the table is partitioned by month, but haven't considered the Truncate partition as an option; the indexes are partitioned as well and am just rebuilding the ones that correspond to the purge data...

I will check more details about truncate partition, thanks for the suggestion!

Regards

David Ramírez Reyes
Profesión: Padre de Familia y DBA en mis ratos libres Profession: Parent and DBA in my spare time

On 13 October 2016 at 08:20, Deas, Scott <Scott.Deas_at_lfg.com> wrote:

> David,
>
>
>
> Throwing parallelism on something that’s not running efficiently may only
> make matters worse. I’d do lots of testing, comparing the execution plans
> between serial and parallel execution to see if it really is helping.
>
>
>
> Based on the size of these tables and the fact that you have a regular
> purge process, I hope you are using partitioning. If you have the tables
> partitioned by month, you could truncate the partition to clear it out, or
> use partition exchange to move the entire partition to an archive table.
> If all of the indexes are local, there would be nothing to rebuild –
> nothing would become unusable, otherwise you would need to rebuild global
> indexes, but it would be much more efficient than DELETING records from
> these tables and leaving fragmented blocks all over the place.
>
>
>
> Thanks,
> Scott
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *David Ramírez Reyes
> *Sent:* Thursday, October 13, 2016 9:02 AM
> *To:* Mark W. Farnham <mwf_at_rsiz.com>
> *Cc:* troach_at_gmail.com; 'oracle-l_at_freelists.org' (oracle-l_at_freelists.org)
> <oracle-l_at_freelists.org>
>
> *Subject:* Re: Massive MERGE statement causing massive locks
>
>
>
> The problem was that the indexes were not rebuild after purging the
> records (every month there's a process that deletes any record older than 3
> months); once rebuilded, all deadlocks disappeared.
>
>
>
> About the use of MERGE, I google it and found that in general gives a
> better performance than just using INSERT/UPDATE instead so, I think I can
> let it working as it is (just need to be sure that indexes are rebuilded
> after the monthly purging).
>
>
>
> I will try anyway the usage of parallel processing for it, that may help
> also,
>
>
>
> Thanks everyone!
>
>
>
> Regards
>
>
> David Ramírez Reyes
>
> Profesión: Padre de Familia y DBA en mis ratos libres
>
> Profession: Parent and DBA in my spare time
>
>
>
>
>
> On 13 October 2016 at 07:01, David Ramírez Reyes <dramirezr_at_gmail.com>
> wrote:
>
> Stored Procedure, sorry...
>
>
> David Ramírez Reyes
>
> Profesión: Padre de Familia y DBA en mis ratos libres
>
> Profession: Parent and DBA in my spare time
>
>
>
>
>
> On 13 October 2016 at 06:41, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
> What is an “SP?”
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *Thomas Roach
> *Sent:* Wednesday, October 12, 2016 8:36 PM
> *To:* dramirezr_at_gmail.com
> *Cc:* 'oracle-l_at_freelists.org' (oracle-l_at_freelists.org)
> *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_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
>
>
>
>
>
>
>
> 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.**
>

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