在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数

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

在12C RAC的in memory测试中由于未正确启用并行,导致测试结果flush buffer cache后,总是出现大量并行，通过ORACLE 各方努力最终确定是由于parallel_degree_policy=AUTO和parallel_force_local=false开始未设置正确导致.在rac中需要imdb的朋友请注意这两个参数.

设置表存放中inmemory

SQL> alter table CHF.XIFENFEI_888 inmemory;
Table altered.
SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
  COUNT(*)
----------
  16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 |  2566   (8)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M|  2566   (8)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M|  2566   (8)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
        213  recursive calls
          0  db block gets
     435058  consistent gets
         40  physical reads
      61180  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> set autot off
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
no rows selected
SQL>  select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
      1 CHF   XIFENFEI_888          TABLE CHF_DATA            469827584   3571449856          2853101568 STARTED   NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
      2 CHF   XIFENFEI_888          TABLE CHF_DATA            332267520   3571449856          3040182272 STARTED   NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
      1 CHF   XIFENFEI_888          TABLE CHF_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
      2 CHF   XIFENFEI_888          TABLE CHF_DATA           1068433408   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0

这里可以看到表加载到inmemory需要时间,不是全表扫描一遍之后里面全表载入到in memory中.

查看执行计划确实走inmemory

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
  COUNT(*)
----------
  16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
     177415  consistent gets
          0  physical reads
      23484  redo size
        545  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

flush buffer cache后,inmemory执行计划中出现大量物理读

SQL> set autot off
SQL> alter system flush buffer_cache;
System altered.
SQL> /
System altered.
SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
      1 CHF   XIFENFEI_888          TABLE CHF_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
      2 CHF   XIFENFEI_888          TABLE CHF_DATA           1068433408   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
  COUNT(*)
----------
  16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
     177413  consistent gets
     176358  physical reads
      23456  redo size
        545  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
SQL> set autot off

再次查询物理读消失

SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
  COUNT(*)
----------
  16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
     177414  consistent gets
          0  physical reads
      23448  redo size
        545  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
SQL> set autot off

这里有奇怪点,启用inmemory之后,flush buffer cache后,物理读非常大(基本上和逻辑读一样),不符合常理,因为inmemory和buffer cache是两个独立的东西,就算是flush buffer cache,也不应该导致in memory内的东西失效(而且从v$im_segments中查询是正常的),对于该问题百思不得其解,最后只好寻求inmemory邮件组和GCS帮忙.最终是由于并行相关参数配置导致该问题

SQL> alter system set parallel_force_local=false sid='*';
System altered.
SQL> alter system set parallel_degree_policy=AUTO sid='*';
System altered.

修改parallel_force_local和parallel_degree_policy后继续测试

SQL> select * from gv$im_segments where owner='CHF' and segment_name='XIFENFEI_888';
INST_ID OWNER SEGMENT_NAME    PARTI SEGME TABLESPACE_NAME INMEMORY_SIZE        BYTES BYTES_NOT_POPULATED POPULATE_ INMEMORY INMEMORY_DISTRI INMEMORY_DUPL INMEMORY_COMPRESS CON_ID
------- ----- --------------- ----- ----- --------------- ------------- ------------ ------------------- --------- -------- --------------- ------------- ----------------- ------
      1 CHF   XIFENFEI_888          TABLE RPT_DATA           1510211584   3571449856          1444610048 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
      2 CHF   XIFENFEI_888          TABLE RPT_DATA           1069481984   3571449856          2058321920 COMPLETED NONE     AUTO            NO DUPLICATE  FOR QUERY LOW          0
SQL> set autot on
SQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
set autot off
  COUNT(*)
----------
  16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
        776  consistent gets
          0  physical reads
          0  redo size
        545  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
SQL> alter system flush buffer_cache
  2  ;
System altered.
SQL> /
System altered.
SQL>
SQL> set autot on
select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;
set autot off
SQL>
  COUNT(*)
----------
  16883988
Execution Plan
----------------------------------------------------------
Plan hash value: 1642441725
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |     1 | 18629   (1)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE                 |              |     1 |            |          |        |      |            |
|   2 |   PX COORDINATOR                |              |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)          | :TQ10000     |     1 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE              |              |     1 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR          |              |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS INMEMORY FULL| XIFENFEI_888 |    16M| 18629   (1)| 00:00:01 |  Q1,00 | PCWP |            |
---------------------------------------------------------------------------------------------------------------------
Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2
   - parallel scans affinitized for inmemory
Statistics
----------------------------------------------------------
          6  recursive calls
          0  db block gets
        776  consistent gets
          2  physical reads
          0  redo size
        545  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
SQL> spool off

通过测试证明,在RAC环境中,如果要使用IN MEMORY特性,需要设置parallel_degree_policy=AUTO和parallel_force_local=false之后,才能够真正意义上的启动IM特性,不然只是执行计划中的启用,可能是假象

Oracle 12C TABLE ACCESS BY INDEX ROWID BATCHED

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：Oracle 12C TABLE ACCESS BY INDEX ROWID BATCHED

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

从Oracle 12C开始执行计划中可能会出现TABLE ACCESS BY INDEX ROWID BATCHED,官方的解释：TABLE ACCESS BY INDEX ROWID BATCHED：means that the database retrieves a few rowids from the index, and then attempts to access rows in block order to improve the clustering and reduce the number of times that the database must access a block.主要意思:对于一个块中多个rowid,通过批量减少访问快的次数.而作为12.1的新特性,数据库是通过_optimizer_batch_table_access_by_rowid来控制的

数据库版本12.1

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production                         0
NLSRTL Version 12.1.0.2.0 - Production                                                    0

TABLE ACCESS BY INDEX ROWID BATCHED执行计划

SQL> set autot traceonly exp stat;
SQL> var  b1 number;
SQL> set lines 150
SQL> set pages 10000
SQL> exec :b1:=18868701138;
PL/SQL procedure successfully completed.
SQL> SELECT BRAND_ID FROM T_USERTYPE_FULLNO WHERE BILL_ID= LTRIM(:B1 ,'0') AND ROWNUM < 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 942613467
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                    |     1 |    15 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                       |                    |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| XIFENFEI           |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | IND_XIFENFEI       |     1 |       |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<2)
   3 - access("BILL_ID"=LTRIM(:B1,'0'))
Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        559  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

_optimizer_batch_table_access_by_rowid参数为true

SQL> col name for a32
SQL> col value for a24
col description for a70
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
SQL> SQL> SQL>   2    from x$ksppi a,x$ksppcv b
  3   where a.inst_id = USERENV ('Instance')
  4     and b.inst_id = USERENV ('Instance')
  5     and a.indx = b.indx
  6     and upper(a.ksppinm) LIKE upper('%&param%')
  7  order by name
  8  /
Enter value for param: _optimizer_batch_table_access_by_rowid
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optimizer_batch_table_access_by_rowid%')
NAME                                 VALUE                    DESCRIPTION
------------------------------------- ------------------------ ----------------------------------------------
_optimizer_batch_table_access_by_rowid TRUE                     enable table access by ROWID IO batching

设置_optimizer_batch_table_access_by_rowid为false,执行计划由TABLE ACCESS BY INDEX ROWID BATCHED变为TABLE ACCESS BY INDEX ROWID

SQL> set autot traceonly exp stat;
SQL> var  b1 number;
SQL> set lines 150
SQL> set pages 10000
SQL> exec :b1:=18868701138;
PL/SQL procedure successfully completed.
SQL> alter session set "_optimizer_batch_table_access_by_rowid"=false;
Session altered.
SQL> SELECT BRAND_ID FROM XIFENFEI WHERE BILL_ID= LTRIM(:B1 ,'0') AND ROWNUM < 2;
Execution Plan
----------------------------------------------------------
Plan hash value: 2797551150
---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |     1 |    15 |     2   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY               |                    |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| XIFENFEI           |     1 |    15 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IND_XIFENFEI       |     1 |       |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<2)
   3 - access("BILL_ID"=LTRIM(:B1,'0'))
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        559  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

optimizer_features_enable修改为11.2之后,_optimizer_batch_table_access_by_rowid会联锁变为fasle

SQL> alter session set optimizer_features_enable = '11.2.0.3';
Session altered.
SQL> col name for a52
col value for a24
col description for a50
set linesize 150
select a.ksppinm name,b.ksppstvl value,a.ksppdesc description
  from x$ksppi a,x$ksppcv b
 where a.inst_idSQL> SQL> SQL> SQL>   2    3   = USERENV ('Instance')
   and b.inst_id = USERENV ('Instance')
   and a.indx = b.indx
   and upper(a.ksppinm) LIKE upper('%&param%')
order by name
  4    5    6    7    8  /
Enter value for param: _optimizer_batch_table_access_by
old   6:    and upper(a.ksppinm) LIKE upper('%&param%')
new   6:    and upper(a.ksppinm) LIKE upper('%_optimizer_batch_table_access_by%')
NAME                                       VALUE          DESCRIPTION
------------------------------------------ -------------- -----------------------------------------
_optimizer_batch_table_access_by_rowid     FALSE          enable table access by ROWID IO batching

这里可以看出来,在调整optimizer_features_enable参数后,会直接影响某些数据库相关的优化器参数,例如:_optimizer_batch_table_access_by_rowid

InMemory评估利器—Oracle Database InMemory Advisor

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：InMemory评估利器—Oracle Database InMemory Advisor

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

我想大家都可能有一个疑问,数据库从以前版本升级到12C,计划使用in-memory特性,那我怎么评估哪些表放进去合适,放进去后,整体性能又会提升多少,需要in-memory内存为多大？这些问题如果人工去判断不一定准确,而且可能有很多遗落,Oracle也考虑到了这一点,因此发布了Oracle Database InMemory Advisor,从而来比较简单的实现数据库使用In-Memory简单配置.Oracle Database InMemory Advisor主要通过DBMS_INMEMORY_ADVISOR包分析 Active Session History (ASH), Automatic Workload Repository (AWR) and SQL Tuning Sets (STS)信息进行分析,给出建议.

Oracle Database InMemory Advisor使用前提
1.数据库版本为11.2.0.3及其以上版本(compatibility>=11.2.0.3)
2.需要有Database Tuning pack liscense支持(仅仅是liscense,不是功能)

安装Oracle Database InMemory Advisor

[oracle@localhost xff]$ unzip imadvisor.zip
Archive:  imadvisor.zip
  inflating: instimadv.sql
  inflating: dbmsimadv.sql
  inflating: prvtimadv.plb
  inflating: dbmsimadvint.plb
  inflating: prvtimadvint.plb
  inflating: schmimadv.sql
  inflating: imadvisor_version.sql
  inflating: imadvisor_load_report_templates.sql
  inflating: imadvisor_clone_view.sql
  inflating: imadvisor_analyze_and_report.sql
  inflating: imadvisor_spool_debug.sql
  inflating: imadvisor_export.sql
  inflating: imadvisor_DataPump.sql
  inflating: imadvisor_awr_augment_export.sql
  inflating: imadvisor_awr_augment_import.sql
  inflating: imadvisor_awr_augment_tables.sql
  inflating: imadvisor_fetch_recommendations.sql
[oracle@localhost xff]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 16:21:08 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @instimadv
Welcome to the Oracle Database In-Memory Advisor (DBMS_INMEMORY_ADVISOR)
installation.
The Oracle Database In-Memory Advisor is licensed as part of the Oracle Tuning Pack.
Do you currently have a valid Oracle Tuning Pack license with this database (Y/N)? Y
DBMS_INMEMORY_ADVISOR uses Active Session History (ASH), Automatic Workload
Repository (AWR) and optionally SQL Tuning Sets (STS) to determine which
tables, partitions and subpartitions to place In Memory for optimized analytics
processing performance.  DBMS_INMEMORY_ADVISOR produces a recommendation report
and a SQLPlus script to implement its recommendations.
This installation script will create user IMADVISOR and add object
definitions to the schema including the DBMS_INMEMORY_ADVISOR package.  This
installation script creates user IMADVISOR using the
IDENTIFIED BY password method. If you prefer to use either the
IDENTIFIED EXTERNALLY or IDENTIFIED GLOBALLY method, abort this installation
by pressing ^C. Then create user IMADVISOR using your preferred
method.  Add no objects to the IMADVISOR schema.  Then run this
installation script again.
These actions will be taken on the database to which you are currently
connected.
Please enter the connection ID for the current database?
qsng
This installation script creates a new Oracle database user and schema
named IMADVISOR for the operation of DBMS_INMEMORY_ADVISOR...
Please enter the password for user IMADVISOR?
For confirmation, please re-enter the password for user IMADVISOR?
Available tablespaces:
TABLESPACE_NAME
------------------------------
FSDATA
MYDATA
SYSAUX
SYSTEM
TEMP
UNDOTBS1
USERS
Please enter the default tablespace name for user IMADVISOR?
users
The In-Memory Advisor uses the Oracle directory object IMADVISOR_DIRECTORY
by default.
If you wish to create the IMADVISOR_DIRECTORY object, please enter an OS host
directory path for the IMADVISOR_DIRECTORY object.
If not, please press ENTER to continue.
? /tmp/xff
Connecting to IMADVISOR @ qsng..
Enter password:
Connected.
No errors.
No errors.
No errors.
No errors.
No errors.
No errors.
DBMS_INMEMORY_ADVISOR installation successful.
Users who will use the DBMS_INMEMORY_ADVISOR package must be GRANTed
EXECUTE on the DBMS_INMEMORY_ADVISOR package.
Please enter a comma separated list of Oracle Database users to whom
you wish EXECUTE on the DBMS_INMEMORY_ADVISOR package to be GRANTed?
SYS
GRANT EXECUTE ON dbms_inmemory_advisor TO SYS
While logged in as IMADVISOR or with sufficient privileges, you can
GRANT EXECUTE ON DBMS_INMEMORY_ADVISOR to additional users as needed.
DBMS_INMEMORY_ADVISOR installation and setup complete.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Oracle Database InMemory Advisor生成报告

[oracle@localhost xff]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 16:30:57 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @imadvisor_analyze_and_report
Specify the IM task name
The IM Advisor generates a report as  imadvisor_<taskname>.html file
in the current working directory
The sql file is generated as imadvisor_sql_<taskname>.sql
Enter value for im_task_name: xifenfei
IM Task name Specified: xifenfei
Enter begin time for report:
--    Valid input formats:
--      To specify absolute begin time:
--        [MM/DD[/YY]] HH24:MI[:SS]
--        Examples: 02/23/03 14:30:15
--                  02/23 14:30:15
--                  14:30:15
--                  14:30
--      To specify relative begin time: (start with '-' sign)
--        -[HH24:]MI
--        Examples: -1:15  (SYSDATE - 1 Hr 15 Mins)
--                  -25    (SYSDATE - 25 Mins)
Enter value for begin_time: -3
Report begin time specified: -3
old 102:   lbtime_in  := nvl('&&begin_time', '-60');
new 102:   lbtime_in  := nvl('-3', '-60');
old 104:   :btime := to_char( begin_time, '&&imadvisor_time_format' );
new 104:   :btime := to_char( begin_time, 'YYYY-MON-DD HH24:MI:SS.FF' );
Enter duration in minutes starting from begin time:
Defaults to SYSDATE - begin_time
Enter value for duration:
Report duration specified:
old   8:   begin_time := to_timestamp(:btime, '&&imadvisor_time_format');
new   8:   begin_time := to_timestamp(:btime, 'YYYY-MON-DD HH24:MI:SS.FF');
old  18:   duration   := nvl('&&duration', since_begin_time);
new  18:   duration   := nvl('', since_begin_time);
old  29:   :etime := to_char( end_time, '&&imadvisor_time_format' );
new  29:   :etime := to_char( end_time, 'YYYY-MON-DD HH24:MI:SS.FF' );
Using 2015-MAR-18 16:28:22.000000000 as report begin time
Using 2015-MAR-18 16:31:22.000000000 as report end time
IM Advisor: Adding Statistics..
IMADVISOR: Finished Adding Statistics
IMADVISOR: Finished Executing the task
IM Advisor: Generating Recommendations..
imadvisor_xifenfei.html
imadvisor_sql_xifenfei.html
imadvisor_object_xifenfei.html
imadvisor_xifenfei.sql
'Fetching recommendation files for task xifenfei'
IM Advisor generated report in imadvisor_xifenfei.html
IM Advisor genreated DDL script in imadvisor_xifenfei.sql
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@localhost xff]$ ls -ltr *xifenfei*
-rw-r--r-- 1 oracle oinstall   887 Mar 18 16:33 imadvisor_xifenfei.sql
-rw-r--r-- 1 oracle oinstall 30175 Mar 18 16:33 imadvisor_xifenfei.html
-rw-r--r-- 1 oracle oinstall 13576 Mar 18 16:33 imadvisor_sql_xifenfei.html
-rw-r--r-- 1 oracle oinstall  8931 Mar 18 16:33 imadvisor_object_xifenfei.html
-rw-r--r-- 1 oracle oinstall  3405 Mar 18 16:33 imadvisor_auxiliary_xifenfei.html

这里输入的Task name为:xifenfei,Oracle Database InMemory Advisor结果
imadvisor_xifenfei.html是InMemory Advisor的一个整体描述
imadvisor_xifenfei.sql是InMemory Advisor生成的表级别的INMEMORY语句,可以直接通过@方式执行,或者修改后执行
imadvisor_sql_xifenfei.html主要是InMemory Advisor中关于sql的分析报告
imadvisor_object_xifenfei.html是InMemory Advisor中建议InMemory处理的对象分析报告
imadvisor_auxiliary_xifenfei.html 是一个辅助的总结

卸载Oracle Database InMemory Advisor

[oracle@localhost ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Mar 18 21:55:49 2015
Copyright (c) 1982, 2013, Oracle.  All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> drop user imadvisor cascade;
User dropped.

补充内容
1.在某些情况下,可能无法直接创建用户imadvisor,或者创建目录imadvisor_directory,可以通过类似命令创建,并修改instimadv.sql脚本屏蔽掉交互式安装

create user IMADVISOR identified by Oracle_123 DEFAULT TABLESPACE system;
CREATE OR REPLACE DIRECTORY imadvisor_directory AS '/tmp/xff/txt';
GRANT READ, WRITE ON DIRECTORY imadvisor_directory TO IMADVISOR;

2.在执行@imadvisor_analyze_and_report生成报告,可能会遇到ORA-24817: Unable to allocate the given chunk for current lob operation,解决方案为：

1. Copy the original script to a new location to serve as a backup.
2. Edit the script imadvisor_fetch_recommendations.sql and change the line in the script:
SET LONGCHUNKSIZE 2000000000
to
SET LONGCHUNKSIZE 32767
3. Save and run the script.

参考文档
Oracle Database InMemory Advisor (Doc ID 1965343.1)
Using Inmemory Advisor Errors When Running Imadvisor_analyze_and_report (Doc ID 1987462.1)

ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C 新特性Identity Columns—实现ORACLE自增长列功能

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

在ORACLE 12C以前的版本中,如果要实现列自增长,需要通过序列+触发器实现,到了12C ORACLE 引进了Identity Columns新特性,从而实现了列自增长功能,和mysql,sql server类似功能.
使用语法
ORACLE 12C IDENTITY
GENERATED ALWAYS AS IDENTITY方式测试

C:\Users\ffcheng>sqlplus chf/xifenfei@pdb
SQL*Plus: Release 12.1.0.2.0 Production on 星期二 3月 10 14:34:46 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
上次成功登录时间: 星期五 12月 19 2014 21:00:26 +08:00
连接到:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing opt
ions
XFF_PDB@CHF> select * from v$version;
BANNER                                                 CON_ID
-------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.          0
1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production                      0
CORE    12.1.0.2.0      Production                          0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Produ          0
ction
NLSRTL Version 12.1.0.2.0 - Production                      0
XFF_PDB@CHF>  create table t_xifenfei (id number GENERATED ALWAYS AS IDENTITY,na
me varchar2(100));
表已创建。
XFF_PDB@CHF> select object_name,object_type from user_objects;
OBJECT_NAME     OBJECT_TYPE
--------------- -----------------------
ISEQ$$_91982    SEQUENCE
T_XIFENFEI      TABLE
XFF_PDB@CHF> set long 10000
XFF_PDB@CHF> select dbms_metadata.get_ddl('TABLE','T_XIFENFEI') FROM DUAL;
DBMS_METADATA.GET_DDL('TABLE','T_XIFENFEI')
--------------------------------------------------------------------------------
  CREATE TABLE "CHF"."T_XIFENFEI"
   (    "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE
   9999999999999999999999999999 INCREMENT BY 1 START WITH 1
   CACHE 20 NOORDER  NOCYCLE  NOT NULL ENABLE,
        "NAME" VARCHAR2(100)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  TABLESPACE "INMEMORY"
XFF_PDB@CHF> INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM');
INSERT INTO T_XIFENFEI VALUES(1,'WWW.XIFNEFEI.COM')
*
第 1 行出现错误:
ORA-32795: 无法插入到“始终生成”身份列
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFNEFEI.COM');
已创建 1 行。
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.orasos.COM');
已创建 1 行。
XFF_PDB@CHF> commit;
提交完成。
XFF_PDB@CHF> col name for a30
XFF_PDB@CHF> select * from t_xifenfei;
        ID NAME
---------- ------------------------------
         1 WWW.XIFNEFEI.COM
         2 WWW.orasos.COM
XFF_PDB@CHF> update t_xifenfei set id=3 where id=2;
update t_xifenfei set id=3 where id=2
                      *
第 1 行出现错误:
ORA-32796: 无法更新“始终生成”身份列
XFF_PDB@CHF> delete from t_xifenfei where id=1;
已删除 1 行。
XFF_PDB@CHF> commit;
提交完成。
XFF_PDB@CHF> select ISEQ$$_91982.nextval from dual;
   NEXTVAL
----------
         3
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.ORASOS.COM');
已创建 1 行。
XFF_PDB@CHF> commit;
提交完成。
XFF_PDB@CHF> select * from t_xifenfei;
        ID NAME
---------- ------------------------------
         2 WWW.orasos.COM
         4 WWW.ORASOS.COM
XFF_PDB@CHF> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 1
2';
会话已更改。
XFF_PDB@CHF>  select value from v$diag_info where name='Default Trace File';
VALUE
--------------------------------------------------------------------------------
D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_10628.trc
XFF_PDB@CHF> INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM');
已创建 1 行。
XFF_PDB@CHF> COMMIT;
提交完成。
--跟踪trace文件
PARSING IN CURSOR #688719640 len=55 dep=0 uid=103 oct=2 lid=103 tim=15129490112 hv=961646460
ad='7ff05d11a18' sqlid='21uzyjhwp33vw'
INSERT INTO T_XIFENFEI(name) VALUES('WWW.XIFENFEI.COM')
END OF STMT
PARSE #688719640:c=15600,e=18909,p=0,cr=44,cu=0,mis=1,r=0,dep=0,og=1,plh=2541165129,tim=15129490112
EXEC #688719640:c=0,e=347,p=0,cr=1,cu=5,mis=0,r=1,dep=0,og=1,plh=2541165129,tim=15129490731
STAT #688719640 id=1 cnt=0 pid=0 pos=1 obj=0 op='LOAD TABLE CONVENTIONAL  T_XIFENFEI (cr=1 pr=0 pw=0 time=296 us)'
STAT #688719640 id=2 cnt=1 pid=1 pos=1 obj=91983 op='SEQUENCE  ISEQ$$_91982 (cr=0 pr=0 pw=0 time=89 us)'
WAIT #688719640: nam='SQL*Net message to client' ela= 2 driver id=1413697536 #bytes=1 p3=0 obj#=-1 tim=15129490971
XFF_PDB@CHF> alter table t_xifenfei2 modify(id number GENERATED ALWAYS AS IDENTI
TY MINVALUE 1 MAXVALUE 99999999999999 INCREMENT BY 1 START WITH 1 CACHE  100);
表已更改。
XFF_PDB@CHF> drop sequence ISEQ$$_91982;
drop sequence ISEQ$$_91982
              *
第 1 行出现错误:
ORA-32794: 无法删除系统生成的序列
XFF_PDB@CHF> drop table t_xifenfei;
表已删除。
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';
OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';
OBJECT_NAME          OBJECT_TYPE
-------------------- -----------------------
ISEQ$$_91982         SEQUENCE
XFF_PDB@CHF> purge table t_xifenfei;
表已清除。
XFF_PDB@CHF> select object_name,object_type from user_objects where object_name=
'ISEQ$$_91982';
未选定行

这里的出来几个结论：
1. GENERATED ALWAYS AS IDENTITY 列无法人工指定值和修改该值
2. GENERATED IDENTITY 本质也是通过sequence实现
3. GENERATED IDENTITY 中sequence不能单独被删除
4. GENERATED IDENTITY 中的表删除,如果存在回收站中,该sequence依然存储,如果表被彻底删除,则sequence也被删除
5. GENERATED IDENTITY 中的sequence可以通过select 语句查询
6. 通过alert table 语句来修改GENERATED IDENTITY 的sequence相关值

GENERATED BY DEFAULT AS IDENTITY方式测试

XFF_PDB@CHF>  create table t_xifenfei2 (id number GENERATED BY DEFAULT AS IDENTI
TY,name varchar2(100)) tablespace users;
表已创建。
XFF_PDB@CHF> insert into t_xifenfei2 values (1,'www.xifenfei.com');
已创建 1 行。
XFF_PDB@CHF> insert into t_xifenfei2(name) values ('www.orasos.com');
已创建 1 行。
XFF_PDB@CHF> col name for a20
XFF_PDB@CHF> select * from t_xifenfei2;
        ID NAME
---------- --------------------
         1 www.xifenfei.com
         1 www.orasos.com
XFF_PDB@CHF> insert into t_xifenfei2 values (null,'www.xifenfei.com');
insert into t_xifenfei2 values (null,'www.xifenfei.com')
                                *
第 1 行出现错误:
ORA-01400: 无法将 NULL 插入 ("CHF"."T_XIFENFEI2"."ID")
XFF_PDB@CHF> desc t_xifenfei2
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ---------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)

得出结论：
1. GENERATED BY DEFAULT AS IDENTITY方式不能在该列中插入null值
2. GENERATED BY DEFAULT AS IDENTITY方式可以指定具体值插入

GENERATED BY DEFAULT ON NULL AS IDENTITY方式测试

XFF_PDB@CHF>  create table t_xifenfei3 (id number GENERATED BY DEFAULT on null A
S IDENTITY,name varchar2(100)) tablespace users;
表已创建。
XFF_PDB@CHF> desc t_xifenfei3
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 ID                                        NOT NULL NUMBER
 NAME                                               VARCHAR2(100)
XFF_PDB@CHF> insert into t_xifenfei3 values (null,'www.xifenfei.com');
已创建 1 行。
XFF_PDB@CHF> select * from t_xifenfei3;
        ID NAME
---------- --------------------
         1 www.xifenfei.com

测试结论：GENERATED BY DEFAULT ON NULL AS IDENTITY的列上可以查询null值，只是默认转换为对应的sequence值

传统自增长列实现方法

XFF_PDB@CHF> create table t_xifenfei4 (id number,name varchar2(100)) tablespace
users;
表已创建。
XFF_PDB@CHF> create sequence xff_sequence
  2  increment by 1
  3  minvalue 1
  4  nomaxvalue
  5  start with 1
  6  cache 20
  7  order;
序列已创建。
XFF_PDB@CHF> create or replace trigger xifenfei_id
  2  before insert on t_xifenfei4
  3  for each row
  4  begin
  5  select xff_sequence.nextval into :new.id from dual;
  6  end;
  7  /
触发器已创建
XFF_PDB@CHF> insert into t_xifenfei4(name) values('www.xifenfei.com');
已创建 1 行。
XFF_PDB@CHF> select * from t_xifenfei4;
        ID NAME
---------- --------------------
         1 www.xifenfei.com

设置pdb随cdb一起启动

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：设置pdb随cdb一起启动

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

在Oracle 12.1.0.1版本中,在cdb数据库启动过程中,业务pdb无法自动open,如果要实现该功能,需要人工写触发器来实现cdb open后,pdb 给open起来.在12.1.0.2及其以后版本,可以通过设置ALTER PLUGGABLE DATABASE PDB SAVE STATE来实现在cdb open之后业务pdb能够自动open.
数据库启动后pdb未自动open

XFF_CDB$ROOT@SYS> startup
ORACLE 例程已经启动。
Total System Global Area  805306368 bytes
Fixed Size                  3050800 bytes
Variable Size             394265296 bytes
Database Buffers          297795584 bytes
Redo Buffers                5337088 bytes
In-Memory Area            104857600 bytes
数据库装载完毕。
数据库已经打开。
XFF_CDB$ROOT@SYS> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0
XFF_CDB$ROOT@SYS> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED

查询dba_pdb_saved_states无记录

XFF_CDB$ROOT@SYS>  select con_name, state from dba_pdb_saved_states;
未选定行

在数据库mount状态下save state

XFF_CDB$ROOT@SYS>  ALTER PLUGGABLE DATABASE PDB save state;
插接式数据库已变更。
XFF_CDB$ROOT@SYS>  select con_name, state from dba_pdb_saved_states;
未选定行

pdb为mount状态下,执行save state无记录,证明save state不成功

在数据库open状态下save state—-设置pdb随cdb启动

XFF_CDB$ROOT@SYS> ALTER PLUGGABLE DATABASE PDB open;
插接式数据库已变更。
XFF_CDB$ROOT@SYS>  ALTER PLUGGABLE DATABASE PDB save state;
插接式数据库已变更。
XFF_CDB$ROOT@SYS> col con_name for a20
XFF_CDB$ROOT@SYS>  select con_name, state from dba_pdb_saved_states;
CON_NAME             STATE
-------------------- --------------
PDB                  OPEN

pdb为open状态下,执save state成功.
需要注意save state需要在pdb open情况下执行才能够生效.

重启数据库测试pdb随cdb启动

XFF_CDB$ROOT@SYS> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
XFF_CDB$ROOT@SYS> startup
ORACLE 例程已经启动。
Total System Global Area  805306368 bytes
Fixed Size                  3050800 bytes
Variable Size             364905168 bytes
Database Buffers          327155712 bytes
Redo Buffers                5337088 bytes
In-Memory Area            104857600 bytes
数据库装载完毕。
数据库已经打开。
XFF_CDB$ROOT@SYS> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            READ WRITE NO

禁用pdb随cdb启动—DISCARD STATE

XFF_CDB$ROOT@SYS>  ALTER PLUGGABLE DATABASE PDB DISCARD  state;
插接式数据库已变更。
XFF_CDB$ROOT@SYS>  select con_name, state from dba_pdb_saved_states;
未选定行
XFF_CDB$ROOT@SYS> shutdown immediate;
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
XFF_CDB$ROOT@SYS> startup
ORACLE 例程已经启动。
Total System Global Area  805306368 bytes
Fixed Size                  3050800 bytes
Variable Size             364905168 bytes
Database Buffers          327155712 bytes
Redo Buffers                5337088 bytes
In-Memory Area            104857600 bytes
数据库装载完毕。
数据库已经打开。
XFF_CDB$ROOT@SYS> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB                            MOUNTED
XFF_CDB$ROOT@SYS>

12.1.0.1中设置pdb随cdb启动

CREATE TRIGGER open_all_pdbs
   AFTER STARTUP
   ON DATABASE
BEGIN
   EXECUTE IMMEDIATE 'alter pluggable database all open';
END open_all_pdbs;
/

对于IN Memory Option 部分细节测试—主要当inmemory_size不足之时

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：对于IN Memory Option 部分细节测试—主要当inmemory_size不足之时

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

本文对于IMDB的几个特性进行了具体测试:
1. 压缩级别和压缩率(具体也需要具体测试),本实验仅提供参考
2. 对于IM空间不足已经存在的对象和加入新对象的现象
3. 对于PRIORITY级别进行了简单测试
数据库基本配置信息

SQL> select * from v$version;
BANNER                                                                               CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production              0
PL/SQL Release 12.1.0.2.0 - Production                                                    0
CORE    12.1.0.2.0      Production                                                        0
TNS for 64-bit Windows: Version 12.1.0.2.0 - Production                                   0
NLSRTL Version 12.1.0.2.0 - Production                                                    0
SQL> show parameter inmemory;
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 100M
inmemory_trickle_repopulate_servers_ integer     1
percent
optimizer_inmemory_aware             boolean     TRUE

数据库版本12.1.0.2,inmemory_size配置为100M

准备测试环境

SQL> create tablespace inmemory datafile 'D:\APP\FFCHENG\ORADATA\XFF\PDB\in_memory01.dbf'
   2  size 100m autoextend on next 4m maxsize 10g;
表空间已创建。
SQL> create user chf identified by xifenfei;
用户已创建。
SQL> grant dba to chf;
授权成功。
SQL> alter user chf default tablespace inmemory;
用户已更改。
SQL> create table chf.t_inmemory1 as select * from dba_objects;
表已创建。

创建测试表空间,用户,测试表

测试压缩级别

SQL> alter table chf.t_inmemory1 inmemory NO MEMCOMPRESS;
表已更改。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     NO MEMCOMPRESS
--NO MEMCOMPRESS 压缩比例非常小，基本上不压缩
SQL> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL>  alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR DML
  2  ;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     FOR DML
--FOR DML 压缩比例非常小，基本上不压缩和NO MEMCOMPRESS在压缩效果上类似
SQL> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR QUERY LOW;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       4325376 NONE     FOR QUERY LOW
-- FOR QUERY LOW为默认压缩级别,这里看压缩比例在3:1左右,具体取决于数据
SQL> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR QUERY HIGH;
表已更改。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
未选定行
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       3276800 NONE     FOR QUERY HIGH
-- FOR QUERY HIGH,这里看压缩比例在4:1左右,具体取决于数据
SQL> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR CAPACITY LOW;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488       2228224 NONE     FOR CAPACITY LOW
-- FOR CAPACITY LOW,这里看压缩比例在6:1左右,具体取决于数据
SQL> alter table chf.t_inmemory1 no inmemory ;
表已更改。
SQL> alter table chf.t_inmemory1 inmemory MEMCOMPRESS FOR CAPACITY HIGH;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488        131072 NONE     FOR CAPACITY HIGH
-- FOR CAPACITY HIGH,这里看压缩比例在10:1左右,具体取决于数据

这里可以看出来,压缩效果确实如Oracle所描述,级别越高压缩效果越好.

测试inmemory_size大小不足之时

SQL> alter table chf.t_inmemory1 inmemory no MEMCOMPRESS;
表已更改。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
     91040
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  13631488      10616832 NONE     NO MEMCOMPRESS
--dml插入数据,不再次查询数据,v$im_segments.inmemory_size不发生改变(这个是bug还是设计考虑？？)
SQL> insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 91040 行。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      10616832 NONE     NO MEMCOMPRESS
SQL> commit;
提交完成。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      10616832 NONE     NO MEMCOMPRESS
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
    182080
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  26214400      22282240 NONE     NO MEMCOMPRESS
SQL> insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 182080 行。
SQL> commit;
提交完成。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      22282240 NONE     NO MEMCOMPRESS
--通过10046证明,虽然v$im_segments.inmemory_size值未及时更新,但是IMDB是生效的
SQL> oradebug setmypid
已处理的语句
SQL> alter session set db_file_multiblocK_read_count=1;
会话已更改。
SQL> oradebug EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12
已处理的语句
SQL> oradebug TRACEFILE_NAME
D:\APP\FFCHENG\diag\rdbms\xff\xff\trace\xff_ora_7604.trc
SQL> SELECT COUNT(object_id) FROM chf.t_inmemory1;
COUNT(OBJECT_ID)
----------------
          364156
SQL> oradebug EVENT 10046 trace name context off
已处理的语句
PARSING IN CURSOR #455134016 len=44 dep=0 uid=0 oct=3 lid=0 tim=126773093621 hv=1133975269
 ad='7ff07339500' sqlid='5909ukj1tf5r5'
SELECT COUNT(object_id) FROM chf.t_inmemory1
END OF STMT
PARSE #455134016:c=15600,e=3912,p=0,cr=2,cu=0,mis=1,r=0,dep=0,og=1,plh=3154396630,tim=126773093620
WAIT #455134016: nam='Disk file operations I/O' ela= 154 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=126773093926
EXEC #455134016:c=0,e=25,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,plh=3154396630,tim=126773094005
WAIT #455134016: nam='SQL*Net message to client' ela= 2 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773094044
FETCH #455134016:c=0,e=13751,p=0,cr=3110,cu=1,mis=0,r=1,dep=0,og=1,plh=3154396630,tim=126773107829
STAT #455134016 id=1 cnt=1 pid=0 pos=1 obj=0 op='SORT AGGREGATE (cr=3110 pr=0 pw=0 time=13751 us)'
STAT #455134016 id=2 cnt=364160 pid=1 pos=1 obj=91914 op='TABLE ACCESS INMEMORY FULL T_INMEMORY1 (cr=3110 pr=0
  pw=0 time=5386 us cost=17 size=455200 card=91040)'
WAIT #455134016: nam='SQL*Net message from client' ela= 116 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773108164
FETCH #455134016:c=0,e=1,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,plh=3154396630,tim=126773108215
WAIT #455134016: nam='SQL*Net message to client' ela= 0 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=126773108246
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      43384832 NONE     NO MEMCOMPRESS
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
    364160
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
  51380224      43384832 NONE     NO MEMCOMPRESS
SQL> insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 364160 行。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      43384832 NONE     NO MEMCOMPRESS
SQL> commit;
提交完成。
SQL> SELECT COUNT(*) FROM chf.t_inmemory1;
  COUNT(*)
----------
    728320
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      43384832 NONE     NO MEMCOMPRESS
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 109051904      71892992 NONE     NO MEMCOMPRESS
--这里可以看出来INMEMORY_SIZE已经使用了71892992,再插入一次数据,一共100M的IM肯定不够使用
SQL> insert into chf.t_inmemory1 select * from chf.t_inmemory1;
已创建 728320 行。
SQL> commit;
提交完成。
SQL> select count(object_id) from chf.t_inmemory1;
COUNT(OBJECT_ID)
----------------
         1456624
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS
--这里现在的INMEMORY_SIZE变为了63438848小于在插入数据之前的71892992,证明IM肯定出现问题,比如已经满了,
  v$im_segments显示值不准确
--测试刷新buffer_cache对IM的影响
SQL> alter system flush buffer_cache;
系统已更改。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS
--结果证明无影响
autotrace结果
SQL> set autot trace exp stat
SQL> set lines 120
SQL> pages 1000
SQL> set pages 1000
SQL> select count(*) from chf.t_inmemory1;
执行计划
----------------------------------------------------------
Plan hash value: 3154396630
-----------------------------------------------------------------------------------
| 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_INMEMORY1 | 91040 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          5  recursive calls
          0  db block gets
      16693  consistent gets
      16690  physical reads
          0  redo size
        546  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
10046结果
SQL ID: 1b61dgunxftdx Plan Hash: 3154396630
select count(object_id)
from
 chf.t_inmemory1
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          2          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      1.26       4.14      16689      22446          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      1.26       4.14      16689      22448          0           1
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: SYS
Number of plan statistics captured: 1
Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  SORT AGGREGATE (cr=22446 pr=16689 pw=0 time=4144536 us)
   1456640    1456640    1456640   TABLE ACCESS INMEMORY FULL T_INMEMORY1 (cr=22446 pr=16689 pw=0
                                         time=2560999 us cost=17 size=455200 card=91040)
Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  Disk file operations I/O                        1        0.00          0.00
  SQL*Net message to client                       2        0.00          0.00
  db file sequential read                     16689        0.03          3.05
  SQL*Net message from client                     2        5.40          5.40
--autotrace和10046都证明,当IM size不足之时,数据库未能够使用IM的特性,哪怕是部分也不能使用
--创建新对象存放IM中
SQL> create table chf.t_inmemory2 as select * from dba_objects;
表已创建。
SQL> alter table chf.t_inmemory2 inmemory;
表已更改。
SQL> select count(*) from chf.t_inmemory2;
  COUNT(*)
----------
     91041
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
  2  from v$im_segments where segment_name = 'T_INMEMORY1';
SEGMENT_NAME
--------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      63438848 NONE     NO MEMCOMPRESS
SQL> select count(*) from chf.t_inmemory2;
执行计划
----------------------------------------------------------
Plan hash value: 2042227318
-----------------------------------------------------------------------------------
| 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_INMEMORY2 | 91041 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1532  consistent gets
       1530  physical reads
          0  redo size
        545  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
SQL> alter table chf.t_inmemory1 no inmemory;
表已更改。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
   2  from v$im_segments where segment_name = 'T_INMEMORY2';
未选定行
SQL> set autot traceonly exp stat
SQL> select count(*) from chf.t_inmemory2;
执行计划
----------------------------------------------------------
Plan hash value: 2042227318
-----------------------------------------------------------------------------------
| 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_INMEMORY2 | 91041 |    16   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
         57  recursive calls
          0  db block gets
       1565  consistent gets
       1532  physical reads
          0  redo size
        545  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          1  rows processed
SQL> set autot off
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION
   2  from v$im_segments where segment_name = 'T_INMEMORY2';
SEGMENT_NAME
-----------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY2
  13631488       4325376 NONE     FOR QUERY LOW
SQL> set autot traceonly exp stat
SQL> select count(*) from chf.t_inmemory2;
执行计划
----------------------------------------------------------
Plan hash value: 2042227318
-----------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    53   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE             |             |     1 |            |          |
|   2 |   TABLE ACCESS INMEMORY FULL| T_INMEMORY2 | 91041 |    53   (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          3  consistent gets
          0  physical reads
          0  redo size
        545  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
--当IM已经无空闲空间之时,创建新对象在PRIORITY未提升之前,即便是设置了IM和对对象进行了访问,也无法存入IM

上述测试几个结论:
1. 随着IM中对象的增加，当INMEMORY_SIZE不足之时,v$im_segments.INMEMORY_SIZE显示不准确
2. 随着IM中对象的增加，当INMEMORY_SIZE不足之时,当IM中的对象不能全部在IM中之时,对其对象操作,会转换成传统数据库操作,
不会使用部分的IM特性,但是执行计划依然提示使用INMEMORY
3. flush buffer_cache 不影响对象的IM
4. 当IM已经无空闲空间之时,创建新对象在PRIORITY未提升之前,即便是设置了IM和对对象进行了访问,也无法存入IM,
访问依然是传统方式,但是执行计划是INMEMORY

测试PRIORITY

SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      57999360 NONE     NO MEMCOMPRESS
SQL> alter table chf.t_inmemory2  inmemory;
表已更改。
SQL> select count(*) from chf.t_inmemory2;
  COUNT(*)
----------
     91041
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY1
 201326592      57999360 NONE     NO MEMCOMPRESS
SQL> alter table chf.t_inmemory1  inmemory no memcompress PRIORITY LOW;
表已更改。
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
未选定行
SQL> select count(*) from chf.t_inmemory2;
  COUNT(*)
----------
     91041
SQL>  select segment_name,bytes,inmemory_size,INMEMORY_PRIORITY,INMEMORY_COMPRESSION from v$im_segments;
SEGMENT_NAME
------------------------------------------------------------------------------------------------------------------------
     BYTES INMEMORY_SIZE INMEMORY INMEMORY_COMPRESS
---------- ------------- -------- -----------------
T_INMEMORY2
  13631488       4325376 NONE     FOR QUERY LOW

这里测试证明：
1. 指定PRIORITY不为none也需要访问对象后才能够放入IM中
2. 当IM不足时,PRIORITY级别高的会把级别低的对象刷出IM

特此声明:本文仅出自个人测试,得出结论,不可作为任何官方依据使用,具体环境需要具体测试

In-Memory整体汇总

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：In-Memory整体汇总

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

本问是对于Oracle 12C中的In-Memory Column Store一个整体的汇总,具体细节知识在以后章节中展开
IM可以针对如下级别进行操作
Column
Table
Materialized view
Tablespace
Partition

可以指定In-Memory操作语句
CREATE TABLE
ALTER TABLE
CREATE TABLESPACE
ALTER TABLESPACE
CREATE MATERIALIZED VIEW
ALTER MATERIALIZED VIEW

压缩级别
IM-Compression-Methods
优先级
IM-Priority-Levels
对象级别操作IM

CREATE TABLE t_xifenfei (
     id        NUMBER(5) PRIMARY KEY,
     test_col  VARCHAR2(15))
  INMEMORY;
ALTER TABLE t_xifenfei INMEMORY;
ALTER TABLE t_xifenfei INMEMORY MEMCOMPRESS FOR CAPACITY LOW;
ALTER TABLE t_xifenfei INMEMORY PRIORITY HIGH;
ALTER TABLE t_xifenfei INMEMORY
  MEMCOMPRESS FOR CAPACITY HIGH
  PRIORITY LOW;
ALTER TABLE t_xifenfei
   INMEMORY MEMCOMPRESS FOR QUERY (
      product_id, product_name, category_id, supplier_id, min_price)
   INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (
      product_description, warranty_period, product_status, list_price)
   NO INMEMORY (
      weight_class, catalog_url);
ALTER TABLE t_xifenfei NO INMEMORY;

补充说明:列级别设置的优先级无效,优先级是表(物化视图)或者分区表级别

表空间级别操作IM

CREATE TABLESPACE xifenfie_im
   DATAFILE '/u02/xifenfei.dbf' SIZE 40M
   ONLINE
   DEFAULT INMEMORY;
ALTER TABLESPACE xifenfie_im DEFAULT INMEMORY
   MEMCOMPRESS FOR CAPACITY HIGH
   PRIORITY LOW;

物化视图级别

CREATE MATERIALIZED VIEW oe.prod_info_mv INMEMORY
  AS SELECT * FROM t_xifenfei;
ALTER MATERIALIZED VIEW oe.prod_info_mv INMEMORY PRIORITY HIGH;

适合使用IN-Memory操作
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

不适合使用IN-Memory操作
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

IM控制参数
INMEMORY_SIZE 指定IM分配内存大小,默认值为0,如果启动该值最小为100M;如果在CDB环境中使用,CDB级别设置为整个库级别限制,PDB默认继承CDB设置,但是在实际使用中PDB中总数不能超过CDB限制
INMEMORY_FORCE 指定是否允许数据库中对象使用IM,默认是DEFAULT,即可以实现在对象级别定义INMEMORY or NO INMEMORY,如果设置为OFF 即表示表或者物化视图无法使用IM
INMEMORY_CLAUSE_DEFAULT 默认为空,和NO INMEMORY意义相同,表示创建新对象默认不启用IM,如果配置为INMEMORY,表示新创建对象默认启用IM
INMEMORY_QUERY 默认为TRUE,表示查询是否使用IM特性,设置为FALSE表示查询不使用IM特性
INMEMORY_MAX_POPULATE_SERVERS 默认和系统core一致,用途是把你的表中数据写入到IM中
INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT 控制IM中对象数据的重新载入的进程数,该值为INMEMORY_MAX_POPULATE_SERVERS参数的百分比
OPTIMIZER_INMEMORY_AWARE 该参数是控制优化器成本计算时是否考虑IM,默认为TRUE

impdp 操作IM
TRANSFORM=INMEMORY:y 继承IM导出对象属性
TRANSFORM=INMEMORY:n 不继承IM导出对象属性
TRANSFORM=INMEMORY_CLAUSE:string 修改IM导出对象关于IM的属性

参考文档:https://docs.oracle.com/database/121/ADMIN/memory.htm#ADMIN14257

如何估算表In-Memory需要内存大小

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：如何估算表In-Memory需要内存大小

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

对于12.1.0.2的In-Memory特性很多朋友都已经知晓,现在可能有这个困惑我一张表启用In-Memory大概需要多少内存呢？该如何估算这个值呢？这里我告诉你通过dbms_compression可以完成你想做的事情
启用In-Memory功能

[oracle@www.xifenfei.com u02]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Aug 7 17:50:47 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
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 0
inmemory_trickle_repopulate_servers_ integer     1
optimizer_inmemory_aware             boolean     TRUE
SQL> alter system set inmemory_size=400M;
alter system set inmemory_size=400M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
SQL>  alter system set inmemory_size=400M scope=spfile;
System altered.
SQL> show pdbs;
    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDB1                           READ WRITE NO
         4 PDB2                           READ WRITE NO
SQL> alter session set container=pdb1;
Session altered.
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 0
inmemory_trickle_repopulate_servers_ integer     1
optimizer_inmemory_aware             boolean     TRUE
SQL> alter system set inmemory_size=200M;
alter system set inmemory_size=200M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
SQL> alter system set inmemory_size=200M scope=spfile;
alter system set inmemory_size=200M scope=spfile
                                               *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this
option
SQL> !oerr ora 2096
02096, 00000, "specified initialization parameter is not modifiable with this option"
// *Cause: Though the initialization parameter is modifiable, it cannot be
//         modified using the specified command.
// *Action: Check the DBA guide for information about under what scope
//          the parameter may be modified
SQL> select 200*1024*1024 from dual;
200*1024*1024
-------------
    209715200
SQL>  alter system set inmemory_size=209715200;
 alter system set inmemory_size=209715200
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-02095: specified initialization parameter cannot be modified
SQL> shutdown immediate;
Pluggable Database closed.
SQL> conn / as sysdba
Connected.
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area  838860800 bytes
Fixed Size                  2929936 bytes
Variable Size             360712944 bytes
Database Buffers           50331648 bytes
Redo Buffers                5455872 bytes
In-Memory Area            419430400 bytes
Database mounted.
Database opened.
SQL> show parameter inmemory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 400M
inmemory_trickle_repopulate_servers_ integer     1
optimizer_inmemory_aware             boolean     TRUE
SQL> alter session set container=pdb1;
Session altered.
SQL> alter database open;
Database altered.
SQL> show parameter inmemory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 400M
inmemory_trickle_repopulate_servers_ integer     1
optimizer_inmemory_aware             boolean     TRUE
SQL> alter system set inmemory_size=100M;
System altered.
SQL> show parameter inmemory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_max_populate_servers        integer     1
inmemory_query                       string      ENABLE
inmemory_size                        big integer 100M
inmemory_trickle_repopulate_servers_ integer     1
optimizer_inmemory_aware             boolean     TRUE

这里可以发现inmemory_query默认为true,但是inmemory_size默认为0,也就是说In-Memory Option默认是关闭的,如果启用直接设置inmemory_size值即可(该值最小值为100M),但是需要注意在cdb中需要重启后生效,pdb需要待cdb生效后才能够设置

估算表设置In-Memory默认需要内存空间

SQL> create user chf identified by xifenfei;
User created.
SQL> grant dba to chf;
Grant succeeded.
SQL> create table chf.t_xifenfei as select * from dba_objects;
Table created.
SQL> select sum(bytes), sum(blocks) from DBA_segments where segment_name = 'T_XIFENFEI';
SUM(BYTES) SUM(BLOCKS)
---------- -----------
  13631488        1664
SQL> set serveroutput on
DECLARE
l_blkcnt_cmp binary_integer;
l_blkcnt_uncmp binary_integer;
l_row_cmp binary_integer;
l_row_uncmp binary_integer;
l_cmp_ratio number;
SQL>   2    3    4    5    6    7  l_comptype_str varchar2(100);
  8  BEGIN
  9  dbms_compression.get_compression_ratio(
 10  scratchtbsname => upper('&ScratchTBS'),
 11  ownname => upper('&ownername'),
 12  objname => upper('&TableName'),
 13  subobjname => NULL,
 14  comptype => DBMS_COMPRESSION.COMP_INMEMORY_QUERY_LOW,
 15  blkcnt_cmp => l_blkcnt_cmp,
 16  blkcnt_uncmp => l_blkcnt_uncmp,
 17  row_cmp => l_row_cmp,
 18  row_uncmp => l_row_uncmp,
 19  cmp_ratio => l_cmp_ratio,
 20  comptype_str => l_comptype_str
 21  );
 22  dbms_output.put_line('.');
 23  dbms_output.put_line('OUTPUT: ');
 24  dbms_output.put_line('LINEORDER '||l_comptype_str||' ratio: '||to_char(l_cmp_ratio,'99.999'));
 25  end;
 26  /
Enter value for scratchtbs: USERS
old  10: scratchtbsname => upper('&ScratchTBS'),
new  10: scratchtbsname => upper('USERS'),
Enter value for ownername: CHF
old  11: ownname => upper('&ownername'),
new  11: ownname => upper('CHF'),
Enter value for tablename: T_XIFENFEI
old  12: objname => upper('&TableName'),
new  12: objname => upper('T_XIFENFEI'),
.
OUTPUT:
LINEORDER "In-memory Memcompress Query Low" ratio:   2.800
PL/SQL procedure successfully completed.
SQL> SELECT 13631488/2.800 FROM DUAL;
13631488/2.800
--------------
    4868388.57
SQL> alter table CHF.T_XIFENFEI inmemory;
Table altered.
SQL> select COUNT(*) FROM CHF.T_XIFENFEI;
  COUNT(*)
----------
     90923
SQL> select inmemory_size from v$im_segments where segment_name = 'T_XIFENFEI';
INMEMORY_SIZE
-------------
      4325376
SQL> select (4868388.57-4325376)/4325376 from dual;
(4868388.57-4325376)/4325376
----------------------------
                  .125541125

这里使用大家在ehcc中熟悉的dbms_compression.get_compression_ratio来估算In-memory需要的大概空间,例如本测试中,创建T_XIFENFEI表占用磁盘空间为13631488byte,使用dbms_compression估算在In-memory默认的压缩比例(Query Low)情况下,大概压缩比例为2.8,也就是通过合理估算,表启用In-memory cache之后,大概需要空间为13631488/2.800=4868388.57byte,最终通过实际测试需要空间为4325376byte,整体误差为(4868388.57-4325376)/4325376=12.5%左右
因此我们在使用In-memory cache一个表之时,如果不确定其需要内存大小,可以通过dbms_compression包来估算.另外In-memory还可以配置不同的压缩级别实现不同的压缩比例,其他压缩比例请见下图
compress-in_memory-1
compress-in_memory-2
详细link请见:https://docs.oracle.com/database/121/ARPLS/d_compress.htm#ARPLS65599

ORACLE 12C In-Memory功能性能测试

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：ORACLE 12C In-Memory功能性能测试

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

启用In-Memory功能
数据库版本12.1.0.2及其以上版本,inmemory_size参数设置为合适值

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
SQL> SHOW PARAMETER inmemory;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
inmemory_clause_default              string
inmemory_force                       string      DEFAULT
inmemory_query                       string      ENABLE
inmemory_size                        big integer 200M

创建表
这里可以知道,创建表大小为13631488，但是未使用In-Memory功能

SQL> create table t_xifenfei_in_memory as select * from dba_objects;
Table created.
SQL> SELECT BYTES FROM USER_SEGMENTS WHERE SEGMENT_NAME='T_XIFENFEI_IN_MEMORY';
     BYTES
----------
  13631488
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>  SELECT * FROM V$INMEMORY_AREA;
POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                     166723584          0 DONE                                3
64KB POOL                     33554432          0 DONE                                3

未使用In-Memory功能测试

SQL> SET AUTOT TRACE
SQL> SELECT * FROM T_XIFENFEI_IN_MEMORY;
90902 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3598036702
------------------------------------------------------------------------------------------
| Id  | Operation         | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                      | 90902 |     9M|   427   (1)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| T_XIFENFEI_IN_MEMORY | 90902 |     9M|   427   (1)| 00:00:01 |
------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          5  recursive calls
          0  db block gets
       7505  consistent gets
       1527  physical reads
          0  redo size
   12125231  bytes sent via SQL*Net to client
      67212  bytes received via SQL*Net from client
       6062  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
      90902  rows processed

这里可以看到未使用In-Memory功能,数据库查询执行计划使用TABLE ACCESS FULL，consistent gets为7505

使用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
--因为只是把该表设置了INMEMORY,但是未查询过,所以查询V$INMEMORY_AREA中未使用相关内存
SQL> SELECT * FROM V$INMEMORY_AREA;
POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                     166723584          0 DONE                                3
64KB POOL                     33554432          0 DONE                                3
--进行一次全表扫描
SQL> SELECT COUNT(*) FROM T_XIFENFEI_IN_MEMORY;
  COUNT(*)
----------
     90902
--再次查看，已经使用了分配的In-Memory中内存
SQL> SELECT * FROM V$INMEMORY_AREA;
POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                     166723584    4194304 DONE                                3
64KB POOL                     33554432     131072 DONE                                3
SQL> SET AUTOT TRACE
SQL> SELECT * FROM T_XIFENFEI_IN_MEMORY;
90902 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3598036702
---------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |                      | 90902 |     9M|    20  (45)| 00:00:01 |
|   1 |  TABLE ACCESS INMEMORY FULL| T_XIFENFEI_IN_MEMORY | 90902 |     9M|    20  (45)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
          3  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
    4946298  bytes sent via SQL*Net to client
      67212  bytes received via SQL*Net from client
       6062  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      90902  rows processed

这里我们可以发现，使用了In-Memory功能之后，数据库consistent gets为4,相比未使用In-Memory之前的7505,性能最少提高近2000倍.

ORACLE 12C In-Memory组件初试

联系：手机/微信(+86 17813235971) QQ(107644445)

标题：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