Re: deadlocks , performance issues

From: Thiago Maciel <thiagomaciel_at_gmail.com>
Date: Wed, 24 Aug 2011 18:09:05 -0300
Message-ID: <CAAHb+wOzrwwspCqewymWwK7s-+9faw=MZEv2B9k6xWtx4mmQHw_at_mail.gmail.com>

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_at_proquest.com>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_at_gmail.com>*
> To: *"Bobak, Mark" <Mark.Bobak_at_proquest.com>*
> 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_at_proquest.com>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_at_gmail.com>*
>> To: *"Bobak, Mark" <Mark.Bobak_at_proquest.com>*
>> 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_at_proquest.com>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_at_gmail.com>*
>>> To: *"oracle-l_at_freelists.org"
>>>
>>>

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

Received on Wed Aug 24 2011 - 16:09:05 CDT