ORACLE 12C in-memory Performance test

1. View Oracle Version

Sql> select * from V$version; bannercon_id-------------------------------------------------------------------------------------------------- --oracle Database 12c Enterprise Edition release 12.1.0.2.0-64bit production0pl/sql release 12.1.0.2.0-production0core 12.1.0.2.0production0tns for linux:version 12.1.0.2.0-production0nlsrtl Version 12.1.0.2.0-production0sql> show PD bscon_id con_nameopen MODE RESTRICTED------------------------------------------------------------2 pdb$seedread Only NO3 Pdb01read WRITE nosql> alter session set CONTAINER=PDB01; Session altered. Sql> show Con_name; Con_name------------------------------PDB01

　　

2. See if InMemory is enabled

Sql> Show parameter Inmemorynametypevalue--------------------------------------------------------------------- --------Inmemory_clause_default Stringinmemory_forcestringdefaultinmemory_max_populate_serversinteger2inmemory_ Querystringenableinmemory_sizebig integer 208minmemory_trickle_repopulate_servers_ integer1percentoptimizer_ Inmemory_awarebooleantrue

　　

3. Create a test table

Sql> conn keven/kevenconnected.sql> CREATE table T1 as select * from Dba_objects; Sql> CREATE TABLE t2 as select * from Dba_objects; Sql> COL table_name for a40sql> select * from V$inmemory_area; Poolalloc_bytes used_bytes populate_statuscon_id---------------------------------------------------------------- -------------------1MB POOL1667235844194304 done164kb POOL33554432131072 done1sql> Select Table_name,inmemory, Inmemory_priority,inmemory_distribute,inmemory_compression,inmemory_duplicate from User_tables; Table_nameinmemory INMEMORY Inmemory_distri inmemory_compress inmemory_ DUPL-----------------------------------------------------------------------------------------------------  T2disabledt1enabled Noneauto for QUERY low NO duplicateelapsed:00:00:00.04sql> set Autot tracesql> SQL> SELECT * From t2;91040 rows selected. Elapsed:00:00:01.22execution Plan----------------------------------------------------------Plan hash Value: 1513984157--------------------------------------------------------------------------| Id | operation| Name | Rows | Bytes | Cost (%CPU) | time|--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 91040 | 9m| 426 (1) | 00:00:01 | | 1 | TABLE ACCESS full| t2| 91040 | 9m| 426 (1) | 00:00:01 |-------------------------------------------------------------------------- Statistics----------------------------------------------------------Recursive CALLS0 db block gets7569 consistent gets1529 physical reads0 Redo size12149139 Bytes sent via sql*net to client67311 bytes received via sql*net from client607 1 sql*net roundtrips to/from client4 sorts (memory) 0 sorts (disk) 91040 rows processedsql>/91040 rows selected. Elapsed:00:00:01.22execution Plan----------------------------------------------------------Plan hash Value: 1513984157--------------------------------------------------------------------------| Id | operation| Name | Rows | Bytes | Cost (%CPU) | time|--------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 91040 | 9m| 426 (1) | 00:00:01 | | 1 | TABLE ACCESS full| t2| 91040 | 9m| 426 (1) | 00:00:01 |-------------------------------------------------------------------------- Statistics----------------------------------------------------------0 Recursive CALLS0 db block gets7497 consistent gets1529 physical reads0 Redo size12149139 Bytes sent via sql*net to client67311 bytes received via sql*net from client607 1 sql*net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 91040 rows processedsql> alter TABLE T2 inmemory; Table altered. Elapsed:00:00:00.03sql> SELECT * from t2;91040 rows selected. Elapsed:00:00:01.32execution Plan----------------------------------------------------------Plan hash Value: 1513984157-----------------------------------------------------------------------------------| Id | operation| Name | Rows | Bytes | Cost (%CPU) | time|-----------------------------------------------------------------------------------| 0 | SELECT statement| | 91040 |9m| 20 (20) | 00:00:01 | | 1 | TABLE ACCESS INMEMORY full| T2 | 91040 |9m| 20 (20) | 00:00:01 |----------------------------------------------------------------------------------- Statistics----------------------------------------------------------9 Recursive CALLS0 db block gets7507 consistent  gets1529 physical reads0 Redo size4980841 Bytes sent via sql*net to client67311 bytes received via sql*net from client6071 Sql*net roundtrips to/from Client2 sorts (memory) 0 sorts (disk) 91040 rows processedsql>/91040 rows selected. Elapsed:00:00:01.12execution Plan----------------------------------------------------------Plan hash Value: 1513984157-----------------------------------------------------------------------------------| Id | operation| Name | Rows | Bytes | Cost (%CPU) | time|-----------------------------------------------------------------------------------| 0 | SELECT statement| | 91040 |9m| 32 (16) | 00:00:01 | | 1 | TABLE ACCESS INMEMORY full| T2 | 91040 |9m| 32 (16) | 00:00:01 |----------------------------------------------------------------------------------- Statistics----------------------------------------------------------0 Recursive CALLS0 db block gets3 consistent Gets0 physical reads0 Redo size4980841 Bytes sent via sql*net to client67311 bytes received via sql*net from client6071 SQ L*net roundtrips to/from client0 sorts (memory) 0 sorts (disk) 91040 rows processed

Conclusion: As we can see here, after using the In-memory function, the database consistent get is 3, the performance improvement is obvious compared to the previous 7507 when the In-memory feature is not used.

ORACLE 12C in-memory Performance test