I have used the following procedure to view the last execution plan

create or replace procedure display_plan
  for c in (select plan_table_output t from table(dbms_xplan.display_cursor('', '', 'allstats advanced last'))) loop
  end loop;

With dbms_xplan.display_cursor it is possible to specify the SQL_ID. I understand that you can get a list of SQL_IDs from the table V$SQL but how does one decide what ones belong to the current session?

  • 1
    You don't need a procedure for that. Just run the select statement directly.
    – user1822
    Aug 27, 2015 at 13:08
  • ... It just makes life a little easier to have it as a procedure
    – Ed Heal
    Aug 27, 2015 at 13:10

1 Answer 1


Yes, you can specify the SQL_ID. The usage would be something like this:

SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('gwp663cqh5qbf',0));

To find the SQL IDs for the current session, you'll first need to get the SID for your session, and then search v$session for the related SQL_IDs:

select sys_context('USERENV','SID') from dual;
select SQL_ID from v$session where SID='yoursid';

If the SQL aged out of the cursor cache, you may be able to find it in v$active_session_history:

select SQL_ID from v$active_session_history where SESSION_ID='yoursid';

I would recommend reading the manual page for DBMS_XPLAN to get the full details.

  • Is it not better just to look into the v$active_session_history table and not bother with the v$session table?
    – Ed Heal
    Aug 27, 2015 at 13:25
  • Unless your query runs in a fraction of a second, v$active_session_history should be fine. The samples are collected every second, so extremely fast queries could theoretically be missed (although I don't think I've ever seen this happen).
    – andba77
    Aug 27, 2015 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.