Home » SQL & PL/SQL » SQL & PL/SQL » Parallel DDL statements in a same table with different partitions (Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production)
Parallel DDL statements in a same table with different partitions [message #674852] Tue, 19 February 2019 08:50 Go to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi All,

We have a LIST Partitioned table on the BRANCH Column.There is procedure bypassing branch as parameter which will truncates branch specific partition.When we are executing the same procedure with different branch parameter at the same time(parallel), some times(not always) we are getting the following exception.


ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

But when I am trying to truncate the two different partition parallel (exactly at the same time) its working fine.
Unable to re product the problem manually.

DROP TABLE TEST;
CREATE TABLE TEST
  (
    SNO       NUMBER(6),    LAST_NAME VARCHAR2(30),    SALARY    NUMBER(6),    BRANCH VARCHAR2(20)
  )
  PARTITION BY LIST (BRANCH)(PARTITION  HYD VALUES ('HYD') , PARTITION  NDL VALUES ('NDL'));

code to truncate the partitions

ALTER TABLE TEST  TRUNCATE PARTITION  HYD;
ALTER TABLE TEST  TRUNCATE PARTITION  NDL;



Here my question was when we truncate the specific partition,will it acquire the total table lock ? Partition Lock ?



Thanks
SaiPradyumn
Re: Parallel DDL statements in a same table with different partitions [message #674853 is a reply to message #674852] Tue, 19 February 2019 08:59 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
is there a GLOBAL INDEX on this table?
Re: Parallel DDL statements in a same table with different partitions [message #674855 is a reply to message #674852] Tue, 19 February 2019 10:50 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I showed you how to test it and get the answer in your previous topic: resource busy and acquire with NOWAIT specified or timeout expired

Re: Parallel DDL statements in a same table with different partitions [message #674868 is a reply to message #674855] Wed, 20 February 2019 00:48 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Blackswan,

We had created the local index on those partitioned table.

Hi Michel,

Yes, In the previous example its working fine when the parallelism is between one partition DDL and another partition DML.
But now as per the logger, when i am trying to invoke the two DDL Statements (truncating the partition) with different partitions that exception is occurring.
More over this problem is coming once in a while not always.We are sure that there are no other operations when those procedures are executing.


Thanks for your help to resolve the issue.

SaiPradyumn





Re: Parallel DDL statements in a same table with different partitions [message #674870 is a reply to message #674868] Wed, 20 February 2019 03:00 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can do the same king of test.
First create a DDL trigger to have the time to see what happens:
SQL> create or replace trigger ddl_trigger
  2  after ddl on michel.schema
  3  begin
  4    if ora_dict_obj_name = 'TEST' then
  5      dbms_lock.sleep(60);
  6    end if;
  7  end;
  8  /

Trigger created.
Then, in first session, truncate a partition:
SQL> ALTER TABLE TEST  TRUNCATE PARTITION  HYD;
During the minute it is waiting, query, in a second session, V$LOCK to get the locks the first session holds:
SQL> @lock

Sid        Status   User            OS Pid     LK Mod W Object
---------- -------- --------------- ---------- -- --- - ---------------------------------------------
145,9      ACTIVE   MICHEL          5596       TM X     MICHEL.TEST.HYD
                                               TX X     _SYSSMU4_3802565882$ tx: 0x0004.0016.0000AB79
                                               TM RX    MICHEL.TEST

As you can see, the session is getting an exclusive lock on the partition (, an exclusive lock on a rollback segment which protects the internal transaction modifying the SYS tables) and a row exclusive lock on the definition of the table.
Your second TRUNCATE will try to get the same latest lock so your error message, but this lock is hold very shortly (in this version) this is why you get the error only from time to time.

[Updated on: Wed, 20 February 2019 03:02]

Report message to a moderator

Re: Parallel DDL statements in a same table with different partitions [message #674878 is a reply to message #674870] Wed, 20 February 2019 07:57 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Thanks Michel
for your detailed explanation .

I tried with the following example :


DROP TRIGGER DDL_TRIGGER;
DROP TABLE TEST1;
DROP TABLE JOB_SUBMIT_TEST;
DROP SEQUENCE JS_SEQ;
DROP INDEX TEST1_BRANCH;

CREATE TABLE TEST1(SNO NUMBER(6),LAST_NAME VARCHAR2(30),SALARY NUMBER(6),BRANCH VARCHAR2(20))
PARTITION BY LIST(BRANCH) (PARTITION HYD VALUES ('HYD'),PARTITION  NDL VALUES ('NDL'),
                           PARTITION REV VALUES ('REV'),PARTITION  SAR VALUES ('SAR'),
                           PARTITION ABC VALUES ('ABC'),PARTITION  XYZ VALUES ('XYZ'),
                           PARTITION QAZ VALUES ('QAZ'),PARTITION  WSX VALUES ('WSX'),
                           PARTITION EDC VALUES ('EDC'),PARTITION  RFV VALUES ('RFV')
                           );
CREATE INDEX TEST_BRANCH ON TEST1(BRANCH) LOCAL;                           
CREATE TABLE JOB_SUBMIT_TEST(TIME_STAMP TIMESTAMP, MODULE VARCHAR2(1000), ID NUMBER );
CREATE SEQUENCE  JS_SEQ START WITH 1 INCREMENT BY 1  ;

CREATE OR REPLACE PROCEDURE TEST_PROC(
    PIN_BRANCH VARCHAR2 , VSTATUS  OUT VARCHAR2)
IS
ERRMSG VARCHAR2(200);
QUERY_STRING  VARCHAR2(200);

BEGIN
QUERY_STRING :='ALTER TABLE TEST1 TRUNCATE PARTITION  '|| PIN_BRANCH;
   
INSERT  
INTO JOB_SUBMIT_TEST VALUES
    (
      SYSTIMESTAMP ,     
      ERRMSG ||' SUCCESS   :'||QUERY_STRING,
      JS_SEQ.NEXTVAL
    );
  
  EXECUTE IMMEDIATE QUERY_STRING;
    COMMIT;
  VSTATUS:=0;
EXCEPTION
WHEN OTHERS THEN
ERRMSG :=' CODE :'||SQLCODE||'ERROR MSG  :'||ERRMSG ;   
VSTATUS :=  SQLCODE;
  INSERT
  INTO JOB_SUBMIT_TEST VALUES
    (
      SYSTIMESTAMP ,     
      ERRMSG ||' QUERY :'||QUERY_STRING,
      JS_SEQ.NEXTVAL
    );
    COMMIT;
END;
/

CREATE OR REPLACE TRIGGER DDL_TRIGGER
    AFTER DDL ON SCHEMA
    BEGIN
      IF ORA_DICT_OBJ_NAME = 'TEST1' THEN
        DBMS_LOCK.SLEEP(60);
      END IF;
    END;
    /

DECLARE
   JOBNO   NUMBER;
   START_DT DATE  :=SYSDATE + (1/(24 *60)) ;
   PROC_TO_EXECUTED_HYD  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''HYD'',VSTATUS); END;';
   PROC_TO_EXECUTED_NDL  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''NDL'',VSTATUS); END;';
   PROC_TO_EXECUTED_REV  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''REV'',VSTATUS); END;';
   PROC_TO_EXECUTED_SAR  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''SAR'',VSTATUS); END;';
   
   PROC_TO_EXECUTED_ABC  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''ABC'',VSTATUS); END;';
   PROC_TO_EXECUTED_XYZ  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''XYZ'',VSTATUS); END;';
   
   PROC_TO_EXECUTED_QAZ  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''QAZ'',VSTATUS); END;';
   PROC_TO_EXECUTED_WSX  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''WSX'',VSTATUS); END;';
   PROC_TO_EXECUTED_EDC  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''EDC'',VSTATUS); END;';
   PROC_TO_EXECUTED_RFV  VARCHAR2(200)  :='DECLARE VSTATUS NUMBER; BEGIN TEST_PROC(''RFV'',VSTATUS); END;';
BEGIN
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_HYD ,NEXT_DATE => START_DT );
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_NDL,NEXT_DATE =>  START_DT );
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_REV ,NEXT_DATE => START_DT );
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_SAR,NEXT_DATE =>  START_DT );
   
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_ABC ,NEXT_DATE => START_DT );
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_XYZ,NEXT_DATE =>  START_DT );
   
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_QAZ ,NEXT_DATE => START_DT );
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_WSX,NEXT_DATE =>  START_DT );
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_EDC ,NEXT_DATE => START_DT );
   DBMS_JOB.SUBMIT(JOB  => JOBNO,WHAT => PROC_TO_EXECUTED_RFV,NEXT_DATE =>  START_DT );
   
   COMMIT;
END;
/
SELECT  Decode(v.locked_mode, 0, 'None',
                             1, 'Null (NULL)',
                             2, 'Row-S (SS)',
                             3, 'Row-X (SX)',
                             4, 'Share (S)',
                             5, 'S/Row-X (SSX)',
                             6, 'Exclusive (X)',
                             v.locked_mode) locked_mode, D.*  FROM GV$LOCKED_OBJECT V , SYS.DBA_OBJECTS D
WHERE D.OBJECT_ID = V.OBJECT_ID;
SELECT  *  FROM JOB_SUBMIT_TEST order by id;
SELECT  *  FROM SYS.DBA_JOBS WHERE LAST_DATE IS   NULL; 


But Still all partitions truncation are working fine :
Below is the result from few tables


Mode            Owner   Obejct Sub Obj  Obj_id Sub_o_id Objecttype      Created
Exclusive (X)	QFXMAIN	TEST1	WSX	217338	217338	TABLE PARTITION	20-FEB-19 01:29:28 PM
Exclusive (X)	QFXMAIN	TEST1	SAR	217334	217334	TABLE PARTITION	20-FEB-19 01:29:28 PM
Exclusive (X)	QFXMAIN	TEST1	NDL	217332	217332	TABLE PARTITION	20-FEB-19 01:29:28 PM
Row-X (SX)	QFXMAIN	TEST1		217330		TABLE	20-FEB-19 01:29:28 PM
Row-X (SX)	QFXMAIN	TEST1		217330		TABLE	20-FEB-19 01:29:28 PM
Row-X (SX)	QFXMAIN	TEST1		217330		TABLE	20-FEB-19 01:29:28 PM

JOB_SUBMIT_TEST

Time_stamp               Module                                                 ID      
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  ABC	1
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  REV	2
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  WSX	3
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  QAZ	4
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  SAR	5
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  EDC	6
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  XYZ	7
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  RFV	8
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  NDL	9
20-FEB-19 01:30:49 PM	 SUCCESS   :ALTER TABLE TEST1 TRUNCATE PARTITION  HYD	10


Still unable to catch the Exact time to replicate the issue.But as you explained able to see the Row Shared lock on the Object also
Re: Parallel DDL statements in a same table with different partitions [message #674905 is a reply to message #674878] Thu, 21 February 2019 07:26 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member

Hi Michel,

Even though, I am unable to replicate the issue with my TEST1 example in development environment ,
this problem was occurring in UAT very frequently when we are running the multiple branches procedures at the same time(parallel)


In order to fix this issue we are going to increase the DDL_LOCK_TIMEOUT Parameter.


EXECUTE immediate 'alter session set  DDL_LOCK_TIMEOUT=60';


Could you please let me know shall i go with this fix ?


Thanks
SaiPradyumn

Re: Parallel DDL statements in a same table with different partitions [message #674906 is a reply to message #674852] Thu, 21 February 2019 07:41 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
saipradyumn wrote on Tue, 19 February 2019 06:50
Hi All,

We have a LIST Partitioned table on the BRANCH Column.There is procedure bypassing branch as parameter which will truncates branch specific partition.When we are executing the same procedure with different branch parameter at the same time(parallel), some times(not always) we are getting the following exception.


ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

How is it that you seem to know the exact error being thrown, but don't know the specific SQL statement throwing this error?
Re: Parallel DDL statements in a same table with different partitions [message #674912 is a reply to message #674905] Thu, 21 February 2019 10:21 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

As I said, the locks are held for a very short time.
I tried it, with the trigger, truncating 2 partitions and here what I saw (with a wait of 2-5 seconds between each return):
SQL> @lock

Sid        Statut   Utilisateur     OS Pid     LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
155,441    ACTIVE   MICHEL          5464       TM X     MICHEL.TEST.NDL
                                               TX X     _SYSSMU4_3802565882$ tx: 0x0004.001D.0000AB67
                                               TM RX    MICHEL.TEST

SQL> @lock

Sid        Statut   Utilisateur     OS Pid     LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
145,11     ACTIVE   MICHEL          3532       TM X     MICHEL.TEST.HYD
                                               TX X     _SYSSMU3_3408062297$ tx: 0x0003.001A.0000E466
                                               TM RX    MICHEL.TEST

SQL> @lock

Sid        Statut   Utilisateur     OS Pid     LK Mod W Objet
---------- -------- --------------- ---------- -- --- - ------------------------------------------------------------
145,11     ACTIVE   MICHEL          3532       TM X     MICHEL.TEST.HYD
                                               TX X     _SYSSMU3_3408062297$ tx: 0x0003.001A.0000E466
                                               TM RX    MICHEL.TEST
...
The 2 sessions (145 and 155) can truncate the partitions.
What I suggest you is to trap the error and reexecute the aborted command, most likely it will pass the second time.

Re: Parallel DDL statements in a same table with different partitions [message #674918 is a reply to message #674912] Fri, 22 February 2019 00:56 Go to previous messageGo to next message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member
Hi Blackswan,


Quote:
How is it that you seem to know the exact error being thrown, but don't know the specific SQL statement throwing this error?

In my actual program we are capturing the exact sql statement when there is a exception.
As per the those log when it is trying to truncate the partition on the same table its failing

Hi Michel ,

Instead of handling in the code, will it impact any where if we increase DLL_LOCK_TIMEPUT Parameter at specific session level

Thanks
SaiPradyumn
Re: Parallel DDL statements in a same table with different partitions [message #674919 is a reply to message #674918] Fri, 22 February 2019 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Setting the parameter you will impact other things.
The only way to know is to test.

Re: Parallel DDL statements in a same table with different partitions [message #674921 is a reply to message #674919] Fri, 22 February 2019 01:32 Go to previous message
saipradyumn
Messages: 419
Registered: October 2011
Location: Hyderabad
Senior Member


Ok Thanks Michel for your suggestion

Previous Topic: difference amount between 2 dates
Next Topic: Get Window User Name and MAC address after insert in RFQ table in oracle application Transaction (merged)
Goto Forum:
  


Current Time: Thu Mar 28 15:14:17 CDT 2024