Administering In-Memory Columnar Caching in Oracle Exadata

Leave a reply

How to use In-Memory columnar caching in Oracle Exadata

Oracle Exadata boosts performance with In-Memory Columnar Caching, which saves data in a columnar format in the Exadata Smart Flash Cache. This feature speeds up queries by using Oracle Database’s In-Memory capabilities without needing complicated setup.

Key Points

  • Columnar Cache: Part of Smart Flash Cache that holds data in a column format.
  • Automatic Use: Exadata handles this automatically when directed by Oracle Database.
  • License Requirement: Available only if you have the Oracle Database In-Memory option.

How to Enable In-Memory Columnar Caching

1. Set INMEMORY_SIZE > 0 : This allocates memory for In-Memory operations.

    ALTER SYSTEM SET INMEMORY_SIZE = 2G;

    2. Use INMEMORY_FORCE (from Oracle 19.8 onwards): This enables columnar caching in Exadata Smart Flash Cache even without a dedicated In-Memory cache.

    ALTER SYSTEM SET INMEMORY_FORCE = cellmemory_level;

    Overriding Default Behavior with CELLMEMORY

    You can control how specific tables or segments use columnar caching with the CELLMEMORY option.

    Options:

    • NO CELLMEMORY
      • Excludes a table from columnar caching.
    ALTER TABLE employees NO CELLMEMORY;

    CELLMEMORY MEMCOMPRESS FOR CAPACITY

    • Stores table in In-Memory format with high compression (saves space).
    ALTER TABLE sales CELLMEMORY MEMCOMPRESS FOR CAPACITY;

    CELLMEMORY MEMCOMPRESS FOR QUERY

    • Less compression, faster query performance (uses more flash space).
    ALTER TABLE orders CELLMEMORY MEMCOMPRESS FOR QUERY;

    LOW / HIGH: Not implemented yet.

    Using INMEMORY and CELLMEMORY Together

    You can combine both options for flexibility: Useful for low-priority tables that may not load into memory but still benefit from columnar caching.

    CREATE TABLE t (c1 NUMBER) 
    INMEMORY CELLMEMORY MEMCOMPRESS FOR QUERY;

    Best Practices

    • Use MEMCOMPRESS FOR CAPACITY for large tables where space savings matter.
    • Use MEMCOMPRESS FOR QUERY for performance-critical tables.
    • Apply NO CELLMEMORY to rarely accessed data to save cache space.
    • Combine INMEMORY + CELLMEMORY for tables that may not fit in memory but still need columnar performance.

    Leave a Reply