Memory Structures in Oracle Database
Key Concepts
1. System Global Area (SGA)
The System Global Area (SGA) is a shared memory region that contains data and control information for one Oracle Database instance. It is used by the Oracle server processes and background processes.
Example:
Configuring the SGA size:
ALTER SYSTEM SET SGA_TARGET = 2G;
2. Shared Pool
The Shared Pool is a component of the SGA that stores shared SQL areas, dictionary cache, and other control structures. It is crucial for optimizing memory usage and improving performance.
Example:
Configuring the Shared Pool size:
ALTER SYSTEM SET SHARED_POOL_SIZE = 500M;
3. Database Buffer Cache
The Database Buffer Cache is a component of the SGA that stores copies of data blocks read from the data files. It reduces the need for disk I/O by keeping frequently accessed data in memory.
Example:
Configuring the Buffer Cache size:
ALTER SYSTEM SET DB_CACHE_SIZE = 1G;
4. Redo Log Buffer
The Redo Log Buffer is a circular buffer in the SGA that stores redo entries. These entries are used to recover the database in case of a failure. The buffer is flushed to the redo log files periodically.
Example:
Configuring the Redo Log Buffer size:
ALTER SYSTEM SET LOG_BUFFER = 10M;
5. Large Pool
The Large Pool is an optional component of the SGA that provides a larger memory area for certain operations, such as parallel execution, RMAN backups, and I/O server processes.
Example:
Configuring the Large Pool size:
ALTER SYSTEM SET LARGE_POOL_SIZE = 200M;
6. Java Pool
The Java Pool is a component of the SGA that provides memory for the Java virtual machine (JVM) and Java stored procedures. It is used for Java-related operations in the database.
Example:
Configuring the Java Pool size:
ALTER SYSTEM SET JAVA_POOL_SIZE = 200M;
7. Streams Pool
The Streams Pool is a component of the SGA that provides memory for Oracle Streams, a feature that captures, transports, and applies data changes. It is used for replication and data sharing.
Example:
Configuring the Streams Pool size:
ALTER SYSTEM SET STREAMS_POOL_SIZE = 100M;
8. PGA (Program Global Area)
The Program Global Area (PGA) is a memory region that is private to a server process. It contains data and control information used by the server process, such as session information and sort areas.
Example:
Configuring the PGA size:
ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 2G;
9. Sort Area
The Sort Area is a component of the PGA that is used for sorting operations, such as ORDER BY, GROUP BY, and DISTINCT. It helps in optimizing the performance of these operations by keeping the data in memory.
Example:
Configuring the Sort Area size:
ALTER SESSION SET SORT_AREA_SIZE = 1M;
10. Hash Area
The Hash Area is a component of the PGA that is used for hash-based operations, such as hash joins and hash partitions. It helps in optimizing the performance of these operations by keeping the data in memory.
Example:
Configuring the Hash Area size:
ALTER SESSION SET HASH_AREA_SIZE = 1M;
11. Session Memory
Session Memory is a component of the PGA that stores session-specific information, such as user credentials, session variables, and temporary tables. It is private to each session.
Example:
Monitoring session memory usage:
SELECT * FROM V$SESSTAT WHERE STATISTIC# = 12;
12. Library Cache
The Library Cache is a component of the Shared Pool that stores shared SQL and PL/SQL areas. It helps in reducing the parsing time and improving the performance of SQL and PL/SQL statements.
Example:
Monitoring Library Cache usage:
SELECT * FROM V$LIBRARYCACHE;
13. Dictionary Cache
The Dictionary Cache is a component of the Shared Pool that stores metadata information, such as table definitions, column definitions, and user information. It helps in reducing the access time to metadata.
Example:
Monitoring Dictionary Cache usage:
SELECT * FROM V$ROWCACHE;
14. Fixed SGA
The Fixed SGA is a small, fixed-size area in the SGA that contains pointers to other structures in the SGA. It is used by the Oracle kernel for internal management purposes.
Example:
Monitoring Fixed SGA usage:
SELECT * FROM V$SGAINFO WHERE NAME = 'Fixed SGA';
15. Log Buffer
The Log Buffer is a component of the SGA that stores redo entries. It is used by the LGWR (Log Writer) process to write redo entries to the redo log files.
Example:
Monitoring Log Buffer usage:
SELECT * FROM V$LOG_HISTORY;
16. Cursor Cache
The Cursor Cache is a component of the Shared Pool that stores parsed SQL and PL/SQL statements. It helps in reducing the parsing time and improving the performance of SQL and PL/SQL statements.
Example:
Monitoring Cursor Cache usage:
SELECT * FROM V$OPEN_CURSOR;
17. Result Cache
The Result Cache is a component of the SGA that stores the results of SQL queries. It helps in reducing the execution time of frequently executed queries by storing their results in memory.
Example:
Configuring the Result Cache size:
ALTER SYSTEM SET RESULT_CACHE_MAX_SIZE = 100M;
Examples and Analogies
Example 1: System Global Area (SGA)
Imagine the SGA as a shared kitchen in a large house. All family members (server processes) can access and use the kitchen (SGA) to prepare meals (perform database operations).
Example 2: Shared Pool
The Shared Pool is like a pantry in the kitchen where commonly used ingredients (shared SQL areas) are stored. This reduces the need to go to the grocery store (disk) every time you need an ingredient.
Example 3: Database Buffer Cache
The Buffer Cache is like a refrigerator in the kitchen where frequently used items (data blocks) are stored. This keeps the items fresh (in memory) and reduces the need to go to the pantry (disk) every time you need them.
Example 4: Redo Log Buffer
The Redo Log Buffer is like a notepad where you jot down every step you take while cooking (redo entries). This helps you recreate the meal (recover the database) in case you forget something.
Example 5: Large Pool
The Large Pool is like a large storage area in the kitchen where you keep bulky items (large operations) that don't fit in the pantry. This ensures that you have enough space for all your cooking needs.
Example 6: Java Pool
The Java Pool is like a special section in the kitchen where you keep all your cooking tools (Java-related operations). This ensures that you have everything you need for your special recipes.
Example 7: Streams Pool
The Streams Pool is like a conveyor belt in the kitchen where you prepare multiple dishes (data changes) at the same time. This helps you serve your guests (users) faster.
Example 8: PGA (Program Global Area)
The PGA is like a personal workstation in the kitchen where each family member prepares their own dish (server process). This ensures that everyone has their own space to work without interfering with others.
Example 9: Sort Area
The Sort Area is like a sorting tray where you arrange your ingredients (sort operations) before cooking. This helps you prepare your dish (query) faster.
Example 10: Hash Area
The Hash Area is like a mixing bowl where you combine ingredients (hash operations) to create a new dish. This helps you prepare complex dishes (queries) faster.
Example 11: Session Memory
Session Memory is like a personal recipe book where each family member keeps their own notes (session information). This ensures that everyone has their own reference while cooking.
Example 12: Library Cache
The Library Cache is like a cookbook where you store your favorite recipes (parsed SQL and PL/SQL statements). This helps you prepare your favorite dishes (queries) faster.
Example 13: Dictionary Cache
The Dictionary Cache is like a reference book where you store all the information about your ingredients (metadata). This helps you quickly find the information you need while cooking.
Example 14: Fixed SGA
The Fixed SGA is like a set of kitchen tools that are always in the same place. This ensures that you can quickly find and use them whenever you need them.
Example 15: Log Buffer
The Log Buffer is like a notepad where you jot down every step you take while cooking (redo entries). This helps you recreate the meal (recover the database) in case you forget something.
Example 16: Cursor Cache
The Cursor Cache is like a recipe box where you store your favorite recipes (parsed SQL and PL/SQL statements). This helps you prepare your favorite dishes (queries) faster.
Example 17: Result Cache
The Result Cache is like a serving tray where you keep your prepared dishes (query results). This helps you serve your guests (users) faster by having the dishes ready.