INTRODUCTION:

This article details the Oracle Database Administration  steps taken to solve a performance issue at a previous client.

Overview:  The client had an application in which much of the processing involved a parent and child table.  The parent table had an “on delete cascade” constraint. 

Problem: The pages in the client application were taking a long time to load and the delete function was taking minutes to finish what should have taken seconds to complete.

Relevant Topics:  Dynamic Sampling, Statistics Generation, Locked Tables, Recursive SQL, Unindexed Foreign Keys, Oracle DBA, database systems, database management

Initial Investigation

In investigating the cause of the problem, my first thought was to look at the explain plan for the delete statement.  The explain plan looked fine.  It was something like:

So according to the explain plan, the delete statement should have executed quickly, but evidently it was not.  I found that the parent table had an “on delete cascade” constraint.  Therefore this was a recursive delete.  Since the explain plan does not show recursive deletes on the child table, I was getting incomplete information here.

Check on the Statistics

Next, I looked at the statistics.  In looking at the schema in the current environment, none of the statistics had been updated.  But when I looked at another environment where the application was running fine, none of the statistics had been updated there either.  Why was the application running fine in the second environment, but not in the first environment?

I ran an explain plan in the second environment and got something like the following.  It was mostly the same as in the first environment except that it was using dynamic sampling. 

This seemed to explain why the statement was working fine in the second environment, but not in the first environment.

I looked briefly at why dynamic sampling was being used on environment 2, and not on environment 1, since dynamic sampling was set to the default of 2 on both environments.  It seems that the SQL Plan Directive above was specifying the use of  dynamic sampling on environment 2, but no such directive was present on environment 1.

Since there was an “on delete cascade” constraint, I checked to see if there were any unindexed foreign keys.   If the child table does not have an index on the foreign key, it can cause a full table scan of the child table for each parent record deleted.  This can also lead to table locks.  In this case, I found that there were no unindexed foreign keys.

Update the Statistics

Obviously, the statistics should have been generated, so I decided to just update the statistics, by running:

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS.

It ran without error, but did not update the statistics.  I found that the tables in the schema were locked as shown by the following query:

I was later informed by the Oracle DBA team that a script was running to update the stats daily, but since the tables were locked and no error was generated, no one realized that it was not having any effect.

I ran the following to unlock the objects in the  schema instead of going table by table:

DBMS_STATS.UNLOCK_SCHEMA_STATS (ownname => ‘<application_owner>’);

Then I was able to run

EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => '<application_owner>');

and it actually updated the statistics.

Success

At that point, the pages in the application loaded much faster and the delete operations were much faster.  The times for delete statements went from 11 to over 29 seconds to under 0.05 seconds!

Summary Points

1. Keep statistics current.
2. Dynamic sampling can help with query performance, but also keep statistics current.
3. The Oracle explain plan will not show information for recursive SQL.
4. Ensure that there are no unindexed foreign keys related to “on delete cascade” constraints.
5. If you need to update statistics, make sure the table is not locked . You can lock the table after gathering statistics if desired and the statistics on this table will change infrequently.