Toad World® Forums

cannot generate Explain Plan from Editor


#1

Ctrl-E and Explain Plan button do nothing in the beta - work fine in 12.5

Once I execute a statement, it will show me the execution plan


#2

I cannot reproduce. I tried threaded queries on and off and all 3 session options for Explain Plan on the Oracle|Transactions options page. Looking at the source there’s little that will prevent it from executing. Do you have multiple statement in the Editor?
If so does it work if you isolate your SQL to a new tab on its own? Are you selecting the statement or just placing caret within and CTRL+E? If not selecting, try that. Is the Explain Plan panel open? It’s not required, but if it’s not open it first. Any difference?

Michael

On 07/29/2014 02:55 PM, droeschley_109 wrote:

cannot generate Explain Plan from Editor

Thread created by droeschley_109
Ctrl-E and Explain Plan button do nothing in the beta - work fine in 12.5

Once I execute a statement, it will show me the execution plan

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.


#3

Editor has only 1 statement - SELECT * FROM dual;

Tried selecting statement - still nothing.

This is the Spool SQL output:


Session: USER1@TOSS2

Timestamp: 14:13:20.575

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[18],IN/OUT)=‘select * from dual’

:outHash(FLOAT,OUT)=


Session: USER1@TOSS2

Timestamp: 14:13:20.579

Select *

from v$sql_plan

Where hash_value = :sqlhv

and child_number =:cn

order by id

:sqlhv(VARCHAR[9],IN/OUT)=‘942515969’

:cn(INTEGER,IN/OUT)=0

I executed the above SQL with the values displayed and get no rows. Database versions are 11.2.0.2 and 11.2.0.3.9


#4

I do have the Explain Plan panel displayed.


#5

I’ve been experiencing this too. I think when it finds a syntactical error it isn’t displaying it, the plan or the error, but I’ve also experienced this with
valid SQL. As he/she said, once I execute it or execute and cancel I can get the plan.

From: Michael Staszewski [mailto:bounce-mstaszew@toadworld.com]

Sent: Tuesday, July 29, 2014 2:12 PM

To: toadoraclebeta@toadworld.com

Subject: Re: [Toad for Oracle - Beta Discussion Forum] cannot generate Explain Plan from Editor

Re:
cannot generate Explain Plan from Editor

Reply by Michael Staszewski

I cannot reproduce. I tried threaded queries on and off and all 3 session options for Explain Plan on the Oracle|Transactions options page. Looking
at the source there’s little that will prevent it from executing. Do you have multiple statement in the Editor? If so does it work if you isolate your SQL to a new tab on its own? Are you selecting the statement or just placing caret within and CTRL+E? If
not selecting, try that. Is the Explain Plan panel open? It’s not required, but if it’s not open it first. Any difference?

Michael

On 07/29/2014 02:55 PM, droeschley_109 wrote:

cannot
generate Explain Plan from Editor

Thread created by droeschley_109

Ctrl-E and Explain Plan button do nothing in the beta - work fine in 12.5

Once I execute a statement, it will show me the execution plan

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.

To reply, please reply-all to this email.

Stop
receiving emails
on this subject.

Or
Unsubscribe from Toad for Oracle - Beta
notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag
this post as spam/abuse.


#6

This is the spooled output from 12.5 doing similar thing:


Session: DARDBA@DEVTOSS1

Timestamp: 14:27:58.936

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[18],IN/OUT)=‘select * from dual’

:outHash(FLOAT,OUT)=


Session: DARDBA@DEVTOSS1

Timestamp: 14:27:58.941

Select *

from v$sql_plan

Where hash_value = :sqlhv

and child_number =:cn

order by id

:sqlhv(VARCHAR[9],IN/OUT)=‘942515969’

:cn(INTEGER,IN/OUT)=0


Session: DARDBA@DEVTOSS1

Timestamp: 14:27:58.953

explain plan set statement_id=‘roeschley:072914142758’ into PLAN_TABLE For select * from dual


Session: DARDBA@DEVTOSS1

Timestamp: 14:27:58.962

Select *

From PLAN_TABLE

Where statement_id = :STATEMENT_ID

order by id

:STATEMENT_ID(VARCHAR[22],IN/OUT)=‘roeschley:072914142758’


#7

I can reproduce this. It seems to happen when “Load cached plan if possible” is checked, and there is no cached plan.

Possible workarounds are either uncheck that option, or run the query and then re-explain if it comes back empty.

I’m stopping for the day but I’ll fix it tomorrow.


#8

OK, it’s fixed for next beta.