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
