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