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!
