感受ORACLE 12C IN_MEMORY性能威力

                    感受ORACLE 12C IN_MEMORY性能威力

      oracle 12C in_memory(简称IM)对数据库性能的提升是巨大的，最重要的一点是这一切
对应用程序是透明的。首先我们来看看实验中IM的相关参数设置   

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 100M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

创建一个普通用户，创建一个表数据来自dba_objects;
SQL> create user c##frank identified by frank;

User created.

SQL> grant connect,resource,unlimited tablespace to c##frank;

Grant succeeded.

SQL> create table c##frank.t1 as select * from dba_objects;

Table created.

把用户表放到IM中.
SQL> alter table t1 c##frank.inmemory;

Table altered.

收集一下统计息.
SQL> exec dbms_stats.gather_table_stats('C##FRANK','T1');

PL/SQL procedure successfully completed.

SQL> set autotrace on;

SQL> select count(object_id) from c##frank.t1;

COUNT(OBJECT_ID)
----------------
           90905

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |     5 |    27   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T1   | 90905 |   443K|    27   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

做一个简单的查询，可以看出有以下几点:1，全表扫描多了INMEMORY关健字;2,consistent gets急剧减少,少得有点
可怜了，说明对内存访问极少;3,cost是27

相比传统的查询，看看in-memory快了多少，使用NO_INMEMORY hint让optimizer不走in-memory,而走传统的buffer cache

SQL> select /*+ NO_INMEMORY */ count(object_id) from t1;

COUNT(OBJECT_ID)
----------------
           90905

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     5 |   416   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |     5 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 90905 |   443K|   416   (1)| 00:00:01 |
---------------------------------------------------------------------------

Statistics
----------------------------------------------------------
         33  recursive calls
          0  db block gets
       1557  consistent gets
          0  physical reads
          0  redo size
        552  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          3  sorts (memory)
          0  sorts (disk)
          1  rows processed

传统的成本是416,consistent gets是1557,相比in-memory，访问数据大量增加.这是由于是传统是存储的是
行式，而in-memory存放的是列式.速度快了非常多.

  有人可能会问，这里选择是单列，如果是count(*)了，性能还有这么明显吗?好，我们来看例子
SQL> select /*+ NO_INMEMORY */ count(*) from c##frank.t1;

  COUNT(*)
----------
     90905

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

-------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |
-------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   426   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |      |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T1   | 90905 |   426   (1)| 00:00:01 |
-------------------------------------------------------------------

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       1536  consistent gets
       1527  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

在实验中还产生了物理读.再来看看in-memory的count(*)

SQL> select /*+ INMEMORY */ count(*) from c##frank.t1;

  COUNT(*)
----------
     90905

Execution Plan
----------------------------------------------------------
Plan hash value: 3724264953

----------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |    27   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |      |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T1   | 90905 |    27   (0)| 00:00:01 |
----------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

即使count(*),in-memory也和原来一样，快上很多倍.估计看到这样一对比很多人都已经坐不住了.