Oracle 12.1.0.2 In Memory Option

Test data setup

To see the impact on plans of using the new Oracle 12.1.0.2 in-memory option, I created the following two tables. One tradition table with a primary key, and the second table with the same structure, but with an INMEMORY option specified.

CREATE TABLE im_test_no_im (
  id        NUMBER NOT NULL,
  num       NUMBER NOT NULL,
  num_loop  NUMBER NOT NULL,
  num_clust NUMBER NOT NULL,
  CONSTRAINT im_test_no_im_pk PRIMARY KEY (id)
);

CREATE TABLE im_test_all_im (
  id        NUMBER NOT NULL,
  num       NUMBER NOT NULL,
  num_loop  NUMBER NOT NULL,
  num_clust NUMBER NOT NULL,
  CONSTRAINT im_test_all_im_pk PRIMARY KEY (id)
) INMEMORY MEMCOMPRESS FOR QUERY LOW; 

I then populated the two tables with the following (in Oracle 12, INSERT APPEND into an empty table causes table statistics to be generated as part of the INSERT, saving the need for a subsequent DBMS_STATS.gather_ package call).

INSERT /*+ APPEND */
INTO   im_test_no_im
  SELECT rownum, rownum, MOD(rownum, 1000), TRUNC(rownum - 1)
  FROM   buncha_numbers
  WHERE  rownum <= 1000000;
COMMIT;

INSERT /*+ APPEND */
INTO   im_test_all_im
  SELECT rownum, rownum, MOD(rownum, 1000), TRUNC(rownum - 1)
  FROM   buncha_numbers
  WHERE  rownum <= 1000000;
COMMIT; 

Plan Change

Following the creation and population of these two tables, the table IM_TEST_ALL_IM will not be in memory. It takes the first query of the table which would have used the in-memory information, had it been there, to trigger Oracle's KTSJ process to start loading the table into memory (or an INMEMORY PRIORITY to be specified for table). Once the table had been loaded into memory, queries on an un-index column showed two plan changes relative to the non in-memory table

  1. The TABLE ACCESS FULL plan line was replaced by a TABLE ACCESS INMEMORY FULL (with a much lower associated cost), and
  2. a new inmemory filter line appeared in the plan Predicate Information
SELECT *
FROM   im_test_no_im
WHERE  num_loop BETWEEN 555 AND 560;

use

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  7g4jrrr3hp76s, child number 0
-------------------------------------
SELECT * FROM   im_test_no_im WHERE  num_loop BETWEEN 555 AND 560

Plan hash value: 3519535333

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |       |       |   920 (100)|          |
|*  1 |  TABLE ACCESS FULL| IM_TEST_NO_IM |  7005 |   129K|   920   (1)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - filter(("NUM_LOOP">=555 AND "NUM_LOOP"<=560))

changed to

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  0h1jp55xydtjq, child number 0
-------------------------------------
SELECT * FROM   im_test_all_im WHERE  num_loop BETWEEN 555 AND 560

Plan hash value: 1254469180

---------------------------------------------------------------------------------------------
| Id  | Operation                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                |       |       |    66 (100)|          |
|*  1 |  TABLE ACCESS INMEMORY FULL| IM_TEST_ALL_IM |  7005 |   129K|    66  (20)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - inmemory(("NUM_LOOP">=555 AND "NUM_LOOP"<=560))
       filter(("NUM_LOOP">=555 AND "NUM_LOOP"<=560))

InMemory Hint

As far as I have found so far, for a table which is not undergoing any updates, Oracle always appears to make a sensible decision as to whether or not to use the in-memory data. This decision is simplied by Oracle not being able to use the in-memory data as an extra index back into the table data (see below for more information on this).

However, in order to pursuade Oracle to use the in-memory information when it would not otherwise do so, the FULL hint can be used. The script below shows an example of this, including the increased cost of the CPU associated with the in-memory option over the traditional index range scan.

SQL> SELECT * FROM table(DBMS_XPLAN.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  ab39t7zz5kz0g, child number 0
-------------------------------------
SELECT count(*) FROM   im_test_all_im WHERE  id BETWEEN 555 AND 560

Plan hash value: 2784577780

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |       |       |     2 (100)|          |
|   1 |  SORT AGGREGATE   |                   |     1 |     5 |            |          |
|*  2 |   INDEX RANGE SCAN| IM_TEST_ALL_IM_PK |     7 |    35 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   2 - access("ID">=555 AND "ID"<=560)

19 rows selected.


SQL> SELECT /*+ full(a) */ count(*)
  2  FROM   im_test_all_im a
  3  WHERE  id BETWEEN 555 AND 560;

  COUNT(*)
----------
         6

SQL> SELECT * FROM table(DBMS_XPLAN.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  77nx376gzxsz6, child number 0
-------------------------------------
SELECT /*+ full(a) */ count(*) FROM   im_test_all_im a WHERE  id
BETWEEN 555 AND 560

Plan hash value: 3135337577

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |       |       |    56 (100)|          |
|   1 |  SORT AGGREGATE             |                |     1 |     5 |            |          |
|*  2 |   TABLE ACCESS INMEMORY FULL| IM_TEST_ALL_IM |     7 |    35 |    56   (6)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - inmemory(("ID"<=560 AND "ID">=555))
       filter(("ID"<=560 AND "ID">=555))

21 rows selected.

Hook back to disk data

There appears to be no hook back to table data on disk (or in the buffer cache) from in-memory data. So it is not possible to use the FULL hint on a table which only has some of its columns in-memory to obtain a more performant access path to all the table data.

The script below shows a table with the same structure as above, but with only the primary key and one other field held in-memory. Attempts to use the in-memory data to access all data for the result set prove fruitless. Using the test table below.

CREATE TABLE im_test_id_num_im (
  id        NUMBER NOT NULL,
  num       NUMBER NOT NULL,
  num_loop  NUMBER NOT NULL,
  num_clust NUMBER NOT NULL,
  CONSTRAINT im_test_id_num_im_pk PRIMARY KEY (id)
) INMEMORY;
ALTER TABLE im_test_id_num_im INMEMORY MEMCOMPRESS FOR QUERY LOW (id, num)
	                               NO INMEMORY (num_loop, num_clust);

INSERT /*+ APPEND */ INTO im_test_id_num_im SELECT * FROM im_test_no_im;
COMMIT; 

Then wait until the data has been loaded into memory (requisite quieres and elapse time not shown here, but waiting until the record for the table IM_TEST_ID_NUM_IM in V$IM_USER_SEGMENTS has a POPULATE_STATUS of 'COMPLETED'). Then attempts to query the table using an un-indexed, but in-memory column result in the whole table being full scanned, the TABLE ACCESS FULL in the plan below never changed to a TABLE ACCESS INMEMORY FULL.

SQL> EXPLAIN PLAN
  2  FOR
  3    SELECT *
  4    FROM   im_test_id_num_im
  5    WHERE  num BETWEEN 555 AND 560;

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2772065872

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     7 |   133 |   919   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| IM_TEST_ID_NUM_IM |     7 |   133 |   919   (1)| 00:00:01 |
---------------------------------------------------------------------------------------

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

   1 - filter("NUM"<=560 AND "NUM">=555)

13 rows selected.  

Using the in-memory data as an index

Wanting to use the in-memory column data to avoid a table full scan, I tried various hints, and couldn't pursuade Oracle to use the rowid held in-memory to access the table columns which were not held in memory. The solution I came up with is to quote the table twice, connected by rowid (or an indexed column). In the example below, I used the rowid. The purpose of the two instances of the table are

  1. the table instance in the sub-query is designed to use the in-memory data to identify the rowid(s) of the result set row(s), and
  2. the main query table instance uses the rowid from the sub-query to access all the table row data (including the columns which are not held in-memory).

Some function like "|| NULL" below is necessary to avoid Oracle optimising away the second instance of the table, and simplifying the query to a single table,..,. and then proceeding to full scan the table (not in-memory!).

SQL> EXPLAIN PLAN
  2  FOR
  3    SELECT *
  4    FROM   im_test_id_num_im
  5    WHERE  rowid IN (SELECT rowid || NULL
  6                     FROM   im_test_id_num_im
  7                     WHERE  num BETWEEN 555 AND 560);

Explained.

SQL> SELECT * FROM table(DBMS_XPLAN.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------
Plan hash value: 1269754241

--------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   | 70000 |  2460K|    46  (18)| 00:00:01 |
|   1 |  NESTED LOOPS                |                   | 70000 |  2460K|    46  (18)| 00:00:01 |
|   2 |   SORT UNIQUE                |                   |     7 |   119 |    41  (18)| 00:00:01 |
|*  3 |    TABLE ACCESS INMEMORY FULL| IM_TEST_ID_NUM_IM |     7 |   119 |    41  (18)| 00:00:01 |
|*  4 |   TABLE ACCESS BY USER ROWID | IM_TEST_ID_NUM_IM | 10000 |   185K|     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

   3 - inmemory("NUM"<=560 AND "NUM">=555)
       filter("NUM"<=560 AND "NUM">=555)
   4 - access(CHARTOROWID(ROWIDTOCHAR(ROWID)||NULL))

Note
-----
   - this is an adaptive plan

 22 rows selected 

You might notice that the plan above is marked at adaptive, using SELECT * FROM table(DBMS_XPLAN.display(format => '+adaptive')); reveals that it is the plan row with id 4 which is considered adaptive (and could potentially be replaced with a TABLE ACCESS FULL at run time).

So obviously the above approach should only be taken if the expected result set is "small".

In-Memory build time

After creating four in-memory tables, with the four compression levels, I queried V$ACTIVE_SESSION_HISTORY to identify the effort require to build the in-memory columnar stores thus

SELECT object_name, count(*)
FROM   v$active_session_history
JOIN   all_objects
ON     object_id = current_obj#
WHERE  module = 'KTSJ'
AND    object_name LIKE 'IM_TEST_%'
GROUP BY object_name
ORDER BY object_name;

This yielded the following build times, where each of the test tables contained one million records.
Compression OptionBuild time (seconds)
QUERY LOW30
QUERY HIGH40
CAPACITY LOW33
CAPACITY HIGH67