Toad World® Forums

Include actual execution plan is not visible in EDITOR tab


#1

Hi ,

Toad version : 12.8.0.49
edition : Toad development tool for oracle

I am trying to generate actual execution plan for oracle sql , but i can only view estimated plan not the actual execution plan , i tried to enable “include actual execution plan” using EDITOR tab , but it is not visible ,

please guide to how to get actual execution ?

Thanks


#2

Right-click in the explain plan dialog. Check “Use cached plan if possible”.
If the query has not been run (and thus not in Oracle’s cache), run the query.
Then do a CTRL+E to do the explain plan.
Above the plan tree, it will say either “New Explain Plan” or “Cached Explain Plan” so you can tell if it is a cached plan or not.


#3

I could see only “LOAD CACHED PLAN IF POSSIBLE” , i enable load cached plan if possible and ran query again and generated explain plan using CTRL+E , but still did not see actual execution plan.


#4

Do you have the SELECT privilege on V$SQL?


#5

yes , i can able to query table


#6

Oh, sorry, check privs on v$sql_plan for Toad 12.8. We also use dbms_utility, but that’s granted to public so you should have that.


#7

Yes , I do have access to above table


#8

That should be all you need. Try turning on spool SQL. (Main Menu: Database -> Spool SQL -> Spool to screen). That will show you what Toad is doing. You should see how Toad gets a hash of your query, then tries to find that has in v$SQL_PLAN. Maybe it isn’t found in v$SQL_PLAN for some reason.


#9

spool SQL screen shows empty , nothing in it.

what are various ways to get actual execution plan in TOAD ?


#10

So, you turn on spool sql, then do a CTRL+E to get an explain plan, and the plan shows up nothing shows up in the spool? I don’t understand how that can happen.

what are various ways to get actual execution plan in TOAD ?

This is the only way. Usually it works. I am not sure why you are having problems.

Your best bet to get this resolved is probably to open a support case. Sorry I am out of ideas.


#11

I just copied plan hash value from explain plan screen and filtered in v$sql_plan view and found some records , i guess this is the actual execution plan , am i correct ?

does v$sql_plan always stores actual execution plan ?


#12

sorry , my mistake , I turn on sql spool , then did CTRL+E ,

it giving

Session: database
Timestamp: 10:03:18.907
Select *
from v$sql_plan
Where hash_value = :sqlhv
and child_number =:cn
order by id
:sqlhv(VARCHAR[10],IN/OUT)=‘2289551384’
:cn(INTEGER,IN/OUT)=0


Session: database
Timestamp: 10:03:19.000
select * from table(dbms_xplan.display_cursor(sql_id => ‘0142yc247gk0s’, cursor_child_no => null, format => ‘TYPICAL, ALLSTATS’))


#13

Well, then it’s showing you the plan from v$sql_plan. If it weren’t you’d see a statement like “explain plan set statement_id = ‘blah’ into TOAD_PLAN_TABLE for ‘’”


#14

This is the script it showing up in sql spool


Session: database1
Timestamp: 10:03:18.902
declare
v_ignore raw(100);
v_oldhash number;
v_hash number;
begin
v_hash := dbms_utility.get_sql_hash(:SQLText || chr(0), v_ignore, v_oldhash);
:outHash := v_hash;
end;
:SQLText(VARCHAR[2679],IN/OUT)=‘select query’


Session: database1
Timestamp: 10:03:18.907
Select *
from v$sql_plan
Where hash_value = :sqlhv
and child_number =:cn
order by id
:sqlhv(VARCHAR[10],IN/OUT)=‘2289551384’
:cn(INTEGER,IN/OUT)=0


Session: database1
Timestamp: 10:03:19.000
select * from table(dbms_xplan.display_cursor(sql_id => ‘0142yc247gk0s’, cursor_child_no => null, format => ‘TYPICAL, ALLSTATS’))


#15

Ok, so it appears that you are getting the actual plan. Why does it seem that it is not?


#16

I am sorry if i am confusing you.

Is that above is actual execution plan ? it is something like showing up list of steps how query executed right ?

You mean i have to query above v$sql_plan to get actual plan ?

please advice


#17

You do not have to query v$sql_plan manually.

Whatever it is showing you in the explain plan output is the actual explain plan. I can tell because of the spool sql ouput.


#18

okay got it.

once we enable sql spool and run CTRL+E , plan showing up in explain plan is the actual execution plan , am i correct ?

if sql spool turn off and plan in explain plan is not actual ?
image

above plan , E – indicates estimated ,but its a actual plan ,how it possible ?


#19

Not exactly. Enabling Spool SQL has nothing to do with Explain Plan. That is just a tool to see what Toad is doing in the background.

To get the actual plan, all you have to do is: 1) make sure that Toad’s “Used Cached Plan if possible” menu item is checked, 2) run the query if it hasn’t been run recently, 3) CTRL+E.

E doesn’t mean that the whole plan is an estimate of what Oracle would do if it ran the query. It means that Oracle estimated the # of rows, the number of bytes, etc, when it executed the query.


#20

can we distinguish actual plan and estimated plan in toad ?

when i run query , then ran crtl+E (this its a actual execution plan used ) ?