Looks like you are getting this issue due to the new oracle lock behavior on 11g. Take a look on Jonathan Lewis blog: http://jonathanlewis.wordpress.com/2010/02/15/lock-horror/ And see the comment 11, and the others one below. From my tests i realized that Oracle does not implement FIFO anymore on 11g, and also inserts into the parent table DO acquire table locks on the child table ( without foreign key index). On Wed, Aug 24, 2011 at 1:45 PM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>wrote: > I don't see how that's possible. Deadlocks are about locking, and that > order locks are taken. > > *Sent from my Motorola ATRIX™ 4G on AT&T* > > > -----Original message----- > > *From: *mek s <sidi.bouzid.meknessy@xxxxxxxxx>* > To: *"Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>* > Sent: *Wed, Aug 24, 2011 15:46:35 GMT+00:00* > Subject: *Re: deadlocks , performance issues > > Hi Mark, > > We are facing huge memory usage problems; do you think deadlocks could be > the cause of this? > > Thanks, > S > > On Wed, Aug 24, 2011 at 3:05 PM, Bobak, Mark <Mark.Bobak@xxxxxxxxxxxx>wrote: > >> Ok, first thing, index those FKs. That alone may solve your problem. >> >> *Sent from my Motorola ATRIX™ 4G on AT&T* >> >> >> -----Original message----- >> >> *From: *mek s <sidi.bouzid.meknessy@xxxxxxxxx>* >> To: *"Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>* >> Sent: *Wed, Aug 24, 2011 13:03:50 GMT+00:00* >> Subject: *Re: deadlocks , performance issues >> >> Hi Mark, >> >> The Type of SQL is Insert ; >> >> Rows waited on: >> Session 197: no row >> Session 1835: no row >> >> Yes the table has unindexed foreign keys. >> here it is DDL: >> >> Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit >> Production >> With the Partitioning option >> >> SQL> SET LONG 10000 >> SQL> SELECT dbms_metadata.get_ddl('TABLE', 'DEVICEWORKFLOW', >> 'NBBS_RES_USER') from dual; >> >> DBMS_METADATA.GET_DDL('TABLE','DEVICEWORKFLOW','NBBS_RES_USER') >> >> -------------------------------------------------------------------------------- >> >> CREATE TABLE "NBBS_RES_USER"."DEVICEWORKFLOW" >> ( "WORKFLOWID" NUMBER NOT NULL ENABLE, >> "DEVICEID" NUMBER NOT NULL ENABLE, >> "TASKNAME" VARCHAR2(64), >> "CREATED" TIMESTAMP (6) NOT NULL ENABLE, >> "STATUS" VARCHAR2(64) NOT NULL ENABLE, >> "STATUSCHANGE" TIMESTAMP (6) NOT NULL ENABLE, >> "STARTED" TIMESTAMP (6), >> "SCRIPTNAME" VARCHAR2(256) NOT NULL ENABLE, >> "SUBSTATUS" VARCHAR2(64) NOT NULL ENABLE, >> >> DBMS_METADATA.GET_DDL('TABLE','DEVICEWORKFLOW','NBBS_RES_USER') >> >> -------------------------------------------------------------------------------- >> "ATTEMPT" NUMBER NOT NULL ENABLE, >> "DATA" CLOB, >> "ITERATION" NUMBER, >> "FAULTCODE" VARCHAR2(64), >> CONSTRAINT "CT_DEVICEWORKFLOW_PK" PRIMARY KEY ("WORKFLOWID") >> USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS >> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 >> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT >> FLASH_CACHE DE >> FAULT CELL_FLASH_CACHE DEFAULT) >> TABLESPACE "NBBS_INDEX_DATA" ENABLE, >> CONSTRAINT "FK_DEVICEWORKFLOW_DEVICE" FOREIGN KEY ("DEVICEID") >> >> DBMS_METADATA.GET_DDL('TABLE','DEVICEWORKFLOW','NBBS_RES_USER') >> >> -------------------------------------------------------------------------------- >> REFERENCES "NBBS_RES_USER"."DEVICE" ("DEVID") ON DELETE CASCADE >> ENABLE, >> CONSTRAINT "FK_DEVICEWORKFLOW_TASK" FOREIGN KEY ("TASKNAME") >> REFERENCES "NBBS_RES_USER"."DEVICETASK" ("NAME") ON DELETE >> CASCADE ENABLE >> ) SEGMENT CREATION IMMEDIATE >> PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING >> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 >> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT >> FLASH_CACHE DE >> FAULT CELL_FLASH_CACHE DEFAULT) >> TABLESPACE "NBBS_USER_DATA" >> LOB ("DATA") STORE AS BASICFILE ( >> TABLESPACE "NBBS_USER_DATA" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION >> >> DBMS_METADATA.GET_DDL('TABLE','DEVICEWORKFLOW','NBBS_RES_USER') >> >> -------------------------------------------------------------------------------- >> NOCACHE LOGGING >> STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 >> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT >> FLASH_CACHE DE >> FAULT CELL_FLASH_CACHE DEFAULT)) >> >> >> >> SQL> >> SQL> >> >> >> Cheers, >> >> On Wed, Aug 24, 2011 at 2:28 PM, Bobak, Mark >> <Mark.Bobak@xxxxxxxxxxxx>wrote: >> >>> Need more information. What types of SQL are involved? Insert? >>> Update? Delete? >>> >>> Do you have a table with child table that has unindexed FKs? >>> >>> *Sent from my Motorola ATRIX™ 4G on AT&T* >>> >>> >>> -----Original message----- >>> >>> *From: *mek s <sidi.bouzid.meknessy@xxxxxxxxx>* >>> To: *"oracle-l@xxxxxxxxxxxxx" >>> >>>