Oracle SGA (System Global Area): Complete Explanation with Diagrams of Its Structure and Components

Bronze_en

In Oracle Database memory management, the System Global Area (SGA) plays a central role in determining performance and stability.

This article provides a clear and detailed explanation of the SGA’s purpose and components, accompanied by text-based diagrams. Whether you’re a beginner or reviewing core architecture, this guide is designed to help you deeply understand the SGA.

1. What Is the SGA?

The System Global Area (SGA) is a shared memory region allocated when an Oracle instance starts.
All server processes and background processes use this area collectively.

Its main purposes include:

  • Caching SQL parse results and execution plans (Shared Pool)
  • Caching table and index blocks (Buffer Cache)
  • Temporarily storing changes before writing to log files (Redo Log Buffer)

Simply put, the SGA is a crucial memory region that significantly affects Oracle performance.

2. Text Diagram: Oracle Instance and SGA Relationship

[Structure of Oracle Instance]

+----------------------------+
|       Oracle Instance      |
|  +----------------------+  |
|  |        SGA           |  | ← Shared memory for all processes
|  +----------------------+  |
|  |  Background Process   | | ← e.g., DBWn, LGWR, CKPT, etc.
|  +----------------------+  |
+----------------------------+

3. Major Components of the SGA

The SGA consists of several memory structures, each with distinct purposes:

Component NameDescription
Shared PoolCaches SQL/PLSQL parse results and data dictionary info
Database Buffer CacheHolds data blocks in memory for fast access
Redo Log BufferTemporarily stores transaction changes
Java PoolUsed for Java-based processes
Large PoolSupports backups, parallel processing, etc.
Streams PoolUsed by Oracle Streams (now rarely used)

4. Details of Each Component

4.1 Shared Pool

+------------------------------+
|     Shared Pool              |
|  +------------------------+  |
|  | Library Cache           | ← SQL parse results
|  +------------------------+  |
|  | Data Dictionary Cache   | ← Dictionary information
|  +------------------------+  |
+------------------------------+
  • Library Cache: Stores parsed SQL, reducing parsing overhead.
  • Data Dictionary Cache: Holds table definitions, privileges, and column info.

4.2 Database Buffer Cache

+----------------------------+
|  Database Buffer Cache     |
|  +--------+  +--------+   |
|  | Block1 |  | Block2 |...|
|  +--------+  +--------+   |
+----------------------------+
  • Caches blocks read from datafiles.
  • Reduces physical I/O by reusing cached data on repeated access.

4.3 Redo Log Buffer

  • Temporarily holds transaction changes.
  • LGWR writes the content to redo log files periodically.
  • If this buffer is too small, it may cause commit delays.

4.4 Java Pool / Large Pool / Streams Pool

  • Java Pool: For Java stored procedures and JVM.
  • Large Pool: Used by RMAN, shared servers, parallel execution.
  • Streams Pool: Memory for Oracle Streams (not widely used anymore).

5. SGA Size Management and ASMM

Oracle supports both manual and automatic configuration of SGA size.

5.1 Automatic Shared Memory Management (ASMM)

Set SGA_TARGET to let Oracle manage component memory allocation dynamically.
Limit the upper bound with SGA_MAX_SIZE.

ALTER SYSTEM SET SGA_TARGET = 1G SCOPE=BOTH;
ALTER SYSTEM SET SGA_MAX_SIZE = 2G SCOPE=SPFILE;

5.2 Manual Settings (Not Recommended)

Set the following parameters individually if not using ASMM:

ParameterDescription
SHARED_POOL_SIZESize of Shared Pool
DB_CACHE_SIZESize of Buffer Cache
LOG_BUFFERSize of Redo Log Buffer
LARGE_POOL_SIZESize of Large Pool

6. Text Diagram: SGA Structure Summary

[SGA Structure Diagram]

+---------------------------------------+
|               SGA                     |
|  +-------------------------------+    |
|  | Shared Pool                   |    |
|  |  +-- Library Cache          | |    |
|  |  +-- Data Dictionary Cache  | |    |
|  +-------------------------------+    |
|  +-- Database Buffer Cache         |  |
|  +-- Redo Log Buffer               |  |
|  +-- Java Pool                     |  |
|  +-- Large Pool                    |  |
|  +-- Streams Pool                  |  |
+---------------------------------------+

7. Notes and Best Practices

  • Lack of SGA leads to significant performance degradation.
  • Inadequate Shared Pool → Frequent SQL reparsing → High CPU usage.
  • Insufficient Buffer Cache → Excessive disk I/O → Poor response times.

8. AMM (Automatic Memory Management)

Oracle also provides AMM, which automatically manages both SGA and PGA.

To enable AMM:

ALTER SYSTEM SET MEMORY_TARGET = 2G SCOPE=SPFILE;
ALTER SYSTEM SET MEMORY_MAX_TARGET = 3G SCOPE=SPFILE;

Difference Between AMM and ASMM

Management TypeTargetsParameters
ASMMSGA onlySGA_TARGET, PGA_AGGREGATE_TARGET
AMMSGA + PGAMEMORY_TARGET, MEMORY_MAX_TARGET

When using AMM, you don’t need to configure SGA_TARGET or PGA_AGGREGATE_TARGET.

9. How to Monitor the SGA

You can use dynamic performance views to monitor SGA status.

9.1 Check overall SGA memory usage

SELECT * FROM V$SGA;
Command execution example

SQL> SELECT * FROM V$SGA;

NAME                      VALUE     CON_ID
-------------------- ---------- ----------
Fixed Size              8896880          0
Variable Size         889192448          0
Database Buffers      637534208          0
Redo Buffers            7876608          0

9.2 Check memory usage per component

SELECT POOL, NAME, BYTES
FROM   V$SGASTAT
ORDER  BY BYTES DESC;
Command execution example

SQL> SELECT POOL, NAME, BYTES
  2  FROM   V$SGASTAT
  3  ORDER  BY BYTES DESC;

POOL           NAME                            BYTES
-------------- -------------------------- ----------
               buffer_cache                587202560
               shared_io_pool               50331648
shared pool    free memory                  20132792
java pool      free memory                  16777216
large pool     free memory                  16285696
shared pool    ksunfy_meta 1                12589184
shared pool    SQLA                         10486928
shared pool    SO private sga                9555320
shared pool    row cache mutex               9019032
               fixed_sga                     8896880
shared pool    KGLH0                         8886624
               log_buffer                    7876608
shared pool    private strands               6945792
shared pool    row cache hash                6012688
shared pool    KGLSG                         5260816
shared pool    obj stats allocation chun     5160960

：

shared pool    LGWR post requested array           8
shared pool    DNFS Disp context arr               8
shared pool    kcrfwy: shutdowns                   8
shared pool    KEWS PDB SGA                        8
shared pool    ksunfy: in-use PSO Numa A           8
shared pool    kffssga                             8
shared pool    ptr to sessions under idl           8
shared pool    kghpinfo ptrs                       8
shared pool    ksmscrsas:1                         8
shared pool    ksuloi: garbage collectio           8
shared pool    KCFIS SGA                           8
shared pool    kglPdbDebugStat                     8

1413 rows selected.

9.3 Check buffer cache hit ratio

SELECT NAME, VALUE
FROM   V$SYSSTAT
WHERE  NAME IN (
  'physical reads',
  'db block gets',
  'consistent gets'
);
Command execution example

SQL> SELECT NAME, VALUE
  2  FROM   V$SYSSTAT
  3  WHERE  NAME IN (
  4    'physical reads',
  5    'db block gets',
  6    'consistent gets'
  7  );

NAME                                                                  VALUE
---------------------------------------------------------------- ----------
db block gets                                                          4535
consistent gets                                                      110722
physical reads                                                         7970

Hit Ratio Formula:

(1 - (physical reads / (db block gets + consistent gets))) * 100

10. Benefits of Properly Configuring the SGA

✅ Faster SQL Execution
→ Reuse of parsed SQL improves CPU efficiency.

✅ Reduced Disk I/O
→ More data served from memory, less physical read/write.

✅ Efficient Redo Log Writing
→ Adequate redo buffer prevents commit delays.

✅ Stable RMAN and Parallel Operations
→ Sufficient Large Pool avoids failures and waits.

11. Risks of Improper SGA Configuration

❌ Frequent SQL reparsing
→ High CPU load due to missing cached SQL.

❌ Excessive disk I/O
→ Buffer cache too small to hold hot data.

❌ Redo write bottlenecks
→ Redo log buffer overflow causes log flush waits.

❌ Backup and parallel failures
→ Lack of Large Pool affects RMAN and parallel query.

Conclusion

The SGA is one of the most critical memory regions in the Oracle instance, shared across all processes.
When configured properly, it can drastically improve SQL parsing, data access, transaction speed, and backup performance.

Use ASMM or AMM for automatic memory allocation, and monitor memory usage regularly using SQL views to ensure stable and efficient database operation.

If configured incorrectly, the SGA can be a source of major performance problems, so continuous tuning and analysis are essential.

[reference]
5.1.4.1 System Global Area

コメント

Copied title and URL