Not all operations read from the database buffer cache

Oracle and Memory Resources

Accessing information in memory is much faster than accessing information on a disk. An Oracle instance uses the database server’s memory resources to cache the information accessed to improve performance. Oracle utilizes an area of shared memory called the System Global Area (SGA) and a private memory area for each server process called the Program Global Area (PGA).

• Caching of database blocks containing table and index data in the database buffer cache

• Caching of parsed and optimized SQL statements, stored procedures, and data dictionary information in the shared pool

Oracle Database 10g eliminated manual tuning of SGA pools with automatic sizing for the SGA. Using automatic shared memory management, the database automati-cally allocates memory for the following SGA pools: database buffer cache, shared pool, large pool, Java pool, and Streams pool. You have to specify only the total amount of memory required by setting the SGA_TARGET initialization parameter.

The database buffer cache

If you decide to disable SGA_TARGET by setting it to 0, you will need to manually set initialization parameters for the memory pools (unless you want to use previous sizes). For the database buffer cache, you would assess the percentage of the data-base blocks requested by users read from the cache versus from the disk. This percentage is termed the hit ratio. If response times are too high and this ratio is lower than 90% (as a rule of thumb), increasing the value of the initialization param-eter DB_CACHE_SIZE can increase performance.

The shared pool is used at several points during the execution of every operation that occurs in an Oracle database. For example, the shared pool is accessed to cache the