Re: deadlocks , performance issues

From: Bobak, Mark <Mark.Bobak_at_proquest.com>
Date: Wed, 24 Aug 2011 12:45:56 -0400
Message-ID: <4df62682-1601-4a4f-b725-8e82365a74a1_at_blur>

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<mailto: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<mailto:sidi.bouzid.meknessy_at_gmail.com>> To: "Bobak, Mark" <Mark.Bobak_at_proquest.com<mailto: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<tel: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<tel: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<tel: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<mailto: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<mailto:sidi.bouzid.meknessy_at_gmail.com>> To: "oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>"

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

Received on Wed Aug 24 2011 - 11:45:56 CDT