ORACLE 12C in-memory Performance test notes

Enable In-memory Features
Database version 12.1.0.2 and above, inmemory_size parameter set to appropriate value
Sql> SELECT * from V$version;

BANNER con_id
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition release 12.1.0.2.0-64bit Beta 0
Pl/sql Release 12.1.0.2.0-beta 0
CORE 12.1.0.2.0 Beta 0
TNS for linux:version 12.1.0.2.0-beta 0
Nlsrtl Version 12.1.0.2.0-beta 0

Sql> show PARAMETER inmemory;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
Inmemory_clause_default string
Inmemory_force string DEFAULT
Inmemory_query string ENABLE
Inmemory_size Big Integer 200M
Create a table
As you can see, creating a table size of 13631488, but not using the In-memory feature
Sql> CREATE TABLE T_xifenfei_in_memory as SELECT * from Dba_objects;

Table created.

Sql> SELECT BYTES from user_segments WHERE segment_name= ' t_xifenfei_in_memory ';

BYTES
----------
13631488

Sql> select Table_name,inmemory_priority,inmemory_distribute,inmemory_compression from User_tables;

table_name InMemory Inmemory_distri inmemory_compress
------------------------------ -------- --------------- -----------------
T_xifenfei_in_memory

Sql> SELECT * from V$inmemory_area;

POOL alloc_bytes used_bytes populate_status con_id
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL 166723584 0 Done 3
64KB POOL 33554432 0 Done 3
In-memory feature test not used
sql> SET Autot TRACE
Sql> SELECT * from T_xifenfei_in_memory;

90902 rows selected.


Execution Plan
----------------------------------------------------------
Plan Hash value:3598036702

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
------------------------------------------------------------------------------------------
| 0 |                      SELECT STATEMENT | |     90902 |   9m| 427 (1) | 00:00:01 |
|  1 | TABLE ACCESS full| T_xifenfei_in_memory |     90902 |   9m| 427 (1) | 00:00:01 |
------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
5 Recursive calls
0 db Block gets
7505 consistent gets
1527 physical Reads
0 Redo Size
12125231 Bytes sent via sql*net to client
67212 bytes received via sql*net from client
6062 sql*net roundtrips To/from Client
2 Sorts (memory)
0 Sorts (disk)
90902 rows processed
Here you can see that the In-memory feature is not used, the database query execution plan uses table ACCESS full,consistent gets is 7505

Using the In-memory function test
sql> ALTER TABLE t_xifenfei_in_memory inmemory;

Table altered.

Sql> select Table_name,inmemory_priority,inmemory_distribute,inmemory_compression from User_tables;

table_name InMemory Inmemory_distri inmemory_compress
------------------------------ -------- --------------- -----------------
T_xifenfei_in_memory NONE AUTO Distribute for QUERY

-Because the table is set inmemory but not queried, the associated memory is not used in query V$inmemory_area
Sql> SELECT * from V$inmemory_area;

POOL alloc_bytes used_bytes populate_status con_id
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL 166723584 0 Done 3
64KB POOL 33554432 0 Done 3

--Perform a full table scan
Sql> SELECT COUNT (*) from t_xifenfei_in_memory;

COUNT (*)
----------
90902

--See again, the memory in the allocated in-memory has been used
Sql> SELECT * from V$inmemory_area;

POOL alloc_bytes used_bytes populate_status con_id
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL 166723584 4194304 done 3
64KB POOL 33554432 131072 done 3

sql> SET Autot TRACE
Sql> SELECT * from T_xifenfei_in_memory;

90902 rows selected.

Execution Plan
----------------------------------------------------------
Plan Hash value:3598036702

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU) | Time |
---------------------------------------------------------------------------------------------------
| 0 |                      SELECT STATEMENT | |     90902 |    9m| 20 (45) | 00:00:01 |
|  1 | TABLE ACCESS inmemory full| T_xifenfei_in_memory |     90902 |    9m| 20 (45) | 00:00:01 |
---------------------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
3 Recursive calls
0 db Block gets
4 consistent gets
0 physical Reads
0 Redo Size
4946298 Bytes sent via sql*net to client
67212 bytes received via sql*net from client
6062 sql*net roundtrips To/from Client
0 Sorts (memory)
0 Sorts (disk)
90902 rows processed
Here we can find that after using the In-memory feature, the database consistent gets is 4, and the performance is at least 2000 times times higher than the 7505 before the in-memory.

Original from: http://www.xifenfei.com/5266.html