Re: How to debug resource busy error

Thank you so much Laurentiu and Dominic.

I tried checking the dash_wait_chains script for 15minutes window , just by passing first parameter as session_type='FOREGROUND' and but not seeing much blocking waits as in below output. And also as i don't have the sql_id for the exact ALTER statement captured as it must be very fast and thus not getting captured in ASH/AWR, so was not able to pass the second parameter i.e sql_id filter.

To Dominic's point , i think this ALTER sql_id wont be captured as its very fast and failing immediately when its seeing the lock from other session. And also we are struggling to catch the culprit session too. So as suggested, will try to set the ddl_lock_timeout to ~300 i.e 5 minutes so as to capture enough blocking session against the ALTER statement logged in the ASH/AWR to get the details around the culprit session. And considering the data-load itself take ~1 hrs+ so this amount of additional wait time(5 mins) should be fine. That should possibly also fix the issue if the blocking lock/culprit session is just holding it for very small time and releasing it immediately after.

[image: User: "image.png"]

On Mon, 2 May 2022, 12:03 pm Dominic Brooks, <dombrooks_at_hotmail.com> wrote:

> Problem is that the error is likely to be immediate.
>
> If you increase/set the param ddl_lock_timeout in your load code before
> you do enable constraints then
> a) you might wait sufficient time not to fail, ie your blocker finishes
> before the timeout end and/or
> b) you should capture some information in ASH about the blocking scenario.
>
> Cheers,
> Dominic
>
> Sent from my iPhone
>
> On 2 May 2022, at 06:55, Laurentiu Oprea <laurentiu.oprea06_at_gmail.com>
> wrote:
>
> 
> Hello Pap,
>
> To diagnose your issue ASH should do the trick.
>
> Here is an example(script dash_wait_chains) :
>
> https://tanelpoder.com/2013/11/06/diagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2/
> <https://nam12.safelinks.protection.outlook.com/?url=https%3A%2F%2Ftanelpoder.com%2F2013%2F11%2F06%2Fdiagnosing-buffer-busy-waits-with-the-ash_wait_chains-sql-script-v0-2%2F&data=05%7C01%7C%7C97ae9142e13241b6ef5308da2c005496%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C637870677178768226%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C3000%7C%7C%7C&sdata=RXOJ6ean3%2FHVZiEqVguXpBwUZWt7sQi4OjPAPTmACX8%3D&reserved=0>
>
> Thank you.
>
> În lun., 2 mai 2022 la 05:58, Pap <oracle.developer35_at_gmail.com> a scris:
>
>> Hi, We are facing Ora-0054 error on specific time of the month for a job
>> and from the line number noted in the error log its pointing to the alter
>> statement which enables reference constraints after a direct path data load
>> and commit. But we tried monitoring it a few times during the error
>> period/run time and surprisingly we are not seeing any lock(from
>> v$locked_object) on the base table(say e.g. MAIN_TAB in below example) but
>> still the job failed and it succeeded after a couple of rerun attempts.
>> But it seems like , some process runs and takes lock(for may be very
>> small time) and makes this 'ALTER' failure and we were unable to find
>> that out.
>>
>> So I wanted to understand , how to get the culprit session/sql/job so
>> that we can have some dependency set so that this failure won't occur?
>> Is there any possible trace to set which will help us to debug this
>> scenario and get the details around the culprit session? Or somehow we
>> can track it from any of the ASH/AWR views for which we have enough
>> retention in place?
>>
>> *****The error log pointing to one of the below statements failure inside
>> the procedure *******
>>
>> ALTER TABLE SCHEMA1.MAIN_TAB ENABLE NOVALIDATE CONSTRAINT MAIN_TAB_R01;
>>
>> ALTER TABLE SCHEMA1.MAIN_TAB ENABLE NOVALIDATE CONSTRAINT MAIN_TAB_R03;
>>
>> ORA-00054: resource busy and acquire with NOWAIT specified or timeout
>> expired
>>
>> ********Table structure***************
>>
>> CREATE TABLE SCHEMA1.MAIN_TAB
>>
>> ( FFKEY NUMBER CONSTRAINT MAIN_TAB_C01 NOT NULL,
>>
>> SCKEY NUMBER CONSTRAINT MAIN_TAB_C02 NOT NULL,
>>
>> ACKEY NUMBER,
>>
>> CKEY NUMBER CONSTRAINT MAIN_TAB_C04 NOT NULL,
>>
>> CSTKEY NUMBER CONSTRAINT MAIN_TAB_C05 NOT NULL,
>>
>> EPC_KEY NUMBER CONSTRAINT MAIN_TAB_C06 NOT NULL,
>>
>> CRRKEY NUMBER CONSTRAINT MAIN_TAB_C07 NOT NULL
>>
>> );
>>
>> ALTER TABLE SCHEMA1.MAIN_TAB ADD ( CONSTRAINT MAIN_TAB_R01 FOREIGN KEY
>> (CKEY) REFERENCES SCHEMA1.REF_TAB1 (CKEY) ENABLE NOVALIDATE,
>>
>> CONSTRAINT MAIN_TAB_R03 FOREIGN KEY (SCKEY) REFERENCES
>> SCHEMA1.REF_TAB2 (SCKEY) ENABLE NOVALIDATE);
>>
>> ******************
>>
>> CREATE TABLE SCHEMA1.REF_TAB1( CKEY NUMBER CONSTRAINT REF_TAB1_C01 NOT
>> NULL );
>>
>> CREATE UNIQUE INDEX SCHEMA1.REF_TAB1_PK ON SCHEMA1.REF_TAB1(CKEY);
>>
>> ALTER TABLE SCHEMA1.REF_TAB1 ADD ( CONSTRAINT REF_TAB1_PK PRIMARY
>> KEY (CKEY) USING INDEX SCHEMA1.REF_TAB1_PK ENABLE VALIDATE);
>>
>> *******************
>>
>> CREATE TABLE SCHEMA1.REF_TAB2( SCKEY NUMBER CONSTRAINT REF_TAB2_C01 NOT
>> NULL );
>>
>> CREATE UNIQUE INDEX SCHEMA1.REF_TAB2_PK ON SCHEMA1.REF_TAB2(SCKEY);
>>
>> ALTER TABLE SCHEMA1.REF_TAB2 ADD ( CONSTRAINT REF_TAB2_PK PRIMARY
>> KEY (SCKEY) USING INDEX SCHEMA1.REF_TAB2_PK ENABLE VALIDATE);
>>
>> ******************
>>
>>
>>

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