联系：手机/微信(+86 17813235971) QQ(107644445)
作者：惜分飞©版权所有[未经本人同意,不得以任何形式转载,否则有进一步追究法律责任的权利.]
根据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
加我QQ
惜分飞,
是对于多列的全表扫没有好处，返回的列数越多越不合适列式存储。
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
你这里有两个问题：
1. 两种场景数据都在内存里面了，所以imm没有优势
2.对于全表扫描，列存储没有优势
环境 是一台物理笔记本 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测试的指标都比一般情况要好，但是查询时间反而低了，而且我是在本机测试，不考虑网络的问题，这个我很不解，希望高手给予指点！