Every now and again I get a developer asking why their query in PL/SQL takes so long to process when their GUI application returns the data so quickly. When asked to demonstrate the problem they provide a query, run it in their GUI of choice, see the first set of results appear and say “there, that was quick”.
What is misunderstood in this situation is that Oracle may not need to resolve the entire result set of a query in order to start returning rows back to the client application. The analogy I tend to use is that of a search engine. You obtain the initial results from a search engine very quickly and generally you will only look at one or two pages of results before trying to refine the search terms. The search engine provides the results back to you in an “on-demand” basis.
Contrast this situation to the processing of query results in some sort of PL/SQL batch process. In this scenario you are going through every single entry returned by the query, performing some action on it. Obtaining just the first few results is pretty much meaningless as the process is not complete until it has trawled through all the results… and you will just have to wait for that to happen.
We can use a simple SQL trick to limit the number of rows returned by a query to illustrate the speed and resource cost in retrieving a subset of rows from a query. First, we create a table with a 1,000,000 rows:
CREATE TABLE fetch_test
(id NUMBER(10) NOT NULL
,cat NUMBER(2) NOT NULL
,padding CHAR(30) NOT NULL)
/
INSERT INTO fetch_test
SELECT ROWNUM
, MOD(ROWNUM,10)
, 'X'
FROM dual
CONNECT BY ROWNUM <= 1000000
/
COMMIT
/
CREATE INDEX fetch_test_ix1
ON fetch_test (cat)
/
EXEC dbms_stats.gather_table_stats ('','fetch_test')
Next we simply run a query that returns all the rows for a specific category value (5) and another version where we get the first 20 entries back to simulate the fetching done by a GUI application. In both situations we time the response and also track the execution statistics via AUTOTRACE:
SQL>SET ARRAYSIZE 500
SQL>SET AUTOTRACE TRACEONLY
SQL>SET TIMING ON
SQL>SELECT /*+ index (f (cat)) */
2 *
3 FROM fetch_test f
4 WHERE cat = 5
5 /
100000 rows selected.
Elapsed: 00:00:00.31
Execution Plan
----------------------------------------------------------
Plan hash value: 1955820726
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100K| 3816K| 6274 (1)| 00:01:16 |
| 1 | TABLE ACCESS BY INDEX ROWID| FETCH_TEST | 100K| 3816K| 6274 (1)| 00:01:16 |
|* 2 | INDEX RANGE SCAN | FETCH_TEST_IX1 | 100K| | 198 (1)| 00:00:03 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CAT"=5)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
6645 consistent gets
0 physical reads
0 redo size
4436704 bytes sent via SQL*Net to client
2688 bytes received via SQL*Net from client
201 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100000 rows processed
SQL>SELECT *
2 FROM (SELECT /*+ index (f (cat)) */
3 *
4 FROM fetch_test f
5 WHERE cat = 5)
6 WHERE ROWNUM <= 20
7 /
20 rows selected.
Elapsed: 00:00:00.00
Execution Plan
----------------------------------------------------------
Plan hash value: 3438997906
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 20 | 780 | 5 (0)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| FETCH_TEST | 21 | 819 | 5 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | FETCH_TEST_IX1 | 100K| | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=20)
3 - access("CAT"=5)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
1550 bytes sent via SQL*Net to client
499 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
SQL>SET TIMING OFF
SQL>SET AUTOTRACE OFF
It took 0.31 seconds to retrieve all 10,000 results for the query whereas obtaining the first 20 of those entries was virtually instantaneous. A look at the consistent gets statistics reveals the reason behind the difference in time. Only 7 consistent gets was required to obtain the first 20 results and 6,645 were required to obtain all results. When retrieving the first 20 entries, Oracle simply stopped retrieving any more data once it had the required 20 rows.
This example shows us that in order to return the first “batch” of results from our query Oracle only performed a small amount of the effort required to process the entire result set. This is why queries run in GUI applications might seem to be faster than when run in some sort of PL/SQL batch mode. Furthermore, I hope it also illustrates that you cannot gauge the performance of a query simply by how long it takes for results to appear in a GUI.
In a GUI situation Oracle would be holding an open cursor, ready to fetch the next batch of rows. If you encounter this situation then my suggestion is to page all the way to the end of the result set and see if it really is as fast as you think. You’ll notice that as you page down the query results the GUI pauses every now and again. This is where it’s fetching more results back from the database and Oracle is having to actually figure out what those results are, i.e. continue to generate them as they are requested. Once you get to the end of the result set you’ll find that it doesn’t really differ much from the batch process that had to process all the results.
So how many rows are returned with each fetch operation a GUI does (or any client for that matter)? This is controlled by the client application itself. If you’re using a programming API you’ll find a property to control this, such as the fetch size in JDBC and ODP.Net. Below are are some details for controlling the fetch size in SQL*Plus, Quest TOAD and Oracle SQL Developer. With modern servers and networks, a reasonable setting would probably be between 100 to 500.
Quest TOAD
The fetch size in Quest TOAD is configured in the TOAD options by selecting the Oracle | General category in the left-hand options menu. The setting is named “OCI Array Buffer size” and defaults to 500.
Oracle SQL Developer
Control of the rows fetched in Oracle’s SQL Developer is done in the Preferences (found in the Tools menu), under the Database | Advanced category.
SQL*Plus
The number of rows fetched at a time in SQL*Plus is determined via the ARRAYSIZE parameter. By default 15 rows are fetched at a time but this can be changed by issuing the command:
SET ARRAYSIZE n
Where ‘n’ is a value from 1 to 5000. Keep in mind that the default value can be overridden in the glogin.sql or login.sql configuration files.
Mark Hoxey 