首页 » ORACLE [C]系列, ORACLE 9i-23c » oracle 12c new feature: In-memory option VS in memory standard row-major

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和国内电信行来在测试,

Related Posts:

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：