Query Performance: Fetch Size
When a query is executed, Oracle initially performs a parse of the code, then executes it, and finally fetches the data to deliver it to the user.
The fetch phase in a SQL statement execution refers to the step where rows are retrieved from the query's result set, after the execution phase has been completed. Here's a summary:
- The fetch phase retrieves the rows identified by the execution phase.
- It's part of the client-server communication process where the database server sends data to the client application.
- After the parse (syntax and semantics validation) and execute (execution of the SQL statement), the fetch phase begins.
Here are some steps that may affect the performance of the fetch phase:
- Row Count: The number of rows fetched depends on the query and how many rows match the criteria.
- Network Impact: In client-server environments, fetching data across the network can introduce latency.
- Performance: Excessive fetches or fetching too many rows can lead to performance bottlenecks, especially in queries returning large datasets.
Fetch sizes are always configured in the client side, there are no configuration for the system side, so you can do the troubleshooting and get all the evidences, but the fix must occur in the client side, e.g. Applications, Batch jobs, etc.
The Oracle execution statistics often display elapsed time, CPU time, disk I/O, and rows fetched during the fetch phase.
Optimizing the fetch phase often involves tuning SQL queries to minimize unnecessary rows and improve execution efficiency.
The fetch phase is crucial in SQL execution, especially in queries that return significant amounts of data, as it directly impacts the application's response time.
To measure how much time your session/query is spending with the fetch phase, you can refer to my how-to trace a session article: Tracing Database Session
In the trace file, you will see something like this (this is from a real case from a client):
call count cpu elapsed disk query current rows
---------------------------------------------------------------------------------
Parse 132 0.00 0.00 0 0 0 0
Execute 132 0.00 0.00 0 0 1 0
Fetch 132 670.92 671.84 0 271687749 0 132
---------------------------------------------------------------------------------
total 396 670.92 671.85 0 271687749 1 132
Overall Totals for All Recursive Statements
call count cpu elapsed disk query current rows
---------------------------------------------------------------------------------
Parse 7032 0.15 0.15 0 0 14 0
Execute 7994 0.73 0.82 9 1665 9772 1061
Fetch 7086 671.45 672.31 13 271780560 135 6219
---------------------------------------------------------------------------------
total 22112 672.33 673.30 22 271782225 9921 7280
In database performance measures, time is what always matters. Just the number of Fetches doesn't say much, but if it's taking too much time (elapsed column), you will see that in my case, it's consuming almost 100% of the time.
Increasing the fetch size, which means it will get more data each time it does a fetch, reducing the number of fetches needed to finish the return of a query doesn't mean that it will help or fix the problem, but usually, it helps or even clears the bottleneck.
The only way to know the impact of increasing the fetch size is by testing. Run the same query multiple times, changing the fetch size on each one of them. Let's make some tests:
Test 1: Create user, table, and populate the table
[oracle@orcl-perf-lab ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 20 14:28:22 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 LAB READ WRITE NO
SQL> alter session set container=lab;
Session altered.
SQL> create user performance identified by "performance" default tablespace users;
User created.
SQL> grant connect, unlimited tablespace, create table to performance;
Grant succeeded.
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
[oracle@orcl-perf-lab ~]$ sqlplus performance/performance@lab
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Dec 20 14:52:59 2024
Version 19.22.0.0.0
Copyright (c) 1982, 2023, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.22.0.0.0
SQL> sho user
USER is "PERFORMANCE"
SQL> CREATE TABLE FETCH_TEST (COL_A VARCHAR2(20));
Table created.
SQL> BEGIN
FOR c IN 1..2000000 LOOP
INSERT INTO FETCH_TEST VALUES (
CASE ROUND(DBMS_RANDOM.VALUE(1, 5)) -- Randomly choose a word from the list
WHEN 1 THEN 'apple'
WHEN 2 THEN 'banana'
WHEN 3 THEN 'cherry'
WHEN 4 THEN 'date'
WHEN 5 THEN 'elderberry'
END
);
END LOOP;
END;
/
SQL> COMMIT;
Commit complete.
SQL> select count(*) from FETCH_TEST;
COUNT(*)
----------
2000000
Test 2: Create some SQL files to run, with different fetch sizes (10, 50, 1000, and 5000)
[oracle@orcl-perf-lab ~]$ cat fetch_10.sql
set arraysize 10
connect performance/performance@lab
select * from fetch_test;
exit
[oracle@orcl-perf-lab ~]$ cat fetch_50.sql
set arraysize 50
connect performance/performance@lab
select * from fetch_test;
exit
[oracle@orcl-perf-lab ~]$ cat fetch_1000.sql
set arraysize 1000
connect performance/performance@lab
select * from fetch_test;
exit
[oracle@orcl-perf-lab ~]$ cat fetch_5000.sql
set arraysize 5000
connect performance/performance@lab
select * from fetch_test;
exit
Test 4: Execute the select statement using the files previously created (I executed 3 times each)
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_10.sql >/dev/null
real 2m22.720s
user 0m5.569s
sys 0m41.306s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_10.sql >/dev/null
real 2m58.230s
user 0m8.888s
sys 0m53.055s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_10.sql >/dev/null
real 1m52.034s
user 0m5.614s
sys 0m32.776s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_50.sql >/dev/null
real 0m43.699s
user 0m3.617s
sys 0m13.086s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_50.sql >/dev/null
real 0m36.703s
user 0m4.923s
sys 0m8.438s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_50.sql >/dev/null
real 0m26.945s
user 0m3.812s
sys 0m7.872s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_1000.sql >/dev/null
real 0m9.062s
user 0m4.201s
sys 0m2.512s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_1000.sql >/dev/null
real 0m9.993s
user 0m5.132s
sys 0m2.086s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_1000.sql >/dev/null
real 0m13.184s
user 0m5.667s
sys 0m2.768s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_5000.sql >/dev/null
real 0m9.517s
user 0m4.993s
sys 0m1.994s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_5000.sql >/dev/null
real 0m9.493s
user 0m5.421s
sys 0m2.202s
[oracle@orcl-perf-lab ~]$ time sqlplus /nolog @fetch_5000.sql >/dev/null
real 0m6.479s
user 0m4.175s
sys 0m1.355s
Summarizing:
Filename Real_Time (s) User_Time (s) Sys_Time (s)
----------------------------------------------------------
fetch_10 144.33 6.69 42.38
fetch_50 35.78 4.12 9.80
fetch_1000 10.75 5.00 2.46
fetch_5000 8.50 4.86 1.85
- To adjust the fetch size in a PL/SQL script, simply add SET ARRAYSIZE 5000 before the query, replacing the numeric value with the desired one. The maximum size for this session parameter is 5000, but there are no better value, it will depend on your application/network.
- If using Java, you can adjust the fetch size with oracle.jdbc.OracleConnection.setDefaultRowPrefetch() on the connection or java.sql.Statement.setFetchSize() during query execution.
- If using PL/SQL, you can adjust the fetch size through bulk operations. In the example below, the LIMIT clause represents the array size:
DECLARE
CURSOR csr IS ...;
BEGIN
OPEN csr;
LOOP
FETCH csr BULK COLLECT INTO xxx LIMIT nnn;
EXIT WHEN csr%NOTFOUND;
END LOOP;
END;
And again! Increasing the array size is not directly proportional to time improvement, so the reduced number of fetches does not necessarily mean faster data retrieval for the client. Performance should always be measured in terms of time, not fetch count.
Comments
Post a Comment