Toad World® Forums

How to display the actual execution plan of the last executed query using dbms_xplan.display_cursor?

#1

When executing the following statements, the second statement is supposed to show the actual execution plan of the last executed statement (supposedly, select * from dual) :
select * from dual;
select * from table(dbms_xplan.display_cursor);

When executed with Toad 13.1.0.78 , the result is as follows :

DUMMY

X
1 row selected.

PLAN_TABLE_OUTPUT

SQL_ID 38j07h1k2b5fz, child number 0

begin :Result := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');end;

NOTE: cannot fetch plan for SQL_ID: 38j07h1k2b5fz, CHILD_NUMBER: 0
Please verify value of SQL_ID and CHILD_NUMBER;
It could also be that the plan is no longer in cursor cache (check v$sql_p
lan)

8 rows selected.

It looks like Toad is executing an intermediate anonymous PL/SQL block "begin :Result := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');end;", right before executing the "select * from table(dbms_xplan.display_cursor);"

Is this the normal behavior of Toad ?

Is there a way to avoid/deactivate the execution of "begin :Result := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');end;" ?

Is there a way to display the actual execution plan of the last executed statement, without having to use the integrated "Explain plan current SQL (Ctrl+E)" functionality with the "Load cached plan if possible" option ?

Thank you for your help.

#2

Hi Zobby,

Yes, this is normal behavior. Toad has to know which schema a SELECT query is executed under in case you want to export it....because in that case we'd need to make sure it's executed the same way.

No way to turn that behavior off, sorry.

CTRL+E is how you'd get an EP for a statement. You don't need to have "Load cached" checked though. Is there some reason this won't work for you?

-John

#3

Hi John,

Thanks for you reply.

Well, yes, CTRL+E method would work.

However, due to personal preferences, I find it not really practical doing it that way. I find it easier to access the plan of the last executed statement through running a simple SELECT on DBMS_XPLAN.DISPLAY or DBMS_XPLAN.DISPLAY_CURSOR. I find it a little bit odd to have a different behavior when executing a script of two simple SELECTs via "SQL*Plus" and Toad.
As I said, those are personal preferences.
The most important is to know how a tool works, in order to get the best out of it.

Do you think that the behavior of Toad could be easily changed, in some future version, in order to be able to SELECT from DBMS_XPLAN.DISPLAY_CURSOR to get the plan of the last executed statement and not the extra "begin :Result := SYS_CONTEXT ('USERENV', 'CURRENT_SCHEMA');end;" ?

Have a nice week and thank you for your time.