oracle 12c R1执行计划新特性-table access by index rowid batched和INMOMEORY OPTION

oracle 12c R1执行计划在索引回表阶段oracle推出了batched特性，类似于oracle 11g中在nested loop中被驱动表回表时的向量IO，也是为了有效的解决表中数据无序性（索引的聚簇因子），下面看实际测试用例：

数据库版本：12.1.0.2版本

sys@CRMDB2>
explain plan for SELECT offering_inst_id,
       offering_id,
       owner_party_role_type,
       owner_party_role_id,
       purchase_seq,
       brand,
       primary_flag,
       rel_pri_offering_inst_id,
       bundle_flag,
       p_offering_inst_id,
       apply_obj_type,
       apply_obj_id,
       status,
       status_detail,
       status_date,
       eff_date,
       。。。。。。
  FROM transdata.bk_INF_OFFERING_INST t
 WHERE be_id = 18
   AND modify_time <= to_date(20151104 || 235959, 'yyyymmddhh24miss')
   AND modify_time >= to_date(20151104 || 0, 'yyyymmddhh24miss')
   AND SUBS_ID >= 1842201100000000
   AND SUBS_ID < 1842211100000000;

sys@CRMDB2>select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3059718575

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                       |     1 |   165 |  5118   (1)| 00:00:01 |
|*  1 |  FILTER                              |                       |       |       |            |          |
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| BK_INF_OFFERING_INST  |     1 |   165 |  5118   (1)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IDX_INF_OFFERING_CQL1 | 28466 |       |    87   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(TO_DATE('201511040','yyyymmddhh24miss')<=TO_DATE(' 2015-11-04 23:59:59', 'syyyy-mm-dd
              hh24:mi:ss'))
   2 - filter("MODIFY_TIME">=TO_DATE('201511040','yyyymmddhh24miss') AND "MODIFY_TIME"<=TO_DATE('
              2015-11-04 23:59:59', 'syyyy-mm-dd hh24:mi:ss') AND "BE_ID"=18)
   3 - access("SUBS_ID">=1842201100000000 AND "SUBS_ID"<1842211100000000)

19 rows selected.
执行计划中的table access by index rowid batched，这个是12.1中的新特性，主要是在通过rowid访问数据块时，对于一个数据块中的多个rowid，通过批量访问减少访问块的次数，该特性由隐含参数_optimizer_batch_table_access_by_rowid来控制，该参数默认为true，也就是开启这个特性。

sys@CRMDB2>select a.ksppinm, a.ksppdesc,b.ksppstvl,a.inst_id
  2    from x$ksppi a, x$ksppcv b
  3   where a.inst_id = 1
  4     and a.ksppinm = '¶m'
  5     and a.indx = b.indx;
Enter value for param: _optimizer_batch_table_access_by_rowid
old   4:    and a.ksppinm = '¶m'
new   4:    and a.ksppinm = '_optimizer_batch_table_access_by_rowid'

KSPPINM                        KSPPDESC                       KSPPSTVL                          INST_ID
------------------------------ ------------------------------ ------------------------------ ----------
_optimizer_batch_table_access_ enable table access by ROWID I TRUE                                    1
by_rowid                       O batching
12.1.0.2版本中，oracle正式发布了内存和列式计算的In-Memory Option，In-Meomory option列存与压缩，数据在内存的独立区域中按照列式存储，数据是被压缩存放的，内存与列式存储可以极大提升查询的性能

SQL> show sga;
Total System Global Area  599785472 bytes
Fixed Size                  2927192 bytes
Variable Size             335545768 bytes
Database Buffers          150994944 bytes
Redo Buffers                5459968 bytes
In-Memory Area            104857600 bytes

IMO的特性相关的参数：
SQL> show parameter inmemory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 100M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE
启动IMO特性，需要设置inmemory_size参数，指定可以作为内存中列存的内存区域，该参数为静态参数，需要重启数据库才能使之生效，sga中会分配一部分内存来作为IMO部分存储,Inmemory_max_populate_servers参数用于将数据加载到内存的后台进程数量。

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      82837504    4194304 DONE                                0
64KB POOL                      4194304     131072 DONE                                0

SQL> select * from t_inmemory;

728520 rows selected.

Elapsed: 00:00:09.99

Execution Plan
----------------------------------------------------------
Plan hash value: 2996090576

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            | 91065 |     9M|    20  (20)| 00:00:01 |
|   1 |  TABLE ACCESS FULL         | T_INMEMORY | 91065 |     9M|    20  (20)| 00:00:01 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
      60188  consistent gets
          0  physical reads
          0  redo size
   40118128  bytes sent via SQL*Net to client
     534788  bytes received via SQL*Net from client
      48569  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     728520  rows processed

在没有将表T_INMEMORY加载到INMEMOYR中之前，消耗逻辑读要60188，INMEMORY_AREA空间没有使用

SQL> alter table t_inmemory inmemory;

Table altered.

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      82837504    4194304 DONE                                0
64KB POOL                      4194304     131072 DONE                                0
将表T_INMEMORY加载到INMEMORY POOL中，此时在没有对表进行查询之前，INMEMORY_AREA pool的空间是没有被使用的
表加载到INMEMORY_AREA pool中后，第一次查询T_INMEMORY表：

SQL> select * from t_inmemory;

728520 rows selected.

Elapsed: 00:00:09.99

Execution Plan
----------------------------------------------------------
Plan hash value: 2996090576

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            | 91065 |     9M|    20  (20)| 00:00:01 |
|   1 |  TABLE ACCESS INMEMORY FULL| T_INMEMORY | 91065 |     9M|    20  (20)| 00:00:01 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          9  recursive calls
          0  db block gets
      60188  consistent gets
          0  physical reads
          0  redo size
   40118128  bytes sent via SQL*Net to client
     534788  bytes received via SQL*Net from client
      48569  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     728520  rows processed
执行计划出现了变化，新增加了INMEMORY选项，此时逻辑读依然是60188，但是INMEMORY_POOL出现了变化，USED_BYTES增加

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      82837504   18874368 DONE                                0
64KB POOL                      4194304     327680 DONE                                0
再次查询，发现逻辑读已经大幅度降低，查询时间也降低到了3秒82

SQL> select * from t_inmemory;

728520 rows selected.

Elapsed: 00:00:03.82

Execution Plan
----------------------------------------------------------
Plan hash value: 2996090576

-----------------------------------------------------------------------------------------
| Id  | Operation                  | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |            | 91065 |     9M|    20  (20)| 00:00:01 |
|   1 |  TABLE ACCESS INMEMORY FULL| T_INMEMORY | 91065 |     9M|    20  (20)| 00:00:01 |
-----------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
   40118128  bytes sent via SQL*Net to client
     534788  bytes received via SQL*Net from client
      48569  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
     728520  rows processed
此时我们查询别的相关的SQL语句，逻辑读大幅度降低
SQL> select count(*) from t_inmemory;

Elapsed: 00:00:00.00

Execution Plan
----------------------------------------------------------
Plan hash value: 2112900194

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

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
         16  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        551  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
而如果我们将表T_INMEMORY移出IN_MEMORY pool中,逻辑读将回归到之前的数据

SQL> select * from v$inmemory_area;

POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      82837504    4194304 DONE                                0
64KB POOL                      4194304     131072 DONE                                0

SQL> select * from t_inmemory;

728520 rows selected.

Elapsed: 00:00:04.10

Execution Plan
----------------------------------------------------------
Plan hash value: 2996090576

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            | 91065 |     9M|   426   (1)| 00:00:01 |oracle 12c R1 R1
|   1 |  TABLE ACCESS FULL| T_INMEMORY | 91065 |     9M|   426   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      60182  consistent gets
          0  physical reads
          0  redo size
   97202838  bytes sent via SQL*Net to client
     534788  bytes received via SQL*Net from client
      48569  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
     728520  rows processed