Improve performance of real-time analytics and mixed workloads using the Database In-Memory option on Amazon RDS for Oracle

select segment_name, bytes disk, inmemory_size, populate_status, inmemory_compression COMPRESSION, bytes/inmemory_size_comp_ratio from v$im_segments where segment_name='<TABLE_NAME>';

For every object being stored, estimate the memory it consumes. For example, if your analytics workload against a table queries 10 out of 200 columns and almost the entire dataset is scanned, then based on the data type of the column and total number of rows in the table, you can estimate approximately how much memory it may consume. You may choose to run the same analytic query against the buffer cache (without in-memory option enabled) and verify the cached blocks (v$bh.block#) and compare with blocks from dba_tables to see what percent of the total blocks are loaded into the cache. You can use the following query for that purpose:

select obj.owner, obj.object_name, obj.object_type,
    count(buf.block#) as cached_blocks,
    tab.blocks as total_blocks
from v$bh buf
inner join dba_objects obj
    on buf.objd = obj.data_object_id
inner join dba_tables tab
    on tab.owner = obj.owner
    and tab.table_name = obj.object_name
    and obj.object_type = 'TABLE'
where buf.class# = 1
and buf.status != 'free'
and obj.owner = '<TABLE_OWNER>'
and obj.object_name = '<TABLE_NAME>'
and obj.object_type = 'TABLE'
group by obj.owner, obj.object_name, obj.object_type, tab.blocks;

You can then reduce the final estimate by the factor of the compression ratio as advised by the compression advisor depending on the type of compression used.

If you run arithmetic functions against your NUMBER type columns in your table, it would be prudent to enable the in_memory_optimized_arithmetic parameter and add another 15% to the estimated size for dual storage. When enabled, the optimizer uses Single Instruction Multiple Data (SIMD) Vector processing to speed up arithmetic operations on columns with the NUMBER format, which would otherwise incur significant performance overhead because of their inability to be performed natively in hardware.

In-Memory population on Amazon RDS for Oracle

When you use DDL to specify objects as INMEMORY, they are eligible to reside in the IM column store, but you need to populate those objects in the store to actually move them. During population, the database reads the existing row format data from disk, transforms it into columnar format, and then stores it in the IM column store. Transforming new data (for example, modified rows in a table after an insert, update, or delete) into columnar format is called repopulation. Population is either on-demand or priority-based.

On-demand population

DDL statements include an INMEMORY PRIORITY subclause that provides more control over the population queue. By default, INMEMORY PRIORITY for an object is set to NONE. The database only populates the object when it’s accessed through a full table scan, therefore any access by index or fetch by rowid won’t populate the object in the IM column store. Moreover, if the segment on disk is 64 KB or less, it isn’t populated in the IM store; therefore, some small objects enabled for the IM store may not be populated either.

Priority-based population

You can choose priority as CRITICAL, HIGH, and LOW for the objects being populated into the IM store, then the database automatically populates the objects using an internally managed priority queue. In this case, a full scan is not a necessary condition for population.

In this case, the database does the following:

• Populates columnar data in the IM column store automatically after the database instance restarts.
• Queues the population of INMEMORY objects based on the specified priority level. For example, a table altered with INMEMORY PRIORITY CRITICAL takes precedence over a table altered with INMEMORY PRIORITY HIGH, which in turn takes precedence over a table altered with INMEMORY PRIORITY LOW. If the IM column store has insufficient space, then Oracle database doesn’t populate additional objects until space is available.
• Waits to return from ALTER TABLE or ALTER MATERIALIZED VIEW statements until the changes to the object are recorded in the IM column store.

You can populate tablespaces, tables, and subsets of columns within a specific object or materialized views into the IM column store. However, the following objects are not eligible for the IM column store:

• Indexes
• Index-organized tables
• Hash clusters
• Objects owned by the sys user and stored in the SYSTEM or SYSAUX tablespace

After a segment is populated in the IM column store, the database only evicts it when the segment is dropped or moved, or when the segment is updated with the NO INMEMORY attribute. You can evict a segment manually or by using an ADO policy.

How to populate database objects In-Memory

Database INMEMORY population can be done using DBMS_INMEMORY program units or the DBMS_INMEMORY_ADMIN.POPULATE_WAIT procedure, or by using SELECT to force a full table scan on the table (/*FULL (table_name) NO_PARALLEL (table_name) */) hint.

How to repopulate database objects In-Memory

There will be modifications on the objects already in the IM column store due to DMLs or DDLs issued against them or their dependent objects; therefore, there is a need to periodically refresh the modified objects. This process is called repopulation.

Because In-memory Compression Unit (IMCU) is a read-only structure, any data changes are tracked as snapshot metadata in a transaction journal.

The repopulation is done automatically (which can be manually controlled using initialization parameters or the DBMS_INMEMORY package) in either of the following two forms:

• Threshold-based repopulation – Repopulation automatically triggers when the threshold on the number of stale entries in the transaction journal or staleness threshold is breached
• Trickle repopulation – The IMC0 (In-Memory Coordinator) process checks periodically whether stale rows exist in the transaction journal irrespective of staleness threshold, and it supplements threshold-based repopulation

Other configurable In-Memory parameters on Amazon RDS for Oracle

The following In-Memory parameters are also available on Amazon RDS for Oracle:

• INMEMORY_CLAUSE_DEFAULT – You can set this parameter to INMEMORY or NOINMEMORY. This setting then applies to all new tables and materialized views by default without the need of explicit specification.
• INMEMORY_EXPRESSIONS_USAGE – This controls which In-Memory expressions are populated into the IM column store to be used by queries. Accepted values are ENABLE, DISABLE, STATIC_ONLY, and DYNAMIC_ONLY. The default is ENABLE, meaning both static and dynamic expressions are populated in the IM column store.
• INMEMORY_FORCE – This allows only two settings: DEFAULT and OFF. Other settings like BASE_LEVEL and CELLMEMORY_LEVEL (exadata-only feature) isn’t applicable for Amazon RDS for Oracle.
• INMEMORY_QUERY – This enables or disables the In-Memory feature for the entire database at the system or session level. It’s useful for testing workloads with and without the In-Memory feature.
• INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT – This controls the number of background processes used for trickle repopulation.

Things to consider before enabling Database In-Memory in Amazon RDS for Oracle

Consider the following before enabling this feature in Amazon RDS for Oracle:

• The In-Memory base level feature and Automatic In-Memory is not available for Amazon RDS for Oracle as of this writing, but you still have the option of manual In Memory population.
• Resizing the INMEMORY_SIZE parameter dynamically without a reboot of the Amazon RDS for Oracle is not possible. Oracle documentation mentions that INMEMORY_SIZE can be dynamically increased from your current setting without a reboot. However, with Amazon RDS for Oracle, you may either increase, decrease, or completely disable it in the associated parameter group, but it requires a reboot.
• It’s important to note that the Database In-Memory option is applicable for Enterprise Edition only as an additional cost option, therefore the LI (License Included) model doesn’t apply. You need to have a BYOL (Bring Your Own License) model. It’s advised to integrate with AWS License Manager to monitor license usage under the BYOL model. License Manager integrates with AWS Systems Manager, helping discovery of any software installed on your AWS resources. You can configure License Manager to help you automatically track licenses of Oracle database engine editions, options, and packs as you grow your database footprint on RDS.

Sometimes, it can be challenging to decide whether or not a table should be populated into the IM column store, especially when you already have a number of tables in the IM store and you are limited by the memory available to your chosen instance type. It is then recommended to use memory optimized instance types which offer you a larger memory for the same number of vCPUs. For example, a general purpose db.m5.12xlarge offers 192 GiB memory with 48 vCPUs, whereas memory optimized db.r5.12xlarge offers 384 GB memory for the same 48 vCPUs. Memory optimized instances are designed to deliver fast performance for workloads that process large data sets in memory, thus being an ideal instance type for In-Memory workloads. The larger memory available with memory optimized instances would also save you from making a difficult choice to evict some of the pre-populated tables to make way for newer ones. Refer to Amazon RDS Oracle Instance types to find the available instance types and their configuration.

In the following sections, we discuss how to implement the Oracle Database In-Memory feature and showcase the benefits achieved across comparable Enterprise mixed workloads running on Amazon RDS for Oracle.

Environment setup

We have chosen a 19c (19.0.0.0.ru-2023-01.rur-2023-01. r1) Enterprise Edition Oracle database with a db.r5b.4xlarge instance class, which we are connecting using a SQL*Plus client installed on an Amazon Elastic Compute Cloud (Amazon EC2) instance. Complete the following steps to set up the environment:

1. Create a table named ORDERS_TAB in a schema named admin. This table contains customers and their order details along with the date or order, city, region, the supply cost, part ordered, and their supplier details, along with the discount and shipping mode.
2. Create a view named ORDERS_SUMMARY_V, which contains the order date, customer details, order details, and revenue generated.
3. Insert some random records into the table using a PL/SQL procedure and function, which loops over a random count and inserts a pre-defined array of records.

See the following:

SQL> select count (*) from ORDERS_TAB;
 
  COUNT(*)
   7934395
 
SQL>PROMPT ******************************************
PROMPT Show table size/usage
PROMPT ******************************************

col segment_name format a20;
col size heading 'Size (MB)' format  999,999,999;
select segment_name, bytes/1024/1024 "size" from user_segments
where segment_name = 'ORDERS_TAB';
 
SEGMENT_NAME     Size (MB)
-------------------- ------------
ORDERS_TAB           1,408

4. Edit the custom parameter group to set INMEMORY_SIZE to 11 GB and reboot the instance. The INMEMORY_SIZE of 11GB has been derived using the In Memory Area sizing guide as described above. The determined size is large enough to accommodate the tables which are being populated into the In Memory store.

Verify the inmemory_size parameter setting on the database.

SQL> show parameter inmemory_size
 
NAME            TYPE          VALUE
------------------------------------
inmemory_size   big integer   11G

SQL>PROMPT ******************************************
PROMPT Show size and populated status in memory
PROMPT ******************************************

col owner format a20;
col segment_name format a20;
col bytes format 999,999,999,999;
col inmemory_size format 999,999,999,999;
col bytes_not_populated heading 'BYTES NOT|POPULATED' format 999,999,999;
col inmemory_priority format a9
select
  owner,
  segment_name,
  bytes,
  populate_status as "POP STATUS",
  inmemory_size,
  bytes_not_populated,
  inmemory_priority
from
  v$im_segments
where
  segment_name in ('ORDERS_TAB')
order by
segment_name;

no rows selected
 
SQL> 

5. Populate the table using a full table scan hint. This is necessary because the table isn’t yet populated in the In-Memory Area.

If you choose to populate objects using a SELECT statement, it may not always force a full table scan; the optimizer may choose an index scan and the population won’t happen. Therefore, it’s recommended to use a /*FULL (table_name) NO_PARALLEL (table_name) */ hint as follows to force a full table scan:

select /*FULL (orders_tab) NO_PARALLEL(orders_tab) */ count(*) from orders_tab;

You can also populate the table In-Memory using the INMEMORY_POPULATE procedure as follows:

PROMPT ******************************************
PROMPT Populate ORDERS_TAB table
PROMPT ******************************************

ALTER TABLE orders_tab INMEMORY PRIORITY HIGH;

Table altered.

EXEC DBMS_INMEMORY.POPULATE(USER,'ORDERS_TAB');

PL/SQL procedure successfully completed.

6. You can now use the DBMS_INMEMORY_ADMIN.POPULATE_WAIT function to wait for IM population and capture the success or failure using the following PL/SQL block:

SET SERVEROUTPUT ON;
PROMPT 
PROMPT Wait for IM Population
PROMPT 
--
DECLARE
  --
  — populate_wait query
  --
  — Return code:
  —   -1 = POPULATE_TIMEOUT
  —    0 = POPULATE_SUCCESS
  —    1 = POPULATE_OUT_OF_MEMORY
  —    2 = POPULATE_NO_INMEMORY_OBJECTS
  —    3 = POPULATE_INMEMORY_SIZE_ZERO 
  --
  co_wait_timeout CONSTANT NUMBER := 3; — Wait up to 3 minutes
  co_priority     CONSTANT VARCHAR2(8) := 'HIGH';
  co_pop_percent  CONSTANT NUMBER := 100;
  co_pop_timeout  CONSTANT NUMBER := 60;
  --
  v_rc            NUMBER;
  v_wait          NUMBER := 0;
  v_done          BOOLEAN := FALSE;
  --
  POP_ERROR       EXCEPTION;
  PRAGMA EXCEPTION_INIT(POP_ERROR, -20000);
  POP_TIMEOUT     EXCEPTION;
  PRAGMA EXCEPTION_INIT(POP_TIMEOUT, -20010);
BEGIN
  WHILE NOT v_done AND v_wait <= co_wait_timeout LOOP
    select dbms_inmemory_admin.populate_wait(
      priority=>co_priority, percentage=>co_pop_percent, timeout=>co_pop_timeout )
    INTO v_rc
    from dual;
    --
    IF v_rc = 0 THEN
      v_done := TRUE;
    ELSIF v_rc = -1 THEN
      v_wait := v_wait + 1;
    ELSE
      RAISE_APPLICATION_ERROR(-20000, 'Error populating IM column store');
    END IF;
    --
    IF v_wait >= co_wait_timeout THEN
      RAISE_APPLICATION_ERROR(-20010, 'Timeout populating IM column store');
    END IF;
  END LOOP;
EXCEPTION
  WHEN POP_ERROR THEN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20000)));
    RAISE;
  WHEN POP_TIMEOUT THEN
    DBMS_OUTPUT.PUT_LINE(TO_CHAR(SQLERRM(-20010)));
    RAISE;
END;
/
PROMPT 
PROMPT In-memory population complete
PROMPT 

PL/SQL procedure successfully completed.

SQL> **********************************************
SQL> In-memory population complete
SQL> **********************************************
SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.18.0.0.0
[ec2-user@ip-xxx-xx-xx-xxx inmem]$

7. Check whether the table got populated in-memory:

PROMPT ******************************************
PROMPT Show size and populated status in memory
PROMPT *****************************************

col owner format a20;
col segment_name format a20;
col bytes format 999,999,999,999;
col inmemory_size format 999,999,999,999;
col bytes_not_populated heading 'BYTES NOT|POPULATED' format 999,999,999;
col inmemory_priority format a9
select
  owner,
  segment_name,
  bytes,
  populate_status as "POP STATUS",
  inmemory_size,
  bytes_not_populated,
  inmemory_priority
from
  v$im_segments
where
  segment_name in ('ORDERS_TAB')
order by
segment_name;

******************************************
SQL> Show size and populated status in memory
SQL> ******************************************

                                                             BYTES NOT
OWNER  SEGMENT_NAME  BYTES        POP STATUS  INMEMORY_SIZE  POPULATED INMEMORY_STATUS
------ ------------ ---------     ----------  -------------- --------- ---------------
ADMIN  ORDERS_TAB   1,470,988,288 COMPLETED   411,959,296       0           HIGH

Running enterprise workloads

SQL> set timing on
SQL> set echo on
SQL> alter session set statistics_level = all;
 
Session altered.
 
Elapsed: 00:00:00.00

SQL> alter session set inmemory_query = enable;

Session altered.

SQL> select
order_key, min(order_revenue)
from   orders_summary_v
where  order_key IN (  select order_key from ORDERS_TAB where line_supplycost = (select max(line_supplycost)
from  ORDERS_TAB where line_quantity > 10 and order_shipmode LIKE 'SHIP%' and order_discount between 5 and 8)
and order_shipmode LIKE 'SHIP%' and order_discount between 5 and 8 ) group by order_key;

ORDER_KEY MIN(ORDER_REVENUE)
---------- ------------------
1504170        828412365

Elapsed: 00:00:00.01

SQL> set lines 500 pages 500;
SQL> set echo off
SQL> set timing off
select * from table(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID    183svsjkyysc4, child number 0
----------------------------------------
select     order_key, min(order_revenue) from   orders_summary_v where 
order_key IN (    select order_key from ORDERS_TAB where line_supplycost = (select max(line_supplycost)
from  ORDERS_TAB where line_quantity > 10 and order_shipmode LIKE 'SHIP%' and order_discount between 5 and 8)
and order_shipmode LIKE 'SHIP%' and order_discount between 5 and 8) group by order_key

Plan hash value: 80309288

-------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | E-Rows |E-Bytes| Cost (%CPU)| E-Time    |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |    |    |  5872 (100)|        |
|   1 |  HASH GROUP BY              |           |      1 |    19 |  5872   (8)| 00:00:01 |
|   2 |   VIEW                  | VM_NWVW_1  |      1 |    19 |  5872   (8)| 00:00:01 |
|   3 |    HASH GROUP BY          |           |      1 |    76 |  5872   (8)| 00:00:01 |
|*  4 |     HASH JOIN              |           |      1 |    76 |  3933   (9)| 00:00:01 |
|   5 |      JOIN FILTER CREATE       | :BF0000    |      1 |    33 |  1945   (8)| 00:00:01 |
|*  6 |       TABLE ACCESS INMEMORY FULL  | ORDERS_TAB |      1 |    33 |  1945   (8)| 00:00:01 |
|   7 |        SORT AGGREGATE          |           |      1 |    18 |         |        |
|*  8 |     TABLE ACCESS INMEMORY FULL| ORDERS_TAB |    573K|     9M|  1937   (7)| 00:00:01 |
|   9 |      JOIN FILTER USE          | :BF0000    |   7934K|   325M|  1968   (9)| 00:00:01 |
|* 10 |       TABLE ACCESS INMEMORY FULL  | ORDERS_TAB |   7934K|   325M|  1968   (9)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

4 - access("ORDER_KEY"="ORDER_KEY")
6 - inmemory(("ORDER_DISCOUNT">=5 AND "ORDER_SHIPMODE" LIKE 'SHIP%' AND
           "ORDER_DISCOUNT"<=8 AND "LINE_SUPPLYCOST"=))
    filter(("ORDER_DISCOUNT">=5 AND "ORDER_SHIPMODE" LIKE 'SHIP%' AND 
           "ORDER_DISCOUNT"<=8 AND "LINE_SUPPLYCOST"=))
8 - inmemory(("ORDER_DISCOUNT">=5 AND "ORDER_SHIPMODE" LIKE 'SHIP%' AND
           "LINE_QUANTITY">10 AND "ORDER_DISCOUNT"<=8))
    filter(("ORDER_DISCOUNT">=5 AND "ORDER_SHIPMODE" LIKE 'SHIP%' AND
           "LINE_QUANTITY">10 AND "ORDER_DISCOUNT"<=8))
10 - inmemory(SYS_OP_BLOOM_FILTER(:BF0000,"ORDER_KEY"))
     filter(SYS_OP_BLOOM_FILTER(:BF0000,"ORDER_KEY"))
     

53 rows selected.

SQL> set timing on
SQL> set echo on
SQL> alter session set statistics_level = all;
 
Session altered.
 
Elapsed: 00:00:00.00

SQL> alter session set inmemory_query = disable;

Session altered.

SQL> select
order_key, min(order_revenue)
from   orders_summary_v
where  order_key IN (  select order_key from ORDERS_TAB where line_supplycost = (select max(line_supplycost)
from  ORDERS_TAB where line_quantity > 10 and order_shipmode LIKE 'SHIP%' and order_discount between 5 and 8)
and order_shipmode LIKE 'SHIP%' and order_discount between 5 and 8 ) group by order_key;

ORDER_KEY MIN(ORDER_REVENUE)
---------- ------------------
1504170        828412365

Elapsed: 00:00:02.49

SQL> set lines 500 pages 500;
SQL> set echo off
SQL> set timing off
select * from table(dbms_xplan.display_cursor(NULL, NULL, 'TYPICAL IOSTATS LAST'));

PLAN_TABLE_OUTPUT
----------------------------------------
SQL_ID    183svsjkyysc4, child number 1
----------------------------------------
select     order_key, min(order_revenue) from   orders_summary_v where 
order_key IN (    select order_key from ORDERS_TAB where line_supplycost = (select max(line_supplycost)
from  ORDERS_TAB where line_quantity > 10 and order_shipmode LIKE 'SHIP%' and order_discount between 5 and 8)
and order_shipmode LIKE 'SHIP%' and order_discount between 5 and 8) group by order_key


Plan hash value: 2606829073

---------------------------------------------------------------------------------------
| Id  | Operation        | Name         | E-Rows |E-Bytes| Cost (%CPU)| E-Time   |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |         |          |       |   146K(100)|          |
|   1 |  HASH GROUP BY        |         |        1 |    19 |   146K    (1)| 00:00:06 |
|   2 |   VIEW            | VM_NWVW_1  |        1 |    19 |   146K    (1)| 00:00:06 |
|   3 |    HASH GROUP BY    |         |        1 |    76 |   146K    (1)| 00:00:06 |
|*  4 |     HASH JOIN        |         |        1 |    76 | 97571    (1)| 00:00:04 |
|*  5 |      TABLE ACCESS FULL    | ORDERS_TAB |        1 |    33 | 48782    (1)| 00:00:02 |
|   6 |       SORT AGGREGATE    |         |        1 |    18 |        |          |
|*  7 |        TABLE ACCESS FULL| ORDERS_TAB |      573K|     9M| 48795    (1)| 00:00:02 |
|   8 |      TABLE ACCESS FULL    | ORDERS_TAB |     7934K|   325M| 48768    (1)| 00:00:02 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
--------------------------------------------------

4 - access("ORDER_KEY"="ORDER_KEY")
5 - filter(("ORDER_DISCOUNT">=5 AND "ORDER_SHIPMODE" LIKE 'SHIP%' AND
           "ORDER_DISCOUNT"<=8 AND "LINE_SUPPLYCOST"=))
7 - filter(("ORDER_DISCOUNT">=5 AND "ORDER_SHIPMODE" LIKE 'SHIP%' AND
           "LINE_QUANTITY">10 AND "ORDER_DISCOUNT"<=8))
           
45 rows selected.

**********************************************
Show table size/usage
**********************************************

SEGMENT_NAME        Size (MB)
-------------------- ------------
C_ORDER_LINE              816
C_CUSTOMER                752
C_ORDER                   47
C_ITEM                     9
C_NEW_ORDER                6
TPC_H_PROPERTIES           0
C_HISTORY                 72
C_WAREHOUSE                0
H_NATION                   0
TPC_C_LOAD_PROGRESS        0
TPC_H_LOAD_PROGRESS        0
C_DISTRICT                 0
C_STOCK                1,408
H_REGION                   0
TPC_C_BLOCK_INFO           0
TPC_C_PROPERTIES           0
TPC_H_BLOCK_INFO           0

17 rows selected.

**********************************************
Show size and populated status in memory
**********************************************
                                                                          BYTES NOT
OWNER    SEGMENT_NAME   BYTES/1024/1024 POP STATUS  INMEMORY_SIZE(MB)     POPULATED
-------------------- -------------------- --------------- ------------- -----------
ADMIN    C_CUSTOMER      748.257813    COMPLETED    733.625                    0
ADMIN C_DISTRICT      .1015625      COMPLETED         1.25                     0
ADMIN C_HISTORY       70.9140625    COMPLETED        33.625                    0
ADMIN C_ITEM          8.796875      COMPLETED         9.25                     0
ADMIN C_NEW_ORDER     5.84375       COMPLETED         2.25                     0
ADMIN C_ORDER         46.203125     COMPLETED         9.625                    0
ADMIN C_ORDER_LINE    812.007813    COMPLETED       253                        0
ADMIN C_STOCK         1402.84375    COMPLETED      1258.5625                   0
 
8 rows selected.