Oracle explain for cascading delete is wrong 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?
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:
&nbsp;&nbsp;&nbsp;-&nbsp;&nbsp;An ordering of the tables referenced by the statement
&nbsp;&nbsp;&nbsp;-&nbsp;&nbsp;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.

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