Oracle 12c In-Memory in Tablespaces

Oracle 12c In-Memory in Tablespaces

As mentioned In the previous article, you can enable In-Memory at the table level. In fact, you can also enable Tablespaces at Tablespaces level, in-Memory is also enabled for all tables stored In Tablespaces. Let's test it.

1. Create a tablespace and specify the INMEMORY
SQL> CREATE TABLESPACE tbs1 datafile 'tbs1' SIZE 40 M DEFAULT INMEMORY;

2. Create a table on the newly created tablespace without specifying INMEMORY.
SQL> create table bmw. tt tablespace tbs1 as select * from dba_objects;
Table created.

3. We can see that all newly created tables have INMEMORY enabled, because we have enabled INMEMORY at the tablespace level.
SQL> l
1 * select TABLE_NAME, INMEMORY_PRIORITY, inmemory_distriity, INMEMORY_COMPRESSION from dba_tables where table_name = 'TT'
SQL>/

TABLE_NAME INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION
-----------------------------------------------------------------------------------------------------------------------
TT NONE AUTO FOR QUERY LOW

SQL> l
1 SELECT * from v $ INMEMORY_AREA
2 *
SQL>/
POOL ALLOC_BYTES USED_BYTES POPULATE_S CON_ID
---------------------------------------------------
1 mb pool 837812224 4194304 DONE 3
64KB POOL 201326592 131072 DONE 3

When the table is not queried, we can see that no memory is allocated.

4. query the table
SQL> set autot trace
SQL> SELECT * FROM bmw. tt;
90935 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
Bytes -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------
| 0 | select statement | 90935 | 9M | 32 (16) | 00:00:01 |
| 1 | table access inmemory full | TT | 90935 | 9M | 32 (16) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
6 recursive cballs
0 db block gets
7485 consistent gets
1527 physical reads
0 redo size
4945264 bytes sent via SQL * Net to client
67234 bytes encoded ed via SQL * Net from client
6064 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90935 rows processed

Although table access inmemory full is used, a large number of consistent gets are generated because the TABLE is not in the memory.

5. query and test again. We can see that the consistent gets is reduced to 3.
SQL>/
90935 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 264906180
Bytes -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------
| 0 | select statement | 90935 | 9M | 32 (16) | 00:00:01 |
| 1 | table access inmemory full | TT | 90935 | 9M | 32 (16) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive cballs
0 db block gets
3 consistent gets
0 physical reads
0 redo size
4945264 bytes sent via SQL * Net to client
67234 bytes encoded ed via SQL * Net from client
6064 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90935 rows processed
SQL> set autot off
SQL> SELECT * FROM V $ INMEMORY_AREA;
POOL ALLOC_BYTES USED_BYTES POPULATE_S CON_ID
---------------------------------------------------
1 mb pool 837812224 8388608 DONE 3
64KB POOL 201326592 262144 DONE 3

You can also view the allocated memory in the V $ INMEMORY_AREA view.

If you cancel the INMEMORY at the TABLESPACE level, you can use the alter tablespace tbsname no inmemory clause.