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;
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
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))
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.
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.
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
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".
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 Option | Build time (seconds) |
---|---|
QUERY LOW | 30 |
QUERY HIGH | 40 |
CAPACITY LOW | 33 |
CAPACITY HIGH | 67 |