As the title implies, I have a very simple Oracle query that is returning in 5 seconds when I go beyond returning 251 results. I am using SQL Developer, and attaching using the built in connection utility (there is no facility for an ODBC connection in this application).
The query found here is fast (fast enough) (pa_stu holds roughly 40k rows):
Select * From pa_stu Where rownum < 252;
Oracle returns the data to me in .521 second, according to SQL Developer.
The following query, and ones that pull larger sets of data, are the culprit:
Select * From pa_stu Where rownum < 253;
Oracle returns the data to me for that last one in 5.327 second, according to SQL Developer.
All queries being used for testing have the same explain plan. That is, the filter predicate of ROWNUM<251 (change the 251 to whatever number is being used) and a TABLE ACCESS of FULL.
The results above are consistent, plus, bumping up the evaluated number to about 1000 doubles the result time to roughly 10 seconds (consistently). It is as if some buffering is going on somewhere, and that buffer is too small. Additionally, this is happening on only one of our Oracle servers. The other, more highly used one (holds different data as well) has no problem returning 100's of thousands of records using similar statements.
The databases are controlled by a DBA, and, I have run all of this by her. She does not have a solution. This actually started happening a month or so back, and was not the case many months ago, if that is meaningful. It was just not as noticeable as it is now.
Thank you for any help.
The following recommendations will help you in your SQL tuning process.
You'll find 3 sections below:
CREATE INDEX pa_stu_idx_rownum ON pa_stu (rownum);
SELECT * FROM pa_stu WHERE pa_stu.rownum < 252