My lunch was interrupted yet again by a developer with a support question. “The users are complaining that it takes too long to clear out some data. There’s a delete that’s taking ages. Can you look into it please?” he asked.
“A long running delete?” I mused. “That’s interesting…”. Like so many systems these days we don’t do much in the way of deletes. Oracle could deprecate the DELETE statement and it would only be a minor inconvenience to us.:-)
Luckily for me the developer had already done an excellent job of narrowing down the problem to a specific packaged SQL statement. The statement itself was quite trivial; delete a single row from a table, identifying the row via its single column primary key. It doesn’t get much simpler than that. I didn’t bother checking for triggers on the table as the system doesn’t use them.
Since the problem occurred in the past hour I turned to my favourite diagnostic tool; Active Session History. A quick query of ASH using the SQL_ID of the delete statement revealed the extent of the problem; 120 samples. Yep, the users were quite right to complain as a single row delete should not take 2 minutes to complete. Looking at the EVENT details I saw that the session had been waiting on IO for they were mostly “db file scattered read” with a handful of “db file sequential read”. This told me what the problem was likely to be…
Foreign keys are constraints in the database to ensure that data values used in child table entry are present in a parent, reference table. This helps enforce the integrity we desire, and generally assume to be, in the data. The presence of foreign keys also has an often overlooked impact to deletions made against the parent table. When a row is deleted from the parent table, Oracle needs to do a check against the child table to ensure that the row being deleted is not referenced by the child table. When the foreign key column(s) in the child table are indexed then this check against the child table is quick and generally not noticable. If the foreign key column(s) are not indexed then Oracle has no choice but to resort to a full table scan of the child table… and if the child table is fairly large then that check may take a while to complete, holding up the delete.
So, knowing about the impact of unindexed foreign keys when deleting from a parent table and observing a lot of physical IO the next step for me was to identify the object that the IO was being performed on to confirm my suspicion. Once again the ASH data contained the necessary details. The CURRENT_OBJ# attribute contains the reference to the object that the IO related to. A quick query against DBA_OBJECTS using the object ID from ASH revealed it to be a table and, yep, it contained an unindexed foreign key to the table that the delete was being performed on. The child table contained tens of millions of rows so a table scan would certainly have explained the delay in deleting from the parent table.
Diagnosis complete. Time to resolve the problem. The solution: drop the foreign key on the child table. No, no, just kidding! The solution was to index the foreign key column in the child table, making sure to specify the ONLINE clause of the CREATE INDEX statement so that the index could be added without blocking acivity on the child the table.
Mark Hoxey 
Excellent post Mark, I liked the way you tracked the problem down using ASH. In the past I have usually used a version of Tom Kyte’s script to look for un-indexed foreign keys when I have suspected that is the cause. When I am next diagnosing a suspected un-indexed foreign key I will look to see if I can get access to ASH myself in order to see the symptoms. Being a developer I doubt that will happen but it’s worth asking! On a side note do you have checks in place to periodically check for un-indexed foreign keys?
The systems I work on have quite a few unindexed foreign keys so a periodic check would reveal those. This isn’t a problem for us though. If the data access paths used aren’t done using the foreign key columns (and, of course, no deletes are made to the parent table) then it’s not necessary to index foreign key columns. Many people consider it considered good practice to always index foreign keys but it needs to be looked at on a case by case basis.
Regards
Mark
Totally agree and it’s refreshing to hear that you look at them on a case by case basis.