“What performance benefit can I get with Oracle Database In-Memory?”

Users may ask this question when evaluating whether Database In-Memory could add value to their workload. In-Memory is still an important topic, and Oracle provides Real-Time Analytics out-of-the-box with Oracle Database In-Memory, thereby greatly accelerating analytical queries. Oracle Database In-Memory is also available to users in the free Oracle Database Express Edition (XE). A total of 2 GB of main memory is available for the SGA & PGA. Nothing stands in the way of getting started.

Just try out this simple example yourself

The following example illustrates the benefits of Oracle Database In-Memory in conjunction with Oracle SQL Developer, which is also available free of charge.

You can install Oracle XE 21c under Windows 10 Professional in just a few minutes. The next step is to adjust the init.ora parameters SGA_TARGET and PGA_TARGET. To do this, connect to the CDB as DBA. Note: Windows Home Edition is not supported and there is also a version of XE available on Linux.

After setting the init.ora parameter INMEMORY_SIZE to 1 GB, the database needs to be restarted.

-- Connect to CDB as DBA
connect / as sysdba 
-- Edit following init.ora parameters
alter system set sga_target=1568M scope=spfile; 
alter system set pga_aggregate_target=412M scope=spfile; 
alter system set inmemory_size=1G scope=spfile; 
shutdown immediate; 
startup;

Please note a new pool is available called the In-Memory Area within the SGA.

You can easily build your own environment with the dump file and the script containing the required steps available for download under the links section below. You can use the script to create a tablespace, create the user SSB, import the dump file with the sample data and update the table statistics. The sample data completely fits into the 1 GB sized In-Memory Column Store.

The SSB schema includes the fact table LINEORDER (11,997,996 rows). The dimension tables CUSTOMER (60,000 rows), DATE_DIM (2556 rows), PART (400,000 rows) and SUPPLIER (4,000 rows) are also included. The tables are base compressed and require approximately 1.3 GB of disk space and 590 MB of space in the In-Memory Column Store. In the In-Memory Column Store, the tables are compressed with the default compression MEMCOMPRESS FOR QUERY LOW.

To add objects to the IM column store the INMEMORY attribute must be set.
By default the INMEMORY PRIORITY is set to NONE, which means Oracle automatically decides when to populate the table into the IM
column store. This is also referred to as “on demand” as in this case Oracle typically populates the table after it has been accessed for the first time.
Alternatively, a priority level can be set which queues the population of the table into the IM column store immediately, e.g. INMEMORY PRIORITY CRITICAL or HIGH.

-- "On demand" Population - Default INMEMORY PRIORITY is set to NONE

alter table CUSTOMER inmemory;
alter table DATE_DIM inmemory;
alter table LINEORDER inmemory;
alter table PART inmemory;
alter table SUPPLIER inmemory;

-- INMEMORY PRIORITY is set to CRITICAL and HIGH

alter table CUSTOMER inmemory priority high;
alter table DATE_DIM inmemory priority high;
alter table LINEORDER inmemory priority critical;
alter table PART inmemory priority high;
alter table SUPPLIER inmemory priority high;

Let’s run the following analytical query in Oracle SQL Developer. You can easily compare the performance improvement of the In-Memory query with the one against the buffer cache. Just mark the buffer cache query with the NO_INMEMORY hint.

-- Run Analytical Query against In-Memory Column Store

select /*+ INMEMORY */ sum(lo_extendedprice * lo_discount) revenue
from
  LINEORDER l,
  DATE_DIM d
where
  l.lo_orderdate = d.d_datekey
  and l.lo_discount between 2 and 3
  and l.lo_quantity < 24
  and d.d_date='December 24, 1996';

Elapsed: 0.009 seconds

-- Run Analytical Query against buffer cache/row store 

select /*+ NO_INMEMORY */ sum(lo_extendedprice * lo_discount) revenue 
from 
  LINEORDER l,
  DATE_DIM d
where
  l.lo_orderdate = d.d_datekey
  and l.lo_discount between 2 and 3
  and l.lo_quantity < 24
  and d.d_date='December 24, 1996';

Elapsed: 0.868 seconds

In SQL Developer, the actual execution plans for in-memory and buffer cache queries are generated by using the Autotrace button (or by pressing the F6 function key). The pin function (red pin icon) is helpful and can be used to make both plans more comparable.

On the left is the in-memory plan with a bloom filter (:BF0000) generated from the D_DATE column of the DATE_DIM table and applied when scanning and filtering the LINEORDER table.

The buffer cache plan is available on the right-hand side, in which the tables DATE_DIM and LINEORDER are each processed using a full-table scan and a hash join.

The session statistics when executing Autotrace are also included in the output below the execution plan. These session statistics can also be pinned and compared with each other via “Compare with…”.

You can filter for the in-memory statistics. Just enter “IM”:

On the other hand, physical read statistics can be filtered by entering “ph”:

Conclusion

The SQL statement in this example is ideal for in-memory processing. The execution plans and session statistics demonstrate that the complete processing regarding scanning and filtering of the data takes place in the IM Column Store without the need for physical I/O. Even in this example with small amounts of data, the response time can be reduced considerably by using Database In-Memory and the In-Memory Column Store (In-Memory 0.009 seconds vs. buffer cache 0.868 seconds).