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.
Very useful :)
ReplyDelete