I'm executing a Query in Oracle SQL Developer and eventually call
SELECT * FROM TABLE ( dbms_xplan.display );
to get an impression what Oracle just did. There's a column Name in the output that states the name of the index that is used in an operation or the name of the table that is being accessed.
Every once in a while it says something like SYS_TEMP_0FD9D6B47_384FBF5. I'm not sure what of make of it. I guess this is a temporary table created from a WITH clause. How can I see what's the content or the SQL code behind this table?
Best Answer
As @pmdba mentioned, no way to peek at that table, and if you could, it would likely be raw data in some strange format. But if you want more detail, find out the
SQL_ID
for the statement in question (viav$session
andv$sql
for example), and then query the v$sql_plan table for the columnOTHER_XML
. For running in SQL*Plus, you'll want to include the notedset
commands to see the output.and you will see all sorts of interesting things, if you are patient in reformatting it into something readable. Note that information is basically a hint used in invoking the SQL; what really happens may or may not match that.
Now if you want to get into what actually happens, including what "guesses" the Oracle optimizer does before actually running a query, you need to (assuming you have DBA privileges), turn on SQL trace for the session, run your SQL, then turn off SQL trace after. Then go to your diagnostics directory, and find the *.trc file associated with your session. And make sure you have some food nearby. A lot of data there, but it has helped me several times to fix long-running queries. Brief example:
The trace file will be in a directory that in Linux is likely named something like