Gathering information to tune oracle row prefetching

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.

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.

StackExchange Database Administrators Q#5814, answer score: 2