{"id":3701,"date":"2025-05-29T16:44:47","date_gmt":"2025-05-29T16:44:47","guid":{"rendered":"https:\/\/w3buddy.com\/?post_type=cposts&p=3701"},"modified":"2025-05-29T16:46:11","modified_gmt":"2025-05-29T16:46:11","slug":"in-memory-area","status":"publish","type":"cposts","link":"https:\/\/w3buddy.com\/blog\/notes\/getting-started-with-oracle-dba\/in-memory-area\/","title":{"rendered":"In-Memory Area"},"content":{"rendered":"\n

\ud83e\udde0 Introduction<\/strong>
Oracle In-Memory sounds complex, right? But it doesn\u2019t have to be.<\/p>\n\n\n\n

It\u2019s simply a smart way to speed up analytics and reporting<\/strong> without slowing down regular transactions<\/strong> (OLTP \u2013 Online Transaction Processing).<\/p>\n\n\n\n

Let\u2019s make this easy to digest.<\/p>\n\n\n\n

\ud83e\udde9 What Is the In-Memory Area?<\/strong>
The In-Memory Area<\/strong> is an optional<\/strong> part of the SGA (System Global Area)<\/strong>.<\/p>\n\n\n\n

It contains the In-Memory Column Store (IM Column Store)<\/strong> \u2014 a special memory space where Oracle stores your data in column format<\/strong> for super-fast scanning<\/strong>.<\/p>\n\n\n\n

\ud83e\udde0 Key point:
Oracle stores data in two formats at the same time<\/strong>:
\u27a4 Row format<\/strong> in Buffer Cache<\/strong> (for fast OLTP)
\u27a4 Column format<\/strong> in IM Column Store<\/strong> (for fast analytics)<\/p>\n\n\n\n

\ud83d\udccc This smart design doesn’t double memory usage!
The buffer cache stays small, while the IM column store handles large scans.<\/p>\n\n\n\n

\ud83e\udde0 What\u2019s Inside the In-Memory Column Store?<\/strong><\/p>\n\n\n\n

    \n
  1. IMCU (In-Memory Compression Unit)<\/strong>
    \u27a4 Compressed, read-only blocks
    \u27a4 Hold columnar data for one or more columns<\/li>\n\n\n\n
  2. SMU (Snapshot Metadata Unit)<\/strong>
    \u27a4 Stores metadata and transaction info for its IMCU<\/li>\n\n\n\n
  3. IMEU (In-Memory Expression Unit)<\/strong>
    \u27a4 Stores virtual columns<\/strong> and expression results<\/strong>
    \u27a4 Linked one-to-one with its parent IMCU<\/li>\n\n\n\n
  4. ESS (Expression Statistics Store)<\/strong>
    \u27a4 Tracks usage of expressions
    \u27a4 Helps Oracle decide which expressions to optimize
    \u27a4 Lives in memory and<\/strong> persists on disk
    \ud83d\udccc ESS is always enabled<\/strong> \u2014 not tied to In-Memory Column Store<\/li>\n<\/ol>\n\n\n\n

    \ud83d\udd0d Example:
    Table EMPLOYEES<\/code>:<\/p>\n\n\n\n

      \n
    • IMCU holds: weekly_salary<\/code><\/li>\n\n\n\n
    • IMEU stores:\n
        \n
      • Auto-calculated: weekly_salary * 52<\/code><\/li>\n\n\n\n
      • User-defined virtual column: weekly_salary * 12<\/code><\/li>\n\n\n\n
      • Row 3 in IMCU = Row 3 in IMEU (1:1 mapping)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n

        \ud83d\udcbe In-Memory Area Breakdown<\/strong><\/p>\n\n\n\n

        Pool Type<\/th>Description<\/th><\/tr><\/thead>
        1MB Data Pool<\/td>Stores actual columnar data (IMCUs + IMEUs)<\/td><\/tr>
        64KB Metadata Pool<\/td>Stores metadata (SMUs)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n

        \ud83d\udccc Most of the memory goes to the 1MB pool.<\/p>\n\n\n\n

        Controlled by<\/strong>: INMEMORY_SIZE<\/code> (default is 0)
        \u27a4 Minimum required<\/strong>: 100MB
        \u27a4 Can be increased on the fly<\/strong> (from Oracle 12.2 onwards)
        \u27a4 Must increase by at least 128MB<\/strong>
        \u27a4 \u274c Cannot be decreased<\/strong> dynamically<\/p>\n\n\n\n

        \u2699\ufe0f How and When Does Data Go In-Memory?<\/strong><\/p>\n\n\n\n

        \u27a4 When a table with the INMEMORY<\/code> attribute is queried<\/strong>
        \u27a4 Or automatically at database startup<\/strong><\/p>\n\n\n\n

        \ud83d\udd04 Data is converted from on-disk row format to columnar format
        \ud83d\udccc This conversion happens every time<\/strong> the instance restarts \u2014 because the IM Column Store exists only in memory<\/strong><\/p>\n\n\n\n

        \u2705 Even if a table is partially loaded<\/strong>, Oracle can run queries using what\u2019s in memory and fetch the rest from disk
        (This is called a hybrid scan<\/strong>)<\/p>\n\n\n\n

        \ud83e\udd16 Automatic In-Memory Magic<\/strong><\/p>\n\n\n\n

        Set INMEMORY_AUTOMATIC_LEVEL = HIGH<\/code> to let Oracle:
        \u2714\ufe0f Decide which objects to store
        \u2714\ufe0f Populate objects based on usage
        \u2714\ufe0f Recompress or evict segments as needed
        \u2714\ufe0f Maximize memory usage automatically<\/p>\n\n\n\n

        \u2699\ufe0f Background Processes That Help<\/strong><\/p>\n\n\n\n

          \n
        • IMCO<\/code> (In-Memory Coordinator): Triggers population<\/li>\n\n\n\n
        • SMCO<\/code> (Space Management Coordinator): Manages memory<\/li>\n\n\n\n
        • Wnnn<\/code> (Worker Processes): Do the actual work<\/li>\n<\/ul>\n\n\n\n

          \ud83d\udd01 What happens on DML (Data Manipulation Language)?
          \u27a4 Changes go to buffer cache \u2192 disk<\/strong>
          \u27a4 Then background processes repopulate<\/strong> the IM Column Store<\/p>\n\n\n\n

          \ud83d\ude80 In-Memory FastStart<\/strong>
          Want faster startup<\/strong>?<\/p>\n\n\n\n

          Enable In-Memory FastStart<\/strong> to write the columnar version of your data back to disk (in compressed format) at shutdown \u2014 making future startups much faster.<\/p>\n\n\n\n

          \ud83d\udccc But note: IMEUs<\/strong> (expression results) are not saved<\/strong> \u2014 they’re always repopulated from IMCUs<\/p>\n\n\n\n

          \u26a1 Performance Boosters to Know<\/strong><\/p>\n\n\n\n

            \n
          1. In-Memory Hybrid Scans<\/strong>
            \u27a4 Combine row + column store access
            \u27a4 Use in-memory data even if not all columns are loaded<\/strong><\/li>\n\n\n\n
          2. Deep Vectorization<\/strong>
            \u27a4 Uses SIMD (Single Instruction, Multiple Data)<\/strong> to boost complex SQL execution
            \u27a4 Enabled by default
            \u27a4 Can be turned off with:
            ALTER SYSTEM SET INMEMORY_DEEP_VECTORIZATION = FALSE<\/code><\/li>\n\n\n\n
          3. Base Level Feature<\/strong>
            \u27a4 Use up to 16GB<\/strong> of column store without licensing impact<\/strong>
            \u27a4 Great for test\/dev or small workloads<\/li>\n<\/ol>\n\n\n\n

            \ud83d\udee0\ufe0f DBA Tip of the Day<\/strong>
            Don\u2019t put everything in memory \u2014 just your hot, performance-critical<\/strong> tables or columns.<\/p>\n\n\n\n

            Start small, monitor usage, and let Oracle automate<\/strong> where possible using INMEMORY_AUTOMATIC_LEVEL<\/code>.<\/p>\n","protected":false},"excerpt":{"rendered":"

            \ud83e\udde0 IntroductionOracle In-Memory sounds complex, right? But it doesn\u2019t have to be. It\u2019s simply a smart way to speed up analytics and reporting without slowing down regular transactions (OLTP \u2013 Online Transaction Processing). Let\u2019s make this easy to digest. \ud83e\udde9 What Is the In-Memory Area?The In-Memory Area is an optional part of the SGA (System […]<\/p>\n","protected":false},"template":"","meta":{"googlesitekit_rrm_CAowu461DA:productID":""},"categories":[960,952],"class_list":["post-3701","cposts","type-cposts","status-publish","hentry","category-getting-started-with-oracle-dba","category-notes"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/cposts\/3701","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=3701"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=3701"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}