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.