Toad consistently crashes when executing an Explain Plan, making it difficult to use a core developer function.
When attempting to raise a support case, I am directed to a KB article suggesting I run Toad as an administrator. This is not a viable solution in our corporate environment, as security policies prevent users from running applications with elevated privileges.
The same activity works without issue in SQL Developer using the same database connection, suggesting this is specific to Toad.
Could this issue please be investigated and prioritised for a future release? Reliable Explain Plan functionality is an essential requirement for database developers.
Hi William,
I agree 100%, you should not have to run Toad as an administrator in order to do an explain plan. I don't believe this is an actual requirement, or I would have heard more reports of this problem.
Questions:
- Can you post a screen shot of the error?
- Does it happen with every explain plan, or just complicated ones? Please try with "select * from DUAL" and let me know.
- Can you provide a link to the KB that support gave you?
Update: I just created a standard user on my machine and ran Toad as non-admin and didn't have any problem with explaining a simple SQL like "Select * from dual"
Thanks
Thank you, John
It is difficult to capture a screenshot as Toad says it's not responding and then crashes—nothing to capture. The same issues occur when using SELECT FROM DUAL as well.
https://support.quest.com/toad-for-oracle/kb/4382570/clicking-in-the-explain-plan-in-the-sql-editor-crashes-toad
I've updated with the latest version and the issue is still ocurring.
thank you
Oh, by "crash" you mean the app disappears. That sounds like a stack overflow. hmmm.
Does it work if you:
- Don't enter any SQL
- Go to the explain plan tab in the editor
- Right-click in the explain plan area. Choose "Display Mode" and set it to DBMS_XPLAN
- Now try an explain plan
Also, I am curious is this is a problem in just one or multiple databases, and...if others in your organization have the same problem.
Thanks again for your response, John. Yes, the app shows a quick "not responding" message and then disappears. The issue occurred after following your steps.
The issue is occurring with others on my team and across all our schema instances and databases.
Thanks again
Hi William,
Thanks for that info. I have some things for you to check:
- I know this seems completely unrelated, but this will test if you are able to use MSXML6.dll as non-admin. Explain plan uses the same DLL. Go to Schema Browser, Select a few tables, hit F4. When the describe dialog comes up click the 2nd toolbar item "Save List of Described Objects". A dialog will appear. type something in and click OK.
- I know that it's not practical to run as administrator, and I'm not proposing this as a solution, but can you confirm if that actually solves the problem?
If #1 caused Toad to crash, there is no need to answer the following, but if not...
- Go to Options -> Oracle -> General, set the options like this if they are not already (You may use a different plan table name, but please make sure the checkboxes are as shown)
Then click the ellipsis button and let me know what happens. This process will make sure that Toad's settings point to a plan table that actually exists in the database, and that you can run "explain plan" SQL against it. It does not go through all the process that the Editor goes through to show an explain plan result, but it does run some of the process, so the result of this test will help me narrow things down.
- Please look at the file
%APPDATA%\Quest Software\Toad for Oracle\26.2\User Files\ExplainPlanColumnDefs.XML and make sure it has data. It should be about 13Kb in size and the first bit looks like this in notepad:
Thanks
-John
Hi John
Thank you again for your assistance.
I have followed all the steps, and it was successful through to the end. The resulting file contains similar information to what is shown in your screenshot.
Note that all of my settings were the same as in the screenshot.
regards
William
Hi William,
I can't tell from your reply above - can you confirm that running Toad as an administrator prevents the crash?
Also, do you have any security software (Defender, or other AntiVirus software, etc) on your PC that could either be temporarily disabled, or that Toad.exe could be whitelisted with? AI suggested to me that maybe such software is interfering with Toad, but not when you run Toad as an administrator. If that's the case, then whitelisting could solve this.
If that doesn't solve anything, please check these two things for me:
1) Loading the plan from XML
- Earlier I had you change the display mode to DBMS_XPLAN. Change it back to Tree.
- Save this to an XML File (it's just the explain data for "Select * from dual"):
<ExplainPlan>
<PlanElement id="0" operation="SELECT STATEMENT" optimizer="ALL_ROWS" cost="2" cardinality="1" bytes="2" cpu_cost="7,271" io_cost="2" time="1">
<PlanElements>
<PlanElement object_ID="0" id="1" operation="TABLE ACCESS" option="FULL" optimizer="ANALYZED" object_owner="SYS" object_name="DUAL" object_type="TABLE" object_instance="1" cost="2" cardinality="1" bytes="2" cpu_cost="7,271" io_cost="2" qblock_name="SEL$1" time="1"/>
</PlanElements>
</PlanElement>
</ExplainPlan>
- Right-click in the explain plan area and choose "Load plan from XML" and select the file.
Does it work or crash?
2) Explain Plan in the Session Browser
- Open Session Browser
- Select Current Statement on the bottom, and "Statement" under that
- Find your own session (it'll be in green) or any session that has a SELECT as the current statement
- Now click from "Statement" to "Explain Plan"
Does it work or crash?
Apologies, John. Previously, when I requested elevated privileges, I used explain plan without issue, including in earlier versions of Toad. I need EP to update Toad to the latest version in our environment.
Our company has whitelisted Toad, but I will confirm this.
With Point 1) Attempted to "Load plan from XML", and it loads, then Toad crashes (Disappears)
With Point 2) Attempted steps, it loaded, and then Toad crashed.
Along with that, we ran ProcMon from SysInternals and limited it to toad.exe to see what it does right before/as it crashes. After running the explain plan, it seems to hang on C:\Windows\System32\UIAutomationCore.dll. This may or may not be the root cause, but we were able to replicate this with multiple users.
An error occurred "FILE LOCKED WITH ONLY READERS"
Thank you for your persistence
Willliam
Hi William
That’s good info, thanks
With Toad shut down, edit the file Toad.ini with notepad. Add these lines under [SETTINGS]
dxUIAutomationEnabled=0
VSTUIAccessibilityEnabled=0
Toad.ini is in the folder %APPDATA%\Quest Software\Toad for Oracle\<version number>\User Files\
That will disable the UIAutomation code and hopefully solve this.
Please let me know if that helps.
Thank you, John
This appears to have solved the issue.
I will test with my team and confirm tomorrow.
Thank you so much for your support
William
Hi John, I can confirm this is working for otherts as well.
Thank you again
That's great news! Thanks for your help with procmon.
I’ll inform support and get that KB updated, and make it easier to turn that code off in Toad
If I can ever figure out how to reproduce it I should be able to fix it too. Now I know where to look
-John
