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 Production0PL/SQL Release 12.1.0.2.0 - Production0CORE12.1.0.2.0Production0TNS for Linux: Version 12.1.0.2.0 - Production0NLSRTL Version 12.1.0.2.0 - Production0SQL> show pdbsCON_ID CON_NAMEOPEN MODE RESTRICTED---------- ------------------------------ ---------- ----------2 PDB$SEEDREAD ONLY NO3 PDB01READ WRITE NOSQL> alter session set container=pdb01;Session altered.SQL> show con_name;CON_NAME------------------------------PDB01

　　

2、查看inmemory是否啟用

SQL> show parameter inmemoryNAMETYPEVALUE------------------------------------ ----------- ------------------------------inmemory_clause_default stringinmemory_forcestringDEFAULTinmemory_max_populate_serversinteger2inmemory_querystringENABLEinmemory_sizebig integer 208Minmemory_trickle_repopulate_servers_ integer1percentoptimizer_inmemory_awarebooleanTRUE

　　

3、建立測試表

SQL> conn keven/kevenConnected.SQL> create table t1 as select * from dba_objects;SQL> create table t2 as select * from dba_objects;SQL> col table_name for a40SQL> select * from v$inmemory_area;POOLALLOC_BYTES USED_BYTES POPULATE_STATUSCON_ID-------------------------- ----------- ---------- -------------------------- ----------1MB POOL1667235844194304 DONE164KB POOL33554432131072 DONE1SQL> select table_name,INMEMORY,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION,INMEMORY_DUPLICATE from user_tables;TABLE_NAMEINMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL---------------------------------------- -------- -------- --------------- ----------------- -------------T2DISABLEDT1ENABLED NONEAUTO FOR QUERY LOW NO DUPLICATEElapsed: 00:00:00.04SQL> set autot traceSQL> SQL> select * from t2;91040 rows selected.Elapsed: 00:00:01.22Execution 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 calls0 db block gets7569 consistent gets1529 physical reads0 redo size12149139 bytes sent via SQL*Net to client67311 bytes received via SQL*Net from client6071 SQL*Net roundtrips to/from client4 sorts (memory)0 sorts (disk)91040 rows processedSQL> /91040 rows selected.Elapsed: 00:00:01.22Execution 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 calls0 db block gets7497 consistent gets1529 physical reads0 redo size12149139 bytes sent via SQL*Net to client67311 bytes received via SQL*Net from client6071 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)91040 rows processedSQL> alter table t2 inmemory;Table altered.Elapsed: 00:00:00.03SQL> select * from t2;91040 rows selected.Elapsed: 00:00:01.32Execution 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 calls0 db block gets7507 consistent gets1529 physical reads0 redo size4980841 bytes sent via SQL*Net to client67311 bytes received via SQL*Net from client6071 SQL*Net roundtrips to/from client2 sorts (memory)0 sorts (disk)91040 rows processedSQL> /91040 rows selected.Elapsed: 00:00:01.12Execution 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 calls0 db block gets3 consistent gets0 physical reads0 redo size4980841 bytes sent via SQL*Net to client67311 bytes received via SQL*Net from client6071 SQL*Net roundtrips to/from client0 sorts (memory)0 sorts (disk)91040 rows processed

結論：這裡我們可以發現，使用了In-Memory功能之後，資料庫consistent gets為3，相比未使用In-Memory特性之前7507，效能提升明顯。

ORACLE 12C In-Memory效能測試