-
Title
Toad performance issue when running certain queries -
Description
User runs a select statement in the Toad Editor on an Oracle data dictionary. This statement takes a long time to return and may seem to hang Toad while it is running. User does not have this problem querying other tables in the same database.
-
Cause
The data set maybe too large for the network to handle. Try the workaround below.
-
Resolution
WORKAROUND 1:
1. In Toad, go to View | Toad Options | Oracle | General. Locate the value for OCI Array Buffer Size.
2. Try decreasing the values so that the packets transfered are smaller but more frequent trips across the network.
3. Click Apply and OK and restart Toad. Retest the issue.
4. If that does not work, try increasing the OCI value so that the packets are larger but take less trips across the network.
5. Click Apply and OK and restart Toad. Retest the issue.
WORKAROUND 2:
Contact your DBA and request that statistics be gathered on the database.
WORKAROUND 3:
1. In Toad, go to View | Toad Options | Oracle | Optimization.
2. Locate the "All Others" drop down menu. Try selecting the RULE hint.
3. Click Apply and OK and restart Toad. Retest the issue.
4. If that does work, try a different optimization hint for the "All Others."