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

One thought on “ORACLE 12C In-Memory组件初试”

环境是一台物理笔记本 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测试的指标都比一般情况要好，但是查询时间反而低了，而且我是在本机测试，不考虑网络的问题，这个我很不解，希望高手给予指点！

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

惜分飞,
是对于多列的全表扫没有好处，返回的列数越多越不合适列式存储。
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

ORACLE 12C In-Memory组件初试

One thought on “ORACLE 12C In-Memory组件初试”

发表评论 取消回复

发表评论取消回复