Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

My deletes in Oracle 11 are very slow. The tables are linked by foreign keys and every foreign key constraint has a delete cascade set on it.

If I hit the explain button on a statement like this DELETE FROM TOP_LEVEL_TABLE WHERE SOMETHING = 'whatever' it only shows my the involvment of the TOP_LEVEL_TABLE even if 30 other tables are involved.

How can I get a more realistic result?

share|improve this question
up vote 5 down vote accepted

I don't think this is possible.

If I'm not mistaken, the optimizer doesn't even know (or at least doesn't try to know) that the statement will "touch" other tables as well.

It's not explicitely documented, but the following quote from the manual only talks about the tables "referenced" in the SQL statement (emphasis is mine):

The row source tree is the core of the execution plan. It shows the following information:
   -  An ordering of the tables referenced by the statement
   -  An access method for each table mentioned in the statement

I think the important part is: "tables referenced by the statement". No mentioning of "dependent" tables.

If you need to extract that information, you probably have to trace your session and look at the TKPROF output.

share|improve this answer
+1 for Trace it. – Leigh Riffel Mar 28 '12 at 13:56
The cascading deletes will be in the trace file, but nowhere else, mostly because Oracle may or may not issue delete statements based on the data. See my answer to… – Adam Musch Mar 28 '12 at 21:20
@AdamMusch: thanks, good to know. – a_horse_with_no_name Mar 28 '12 at 21:22

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.