当前位置:Gxlcms > mysql > Oracle12cNewFeatures—InMemoryOption(1)

Oracle12cNewFeatures—InMemoryOption(1)

时间:2021-07-01 10:21:17 帮助过:26人阅读

本站文章除注明转载外,均为本站原创: 转载自love wife love life —Roger 的Oracle技术博客 本文链接地址: Oracle 12c New FeaturesIn Memory Option(1) Oracle 12.1.0.2版本了引入了一个非常牛叉的功能,即In Memory option,这是十分具有诱惑力的,针对

本站文章除注明转载外,均为本站原创: 转载自love wife & love life —Roger 的Oracle技术博客

本文链接地址: Oracle 12c New Features—In Memory Option(1)

Oracle 12.1.0.2版本了引入了一个非常牛叉的功能,即In Memory option,这是十分具有诱惑力的,针对这个重大的变化,我会进行一个系列文章的分析,这是第一篇。

首先,我们来尝试下In Memory option功能。

[oracle@ora12012 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 12.1.0.2.0 Beta on Tue Jul 29 05:30:26 2014

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

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

SQL>
SQL>
SQL>
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
READ WRITE
SQL> select * from v$option where PARAMETER like '%In-Memory%';

PARAMETER                        VALUE                   CON_ID
-------------------------------- ------------------- ----------
In-Memory Column Store           TRUE                         0
In-Memory Aggregation            TRUE                         0
SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDBORCL                        READ WRITE
KILLDB                         READ WRITE
SQL> conn roger/roger@killdb
Connected.

SQL> create table t_memory as select * from dba_objects;

Table created.

SQL> select owner,table_name,INMEMORY,INMEMORY_DISTRIBUTE from dba_tables where table_name='T_MEMORY';

OWNER      TABLE_NAME                     INMEMORY INMEMORY_DISTRI
---------- ------------------------------ -------- ---------------
ROGER      T_MEMORY                       DISABLED
SQL> set autot on
SQL> select count(1) from T_MEMORY;

COUNT(1)
----------
 90922

Execution Plan
----------------------------------------------------------
Plan hash value: 620019089

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   416   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_MEMORY | 90922 |   416   (1)| 00:00:01 |
-----------------------------------------------------------------------

Statistics
----------------------------------------------------------
 2  recursive calls
 0  db block gets
 1529  consistent gets
 1525  physical reads
 0  redo size
 544  bytes sent via SQL*Net to client
 551  bytes received via SQL*Net from client
 2  SQL*Net roundtrips to/from client
 0  sorts (memory)
 0  sorts (disk)
 1  rows processed

SQL>  select count(1) from T_MEMORY;

COUNT(1)
----------
 90922

Execution Plan
----------------------------------------------------------
Plan hash value: 620019089

-----------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   416   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE    |          |     1 |            |          |
|   2 |   TABLE ACCESS FULL| T_MEMORY | 90922 |   416   (1)| 00:00:01 |
-----------------------------------------------------------------------

Statistics
----------------------------------------------------------
 36  recursive calls
 0  db block gets
 1575  consistent gets
 1525  physical reads
 0  redo size
 544  bytes sent via SQL*Net to client
 551  bytes received via SQL*Net from client
 2  SQL*Net roundtrips to/from client
 5  sorts (memory)
 0  sorts (disk)
 1  rows processed

未启用in Memory 特性之前,大家可以看到执行计划是上述这样的,虽然SQL执行过一次了,然而再次执行仍然会有不少的代价,下面我们来看下开启in Memory 特性后的情况。首先我们来了解下in Memory option相关的几个参数:

SQL> show parameter inmemory

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

这里针对in memory option的几个参数进行简单说明:

inmemory_size:该参数可以在cdb或pdb级别进行设置,即In memory column store的内存区域。
inmemory_force:该参数默认值为default,表示Oracle仅仅通过对象的inmemory或no inmemory特殊来决定是否启用in memory column store特性。
inmemory_max_populate_servers:表示后台启动多少个进程来加载数据到memory中。对于多core(多cpu)的系统来讲,可以设置相对大一点。
inmemory_query: 表示In memory query是否被启动,默认为enable。

还有有一个参数:OPTIMIZER_INMEMORY_AWARE,表示Oracle CBO是否参考in memory column store功能。这里Oracle的官方文档描述其实有误。

这个参数在12.1.0.2中其实是一个隐含参数:

SQL> show parameter OPTIMIZER_INMEMORY

NAME                                      TYPE        VALUE
------------------------------------      ----------- ------------------------------
_optimizer_inmemory_access_path           boolean     TRUE
_optimizer_inmemory_autodop               boolean     TRUE
_optimizer_inmemory_aware                 boolean     TRUE
_optimizer_inmemory_bloom_filter          boolean     TRUE
_optimizer_inmemory_gen_pushable_preds    boolean     TRUE
_optimizer_inmemory_minmax_pruning        boolean     TRUE
_optimizer_inmemory_pruning_ratio_rows    integer     100
_optimizer_inmemory_quotient              integer     0
_optimizer_inmemory_table_expansion       boolean     TRUE

从这部分隐含参数,我们其实可以大概看出来12c中的优化器的一些机制。下面再调整下另外一个参数,注意这个参数只能在CDB级别进行调整。

SQL> conn /as sysdba
Connected.
SQL> alter system set inmemory_max_populate_servers=2 scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  637534208 bytes
Fixed Size                  2919088 bytes
Variable Size             478152016 bytes
Database Buffers           46137344 bytes
Redo Buffers                5468160 bytes
In-Memory Area            104857600 bytes
Database mounted.

SQL> alter table t_memory INMEMORY;

Table altered.

SQL> select count(1) from T_MEMORY;

COUNT(1)
----------
 90922

Execution Plan
----------------------------------------------------------
Plan hash value: 620019089

--------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |   416   (1)| 00:00:01 |
|   1 |  SORT AGGREGATE             |          |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_MEMORY | 90922 |   416   (1)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
 332  recursive calls
 0  db block gets
 1872  consistent gets
 1534  physical reads
 0  redo size
 544  bytes sent via SQL*Net to client
 551  bytes received via SQL*Net from client
 2  SQL*Net roundtrips to/from client
 22  sorts (memory)
 0  sorts (disk)
 1  rows processed

SQL>
SQL> set autot off
SQL> select owner,table_name,INMEMORY,INMEMORY_PRIORITY,INMEMORY_DISTRIBUTE,INMEMORY_COMPRESSION
 2  from dba_tables where table_name='T_MEMORY';

OWNER      TABLE_NAME                     INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS
---------- ------------------------------ -------- -------- --------------- -----------------
ROGER      T_MEMORY                       ENABLED  NONE     AUTO            FOR QUERY LOW

SQL> set autot on
SQL> select count(1) from T_MEMORY;

COUNT(1)
----------
 90922

Execution Plan
----------------------------------------------------------
Plan hash value: 620019089

--------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    16   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |          |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_MEMORY | 90922 |    16   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
 187  recursive calls
 0  db block gets
 151  consistent gets
 1  physical reads
 0  redo size
 544  bytes sent via SQL*Net to client
 551  bytes received via SQL*Net from client
 2  SQL*Net roundtrips to/from client
 16  sorts (memory)
 0  sorts (disk)
 1  rows processed

我们可以看到如果你开启了in memory option的话,那么SQL的执行代价确实要少很多,优势比较明显。然而,这里不知道大家
注意到了没有? 为什么开启了in memory 特性之后,再次执行SQL时,还有一个物理读呢?

对于In memory Option这里有几种属性:

1) IM Column Store Compression Methods
2) IM Column Store Data Population Options

对于In Memory column store compression来讲,又分为几种情况,上面的FOR QUERY LOW 就是默认行为. 根据官方文档的描述分为如下几种:

NO MEMCOMPRESS??? :? 即存在In memory内存中的该对象的列数据是不会被压缩的。
MEMCOMPRESS FOR DML: 这种属性的设置下可以优化DML操作以及最小程度的压缩In memory column store的数据。
MEMCOMPRESS FOR QUERY LOW: 这是默认设置,Oracle认为这是最利于查询性能的设置。这种情况下数据的压缩比例是介于FOR DML 和 QUERY HIGH之间的.
MEMCOMPRESS FOR QUERY HIGH: 这种情况下查询性能也是相对较优的,列数据的压缩比例在FOR QUERY LOW 和 FOR CAPACITY LOW 两种默认之间。
MEMCOMPRESS FOR CAPACITY LOW:这种情况下也能获得不错的查询性能,数据压缩情况介于FOR QUERY HIGH和FOR CAPACITY HIGH之间。
MEMCOMPRESS FOR CAPACITY HIGH:这种情况下压缩比例是最高的,通常是不推荐的,没有性能上的优势。


对于IM Column Store Data Population Options来讲,这种属性的设置只能是对象级别,分别表级别或分区级别,不能进行column级别的设置。
PRIORITY NONE? :即 不启用对象的PRIORITY特性。什么是PRIORITY呢,其实就是定义对象的活跃程度。
当达到一定的活跃程度之后,该对象会被放到in memory area中。默认情况下oracle不会启用这个属性,即默认为None。

PRIORITY LOW :表示启用priority特性,只是level较低,为low。
PRIORITY MEDIUM :表示启用priority特性,level介于LOW 和High之间. 目前暂时不知道oracle是如何来定义这个low,medium和high的。
PRIORITY HIGH?? :表示启用priority特性,level介于MEDIUM 和CRITICAL之间
PRIORITY CRITICAL:表示启用priority特性,level是最高的。

未完待续!

Related posts:

  1. 11g 新特性之–query result cache(3)
  2. full text index 探秘(1)
  3. 11gR2 新特性之—In-Memory Parallel execution
  4. 10g中distinct加强以及anti jion,semi jion
  5. Oracle materizlized view Study (1)

人气教程排行