Unindexed foreign keys – Part IV

Very recently got into an application to investigate a performance issue, where one of the delete statement took very long to complete.

rajesh@ORA12C> set timing on

rajesh@ORA12C> delete from t1 where id = 55;

1 row deleted.

Elapsed: 00:00:15.27

rajesh@ORA12C>

Say in this case it took nearly fifteen seconds to just delete one row from this table.  So what is causing this slowness, what could be the reason?

With the help of few trace file, we are able to identify the issue.

********************************************************************************

delete from t1

where

 id = 55

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.01       0.03          0          0          0           0

Execute      1      0.00       0.01          1          3          8           1

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        2      0.01       0.04          1          3          8           1

Misses in library cache during parse: 1

Optimizer mode: ALL_ROWS

Parsing user id: 111 

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         0          0          0  DELETE  T1 (cr=156871 pr=156774 pw=0 time=15110100 us)

         1          1          1   INDEX UNIQUE SCAN T1_PK (cr=3 pr=1 pw=0 time=12999 us cost=2 size=6 card=1)(object id 108040)

********************************************************************************

SQL ID: 53d5nbqjhjj85 Plan Hash: 3321871023

select /*+ all_rows */ count(1)

from

 "RAJESH"."T2" where "ID" = :1

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute      1      0.00       0.00          0          0          0           0

Fetch        1      2.29      15.09     156773     156861          0           1

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total        3      2.29      15.09     156773     156861          0           1

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: SYS   (recursive depth: 1)

Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation

---------- ---------- ----------  ---------------------------------------------------

         1          1          1  SORT AGGREGATE (cr=156861 pr=156773 pw=0 time=15094891 us)

         0          0          0   TABLE ACCESS FULL T2 (cr=156861 pr=156773 pw=0 time=15094882 us cost=56093 size=6 card=1)

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file scattered read                       3848        0.48         12.81

********************************************************************************    

The delete against Table T1 is really faster, it is the SELECT statement against T2 is causing slowness (took almost 15 secs), but why does Oracle have this Select against T2 when performing Delete against T1?

Peeking into the data dictionary helped us here.

rajesh@ORA12C> select t2.table_name,t2.constraint_name,t2.constraint_type

  2  from user_constraints t1 ,

  3     user_constraints t2

  4  where t1.table_name ='T1'

  5  and t1.constraint_type ='P'

  6  and t2.r_constraint_name = t1.constraint_name ;

TABLE_NAME CONSTRAINT CONSTRAINT

---------- ---------- ----------

T2         T2_FK      R

1 row selected.

rajesh@ORA12C>

rajesh@ORA12C> select index_name

  2  from user_ind_columns

  3  where table_name ='T2' ;

no rows selected

rajesh@ORA12C>

So basically we had unindexed foreign key on table T2 which references table T1, for each delete on T1 we full scan the Table T2 to check if any child record exists, if exists then delete fails, if not then delete succeeds. 

Having index on foreign keys will eliminate those full scan on T2 and replace them in small index range scans – that in turn make things to go faster.

rajesh@ORA12C> delete from t1 where id = 54;

1 row deleted.

Elapsed: 00:00:11.67

rajesh@ORA12C> rollback;

Rollback complete.

Elapsed: 00:00:00.03

rajesh@ORA12C> create index t2_idx on t2(id) nologging;

Index created.

Elapsed: 00:00:32.71

rajesh@ORA12C> delete from t1 where id = 54;

1 row deleted.

Elapsed: 00:00:00.08

rajesh@ORA12C>

The dreaded “unindexed foreign key” strikes again!