Oracle 12c In-Memory

Oracle 12c In-Memory

Oracle introduced the In-Memory and columnar compression option In 12c, which greatly improves query performance.

Here, we can see how much performance can be improved in the same query through a comparison.

1. allocate Memory to In-Memory first
SQL> show parameter inmemory;
NAME TYPE VALUE
---------------------------------------------------------------------------------------------------
Inmemory_clause_default string
Inmemory_force string DEFAULT
Inmemory_max_populate_servers integer 0
Inmemory_query string ENABLE
Inmemory_size big integer 0
Inmemory_trickle_repopulate_servers _ integer 1
Percent
Optimizer_inmemory_aware boolean TRUE

2. INMEMORY_SIZE defines the in-memory size.
SQL> alter system set inmemory_size = 1000 m scope = spfile;
Connected:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0-64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Restart after modification.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup
ORACLE instance started.

Total System Global Area 5016387584 bytes
Fixed Size 3721128 bytes
Variable Size 1056966744 bytes
Database Buffers 3724541952 bytes
Redo Buffers 13053952 bytes
In-Memory Area 218103808 bytes
Database mounted.
Database opened.

 

3. Create a test table
SQL> create table bmw. t as select * from dba_objects;

Table created.

SQL> select TABLE_NAME, INMEMORY_PRIORITY, inmemory_distriity, INMEMORY_COMPRESSION from dba_tables where table_name = 'T ';

TABLE_NAME INMEMORY_PRIORITY INMEMORY_DISTRIBUTE INMEMORY_COMPRESSION
----------------------------------------------------------------------------------------------------------------------------------
T

We can see that USED_BYTES is not actually allocated.
SQL> l
1 * SELECT * from v $ INMEMORY_AREA
SQL>/

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-----------------------------------------------------------------------
1 mb pool 837812224 0 DONE 3
64KB POOL 201326592 0 DONE 3

 

4. Check the query when In-Memory is not used.
SQL> set autot trace
SQL> SELECT * FROM bmw. t;
90927 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
--------------------------------------------------------------------------
| 0 | select statement | 90927 | 9M | 416 (1) | 00:00:01 |
| 1 | table access full | T | 90927 | 9M | 416 (1) | 00:00:01 |
--------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5 recursive cballs
0 db block gets
7487 consistent gets
1525 physical reads
0 redo size
12128303 bytes sent via SQL * Net to client
67223 bytes encoded ed via SQL * Net from client
6063 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90927 rows processed

In the execution plan, we can also see that the use of table access full scan, consistent gets also reached 7487.

5. Put the table In-Memory
SQL> set autot off
SQL> alter table bmw. t inmemory;

Table altered.

SQL> select TABLE_NAME, INMEMORY_PRIORITY, inmemory_distriity, INMEMORY_COMPRESSION from dba_tables where table_name = 'T ';

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

6. The table is not actually stored In-Memory before it is queried.
SQL> SELECT * FROM V $ INMEMORY_AREA;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-----------------------------------------------------------------------
1 mb pool 837812224 0 DONE 3
64KB POOL 201326592 0 DONE 3

SQL> select count (*) from bmw. t;

COUNT (*)
----------
90927

7. After one query, we can see that USED_BYTES has been allocated memory.
SQL> SELECT * FROM V $ INMEMORY_AREA;

POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID
-----------------------------------------------------------------------
1 mb pool 837812224 4194304 DONE 3
64KB POOL 201326592 131072 DONE 3

8. Check the execution plan.
SQL> set autot trace
SQL> SELECT * FROM bmw. t;
90927 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873

Bytes -----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (% CPU) | Time |
Bytes -----------------------------------------------------------------------------------
| 0 | select statement | 90927 | 9M | 20 (20) | 00:00:01 |
| 1 | table access inmemory full | T | 90927 | 9M | 20 (20) | 00:00:01 |
Bytes -----------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
5 recursive cballs
0 db block gets
5 consistent gets
0 physical reads
0 redo size
4944325 bytes sent via SQL * Net to client
67223 bytes encoded ed via SQL * Net from client
6063 SQL * Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
90927 rows processed

Here we can see that the table access inmemory full scan is used, and the consistent gets is 5. We can see that INMEMORY will bring a very high performance improvement to the query.