oracleoracle-sql-developer
I'm trying to understand the performance characteristics of SQL statement that does DELETE cascade on 4 linked tables.
When I do the auto tracing of the statement it only shows me the DELETE statement executed on the table. It does not show details of deletes done on other tables.
Why doesn't SQL Developer show cascades in tracing?
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.
Edit:
If you need to extract that information, you probably have to trace your session and look at the TKPROF output.
The data dictionary will only show what it can.
Users can design their schema with or without referential integrity defined in the actual database.
When you import a data dictionary, using SQL Developer, to a new or existing relational data model, the foreign key constraints will be shown by default - if they are there to begin with.
Now, specific to the tool, it CAN attemp to infer any relationships. It does this by looking for common column names sprinkled around your tables, like an 'XYZ_ID' column, that happens to be a PRIMARY KEY in a driving table.
I have step-by-step instructions for this here, on my blog.
Best Answer
Autotrace in SQL Developer gets you the v$sql_plan of your query and the session stats accumulated during the execution of your query - it does not show any recursive SQL that may get executed.
To see that you'd actually want to trace your session.