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，性能提升明显。

转载于:https://www.cnblogs.com/osdba/p/4422735.html