Unindexed Foreign Keys on empty/unused table and locks

Thursday, December 17, 2015

It is widely known that unindexed foreign keys can be performance issue. Unindexed foreign keys on child tables can cause table locks or performance problems in general.
There are many articles on this subject so I won't go in details.

My plan is to show simple demo case where empty child table with unindexed foreign key column can cause big problems.


Imagine that you have highly active table (supplier) with lots DML operations from many sessions.
In the meantime someone created new child table (product) in relationship with parent table (supplier). This table is empty and unused so why should you bother about indexing foreign key columns on empty table.

I will show you case where this empty table can cause lock contention and serious performance issues.

Oracle version - 11.2.0.4.0

CREATE TABLE supplier 
( id number(10) not null,
  supplier_id number(10) not null, 
  supplier_name varchar2(50) not null, 
  contact_name varchar2(50), 
CONSTRAINT id_pk PRIMARY KEY (id),
CONSTRAINT supplier_uk UNIQUE(supplier_id)
); 


INSERT INTO supplier VALUES (1,100, 'Supplier 1', 'Contact 1'); 
INSERT INTO supplier VALUES (2,200, 'Supplier 2', 'Contact 2'); 

COMMIT; 



CREATE TABLE product 
( product_id number(10) not null, 
product_name varchar2(50) not null, 
supplier_id number(10) not null, 
CONSTRAINT fk_supplier 
FOREIGN KEY (supplier_id) 
REFERENCES supplier(supplier_id)
 ); 
  
 
 
SQL> select id, supplier_id, supplier_name, contact_name from supplier;

        ID SUPPLIER_ID SUPPLIER_NAME                                      CONTACT_NAME
---------- ----------- -------------------------------------------------- ------------
         1         100 Supplier 1                                         Contact 1
         2         200 Supplier 2                                         Contact 2
    
    
-- Product table is empty and unused
SQL> select product_id, product_name, supplier_id from product;

no rows selected

User from SESSION1 inserts row and waits some time to end transaction.
--SESSION 1:
INSERT INTO supplier VALUES (3,300, 'Supplier 3', 'Contact 3'); --(Without COMMIT)
1 row created.

In the same time there are lots sessions which are trying to update record with column used in foreign-key relationship. All sessions are hanging and you have big problem.
--SESSION 2:
UPDATE supplier SET supplier_id=200 WHERE supplier_id = 200;  --(HANG)

Let's try another INSERT in next session:
--SESSION 3:
INSERT INTO supplier VALUES (4,400, 'Supplier 4', 'Contact 4');  --(HANG)

Now we have inserts hanging which could lead to major problems for very active table.

Check locks:

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, 
       l.request, o.object_name, o.object_type 
FROM v$lock l, dba_objects o, v$session s 
WHERE UPPER(s.username) = UPPER('MSUTIC') 
AND l.id1 = o.object_id (+) 
AND l.sid = s.sid 
ORDER BY sid, type;

       SID    BLOCKER EVENT                                 TY      LMODE    REQUEST OBJECT_NAME                 OBJECT_TYPE
---------- ---------- -------------------------------------- -- ---------- ---------- -------------------------- ------------
        63       1641 enq: TM - contention                   AE          4          0 ORA$BASE                    EDITION
        63       1641 enq: TM - contention                   TM          3          0 SUPPLIER                    TABLE
        63       1641 enq: TM - contention                   TM          0          4 PRODUCT                     TABLE
      1390            SQL*Net message to client              AE          4          0 ORA$BASE                    EDITION
      1641            SQL*Net message from client            AE          4          0 ORA$BASE                    EDITION
      1641            SQL*Net message from client            TM          3          0 SUPPLIER                    TABLE
      1641            SQL*Net message from client            TM          3          0 PRODUCT                     TABLE
      1641            SQL*Net message from client            TX          6          0 TPT                         SYNONYM
      2159            SQL*Net message from client            AE          4          0 ORA$BASE                    EDITION
      2729         63 enq: TM - contention                   AE          4          0 ORA$BASE                    EDITION
      2729         63 enq: TM - contention                   TM          0          3 PRODUCT                     TABLE
      2729         63 enq: TM - contention                   TM          3          0 SUPPLIER                    TABLE


Unused and empty product table is culprit for performance issues.


Create index on foreign key column and check behaviour.

CREATE INDEX fk_supplier ON product (supplier_id);

--SESSION 1:
INSERT INTO supplier VALUES (3,300, 'Supplier 3', 'Contact 3');
1 row created.


--SESSION 2:
UPDATE supplier SET supplier_id=200 WHERE supplier_id = 200;
1 row updated.

Now everything worked without locking problems.



Notice that we have different behaviour in 12c version.

Oracle version - 12.1.0.2.0

CREATE TABLE supplier 
( supplier_id number(10) not null, 
  supplier_name varchar2(50) not null, 
  contact_name varchar2(50), 
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) 
); 

INSERT INTO supplier VALUES (1, 'Supplier 1', 'Contact 1'); 
INSERT INTO supplier VALUES (2, 'Supplier 2', 'Contact 2'); 
COMMIT; 

CREATE TABLE product 
( product_id number(10) not null, 
product_name varchar2(50) not null, 
supplier_id number(10) not null, 
CONSTRAINT fk_supplier 
FOREIGN KEY (supplier_id) 
REFERENCES supplier(supplier_id)
); 
  

--SESSION 1:
INSERT INTO supplier VALUES (3, 'Supplier 3', 'Contact 3'); --(Without COMMIT)
1 row created.

--SESSION 2:
UPDATE supplier SET supplier_id=2 WHERE supplier_id = 2; -- (No HANG)
1 row updated.

Check locks:

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode,
       l.request, o.object_name, o.object_type 
FROM v$lock l, dba_objects o, v$session s 
WHERE UPPER(s.username) = UPPER('MSUTIC') 
AND l.id1 = o.object_id (+) 
AND l.sid = s.sid 
ORDER BY sid, type;


  SID    BLOCKER EVENT                          TY      LMODE    REQUEST OBJECT_NAME
------ ---------- ------------------------------ -- ---------- ---------- ------------
 4500            SQL*Net message from client    AE          4          0 ORA$BASE
 4500            SQL*Net message from client    TM          3          0 SUPPLIER
 4500            SQL*Net message from client    TX          6          0
 6139            SQL*Net message to client      AE          4          0 ORA$BASE
 6144            SQL*Net message from client    AE          4          0 ORA$BASE
 6144            SQL*Net message from client    TM          3          0 SUPPLIER
 6144            SQL*Net message from client    TM          2          0 PRODUCT
 6144            SQL*Net message from client    TX          6          0



I don't think that you should index all foreign keys all the time. Sometimes this is not needed and it could be overhead. Unnecessary indexes on foreign keys are wasting storage space and cause slower DML operations on the table.

Think about application and how parent/child tables will be used before creating indexes and check articles from Tom Kyte on this subject.





Update 2016-07-08:


Oracle version - 11.2.0.4.0

What if we index column using descending order.

CREATE INDEX fk_supplier ON product (SUPPLIER_ID DESC);

Index created.

--SESSION 1:
INSERT INTO supplier VALUES (3,300, 'Supplier 3', 'Contact 3'); --(Without COMMIT)

--SESSION 2:
UPDATE supplier SET supplier_id=200 WHERE supplier_id = 200;  --(HANG)

--Try another INSERT in next session:

--SESSION 3:
INSERT INTO supplier VALUES (4,400, 'Supplier 4', 'Contact 4');  --(HANG)

Check locks:

SELECT l.sid, s.blocking_session blocker, s.event, l.type, l.lmode, 
       l.request, o.object_name, o.object_type 
FROM v$lock l, dba_objects o, v$session s 
WHERE UPPER(s.username) = UPPER('MSUTIC') 
AND l.id1 = o.object_id (+) 
AND l.sid = s.sid 
ORDER BY sid, type;


   SID    BLOCKER EVENT                          TY      LMODE    REQUEST OBJECT_NAME   OBJECT_TYPE
------ ---------- ------------------------------ -- ---------- ---------- ------------- -----------
   192       1137 enq: TM - contention           AE          4          0 ORA$BASE      EDITION
   192       1137 enq: TM - contention           TM          3          0 SUPPLIER      TABLE
   192       1137 enq: TM - contention           TM          0          3 PRODUCT       TABLE
   382            SQL*Net message from client    AE          4          0 ORA$BASE      EDITION
   949            SQL*Net message from client    AE          4          0 ORA$BASE      EDITION
   949            SQL*Net message from client    TM          3          0 SUPPLIER      TABLE
   949            SQL*Net message from client    TM          3          0 PRODUCT       TABLE
   949            SQL*Net message from client    TX          6          0
  1137        949 enq: TM - contention           AE          4          0 ORA$BASE      EDITION
  1137        949 enq: TM - contention           TM          3          0 SUPPLIER      TABLE
  1137        949 enq: TM - contention           TM          0          4 PRODUCT       TABLE
  1516            SQL*Net message to client      AE          4          0 ORA$BASE      EDITION
  2459            SQL*Net message from client    AE          4          0 ORA$BASE      EDITION


Keep in mind - using descending order for the column to create index will not solve problem with concurrency.


Share/Bookmark

0 comments:

Post a Comment