oracle 12c new feature: In-memory option VS in memory standard row-major
Oracle 12.1.0.2 发布引入了一系列的新特性，当然还有关注度非常高的ORACLE In-Memory Option,在内存中提供一种列级的存储， 在OLTP中提供更好的性能，当然在Exadata中ORACLE已在存储级提供了列级的性能优化hybrid columnar compressed (HCC) format,访问数据从内存中要快于物理磁盘， 只前的做法是把对象放到buffer cache中，In-Memory 在12.1.0.2中引入的参数就有110多个，可见是一种非常复杂的技术，关于IN-Memory不多介绍可以参考官方文档，在这里我只是简单比较一下之前版本中标row格式in buffer cache和新特性in-memory option中的column格式的执行性能.
1. Enable IN-Memory Option
[oracle@db231 ~]$ ora SQL*Plus: Release 12.1.0.2.0 Production on Thu Sep 11 14:53:58 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options # in CDB sys@ORA12102>alter system set inmemory_size=500m scope=spfile; sys@ORA12102>shutdown immediate sys@ORA12102>startup sys@ORA12102>select * from v$sgainfo; NAME BYTES RES CON_ID -------------------------------- -------------------- --- -------------------- Fixed SGA Size 2927000 No 0 Redo Buffers 13848576 No 0 Buffer Cache Size 1191182336 Yes 0 In-Memory Area Size 536870912 No 0 ...
2. Create a segment in PDB(not in sys schema,not in system tablespace.)
sys@ORA12102>alter pluggable database pdb12102 open;
Pluggable database altered.
# switch to PDB
sys@ORA12102>alter session set container=pdb12102;
Session altered.
sys@ORA12102>create table anbob.tt as select * from dba_objects;
Table created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
91733 rows created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
183466 rows created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
366932 rows created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
733864 rows created.
sys@ORA12102>insert into anbob.tt select * from anbob.tt;
1467728 rows created.
sys@ORA12102>exec dbms_stats.gather_table_stats('ANBOB','TT');
PL/SQL procedure successfully completed.
sys@ORA12102>select bytes/1024/1024 mb,blocks from dba_segments where segment_name='TT' and owner='ANBOB';
MB BLOCKS
-------------------- --------------------
390 49920
sys@ORA12102>show parameter inme
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 500M
inmemory_trickle_repopulate_servers_ integer 1
percent
optimizer_inmemory_aware boolean TRUE
3. let the table inmemory and cache in buffer
sys@ORA12102>alter table anbob.tt inmemory;
Table altered.
sys@ORA12102>alter table anbob.tt cache;
Table altered.
sys@ORA12102>SELECT TABLE_NAME,CACHE,INMEMORY,INMEMORY_PRIORITY ,INMEMORY_DISTRIBUTE, INMEMORY_COMPRESSION,INMEMORY_DUPLICATE
FROM DBA_TABLES WHERE OWNER='ANBOB';
TABLE_NAME CACHE INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- ----- -------- -------- --------------- ----------------- -------------
TT Y ENABLED NONE AUTO FOR QUERY LOW NO DUPLICATE
sys@ORA12102>select * from v$im_segments;
no rows selected
Elapsed: 00:00:00.01
# execute a query load obj in memory
sys@ORA12102>select count(*) ,object_type from anbob.tt group by object_type;
..
sys@ORA12102>select segment_name,TABLESPACE_NAME,INMEMORY_SIZE, BYTES,trunc(408944640/52887552,2) comp_ratio from v$im_segments;
SEGMENT_NA TABLESPACE INMEMORY_SIZE BYTES COMP_RATIO
---------- ---------- -------------------- -------------------- --------------------
TT USERS 52887552 408944640 7.73
sys@ORA12102>select * from (
2 select
3 count(*) buffers
4 , o.owner bhobjects_owner
5 , o.object_name bhobjects_object_name
6 , o.subobject_name bhobjects_subobject_name
7 , o.object_type bhobjects_object_type
8 from
9 v$bh bh
10 , dba_objects o
11 where
12 bh.objd = o.data_object_id
13 group by
14 o.owner, o.object_name, o.subobject_name, o.object_type
15 order by
16 buffers desc
17 )
18 where object_name='ANBOB'
19 /
BUFFERS OWNER OBJECT_NAME SUBOBJECT_NAME OBJECT_TYPE
-------------------- ------------------------------ ------------------------------ ------------------------------ --------------------
24528 ANBOB TT TABLE
4. To compare speeds of queries executed on data in memory using both the standard row-major format and the new columnar format.
sys@ORA12102>set autot trace exp stat #下面都是第二次运行的结果 sys@ORA12102>select count(object_id) from anbob.tt where object_id between 1 and 10000; Elapsed: 00:00:00.03 Execution Plan ---------------------------------------------------------- Plan hash value: 3133740314 ------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 5 | 530 (4)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS INMEMORY FULL| TT | 316K| 1545K| 530 (4)| 00:00:01 | ------------------------------------------------------------------------------------ Predicate Information (identified by operation id): --------------------------------------------------- 2 - inmemory("OBJECT_ID"<=10000 AND "OBJECT_ID">=1) filter("OBJECT_ID"<=10000 AND "OBJECT_ID">=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 10 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 sys@ORA12102>ALTER SESSION set inmemory_query = disable; Session altered. sys@ORA12102>select count(object_id) from anbob.tt where object_id between 1 and 10000; Elapsed: 00:00:00.22 Execution Plan ---------------------------------------------------------- Plan hash value: 3133740314 --------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 5 | 13479 (1)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 5 | | | |* 2 | TABLE ACCESS FULL| TT | 316K| 1545K| 13479 (1)| 00:00:01 | --------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("OBJECT_ID"<=10000 AND "OBJECT_ID">=1) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 49134 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
Note:
ON Exadata machine ,the FTS event maybe show ‘TABLE ACCESS STORAGE FULL’
另外有一点需要注意，虽然执行计划TABLE ACCESS前后是不一样，但是Plan hash value是相同的，Randolf Geist几年前写过How PLAN_HASH_VALUES Are Calculated解释
So in summary the following conclusions can be made:
– The same PLAN_HASH_VALUE is merely an indicator that the same operations on objects of the same name are performed in the same order.
– It tells nothing about the similarity of the expected runtime performance of the execution plan, due to various reasons as demonstrated. The most significant information that is not covered by the PLAN_HASH_VALUE are the filter and access predicates, but there are other attributes, too, that are not part of the hash value calculation.
Conclusion：
通过上面简单的测试发现 in-memory 的响应时间有了7X的提升，Cost也要从有标准bh的13479减少到530， 但是CPU使用In-Memory是标准bh的4X， consistent gets从标准bh的49134降到in-memory的10.TABLE ACCESS访问路径有标准bh的TABLE ACCESS FULL 变成了TABLE ACCESS INMEMORY FULL. 当然在不同的物理环境可能数值有所不同，我的测试是在DELL R610的机器上，但是总体in-memory 的性能优化还是很明显的， 也有可能等不到12c r2 就会有生产环境升级到12c, 据说SAP和国内电信行来在测试,
微信扫一扫，打赏作者吧～
对不起，这篇文章暂时关闭评论。