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