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.

#4

Well, I agree that it would be nice for DBMS_XPLAN to work as you'd expect it to.

I might be able to move the SYS_CONTEXT so that it happens immediately before your selects instead of after. I'll take a look.

#5

That would be great!

#6

So, I've been playing with this at bit this morning and found some surprising results.

I temporarily disabled the "set current schema" code.

Then when I run
select * from scott.emp
followed by
select * from table(dbms_xplan.display_cursor);
...I get the expected result.

But if I do
select * from DBA_TABLES
followed by
select * from table(dbms_xplan.display_cursor);
...I don't get the xplan info for the DBA_TABLES query. I get info for whatever query I did before that.

It seems to be because I didn't fetch all of the rows from dba_tables, just the first 500. If I scroll down to the end of the grid before I do the XPLAN query, then it works as expected.

So it looks like even if I make Toad to not check the current_session when you do the xplan query, you're still going to get the plan for a different query when you don't fetch all of the rows. I'm not saying I won't make the change, but this is something to be aware of.

#7

Hi John,

I tested this in SQL*Plus as well, exactly the same thing if you don't let the query results display to the very end. So, it's looking like an Oracle "problem" as opposed to something wrong in Toad.

I also traced the execution of a Toad session that didn't let the output complete, and there is the various STAT lines at the end of the query, so, in theory, Oracle should be able to extract the appropriate data. It looks like it queries GV$SQL though, and if the SQL is no longer there, it also fails to output the plan.

HTH

Cheers,
Norm. [TeamT]

#8

@NormTeamT thanks for the info.

#9

note the EXPLAIN plan may not be the same as the EXECUTION plan. ctrl+E should only be used when starting off looking at a performance issue.

to get execution plan add a hint to your select and join the display_cursor to v$sql or gv$ in a RAC

select /* MYPLAN1 */ * from dba_tables

select plan_table_output
from gv$sql s,
table(dbms_xplan.display_cursor(s.sql_id, s.child_number, 'ALLSTATS LAST +OUTLINE')) t
where s.sql_text like '%MYPLAN1%'
and s.sql_text not like '%gv$sql%';

#10

CTRL+E uses the SQLID to find execution plan if you have the "Load cached plan if possible" option checked.

Update 12/9/2019: Fixed in Toad for Oracle 13.3 Beta