i run explain plan once fine in one connection. if i try to run it again for the current sql or any subsequent sql, i don’t get any results. the only way i can get it to work again is by creating another database connection. has anybody seen this?
I have had this issue, also. It is really annoying. I have to run the query in order for it to recognize that it has changed and should update the explain. I often want to see the explain before I run the query so that I can tell if it is going to be really slow.
Am I missing something obvious? Please help. Thanks.
You do not need to run the query to see the explain plan. You press the explain button and toad gets the explain from oracle. But an Oracle fact – Oracle guestimates
the explain plan. The only way to see the actual plan is to run it and then use SQL ID to fetch back the actual executed plan. See this blog for more details – some of which I copied below:
Cached Plan Support for Queries Ran in Editor
Now when you ask for an Explain in the editor, Toad will load the cached plan if possible. There is a new mouse
right click option: “Load cached plan if possible”. When checked, Toad will use dbms_utility.get_sql_hash to determine the hash value for your query, then attempt to locate a cached plan for that sql statement. If found, that plan will be displayed. If not
found, an ‘explain plan’ statement will be issued and the result of that will be displayed.
See the Actual vs the Theoretical Plan for your Executed Queries
From: fportilla [mailto:bounce-fportilla@toadworld.com]
Sent: Friday, September 06, 2013 10:46 AM
Subject: RE: [Toad for Oracle - Discussion Forum] 11.0.0.116 explain plan flaky
RE: 11.0.0.116 explain plan flaky
Reply by fportilla
I have had this issue, also. It is really annoying. I have to run the query in order for it to recognize that it has changed and should update the explain. I often want to see the explain
before I run the query so that I can tell if it is going to be really slow.
Am I missing something obvious? Please help. Thanks.
To reply, please reply-all to this email.
Stop receiving emails on this subject.
Or
Unsubscribe from Toad for Oracle - General notifications altogether.
Toad for Oracle - Discussion Forum
Flag
this post as spam/abuse.
