Re: deadlocks , performance issues

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"
>>>
>>>

Other related posts: