Get "Unable to Establish Optimization Session" error when trying to use "Auto Optimize SQL" (4311223)

Get "Unable to Establish Optimization Session" error when trying to use "Auto Optimize SQL"

When right clicking a query in the Editor and choosing 'Optimize' | 'Auto Optimize SQL', after accepting the default settings and clicking 'OK', the following error is displayed:

"Unable to establish optimization session."

1) Using a 32-bit 10g client with direct connect on a 64-bit box.

2) PC has many multiple Oracle Clients installed (i.e. 10 clients).  It could be valid or invalid or left over client installations.

 

RESOLUTION 1:

(Cause 1)

Connect using TNS or LDAP instead of direct connect. Then try to run Auto Optimizer. 

RESOLUTION 2:

(Cause 1)

Uninstall Toad for Oracle and reinstall into a folder outside of C:\Program Files(x86) e.g. C:\Quest Software
See SOL12495: Error connecting to database after installing on Windows 64-Bit OS: "ORA-12154" for additional information.

Also, the issue can be resolved by uninstalling either just Toad for Oracle or all Quest applications and reinstalling.

RESOLUTION 3:

(Cause 2)

In the Toad login screen (disconnect all sessions first), check-mark the “Make this the TOAD default home” check-box, connect again, and then re-test the issue.

RESOLUTION 4:

(Cause 2)

Clean out your Oracle Client installations and only leave the one or two that you use.  Ensure the Oracle folders and registry locations are also cleaned out so it will not be detected by Toad.

Verify that there are no other error messages that come up along with this issue. The trouble may be related to the other error message more specifically, then the "unable to establish session" error. If prompted to create plan table before receiving the session error, it may be necessary to get the privileges for create table. Optimizer runs explain plan, and will attempt to create a temporary plan table for this. If there is a limitation in privileges to create table, Toad will prompt accordingly, noting the script for the plan table that is needed. This may need to be created manually for that database/user before optimizer is run, or the privileges need to be granted to the user.