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
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.
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.
Do you have the SELECT privilege on V$SQL?
yes , i can able to query table
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.
Yes , I do have access to above table
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.
spool SQL screen shows empty , nothing in it.
what are various ways to get actual execution plan in TOAD ?
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.
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 ?
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’))
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 ‘’”
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’))
Ok, so it appears that you are getting the actual plan. Why does it seem that it is not?
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
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.
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 ?
above plan , E – indicates estimated ,but its a actual plan ,how it possible ?
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.
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 ) ?
