oracle12c性能测试,ORACLE 12C In-Memory性能测试

1、查看oracle版本

SQL> select * from v$version;

BANNERCON_ID

------------------------------------------------------------------------------------------ ----------

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production0

PL/SQL Release 12.1.0.2.0 - Production0

CORE12.1.0.2.0Production0

TNS for Linux: Version 12.1.0.2.0 - Production0

NLSRTL Version 12.1.0.2.0 - Production0

SQL> show pdbs

CON_ID CON_NAMEOPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEEDREAD ONLY NO

3 PDB01READ WRITE NO

SQL> alter session set container=pdb01;

Session altered.

SQL> show con_name;

CON_NAME

------------------------------

PDB01

2、查看inmemory是否启用

SQL> show parameter inmemory

NAMETYPEVALUE

------------------------------------ ----------- ------------------------------

inmemory_clause_default string

inmemory_forcestringDEFAULT

inmemory_max_populate_serversinteger2

inmemory_querystringENABLE

inmemory_sizebig integer 208M

inmemory_trickle_repopulate_servers_ integer1

percent

optimizer_inmemory_awarebooleanTRUE

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;

POOLALLOC_BYTES USED_BYTES POPULATE_STATUSCON_ID

-------------------------- ----------- ---------- -------------------------- ----------

1MB POOL1667235844194304 DONE1

64KB POOL33554432131072 DONE1

SQL> select table_name,INMEMORY,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION,INMEMORY_DUPLICATE from user_tables;

TABLE_NAMEINMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL

---------------------------------------- -------- -------- --------------- ----------------- -------------

T2DISABLED

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

原文：http://www.cnblogs.com/osdba/p/4422735.html