Gathering information to tune oracle row prefetching

Question

I'm trying to determine a value for the number of rows an oracle client will prefetch (i.e. OCI_ATTR_PREFETCH_ROWS, exposed as setFetchSize in JDBC) that will minimize the number of server round trips without excessively increasing memory usage.

To do this, I need to gather information on queries either from the application or from the database. Can oracle provide disaggregated historical information about

• how many rows were in the results of queries
• how much data was returned for queries

Here is an example of the type of report I'm interested in generating:

On the instance named foo, 20,000 queries were executed on Monday.

• 60% of the queries returned fewer than 100 rows, 20% returned between 100 and 1000 rows, and 20% returned between 1000 and 10,000 rows.

• 50% of the queries returned less than 1KB of data, 30% returned between 1KB and 10KB, and 20% returned between 100KB and 1,000KB

If the database server cannot provide this information, but you have ideas for other ways I could gather it, feel free to answer with those.

Answer 1

This is an interesting question and differs significantly from the approach I have used. The kind of information you are looking for would not be of much interest to me. I would be more concerned with the number of rows displayed (or processed) for each query. The place to capture that is likely in your application(s).

You may be able to gather most of what you are looking by enabling auditing for your queries and analyzing the audit data.

For batch retrieval which will use the full result set, I generally set a relatively large pre-fetch value (100 or so). Tuning might be of value in some cases, but I would limit it to frequently run queries with large result sets where database fetch is a significant portion of the overhead.

For interactive applications I usually tune prefetch limits towards the number of items displayed on a page. There isn't much value in prefetching data for the next page unless the users actually page to it. It has been my experience, that users don't page down that often. Anything past the second page would be overkill for the initial prefetch.

EDIT: Based on your comment, setting prefetch size to one or two screens of data may decrease latency on the link. Fetching unused data will increase the load on the link. Setting the link to prefetch data should also reduce the load on the link.

Answer 2

Note that prefetching the entire result set may not be the most efficient approach. For example, when I tested with a 5000-row result set, I found a 500-row prefetch to be fastest. I recommend putting together a test harness and running some scenarios based on real application SQL.

Answer 3

the purpose of prefetching is to fill your network packets and reduce roundtrips.

how many rows fit in your network packet depends on the MSS (for MTU 1500 around 1460), and obviously the size of a returned row.

a better question would be:

what is the average packet size leaving my Oracle DB?

the ratio SQL*Net bytes sent to client to SQL*Net roundtrips to/from client will tell you this.

You could try and tune this per SQL, which is what i do for the SQL where it matters. In fact most software i write using OCI will 'learn and adjust' the fetch size automatically.

however, note that using 1.1 packets/exec is better than 0.1 packets/exec, but still inefficient. a more practical approach is to make sure each fetch fill enough packets to make the last not-so-filled packet insignificant with respect to the total number of packets sent.

many clients simply pick a generic value of say 10 rows (as JDCB does i think) which makes it pretty unlikely the packet use is -very- inefficient for typical row sizes.