Re: merge waits on library cache lock when reference a table alias

Hi

This was 3 years ago, but someone asked me about this today, I thought I would share the solution with all of you after all these time :-)

I raised a SR on March 2015 and I got the fix on 18th of Feb on 2016! It was due to this bug

*Bug 14380605 - ORA-904/mutex contention from recursive 'SELECT 1 FROM DUAL WHERE' done by "MERGE INTO"*

BR

<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> Virus-free.
www.avast.com
<https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

On Tue, Mar 3, 2015 at 6:03 PM, Ls Cheng <exriscer_at_gmail.com> wrote:

> Hi Mark
>
> In session 1 run CTAS of T1 and Z, right afterwards run the merge
> statement in session 2.
>
> Table alias has to be same as the table being created.
>
> Thanks
>
>
> On Tue, Mar 3, 2015 at 1:33 PM, Mark W. Farnham <mwf_at_rsiz.com> wrote:
>
>> er, sorry, … with session 2 starting before session 1 COMPLETES? (not
>> begins).
>>
>>
>>
>> And also that you have no hang using a similar scenario if the table
>> alias in session 2 for the select from dba_users is, say, z1, instead of z?
>>
>>
>>
>> mwf
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
>> freelists.org] *On Behalf Of *Mark W. Farnham
>> *Sent:* Tuesday, March 03, 2015 7:27 AM
>> *To:* exriscer_at_gmail.com; 'Oracle Mailinglist'
>> *Subject:* RE: merge waits on library cache lock when reference a table
>> alias
>>
>>
>>
>> Just to verify, is it correct that the first CTAS of T1 is certainly
>> complete as a preamble before the session1 and session 2 are started with
>> session 2 starting before session 1 begins?
>>
>>
>>
>> mwf
>>
>>
>>
>> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
>> freelists.org <oracle-l-bounce_at_freelists.org>] *On Behalf Of *Ls Cheng
>> *Sent:* Monday, March 02, 2015 11:12 AM
>> *To:* Oracle Mailinglist
>> *Subject:* merge waits on library cache lock when reference a table alias
>>
>>
>>
>> Hi
>>
>> I hit a problem recently in 11.2.0.4 where a merge statement references a
>> table alias, for example Z, and Z is being created as a tabe in another
>> session using CTAS. The merge session hangs waiting for library cache lock.
>>
>> I am not aware of such behaviour, anyone faced this before? Or it's time
>> to file a SR?
>>
>> Thanks
>>
>>
>>
>> Test Case:
>>
>> CREATE TABLE T1
>> AS
>> SELECT * from dba_users;
>>
>> -- session 1
>> CREATE TABLE Z
>> AS
>> SELECT *
>> FROM (SELECT rownum rn, 'A|B|C' ABC
>> FROM dba_source)
>> CONNECT BY LEVEL <= length(regexp_replace (ABC, '[^\|]+')) + 1;
>>
>> -- session 2 hangs, library cache lock
>> MERGE INTO t1 x
>> USING (SELECT *
>> FROM DBA_USERS
>> ) z
>> ON (x.user_id = z.user_id)
>> WHEN MATCHED THEN
>> UPDATE SET x.username = z.username
>> WHEN NOT MATCHED THEN
>> INSERT (username,
>> user_id,
>> password,
>> account_status,
>> lock_date,
>> expiry_date,
>> default_tablespace,
>> temporary_tablespace,
>> created,
>> profile,
>> initial_rsrc_consumer_group,
>> external_name,
>> password_versions,
>> editions_enabled,
>> authentication_type
>> )
>> VALUES (z.username,
>> z.user_id,
>> z.password,
>> z.account_status,
>> z.lock_date,
>> z.expiry_date,
>> z.default_tablespace,
>> z.temporary_tablespace,
>> z.created,
>> z.profile,
>> z.initial_rsrc_consumer_group,
>> z.external_name,
>> z.password_versions,
>> z.editions_enabled,
>> z.authentication_type
>> );
>>
>
>

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