Mayank Gupta, October 28, 2016 - 8:30 pm UTC
Thank You for your time Sir.
Your reply is very much true but is that the only case when this could happen ?
Since the primary key of my table gets populated by sys_guid()function which is always unique and there is no other unique key on my table. And the scenario you mentioned should not occur.
October 29, 2016 - 6:29 am UTC
Perhaps rather than us taking wild guesses, you could provide a test case demonstrating the problem in your instance.
Akram, October 29, 2016 - 4:23 am UTC
Do you have a Bitmap index on table ?
Or do you have a un indexed foreign key on table?
Akram Khan
More details
Gh, October 29, 2016 - 8:00 am UTC
..and provide the table description. .
Is it heap? Clustered? Iot? .....partitioned? Indexed? Provide indexes description ;
Also do the A jobs do a explicit lock.. so the B one will wait?...
Mayank Gupta, October 31, 2016 - 12:09 pm UTC
Below is all about the table that is getting locked.
And there is no explicit locks in process A
CREATE TABLE PARTY
(
PARTY_ID VARCHAR2(32 CHAR) NOT NULL,
PARTY_NAME VARCHAR2(300 CHAR) NOT NULL,
IS_INTERNAL VARCHAR2(1 CHAR) DEFAULT '0' NOT NULL,
IS_LEGAL_ENTITY VARCHAR2(1 CHAR) DEFAULT '0' NOT NULL,
EFFECTIVE_START_DATE DATE DEFAULT SYSDATE NOT NULL,
EFFECTIVE_END_DATE DATE,
PARTY_NUMBER VARCHAR2(30 CHAR),
PARTY_TYPE VARCHAR2(20 CHAR) NOT NULL,
EMAIL_ADDRESS VARCHAR2(150 CHAR),
PHONE_NUMBER VARCHAR2(160 CHAR),
ADDRESS_ID VARCHAR2(32 CHAR),
STREET1 VARCHAR2(360 CHAR),
STREET2 VARCHAR2(360 CHAR),
STREET3 VARCHAR2(360 CHAR),
STREET4 VARCHAR2(360 CHAR),
CITY VARCHAR2(150 CHAR),
STATE VARCHAR2(150 CHAR),
COUNTY VARCHAR2(300 CHAR),
POSTAL_CODE VARCHAR2(60 CHAR),
COUNTRY VARCHAR2(150 CHAR),
NOTES VARCHAR2(4000 CHAR),
EXTERNAL_SYSTEM_ID VARCHAR2(50 CHAR),
EXTERNAL_SYSTEM_NAME VARCHAR2(50 CHAR),
CREATED_ON DATE DEFAULT SYSDATE NOT NULL,
CREATED_BY VARCHAR2(32 CHAR) NOT NULL,
MODIFIED_ON DATE,
MODIFIED_BY VARCHAR2(32 CHAR),
MODULE_VISIBILITY NUMBER(10),
SSO_ID VARCHAR2(32 CHAR)
)
TABLESPACE DICARTA_DATA
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 35
INITRANS 35
MAXTRANS 255
STORAGE (
INITIAL 64M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
CREATE INDEX PARTY_ADDR_ID_IDX ON PARTY
(ADDRESS_ID)
LOGGING
TABLESPACE DICARTA_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX PARTY_NUMBER_UHG1 ON PARTY
(PARTY_NUMBER)
LOGGING
TABLESPACE DICARTA_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX PARTY_EXTERNAL_SYSTEM_ID_UHG1 ON PARTY
(EXTERNAL_SYSTEM_ID)
LOGGING
TABLESPACE DICARTA_INDEX
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX PARTY_NAME_IDX ON PARTY
(PARTY_NAME, PARTY_NUMBER)
LOGGING
TABLESPACE DICARTA_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX PARTY_UHG1 ON PARTY
(UPPER("PARTY_NUMBER"), TRUNC("EFFECTIVE_START_DATE"), TRUNC("EFFECTIVE_END_DATE"))
LOGGING
TABLESPACE DICARTA_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE BITMAP INDEX PARTY_IS_INTERNAL_BI01 ON PARTY
(IS_INTERNAL)
LOGGING
TABLESPACE DICARTA_DATA
PCTFREE 60
INITRANS 20
MAXTRANS 255
STORAGE (
INITIAL 64M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE INDEX PARTY_ISINT_PID_IDX ON PARTY
(IS_INTERNAL, PARTY_ID)
LOGGING
TABLESPACE DICARTA_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64M
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
CREATE OR REPLACE TRIGGER TR_DELETE_PARTY after DELETE on party for each row
begin
if (:old.external_system_name = 'Sourcing Portfolio' AND :old.created_by = 'SourcingAdmin_ID' )
then
raise_application_error (-20100, 'You can not delete this record');
end if;
end;
/
ALTER TABLE PARTY ADD (
PRIMARY KEY
(PARTY_ID)
USING INDEX
TABLESPACE DICARTA_DATA
PCTFREE 10
INITRANS 2
MAXTRANS 255
STORAGE (
INITIAL 64K
NEXT 1M
MAXSIZE UNLIMITED
MINEXTENTS 1
MAXEXTENTS UNLIMITED
PCTINCREASE 0
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
ENABLE VALIDATE);
ALTER TABLE PARTY ADD (
FOREIGN KEY (ADDRESS_ID)
REFERENCES ADDRESS (ADDRESS_ID)
ENABLE VALIDATE);
October 31, 2016 - 12:59 pm UTC
What exactly are the inserts that are blocked?
Mayank Gupta, October 31, 2016 - 1:05 pm UTC
One of the blocked sessions
INSERT INTO PARTY ( PARTY_ID, PARTY_NAME, IS_INTERNAL, IS_LEGAL_ENTITY, EFFECTIVE_START_DATE, EFFECTIVE_END_DATE, PARTY_NUMBER, PARTY_TYPE, EMAIL_ADDRESS, PHONE_NUMBER, STREET1, STREET2, STREET3, STREET4, CITY, STATE, COUNTY, POSTAL_CODE, COUNTRY, NOTES, EXTERNAL_SYSTEM_ID, EXTERNAL_SYSTEM_NAME, CREATED_ON, CREATED_BY, SSO_ID, MODULE_VISIBILITY) VALUES ('5fd6ed230a7b44b798db77302433cb64','office Manager','0','1',To_Date('2016-08-02 12:00:00 AM', 'YYYY-MM-DD HH:MI:SS AM'),null,'','Individual','',null,null,null,null,null,null,null,null,null,null,'',null,null,SYSDATE,'54aa1b1839cf4eefb666269eee5388c0',null,0)
Bitmap Ahoy!
Tubby, October 31, 2016 - 2:43 pm UTC
Best guess is that
CREATE BITMAP INDEX PARTY_IS_INTERNAL_BI01 ON PARTY
(IS_INTERNAL)
Should not be a BITMAP index at all, and just a regular B-Tree index.
Cheers,
October 31, 2016 - 5:16 pm UTC
A reader, November 01, 2016 - 12:22 am UTC
Thanks for that link very nicely explained bitmap index.
I will try to test my process after changing the index.
Thank you for all your help, its something new I learned.
Sure
Gh, November 01, 2016 - 5:26 am UTC
You may have a tx6 lock on that table .
Even so you drop the bitmap index .The col is selective y/n I suppose right?
So no index is required since the bitmap is not reliable here because if tx6 .
Other rem is ensure you have an index on the fk adresses table.
Gh