Many FETCH calls with few rows

Each FETCH is a call from the application to the database. With few rows per FETCH, the application pays a round trip for every handful of rows to read what would fit in far fewer calls.

Symptoms

In the Diagnosis, the finding Many FETCH calls with few rows (FETCH_PEQUENO) appears. The text says how many FETCH calls there were and how many rows each one returned on average, for example 5,000 FETCH calls returned 1.0 rows each, on average, suggests increasing the client array size (fetch size) and ends with View call types.

The finding counts only level-0 FETCH calls, the ones the application requested: it appears when they are many and return, on average, few rows each. FETCH calls of recursive SQL, such as those of PL/SQL, are not counted. The severity is always medium, and the percentage next to it is the duration of these FETCH calls over the total time.

Cause

The number of rows per FETCH is the client array size (or fetch size). With an array size of 1, reading 10,000 rows costs 10,000 FETCH calls. In general, the larger cost is not in the FETCH itself but in the SQL*Net message from client wait between one FETCH and the next, which the percentage of the finding does not include. Two situations lead to the finding:

• Cursor read in small batches: few EXEC and many FETCH calls. The client array size is low.
• Many single-row queries: the number of FETCH calls is close to the number of EXEC calls, and each execution returns a single row. Increasing the array size does not help: the application runs one query per item.

Resolution

1. Tell the two situations apart. In SQL statements, compare Exec, Fetch and Rows of the statements with Depth 0.
2. If there are few EXEC and many FETCH calls, increase the array size in the client. In SQL*Plus:

   set arraysize 500

   In JDBC, per statement:

   stmt.setFetchSize(500);

   In python-oracledb:

   cursor.arraysize = 500

3. Test a few values: the gain is large in the first increases and then levels off, and very high values use memory in the client.
4. If FETCH and EXEC are similar, replace the single-row queries with one query that brings everything at once, with a join or with the list of values.
5. For DML, the equivalent is sending the rows in batches: array DML, or addBatch and executeBatch in JDBC.

How to confirm

Generate a new trace of the same operation after the change. For the statement, Fetch should drop and Rows stay the same; in the profile, the Occurrences of SQL*Net message from client drop as well, and the Total time with them. The finding disappears when the application FETCH calls start returning many rows each.

In the report

The sample report has only 2 FETCH calls, without this finding. The links open the sections of the example:

• Diagnosis: the finding and the View call types link.
• Call types: FETCH row, Count and Rows. This table adds up all depths, including recursive SQL, and the finding uses only level 0: in the example, one of the 2 FETCH calls belongs to the recursive statement #2.
• SQL statements by contribution: Depth, Exec, Fetch and Rows of each statement.
• Profile, SQL*Net message from client row: the round trips between the FETCH calls.
• FETCH latency distribution: the duration of each FETCH call.

This finding often appears together with the one for the session waiting for the application.