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特性,不然只是执行计划中的启用,可能是假象

联系：手机(13429648788) QQ(107644445)

链接：http://www.xifenfei.com/5906.html

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

作者：惜分飞

时间： 2024-09-29 08:39:52

ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数的相关文章

【RAC】Oracle 11gR2 RAC 中的 Grid Plug and Play(GPnP) 是什么？

[RAC]Oracle 11gR2 RAC 中的 Grid Plug and Play(GPnP) 是什么? 一. 什么是GPnP?   Grid Plug and Play (GPnP):Foundation for a Dynamic Cluster Management    (1)GPnPeliminates the need for a per node configuration –It is an underlying gridconcept that enables the au

oracle 12c rac安装acfs文件系统教程

这篇文章介绍了如何在一个已经安装12c rac的虚拟机上,如何建立acfs文件系统,并且利用acfs snapshot刷一个COW(Copy-On-Write)库出来做测试库. Highlight Step: 一.给虚拟机增加asm盘,以便建立acfs文件系统 二.创建acfs文件系统 三.在节点1创建数据库在acfs文件系统上.(12c支持数据文件,控制文件,日志文件等数据库的文件放在acfs上.参考Doc ID 1369107.1中ACFS Advanced Features Platfor

ORACLE 12C RAC修改ocr/votedisk/asm spfile所在磁盘组名称

今天看着我这个单节点的12C rac,突然觉得ocr所在的磁盘组叫做+DG_SYS有点不舒服,想改成+SYS_DG.处理方法是先把ocr/votedisk/asm spfile迁移到已经存在的asm中,然后修改磁盘组名称,最后迁移到新名称磁盘组中(本次处理流程+DG_SYS->+DATA->+SYS_DG) 当前运行情况 [grid@xifenfei ~]$ crsctl status res -t -----------------------------------------------

ORACLE 12C CDB中PDB参数管理机制

转自:http://www.xifenfei.com/2013/06/oracle-12c-cdb%E4%B8%ADpdb%E5%8F%82%E6%95%B0%E7%AE%A1%E7%90%86%E6%9C%BA%E5%88%B6.html 在ORACLE 12C中参数文件只是记录了cdb的参数信息,没有记录任何的pdb的信息,那ORACLE是如何管理使得各个pdb有自己的参数,这里通过试验的出来ORACLE 12C CDB环境中是通过参数文件结合PDB_SPFILE$来实现参数管理数据库版本

Oracle 12c PDB中碰到的DG问题

Oracle 12c中的PDB一下子让数据文件的格式复杂了一些,所以Data Guard就很有必要了,一旦出现问题,受损失的数据库是全局的.没想到在搭建Data Guard的时候还是碰到了一些小问题. 问题源自于一次PDB创建的时候,早些时候我在搭建好Data Guard后,主备库的日志应用都没有问题,过了几天,根据需求需要再添加一个PDB,导入一些数据供应用使用.按照要求创建了数据文件然后导入数据,但是查看备库的状态发现MRP异常停止了. DG Broker检测的状态如下: DGMGRL> s

[INS-30131]installing ORACLE 12C RAC DATABASE on AIX 6.1

    第一次搭建12C的RAC,可真是一波三折啊,系统环境配置,安装GI,安装DB各个环节都碰到不少问题,幸好的是不用翻工,只要在哪个环节有问题,就针对该环节的问题去解决就可以了.    以下是讲述的是在安装DB软件的时候,在第2步的时候,碰到的报错(如下图所示),让安装无法进行.查找了网上很多的文章,都没有提及到在AIX或者Linux搭建12.1.0.2.0RAC安装DB时遇到这用情况,基本都是讲述在Windows系统安装12c的RAC时遇到这个报错,然后提及共享文件的事情.对于这样的文章,

《Oracle数据库管理与维护实战》——1.3　Oracle 12c新特性

1.3 Oracle 12c新特性 Oracle数据库管理与维护实战 纵观甲骨文全球大会和甲骨文公司的各种资讯,我们可以发现云计算和大数据是两个重要的主题,Oracle 12c则融合了这两大主题.与以往的Oracle数据库相比,Oracle 12c在16个方面进行了更新.本节将详细介绍Oracle 12c数据库中的16个新特性. 1.3.1 支持多线程模式 在Oracle 12c中,Oracle引入了多线程模式,允许在Windows平台之外的UNIX.Linux等系统使用多线程模式.结合多进程与

Oracle 12C优化器的巨大变化，上生产必读（上）

序言 优化器是Oracle数据库最吸引人的部件之一,因为它对每一个SQL语句的处理都必不可少.优化器为每个SQL语句确定最有效的执行计划,这是基于给定的查询的结构,可用的关于底层对象的统计信息,以及所有与优化器和执行相关的特性. 随着每个新版本的发布,优化器都会进化,利用新功能以及新的统计信息来生成更好的执行计划.随着对查询优化的新的自适应方法的引入,Oracle 12c数据库把这种进化更推上了一个台阶. 这份白皮书介绍了在Oracle 12c数据库中与优化器和统计相关的所有新特性并且提供了简单

Oracle 12C优化器的巨大变化，上生产必读（下）

在Oracle 12c数据库中,随着新的查询优化自适应方法的引入,还有对可用的统计信息的强化,优化器实现了一个巨大的飞跃.今天就让我们继续这个话题,一起来揭晓Oracle 12C其他的强大功能吧. 16 全局临时表上的会话级统计信息  全局临时表通常用于存储应用程序上下文中的中间结果.一个全局临时表的定义,是全系统中拥有适当权限的所有用户所共享的,但其数据永远是会话私有的.在全局临时表(必须是会话级临时表,PRESERVE ROWS ON COMMIT,提交时保留数据)上收集统计信息是行得通的: