Is there any particular reason why Toad for D.A. executes SQL statements against our Oracle database slower than other SQL applications (particularly SQL Tools) on the same machine against the same server?
One thing we’ve noticed is that a simple, one-table select that should return well under 1k rows will take minutes longer in Toad than in SQL Tools.
This is partially slowing the adoption of Toad in my Department, despite it’s other advantages.
How are you connecting to Oracle? ODBC, OCI Client, Direct Connect?
Also, please provide example of SQL and time differences. Screenshots are fine.
I believe, based on the settings in the screenshots, that it's setup as a Direct connection.
The first screenshot is the settings in SQL Tools; the second is from Toad.
(note - I had to blur some of the information due to my company's Information Security policies)
I believe, based on the settings in the screenshots, that it's setup as a Direct connection.
The first screenshot is the settings in SQL Tools; the second is from Toad.
(note - I had to blur some of the information due to my company's Information Security policies)
Specific query example:
SELECT acctcorp,
house,
complex,
dwell,
stnum,
name,
apt,
aptn
FROM idst_house
WHERE acctcorp = 99999 AND complex IN (‘XX999’, ‘XX998’)
ORDER BY complex, stnum, aptn
This is a single-table select. In SQL Tools, the select (based on what SQL Tools reported) took approximately 141s (however, this was run during the day when load is high) and returned 203 rows.
In Toad for DA, the select took 4m57s last night.
I mention that because if I run the select in Toad for DA after I’ve run it in SQL Tools, it returns much faster - I’m thinking that there is some caching done on the Oracle server (my group does not admin the server, nor can we really ask questions of those who do - it’s one of those kinds of organizations).
I would expect the performance to be the same between SQL Tools and Toad, but it’s not.
First of all, Oracle does cache explain plans and will look for and use explain plans that go with your query. If not cached it will take longer but should not be double the time. To exclude this issue only compare the execution from each app on the second time you execute it.
Next, try changing your connection in TDA to a direct connection. in the connection window choose the Direct tab in the middle of the properties page. Fill in server and sid.
Using the two itmes above, do the comparison again and see what the results are. This will be as close as we can get comapring apples to apples.
Debbie
Debbie Peabody wrote:
First of all, Oracle does cache explain plans and will look for and use explain plans that go with your query. If not cached it will take longer but should not be double the time. To exclude this issue only compare the execution from each app on the second time you execute it.
Next, try changing your connection in TDA to a direct connection. in the connection window choose the Direct tab in the middle of the properties page. Fill in server and sid.
Using the two itmes above, do the comparison again and see what the results are. This will be as close as we can get comapring apples to apples.
Debbie
Debbie,
TDA is set to Direct - in the screenshot the Direct tab is selected and showing my settings.
The best I can do to try this again is to run this tonight when load should not be an issue, and put some time between when I run the queries - I think the explain plans are only cached so long as the user is still logged in. Though what I find odd is that when I attempt to have Oracle execute an explain plan in TDA, it tells me I have insufficent privileges, though I realize this may be different.
