Reading from a disk of a computer is very slow compared to accessing main memory. In addition, it is common to read the same part of a disk several times during relatively short periods of time. For example, one might first read an e-mail message, then read the letter into an editor when replying to it, then make the mail program read it again when copying it to a folder. By reading data from disk only once and then keeping the data in memory until no longer needed, the computer can be programmed to speed up all but the first read. This is called disk buffering, and the memory used for the purpose is called the buffer cache.
A relational database (available from Oracle Corporation) maintains its own buffer cache inside a portion of the main memory (called “system global area” and abbreviated “SGA”) for each instance of the database. The database is typically installed in a computer (commonly called “server”) of the type available from Sun Corporation, running the operating system Solaris or Unix. A server is a mid-sized computer where all the data is actually kept on a disk. Such a server may be coupled to one or more clients. Clients are typically Personal Computers (or “PCs”) that pull data of the server in ways that are useful to the user (i.e., for display and interpretation).
A server typically executes a query supplied by a client, and in executing the query applies certain operators (as required by the query) to data in the database. When executing such queries, the server may be programmed to fetch data blocks ahead of time (in an operation called “prefetch”) from disk, and store the data blocks in the buffer cache. Such prefetching of data blocks of a database table (which are pointed to by rowids often provided by an index) allows the query to execute quickly, by taking advantage of parallel I/O capability of the server. The number of data blocks that are prefetched may depend on the type of query as described in a white paper entitled “Hardware Strategies to Improve Database Application Performance” available at www-seeksystems-com%products%oracle app whitepaper.pdf (wherein “.” and “/” in the hyperlink have been replaced with “-” and “%” respectively).
U.S. Pat. No. 5,822,749 granted to Agarwal (that is incorporated by reference herein in its entirety) states the “Two types of prefetch exist: “hard” prefetch and “soft” prefetch. In a soft prefetch, if the system cannot fetch or retrieve the requested size (e.g., because sufficient memory does not exist), it will fetch the size which best matches the request (given that the request itself cannot be satisfied exactly). In a hard prefetch, in contrast, the amount prefetched must satisfy that which was requested. If the amount requested is unavailable for some reason, the system will nevertheless wait until it can satisfy the hard prefetch request alternatively, a value (e.g., NULL) can be returned, indicating that the client is to handle the inability to perform a hard prefetch. For example, if much contention exists for a particular size which has been requested for a hard prefetch, the system will wait until that size is available (or return). A hard prefetch is typically employed in those instances where system operation is greatly improved by large buffer I/O, such as when needed for creating a database. A hard prefetch will generally not be employed within the main transaction path.” See also U.S. Pat. No. 5,812,996 (also incorporated by reference herein in its entirety).
We, the applicants, have found that prefetching of data blocks by prior art queries does not take into account the overall system load. Specifically, excessive prefetching may cause degradation in overall performance, e.g. if queries unilaterally prefetch data blocks identified by rowids from an index range scan, regardless of what is happening with other queries. For example, a query may prefetch data blocks that remain unused and need to be swapped out (to make room for data blocks prefetched by other queries), and the same data blocks have to be re-fetched later.