联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
启用In-Memory功能
数据库版本12.1.0.2及其以上版本,inmemory_size参数设置为合适值
SQL> SELECT * FROM V$VERSION; BANNER CON_ID -------------------------------------------------------------------------------- ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit 0 PL/SQL Release 12.1.0.2.0 - 0 CORE 12.1.0.2.0 0 TNS for Linux: Version 12.1.0.2.0 - 0 NLSRTL Version 12.1.0.2.0 - 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
创建表
这里可以知道,创建表大小为13631488，但是未使用In-Memory功能
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功能测试
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
这里可以看到未使用In-Memory功能,数据库查询执行计划使用TABLE ACCESS FULL，consistent gets为7505
使用In-Memory功能测试
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
--因为只是把该表设置了INMEMORY,但是未查询过,所以查询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
--进行一次全表扫描
SQL> SELECT COUNT(*) FROM T_XIFENFEI_IN_MEMORY;
COUNT(*)
----------
90902
--再次查看，已经使用了分配的In-Memory中内存
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
这里我们可以发现，使用了In-Memory功能之后，数据库consistent gets为4,相比未使用In-Memory之前的7505,性能最少提高近2000倍.