My query runs faster in SQL developer or TOAD?

As a DBA, I have heard this numerous times from my users that my SQL query runs faster in SQL developer or TOAD but it takes a long time when the same SQL query is running in the database or through an application.

There is a very simple explanation for this anomaly. These tools (SQL developer/TOAD) by default run for FIRST_ROWS. This means the goal of these tools is to simply return the first few rows as quickly as possible. Hence, these tools run with an execution plan that returns the first few rows quickly rather than all the data that the query satisfies. However, when the same SQL runs through the application, the goal is to show the complete result data which might be running with a different execution plan.

In order to get the realistic execution time, you should consider increasing the SQL Array Fetch size. Click on Tools-> Preferences-> Database->Advanced Parameters-> SQL Array Fetch Size.
Increase the array size to 1million. Now, when you run your SQL, it should take the same time as the application is taking.

Have a question? Please leave a comment and I would be glad to answer.