Testing Oracle 12.1.0.2 "in-memory" feature

As some may know Oracle resently realised RDBMS 12.1.0.2 version, which features, among others, IN-MEMORY option. So I disided to test how it works, and what benefits would it give if deployed to production.

TestLab configuration

On host with VMWare ESXi 5.0 I created virtual machine of 4 cpu cores and 8Gb RAM. Operating system used was Oracle Linux 6.5.
Database server - ofcourse Oracle EE 12.1.0.2

Instance was configured with following parameters:

sga_max_size                         big integer 6G
sga_target                           big integer 6G
pga_aggregate_limit                  big integer 1057M
pga_aggregate_target                 big integer 800M
inmemory_size                        big integer 512M

HugePages was enabled for this installation.

Configure in-memory

ALTER SYSTEM SET INMEMORY_SIZE=512M SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP;

Total System Global Area 6442450944 bytes

Fixed Size                  2938792 bytes

Variable Size            1157630040 bytes

Database Buffers         4731174912 bytes

Redo Buffers               13836288 bytes

In-Memory Area            536870912 bytes

Database mounted.

Database opened.

Createing test user and tables

First of all I need to create some tables to do full scans on.

GRANT DBA TO DBAX IDENTIFIED BY DBAX;

Granting DBA to my test user, and no worries about futher permissions.

CONNECT DBAX/DBAX

Now I'll create two tables one with inmemory enabled and another - traditional table.

CREATE TABLE IMMEM AS SELECT * FROM DBA_TABLES;

BEGIN

FOR I IN 1..1000

LOOP

INSERT INTO INMEM (SELECT * FROM DBA_TABLES);

END LOOP;

COMMIT;

END;

ALTER TABLE INMEM INMEMORY;

CREATE TABLE NOMEM AS SELECT * FROM INMEM;

SET TIMI ON
SET AUTOTRACE ON

Now I'm going to run analitycal query as this is what Oracle declares in-memory feature's main purpose.

Test Traditional table

SELECT COUNT (*) 
            FROM (
SELECT OWNER,SUM(NUM_ROWS) OVER(PARTITION BY OWNER) 
            FROM DBAX.NOMEM 
            WHERE OWNER = 'DBAX');

  COUNT(*)

----------

      2006

 

Elapsed: 00:00:04.79

Explain plan:
-----------------------------------------------------------------
| Id  | Operation          | Name  | Rows  | Bytes | Cost (%CPU)| Time  |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |     1 |     8 | 29742   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE    |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS FULL| NOMEM |  2006 | 16840 | 29742   (1)| 00:00:02 |
-----------------------------------------------------------------

Test In-memory table

SELECT COUNT (*) 
            FROM (
            SELECT OWNER,SUM(NUM_ROWS) OVER(PARTITION BY OWNER)               FOM DBAX.INMEM
            WHERE OWNER = 'DBAX');

  COUNT(*)

----------

      2006

Elapsed: 00:00:00.04

Explain plan:
-----------------------------------------------------------------
| Id  | Operation                   | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------
|   0 | SELECT STATEMENT            |       |     1 |     8 |  1150   (5)| 00:00:01 |
|   1 |  SORT AGGREGATE             |       |     1 |     8 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| INMEM |  2006 | 16840 |  1150   (5)| 00:00:01 |

-----------------------------------------------------------------

As we may see results are speacking for themselfes.

Test In-memory compression

Inmemory table is created by default with "MEMCOMPRESS FOR QUERY LOW" In-memory data compression. Lets test more radical option whish is going to save us a lot fo RAM space, but would lower performance.

CREATE TABLE INMEM2 AS SELECT * FROM INMEM;

ALTER TABLE DBAX.INMEM2 INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

SELECT COUNT (*) 
             FROM (
             SELECT OWNER,SUM(NUM_ROWS) OVER(PARTITION BY OWNER)              FROM DBAX.INMEM 
             WHERE OWNER = 'DBAX');

  COUNT(*)

----------

      2006

Elapsed: 00:00:00.11

As I presumed it took more time to process my query, by this time is still much lower comparing whith traditional table.

Now lets what benefits did we got from high level of compression.

SELECT V.OWNER,
       V.SEGMENT_NAME, 

       V.BYTES ORIG_SIZE, 

       V.INMEMORY_SIZE IN_MEM_SIZE, 

       V.BYTES / V.INMEMORY_SIZE COMP_RATIO

FROM V$IM_SEGMENTS V;

OWNER SEGMENT_NAME ORIG_SIZE IN_MEM_SIZE COMP_RATIO

-----------------------------------------------------------------

DBAX  INMEM        905969664 45547520    19.8906475

                                                

DBAX  INMEM2       900726784 7798784     115.495798

It' almost 6 time less space against default compression.

 I ran  all tests several times and results were the same (add or remove several centiseconds)

Nota bene:

After first table access it can take up to half an hour in my environment to populate inmemory segments. During this time running query will take almost same time as traditional table query, but explain plan will still claim INMEMORY access.

Progress of inmemory segments population can be observed by queriing POPULATE_STATUS from:
SELECT * FROM V$IM_SEGMENTS;
and
SELECT * FROM V$INMEMORY_AREA;

We can force inmemory segments population on instance startup by setting INMEMORY PRIORITY for our table (default is NONE).