My Oracle Support Banner

Things to Consider When Calculating OCI Prefetch Count. (Doc ID 185738.1)

Last updated on DECEMBER 26, 2019

Applies to:

Oracle Database - Standard Edition - Version 8.0.3.0 and later
Oracle Database - Enterprise Edition - Version 8.0.3.0 and later
Oracle Database - Personal Edition - Version 8.0.3.0 and later
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
Information in this document applies to any platform.
***Checked for relevance on 31-Aug-2012***

Purpose

ORACLE OCI8 & PreFetching

CONTENTS:

1. How does one calculate the total number of round trips it takes to fetch all the data?
2. If the application set the prefetch to 100+ instead of the default (10), would it slow down the queries that only return 1 row?
3. If the application set the prefetch to 100+ instead of the default (10), would there be more memory used on the app server?
4. If the application set the prefetch to 100+ instead of the default (10), would there be more memory used on the database server (dedicated)?
5. If the application set the prefetch to 100+ instead of the default (10), would it actually decrease the CPU time for large resultsets down?
6. Is there any Rule of Thumb out there for the optimal number of records to fetch at a time?
7. Is there any advantage to prefetching as opposed to using arrays?

Questions and Answers

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Purpose
Questions and Answers

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.