ORACLE 12C In-Memory性能测试
1、查看oracle版本
SQL> select * from v$version; BANNER CON_ID ------------------------------------------------------------------------------------------ ---------- Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production 0 PL/SQL Release 12.1.0.2.0 - Production 0 CORE 12.1.0.2.0 Production 0 TNS for Linux: Version 12.1.0.2.0 - Production 0 NLSRTL Version 12.1.0.2.0 - Production 0 SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- ------------------------------ ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB01 READ WRITE NO SQL> alter session set container=pdb01; Session altered. SQL> show con_name; CON_NAME ------------------------------ PDB01
2、查看inmemory是否启用
SQL> show parameter inmemory NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ inmemory_clause_default string inmemory_force string DEFAULT inmemory_max_populate_servers integer 2 inmemory_query string ENABLE inmemory_size big integer 208M inmemory_trickle_repopulate_servers_ integer 1 percent optimizer_inmemory_aware boolean TRUE
3、创建测试表
SQL> conn keven/keven Connected. SQL> create table t1 as select * from dba_objects; SQL> create table t2 as select * from dba_objects; SQL> col table_name for a40 SQL> select * from v$inmemory_area; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------------------------- ----------- ---------- -------------------------- ---------- 1MB POOL 166723584 4194304 DONE 1 64KB POOL 33554432 131072 DONE 1 SQL> select table_name,INMEMORY,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION,INMEMORY_DUPLICATE from user_tables; TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL ---------------------------------------- -------- -------- --------------- ----------------- ------------- T2 DISABLED T1 ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE Elapsed: 00:00:00.04 SQL> set autot trace SQL> SQL> select * from t2; 91040 rows selected. Elapsed: 00:00:01.22 Execution 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 ---------------------------------------------------------- 53 recursive calls 0 db block gets 7569 consistent gets 1529 physical reads 0 redo size 12149139 bytes sent via SQL*Net to client 67311 bytes received via SQL*Net from client 6071 SQL*Net roundtrips to/from client 4 sorts (memory) 0 sorts (disk) 91040 rows processed SQL> / 91040 rows selected. Elapsed: 00:00:01.22 Execution 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 calls 0 db block gets 7497 consistent gets 1529 physical reads 0 redo size 12149139 bytes sent via SQL*Net to client 67311 bytes received via SQL*Net from client 6071 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 91040 rows processed SQL> alter table t2 inmemory; Table altered. Elapsed: 00:00:00.03 SQL> select * from t2; 91040 rows selected. Elapsed: 00:00:01.32 Execution 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 calls 0 db block gets 7507 consistent gets 1529 physical reads 0 redo size 4980841 bytes sent via SQL*Net to client 67311 bytes received via SQL*Net from client 6071 SQL*Net roundtrips to/from client 2 sorts (memory) 0 sorts (disk) 91040 rows processed SQL> / 91040 rows selected. Elapsed: 00:00:01.12 Execution 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 calls 0 db block gets 3 consistent gets 0 physical reads 0 redo size 4980841 bytes sent via SQL*Net to client 67311 bytes received via SQL*Net from client 6071 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 91040 rows processed
结论：这里我们可以发现，使用了In-Memory功能之后，数据库consistent gets为3，相比未使用In-Memory特性之前7507，性能提升明显。
