The database buffer cache is the portion of the SGA that holds copies of data blocks read from datafiles. All user processes concurrently connected to the instance share access to the database buffer cache.
The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:
DB_CACHE_SIZE - The size of the cache of standard blocks.
LOG_BUFFER - The number of bytes allocated for the redo log buffer.
SHARED_POOL_SIZE - The size in bytes of the area devoted to shared SQL and PL/SQL statements.
LARGE_POOL_SIZE - The size of the large pool; the default is 0.
JAVA_POOL_SIZE - The size of the Java pool.
Shared pool:
The shared pool contains shared memory constructs, such as shared SQL areas, cached SQL and PL/SQL statements. It is called the shared pool because its contents are shared by all database users. The shared pool contains the library cache, the dictionary cache, and an area for control structures and character sets.
Java pool:
Optional SGA component that caches the most recently used Java objects and application code.
Large pool:
Optional SGA component that caches data for large operations, such as Shared Server or Parallel Execution.
Streams pool:
Optional SGA component that caches data from queued message requests.
Dictionary:
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.
The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache , also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).
The other area in memory to hold dictionary data is the library cache.
All Oracle user processes share these two caches for access to data dictionary information.
Library cache:
Acts as a warehouse for database commands. When a user issues a command, Oracle looks in the library cache to see if a duplicate statement exists. If it does, the new statement is discarded, and the stored statement is used instead. This approach saves processing time.
The size of the SGA is determined by several initialization parameters. The following parameters have the greatest effect on SGA size:
DB_CACHE_SIZE - The size of the cache of standard blocks.
LOG_BUFFER - The number of bytes allocated for the redo log buffer.
SHARED_POOL_SIZE - The size in bytes of the area devoted to shared SQL and PL/SQL statements.
LARGE_POOL_SIZE - The size of the large pool; the default is 0.
JAVA_POOL_SIZE - The size of the Java pool.
Shared pool:
The shared pool contains shared memory constructs, such as shared SQL areas, cached SQL and PL/SQL statements. It is called the shared pool because its contents are shared by all database users. The shared pool contains the library cache, the dictionary cache, and an area for control structures and character sets.
Java pool:
Optional SGA component that caches the most recently used Java objects and application code.
Large pool:
Optional SGA component that caches data for large operations, such as Shared Server or Parallel Execution.
Streams pool:
Optional SGA component that caches data from queued message requests.
Dictionary:
The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle accesses the data dictionary frequently during SQL statement parsing. This access is essential to the continuing operation of Oracle.
The data dictionary is accessed so often by Oracle that two special locations in memory are designated to hold dictionary data. One area is called the data dictionary cache , also known as the row cache because it holds data as rows instead of buffers (which hold entire blocks of data).
The other area in memory to hold dictionary data is the library cache.
All Oracle user processes share these two caches for access to data dictionary information.
Library cache:
Acts as a warehouse for database commands. When a user issues a command, Oracle looks in the library cache to see if a duplicate statement exists. If it does, the new statement is discarded, and the stored statement is used instead. This approach saves processing time.
