See content of temporary Table in Oracles' EXPLAIN

Question

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?

Answer 1

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 (via v$session and v$sql for example), and then query the v$sql_plan table for the column OTHER_XML. For running in SQL*Plus, you'll want to include the noted set commands to see the output.

set long 65000
set linesize 200

select sql_id, other_xml from v$sql_plan where sql_id='26upzh3jd5fd7'
and other_xml is not null;

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:

execute DBMS_SESSION.SESSION_TRACE_ENABLE(binds=>true);
-- invoke your SQL after a brief SQL to help you find it later in the trace
select 'HEY my sql start' from dual;
-- your select query goes here
select 'HEY my sql stop' from dual;
execute DBMS_SESSION.SESSION_TRACE_DISABLE;

The trace file will be in a directory that in Linux is likely named something like

/u01/app/oracle/diag/rdbms/${ORACLE_SID}/${ORACLE_SID}/trace