Does a cursor store a SELECT result record-set in memory?

Question

From a book, I have read

Whenever you perform a SQL statement, the Oracle opens an area of memory in which the command is parsed and executed. This area is called a cursor.

It applies both to explicit and implicit cursors. But I'm curious about another thing - can a cursor be considered as a intermediate place (in memory) where Oracle returns final record set and uses it as a source for fetching records?

Answer 1

Oracle stores records retrieved from disk in a an area of allocated memory called the DB Buffer Cache. But these are blocks of records which are then filtered to prepare a final result set. Any other query on the same table can use these cached records rather than going to disk. There's lots more on Oracle's memory architecture in the Concepts Guide. Read it here.

Now what you're talking about is slightly different. Oracle +can+ cache individual result sets. At least it can since 11g, and only in the enterprise edition. Obviously it only makes sense to cache sets for queries which are run frequently and whose results won't stale quickly. There's no way the database can figure that out, which is why we have to tell it which result sets to cache. Find out more.