联系:手机/微信(+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倍.