Database buffer cache: Holds data blocks that have been read from datafiles.
Redo Log Buffer: Holds redo log records prior to their being written to the log files.
Shared Pool: Contains parsed versions of SQL statements and other information.
Designing Database Applications
The primary goal of designing database applications is to return the correct results as quickly as possible. Application developers spend countless hours trying to write efficient SQL code, and Oracle DBAs tune the database memory structures to provide efficient access to the data and index blocks needed to satisfy the queries.
Tuning an application normally involves finding the best path to touch the least amount of data and/or index blocks in the Oracle database buffer cache. Accessing data in memory is always much faster than accessing data from disk. Oracle uses algorithms to keep highly used data blocks in memory to reduce the number of physical disk reads. Oracle DBAs can increase the size of the
default buffer cache or make use of the KEEP buffer pool to increase the number of blocks resident in memory for highly used tables and indexes. Even if all the data and index blocks are in memory, the query still has to read them to obtain the result set.
SQL Result Cache
Oracle has introduced a feature called the SQL Result Cache with the release of 11g. It can be used to cache a read-consistent version of the query results of commonly executed queries.
Static queries that are run time and no longer need to read the underlying data and index blocks but instead can read the results of queries from the new SQL Result Cache memory area within the Shared Pool of SGA.
A SQL statement is run once to read all the necessary index and data blocks that produce a query result, which can optionally be stored in the SQL Result Cache by means of a hint in the statement or by setting a database initialization parameter.
The SQL Result Cache is stored within the SGA so that all users connecting to the database instance have access to the query results stored there. Anyone executing the same SQL statement including the RESULT_CACHE hint will be able to make use of the SQL Result Cache and will not have to perform the physical and logical I/O to generate the output of the query.