2

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

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

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. – a_horse_with_no_name Aug 27 '15 at 13:08
  • ... It just makes life a little easier to have it as a procedure – Ed Heal Aug 27 '15 at 13:10
2

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 '15 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 '15 at 13:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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