标签归档：parallel_degree_policy

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

发表于 2015 年 5 月 27 日 由 惜分飞

在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 | 标签为 , , , | 评论关闭