ORACLE 12C In-Memory组件初试

联系:手机/微信(+86 17813235971) QQ(107644445)QQ咨询惜分飞

标题:ORACLE 12C In-Memory组件初试

作者:惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]

根据Oracle官方的宣传12.1.0.2的In-Memory组件实现内存列存储提高Oracle性能而且弥补在列存储中的不足。感谢Lunar的文档支持
12.1.0.2版本

[oracle@localhost ~]$ sqlplus chf/xifenfei@pdb1

SQL*Plus: Release 12.1.0.2.0 Beta on Thu Apr 24 21:39:43 2014

Copyright (c) 1982, 2014, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit     
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> select * from v$version;

BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit                         0
PL/SQL Release 12.1.0.2.0 -                                                               0
CORE    12.1.0.2.0                                                                        0
TNS for Linux: Version 12.1.0.2.0 -                                                       0
NLSRTL Version 12.1.0.2.0 -                                                               0

关于In-Memory组件

SQL>  select parameter,value from v$option where parameter like 'In-Memory%';

PARAMETER                                                        VALUE
---------------------------------------------------------------- -------------
In-Memory Column Store                                           TRUE
In-Memory Aggregation                                            TRUE

关于inmemory参数

SQL> select NAME,value,DESCRIPTION from v$parameter where NAME like 'inmemory%';

NAME                 VALUE
-------------------- --------------------
DESCRIPTION
---------------------------------------------------
inmemory_size        0
size in bytes of in-memory area

inmemory_clause_defa
ult
Default in-memory clause for new tables

inmemory_force       DEFAULT
Force tables to be in-memory or not

inmemory_query       ENABLE
Specifies whether in-memory queries are allowed

启用In-Memory功能

SQL> show parameter inmemory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_query                       string      ENABLE
inmemory_size                        big integer 0

SQL> alter system set inmemory_size=200M scope=spfile;

System altered.

SQL> shutdown immediate
Pluggable Database closed.
SQL> startup
Pluggable Database opened.
SQL> show parameter inmemory;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ----------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_query                       string      ENABLE
inmemory_size                        big integer 200M

创建测试表

SQL> create table t_xifenfei_in_memory as select * from dba_objects;

Table created.

SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables;

TABLE_NAME                     INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS
------------------------------ -------- --------------- -----------------
T_XIFENFEI_IN_MEMORY

SQL> alter table  T_XIFENFEI_IN_MEMORY inmemory;

Table altered.

SQL> select TABLE_NAME,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION from user_tables;

TABLE_NAME                INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS
------------------------- -------- --------------- -----------------
T_XIFENFEI_IN_MEMORY      NONE     AUTO DISTRIBUTE FOR QUERY
此条目发表在 ORACLE 12C 分类目录,贴了 , 标签。将固定链接加入收藏夹。

ORACLE 12C In-Memory组件初试》有 3 条评论

  1. zz 说:

    惜分飞,

    是对于多列的全表扫没有好处,返回的列数越多越不合适列式存储。
    Storing a database object in the IM column store can improve performance significantly for the following types of operations performed on the database object:
    A query that scans a large number of rows and applies filters that use operators such as the following: =, , and IN
    A query that selects a small number of columns from a table or materialized view with a large number of columns, such as a query that selects five columns from a table with 100 columns
    A query that joins a small table to a large table
    A query that aggregates data
    *****************************************
    The IM column store does not improve performance for the following types of operations:
    Queries with complex predicates
    Queries that select a large number of columns
    Queries that return a large number of rows
    Queries with multiple large table joins

  2. 惜分飞 说:

    你这里有两个问题:
    1. 两种场景数据都在内存里面了,所以imm没有优势
    2.对于全表扫描,列存储没有优势

  3. lish 说:

    环境 是一台物理笔记本 IBM W500

    安装了 Linux6.4 和Oracle12.1.0.2

    我在测试In Memory组件发现

    [oracle@oracle12c Desktop]$ sqlplus / as sysdba

    SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 30 05:05:42 2014

    Copyright (c) 1982, 2014, Oracle. All rights reserved.

    Connected to an idle instance.

    SQL> startup
    ORACLE instance started.

    Total System Global Area 4294967296 bytes
    Fixed Size 2932632 bytes
    Variable Size 436207720 bytes
    Database Buffers 536870912 bytes
    Redo Buffers 13844480 bytes
    In-Memory Area 3305111552 bytes
    Database mounted.
    Database opened.
    SQL> alter pluggable database pdborcl open;

    Pluggable database altered.

    SQL> conn sde/sde@pdborcl
    Connected.
    SQL> create table t1 as select * from dba_objects;

    Table created.

    SQL> select bytes from user_segments where segment_name=’T1′;

    BYTES
    ———-
    13631488

    SQL> select * from v$inmemory_area;

    POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS
    ————————– ———– ———- ————————–
    CON_ID
    ———-
    1MB POOL 2632974336 33554432 DONE
    3

    64KB POOL 654311424 34996224 DONE
    3

    SQL> set timing on
    SQL> set autot trace
    SQL> select * from t1;

    91710 rows selected.

    Elapsed: 00:00:01.65

    Execution Plan
    ———————————————————-
    Plan hash value: 3617692013

    ————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ————————————————————————–
    | 0 | SELECT STATEMENT | | 91710 | 10M| 429 (1)| 00:00:01 |
    | 1 | TABLE ACCESS FULL| T1 | 91710 | 10M| 429 (1)| 00:00:01 |
    ————————————————————————–

    Statistics
    ———————————————————-
    2 recursive calls
    0 db block gets
    7553 consistent gets
    1538 physical reads
    0 redo size
    12218706 bytes sent via SQL*Net to client
    67795 bytes received via SQL*Net from client
    6115 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    91710 rows processed

    =======================================================================

    将该表进行memory测试

    SQL> alter table t1 inmemory;

    Table altered.

    Elapsed: 00:00:00.07
    SQL> set autot off
    SQL> select * from v$inmemory_area;

    POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS
    ————————– ———– ———- ————————–
    CON_ID
    ———-
    1MB POOL 2632974336 33554432 DONE
    3

    64KB POOL 654311424 34996224 DONE
    3

    Elapsed: 00:00:00.00
    SQL> select count(*) from t1;

    COUNT(*)
    ———-
    91710

    Elapsed: 00:00:00.02
    SQL> select * from v$inmemory_area;

    POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS
    ————————– ———– ———- ————————–
    CON_ID
    ———-
    1MB POOL 2632974336 37748736 DONE
    3

    64KB POOL 654311424 35127296 DONE
    3

    Elapsed: 00:00:00.01
    SQL> set autot trace
    SQL> select * from t1;

    91710 rows selected.

    Elapsed: 00:00:02.98

    Execution Plan
    ———————————————————-
    Plan hash value: 3617692013

    ——————————————————————————–

    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
    |

    ——————————————————————————–

    | 0 | SELECT STATEMENT | | 91710 | 10M| 32 (16)| 00:00:0
    1 |

    | 1 | TABLE ACCESS INMEMORY FULL| T1 | 91710 | 10M| 32 (16)| 00:00:0
    1 |

    ——————————————————————————–

    Statistics
    ———————————————————-
    5 recursive calls
    0 db block gets
    5 consistent gets
    0 physical reads
    0 redo size
    4987819 bytes sent via SQL*Net to client
    67795 bytes received via SQL*Net from client
    6115 SQL*Net roundtrips to/from client
    0 sorts (memory)
    0 sorts (disk)
    91710 rows processed

    SQL>

    测试结论:为什么在memory测试的指标都比一般情况要好,但是查询时间反而低了,而且我是在本机测试,不考虑网络的问题,这个我很不解,希望高手给予指点!