{"id":5266,"date":"2026-01-21T12:36:46","date_gmt":"2026-01-21T07:06:46","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=5266"},"modified":"2026-01-21T12:36:47","modified_gmt":"2026-01-21T07:06:47","slug":"memory-architecture","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/oracle-database-architecture\/memory-architecture\/","title":{"rendered":"MEMORY ARCHITECTURE"},"content":{"rendered":"\n

A. SGA (System Global Area) – Shared Memory<\/strong><\/h3>\n\n\n\n

Purpose:<\/strong> Shared by all users and processes. Allocated at instance startup.<\/p>\n\n\n\n

Main Components:<\/strong><\/h4>\n\n\n\n

1. Database Buffer Cache<\/strong><\/p>\n\n\n\n

    \n
  • Stores copies of data blocks read from datafiles<\/li>\n\n\n\n
  • When you query data, Oracle first checks here (not disk)<\/li>\n\n\n\n
  • Size parameter:<\/strong> DB_CACHE_SIZE<\/code> or auto-managed by SGA_TARGET<\/code><\/li>\n\n\n\n
  • Uses LRU algorithm (Least Recently Used for eviction)<\/li>\n\n\n\n
  • Types of buffers:<\/strong>\n
      \n
    • Dirty buffers:<\/strong> Modified but not yet written to disk<\/li>\n\n\n\n
    • Clean buffers:<\/strong> Not modified or already written to disk<\/li>\n\n\n\n
    • Pinned buffers:<\/strong> Currently being accessed<\/li>\n<\/ul>\n<\/li>\n\n\n\n
    • DBA concern:<\/strong> Too small = physical reads increase = slow queries<\/li>\n<\/ul>\n\n\n\n
      \n\n\n\n

      2. Shared Pool<\/strong><\/p>\n\n\n\n

      a) Library Cache:<\/strong> Stores parsed SQL, PL\/SQL code (execution plans)<\/p>\n\n\n\n

        \n
      • Prevents re-parsing same queries (soft parse vs hard parse)<\/li>\n\n\n\n
      • Hard parse = expensive<\/strong> (CPU intensive, takes locks)<\/li>\n\n\n\n
      • Soft parse = reuses existing plan<\/strong> (fast)<\/li>\n\n\n\n
      • Components:<\/strong> SQL area, PL\/SQL area, Java pool cache<\/li>\n<\/ul>\n\n\n\n

        b) Data Dictionary Cache (Row Cache):<\/strong><\/p>\n\n\n\n

          \n
        • Metadata about tables, users, privileges, tablespaces<\/li>\n\n\n\n
        • Cached from SYS<\/code> schema objects<\/li>\n<\/ul>\n\n\n\n

          c) Result Cache (19c):<\/strong><\/p>\n\n\n\n

            \n
          • Stores query results for reuse<\/li>\n\n\n\n
          • Can dramatically improve performance for repetitive queries<\/li>\n\n\n\n
          • Size parameter:<\/strong> SHARED_POOL_SIZE<\/code><\/li>\n\n\n\n
          • DBA concern:<\/strong> Insufficient size = hard parses spike = CPU saturation<\/li>\n<\/ul>\n\n\n\n
            \n\n\n\n

            3. Redo Log Buffer<\/strong><\/p>\n\n\n\n

              \n
            • Small circular buffer for redo entries (change vectors)<\/li>\n\n\n\n
            • LGWR writes it to redo log files<\/li>\n\n\n\n
            • Size parameter:<\/strong> LOG_BUFFER<\/code> (typically 10-16 MB is enough)<\/li>\n\n\n\n
            • Rarely needs tuning in modern systems<\/li>\n\n\n\n
            • DBA concern:<\/strong> If too small, LGWR can’t keep up = “log file sync” waits<\/li>\n<\/ul>\n\n\n\n
              \n\n\n\n

              4. Large Pool<\/strong> (Optional but recommended)<\/p>\n\n\n\n

                \n
              • Used for RMAN backup\/restore operations<\/li>\n\n\n\n
              • Parallel execution message buffers<\/li>\n\n\n\n
              • Shared server (MTS) session memory<\/li>\n\n\n\n
              • Size parameter:<\/strong> LARGE_POOL_SIZE<\/code><\/li>\n\n\n\n
              • Best practice:<\/strong> Set to at least 128MB if using RMAN<\/li>\n<\/ul>\n\n\n\n
                \n\n\n\n

                5. Java Pool<\/strong> (If using Java in DB)<\/p>\n\n\n\n

                  \n
                • For Java stored procedures, SQLJ<\/li>\n\n\n\n
                • Size parameter:<\/strong> JAVA_POOL_SIZE<\/code><\/li>\n\n\n\n
                • Not needed if you don’t use Java in database<\/li>\n<\/ul>\n\n\n\n
                  \n\n\n\n

                  6. Streams Pool<\/strong> (For replication\/streams)<\/p>\n\n\n\n

                    \n
                  • Used by Oracle Streams, GoldenGate<\/li>\n\n\n\n
                  • Size parameter:<\/strong> STREAMS_POOL_SIZE<\/code><\/li>\n<\/ul>\n\n\n\n
                    \n\n\n\n

                    7. Fixed SGA<\/strong><\/p>\n\n\n\n

                      \n
                    • Internal housekeeping structures<\/li>\n\n\n\n
                    • Contains general information about instance state<\/li>\n\n\n\n
                    • You cannot control this size<\/strong><\/li>\n<\/ul>\n\n\n\n
                      \n\n\n\n

                      INTERVIEW QUESTIONS:<\/strong><\/p>\n\n\n\n

                      Q1: What is SGA and its main components?<\/strong>
                      A:<\/strong> SGA is System Global Area\u2014shared memory allocated at instance startup. Main components: Database Buffer Cache (data blocks), Shared Pool (parsed SQL, data dictionary), Redo Log Buffer (change vectors), and optional pools like Large Pool (RMAN) and Java Pool.<\/p>\n\n\n\n

                      Q2: What’s the difference between hard parse and soft parse?<\/strong>
                      A:<\/strong><\/p>\n\n\n\n

                        \n
                      • Hard parse:<\/strong> SQL is new, Oracle parses it, creates execution plan, stores in library cache. CPU intensive.<\/li>\n\n\n\n
                      • Soft parse:<\/strong> SQL already in library cache, Oracle reuses existing plan. Very fast.<\/li>\n\n\n\n
                      • Use bind variables to avoid hard parses.<\/li>\n<\/ul>\n\n\n\n

                        Q3: How to reduce hard parses?<\/strong>
                        A:<\/strong><\/p>\n\n\n\n

                          \n
                        1. Use bind variables instead of literals<\/li>\n\n\n\n
                        2. Increase SHARED_POOL_SIZE<\/code><\/li>\n\n\n\n
                        3. Set CURSOR_SHARING=FORCE<\/code> (last resort, has side effects)<\/li>\n\n\n\n
                        4. Check V$SQL<\/code> for similar queries with different literals<\/li>\n<\/ol>\n\n\n\n

                          Q4: What is stored in Database Buffer Cache?<\/strong>
                          A:<\/strong> Copies of data blocks read from datafiles. Includes table data, index data. Uses LRU algorithm. When data is modified, blocks become “dirty” until DBWn writes them to disk.<\/p>\n\n\n\n

                          Q5: What happens when buffer cache is full?<\/strong>
                          A:<\/strong> Oracle uses LRU algorithm to evict least recently used clean buffers. If no clean buffers available, DBWn is signaled to write dirty buffers to disk, causing “free buffer waits” (performance issue).<\/p>\n\n\n\n

                          \n\n\n\n

                          B. PGA (Program Global Area) – Private Memory<\/strong><\/h3>\n\n\n\n

                          Purpose:<\/strong> Private memory for each server process. NOT shared.<\/p>\n\n\n\n

                          What’s stored in PGA:<\/strong><\/h4>\n\n\n\n
                            \n
                          • Session memory:<\/strong> Session variables, logon information, session cursor state<\/li>\n\n\n\n
                          • Private SQL area:<\/strong> Bind information, runtime memory structures<\/li>\n\n\n\n
                          • Sort area:<\/strong> For ORDER BY, GROUP BY, DISTINCT operations<\/li>\n\n\n\n
                          • Hash area:<\/strong> For hash joins<\/li>\n\n\n\n
                          • Bitmap merge area:<\/strong> For bitmap index operations<\/li>\n<\/ul>\n\n\n\n

                            Size parameters:<\/strong><\/p>\n\n\n\n

                              \n
                            • PGA_AGGREGATE_TARGET<\/code> = Total PGA for all sessions (Oracle auto-manages individual allocations)<\/li>\n\n\n\n
                            • PGA_AGGREGATE_LIMIT<\/code> = Hard limit (19c feature to prevent runaway processes from consuming all memory)<\/li>\n\n\n\n
                            • WORKAREA_SIZE_POLICY=AUTO<\/code> (default, recommended)<\/li>\n<\/ul>\n\n\n\n

                              How Oracle allocates PGA:<\/strong><\/p>\n\n\n\n

                                \n
                              • Oracle estimates memory needed for each operation<\/li>\n\n\n\n
                              • Distributes available PGA among active operations<\/li>\n\n\n\n
                              • If insufficient PGA, operations spill to TEMP tablespace (slower)<\/li>\n<\/ul>\n\n\n\n

                                DBA concern:<\/strong><\/p>\n\n\n\n

                                  \n
                                • Too small = sorts\/joins spill to TEMP tablespace (disk-based, very slow)<\/li>\n\n\n\n
                                • Monitor: V$PGASTAT<\/code>, V$PROCESS<\/code>, V$SQL_WORKAREA<\/code><\/li>\n<\/ul>\n\n\n\n
                                  \n\n\n\n

                                  INTERVIEW QUESTIONS:<\/strong><\/p>\n\n\n\n

                                  Q1: What’s the difference between SGA and PGA?<\/strong>
                                  A:<\/strong><\/p>\n\n\n\n

                                    \n
                                  • SGA:<\/strong> Shared memory, allocated at instance startup, used by all processes. Contains buffer cache, shared pool.<\/li>\n\n\n\n
                                  • PGA:<\/strong> Private memory, allocated per server process, not shared. Contains sort areas, session variables, private SQL areas.<\/li>\n<\/ul>\n\n\n\n

                                    Q2: What happens if PGA is insufficient?<\/strong>
                                    A:<\/strong> Sort and hash operations spill to TEMP tablespace (disk). This causes performance degradation. You’ll see “direct path read temp” and “direct path write temp” wait events.<\/p>\n\n\n\n

                                    Q3: How to identify if PGA is undersized?<\/strong>
                                    A:<\/strong> Check:<\/p>\n\n\n\n

                                    SELECT name, value FROM V$PGASTAT \nWHERE name IN ('over allocation count', 'global memory bound');<\/code><\/pre>\n\n\n\n
                                    If “over allocation count” > 0, increase PGA. Also check V$SQL_WORKAREA_ACTIVE<\/code> for operations using disk.<\/p>\n\n\n\n
                                    Q4: What is PGA_AGGREGATE_LIMIT (19c)?<\/strong>
                                    A:<\/strong> Hard limit on total PGA usage. If exceeded, Oracle terminates sessions consuming most PGA to protect instance. Prevents runaway queries from crashing the database. Default is 2x PGA_AGGREGATE_TARGET<\/code> or 3GB (whichever is higher).<\/p>\n\n\n\n
                                    \n\n\n\n
                                    C. Automatic Memory Management (AMM vs ASMM)<\/strong><\/h3>\n\n\n\n
                                    Oracle 19c offers three memory management modes:<\/p>\n\n\n\n
                                    1. Automatic Shared Memory Management (ASMM) – RECOMMENDED<\/strong><\/h4>\n\n\n\n
                                    SGA_TARGET = 10G          -- Oracle auto-tunes components within this<\/em>\nSGA_MAX_SIZE = 12G        -- Maximum SGA can grow to (requires restart to increase)<\/em>\nPGA_AGGREGATE_TARGET = 3G -- Oracle manages PGA automatically<\/em><\/code><\/pre>\n\n\n\n
                                    How it works:<\/strong><\/p>\n\n\n\n
                                      \n
                                    • You set total SGA size<\/li>\n\n\n\n
                                    • Oracle automatically resizes buffer cache, shared pool, large pool, java pool<\/li>\n\n\n\n
                                    • You can still set minimums: DB_CACHE_SIZE<\/code>, SHARED_POOL_SIZE<\/code> (Oracle won’t go below these)<\/li>\n\n\n\n
                                    • Downtime needed:<\/strong> Only to increase SGA_MAX_SIZE<\/code><\/li>\n<\/ul>\n\n\n\n
                                      Best for:<\/strong> Most production systems, especially large databases<\/p>\n\n\n\n
                                      \n\n\n\n
                                      2. Automatic Memory Management (AMM)<\/strong><\/h4>\n\n\n\n
                                      MEMORY_TARGET = 13G       -- Total memory (SGA+PGA combined)<\/em>\nMEMORY_MAX_TARGET = 16G   -- Maximum total memory<\/em><\/code><\/pre>\n\n\n\n
                                      How it works:<\/strong><\/p>\n\n\n\n
                                        \n
                                      • Oracle manages BOTH SGA and PGA automatically<\/li>\n\n\n\n
                                      • Can transfer memory between SGA and PGA as needed<\/li>\n\n\n\n
                                      • More flexible but has overhead<\/li>\n<\/ul>\n\n\n\n
                                        Limitations:<\/strong><\/p>\n\n\n\n
                                          \n
                                        • Not supported on Linux with huge pages enabled<\/li>\n\n\n\n
                                        • Performance overhead on very large systems (>40GB SGA)<\/li>\n\n\n\n
                                        • Downtime needed:<\/strong> Only to increase MEMORY_MAX_TARGET<\/code><\/li>\n<\/ul>\n\n\n\n
                                          Best for:<\/strong> Smaller databases (<20GB SGA), test\/dev environments<\/p>\n\n\n\n
                                          \n\n\n\n
                                          3. Manual Memory Management<\/strong><\/h4>\n\n\n\n
                                          SGA_TARGET = 0                    -- Disables ASMM<\/em>\nDB_CACHE_SIZE = 6G\nSHARED_POOL_SIZE = 2G\nLARGE_POOL_SIZE = 256M\nLOG_BUFFER = 16M\nPGA_AGGREGATE_TARGET = 3G<\/code><\/pre>\n\n\n\n
                                          When to use:<\/strong><\/p>\n\n\n\n
                                            \n
                                          • Very specific tuning requirements<\/li>\n\n\n\n
                                          • You know exact optimal sizes (rare)<\/li>\n\n\n\n
                                          • Legacy systems<\/li>\n<\/ul>\n\n\n\n
                                            Downtime needed:<\/strong> Changes to most SGA components require restart<\/p>\n\n\n\n
                                            \n\n\n\n
                                            CRITICAL RULES:<\/strong><\/h3>\n\n\n\n
                                            \u274c NEVER SET BOTH:<\/strong><\/p>\n\n\n\n
                                            -- WRONG - Don't mix AMM and ASMM<\/em>\nMEMORY_TARGET = 10G       -- AMM<\/em>\nSGA_TARGET = 8G           -- ASMM (will be ignored!)<\/em><\/code><\/pre>\n\n\n\n
                                            \u2705 CORRECT COMBINATIONS:<\/strong><\/p>\n\n\n\n
                                            Option A: Use ASMM (Recommended for 19c)<\/strong><\/p>\n\n\n\n
                                            SGA_TARGET = 10G\nSGA_MAX_SIZE = 12G\nPGA_AGGREGATE_TARGET = 3G\nPGA_AGGREGATE_LIMIT = 6G\nMEMORY_TARGET = 0         -- Explicitly disable AMM<\/em><\/code><\/pre>\n\n\n\n
                                            Option B: Use AMM<\/strong><\/p>\n\n\n\n
                                            MEMORY_TARGET = 13G\nMEMORY_MAX_TARGET = 16G\nSGA_TARGET = 0            -- Must be 0 or not set<\/em>\nPGA_AGGREGATE_TARGET = 0  -- Must be 0 or not set<\/em><\/code><\/pre>\n\n\n\n
                                            Option C: Manual<\/strong><\/p>\n\n\n\n
                                            SGA_TARGET = 0\nMEMORY_TARGET = 0\nDB_CACHE_SIZE = 6G\nSHARED_POOL_SIZE = 2G\nPGA_AGGREGATE_TARGET = 3G<\/code><\/pre>\n\n\n\n
                                            \n\n\n\n
                                            PARAMETER CHANGE – DOWNTIME REQUIREMENTS:<\/strong><\/h3>\n\n\n\n
                                            Parameter<\/th>Dynamic Change?<\/th>Requires Restart?<\/th>Notes<\/th><\/tr><\/thead>
                                            SGA_TARGET<\/code><\/td>\u2705 Yes (up to SGA_MAX_SIZE<\/code>)<\/td>\u274c No<\/td>Can increase\/decrease online<\/td><\/tr>
                                            SGA_MAX_SIZE<\/code><\/td>\u274c No<\/td>\u2705 Yes<\/td>Requires instance restart<\/td><\/tr>
                                            PGA_AGGREGATE_TARGET<\/code><\/td>\u2705 Yes<\/td>\u274c No<\/td>Can change anytime<\/td><\/tr>
                                            PGA_AGGREGATE_LIMIT<\/code><\/td>\u2705 Yes<\/td>\u274c No<\/td>19c feature, dynamic<\/td><\/tr>
                                            MEMORY_TARGET<\/code><\/td>\u2705 Yes (up to MEMORY_MAX_TARGET<\/code>)<\/td>\u274c No<\/td>Can change online<\/td><\/tr>
                                            MEMORY_MAX_TARGET<\/code><\/td>\u274c No<\/td>\u2705 Yes<\/td>Requires restart<\/td><\/tr>
                                            DB_CACHE_SIZE<\/code><\/td>\u2705 Yes (if ASMM disabled)<\/td>\u26a0\ufe0f Depends<\/td>Dynamic only in manual mode<\/td><\/tr>
                                            SHARED_POOL_SIZE<\/code><\/td>\u2705 Yes (if ASMM disabled)<\/td>\u26a0\ufe0f Depends<\/td>Dynamic only in manual mode<\/td><\/tr>
                                            LOG_BUFFER<\/code><\/td>\u274c No<\/td>\u2705 Yes<\/td>Static parameter<\/td><\/tr>
                                            LARGE_POOL_SIZE<\/code><\/td>\u2705 Yes<\/td>\u274c No<\/td>Dynamic<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n
                                            How to change dynamically:<\/strong><\/p>\n\n\n\n
                                            -- Check if parameter is dynamic<\/em>\nSELECT name, value, isses_modifiable, issys_modifiable \nFROM v$parameter \nWHERE name = 'sga_target';\n\n-- Change SGA_TARGET online (within SGA_MAX_SIZE limit)<\/em>\nALTER SYSTEM SET sga_target=12G SCOPE=BOTH;\n\n-- To exceed SGA_MAX_SIZE, must restart<\/em>\nALTER SYSTEM SET sga_max_size=16G SCOPE=SPFILE;  -- Change in SPFILE<\/em>\nSHUTDOWN IMMEDIATE;\nSTARTUP;<\/code><\/pre>\n\n\n\n
                                            \n\n\n\n
                                            INTERVIEW QUESTIONS:<\/strong><\/p>\n\n\n\n
                                            Q1: Can I set both MEMORY_TARGET and SGA_TARGET?<\/strong>
                                            A:<\/strong> No. If you set MEMORY_TARGET<\/code> (AMM), you must leave SGA_TARGET=0<\/code>. If you set SGA_TARGET<\/code> (ASMM), you must leave MEMORY_TARGET=0<\/code>. They are mutually exclusive. Setting both causes Oracle to ignore SGA_TARGET<\/code>.<\/p>\n\n\n\n
                                            Q2: Which is better – AMM or ASMM?<\/strong>
                                            A:<\/strong> For 19c production systems, ASMM is recommended<\/strong>. AMM has overhead and isn’t supported with huge pages on Linux. ASMM gives you better control and performance for large SGA sizes (>20GB).<\/p>\n\n\n\n
                                            Q3: Can I increase SGA_TARGET without downtime?<\/strong>
                                            A:<\/strong> Yes, but only up to SGA_MAX_SIZE<\/code>. If you need to go beyond SGA_MAX_SIZE<\/code>, you must change SGA_MAX_SIZE<\/code> in SPFILE and restart the instance.<\/p>\n\n\n\n
                                            Q4: How to check current memory usage?<\/strong>
                                            A:<\/strong><\/p>\n\n\n\n
                                            -- SGA components<\/em>\nSELECT * FROM v$sga;\nSELECT * FROM v$sgastat ORDER BY bytes DESC;\n\n-- PGA usage<\/em>\nSELECT * FROM v$pgastat;\nSELECT name, value\/1024\/1024 MB FROM v$pgastat \nWHERE name IN ('total PGA allocated', 'total PGA inuse');\n\n-- Memory parameters<\/em>\nSHOW PARAMETER sga\nSHOW PARAMETER pga\nSHOW PARAMETER memory<\/code><\/pre>\n\n\n\n
                                            Q5: What if I set SGA_TARGET=10G but SGA_MAX_SIZE=8G?<\/strong>
                                            A:<\/strong> Database won’t start. You’ll get error: “ORA-00844: Parameter not taking MEMORY_MAX_TARGET into account”. SGA_MAX_SIZE<\/code> must be >= SGA_TARGET<\/code>.<\/p>\n\n\n\n
                                            Q6: Does changing PGA_AGGREGATE_TARGET require restart?<\/strong>
                                            A:<\/strong> No. It’s a dynamic parameter. Change takes effect immediately for new sessions. Existing sessions adjust gradually.<\/p>\n\n\n\n
                                            Q7: I increased SGA_TARGET but free memory didn’t decrease on OS level. Why?<\/strong>
                                            A:<\/strong> Oracle allocates up to SGA_MAX_SIZE<\/code> at startup (on Linux). Increasing SGA_TARGET<\/code> doesn’t allocate more OS memory\u2014it just allows Oracle to use more of the already-allocated space.<\/p>\n\n\n\n
                                            Q8: How to switch from AMM to ASMM?<\/strong>
                                            A:<\/strong><\/p>\n\n\n\n
                                            -- First, note current values<\/em>\nSHOW PARAMETER memory_target\nSHOW PARAMETER sga_target\n\n-- Set ASMM parameters<\/em>\nALTER SYSTEM SET memory_target=0 SCOPE=SPFILE;\nALTER SYSTEM SET sga_target=10G SCOPE=SPFILE;\nALTER SYSTEM SET sga_max_size=12G SCOPE=SPFILE;\nALTER SYSTEM SET pga_aggregate_target=3G SCOPE=SPFILE;\n\n-- Restart required<\/em>\nSHUTDOWN IMMEDIATE;\nSTARTUP;<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
                                            A. SGA (System Global Area) – Shared Memory Purpose: Shared by all users and processes. Allocated at instance startup. Main Components: 1. Database Buffer Cache 2. Shared Pool a) Library Cache: Stores parsed SQL, PL\/SQL code (execution plans) b) Data Dictionary Cache (Row Cache): c) Result Cache (19c): 3. Redo Log Buffer 4. Large Pool […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[952,1241],"class_list":["post-5266","cposts","type-cposts","status-publish","hentry","category-notes","category-oracle-database-architecture"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/5266","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/cposts"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5266"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5266"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}