热搜词
简体
简体
繁体
返回
顶部

修改密码

首页 > 数据库 > Oracle > 正文
在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数

+1

-1

收藏

+1

-1

点赞0

评论0

设置表存放中inmemorySQL> alter table CHF.XIFENFEI_888 inmemory;Table altered.SQL> set autot onSQL> select /*+full(t)*/ count(*) from CHF.XIFENFEI_888 t;COUNT(*) ---------- 16883988 Execution Plan--------------------------------…

   

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

扫一扫在手机打开

评论
已有0条评论
0/150
提交
热门评论
查看更多评论
相关推荐
数据库表连接的简单解释
  • 操作技巧
  • 2022-05-25 18:24
  • 22 0 0
+1
SQL 行转列，列转行
  • 操作技巧
  • 2022-05-25 18:24
  • 20 0 0
+1
Redis是单线程的，但Redis为什么这么快？
  • 操作技巧
  • 2022-05-25 18:24
  • 29 0 0
+1
一起看懂Redis两种持久化方式的原理
  • 操作技巧
  • 2022-05-25 18:24
  • 32 0 0
+1
为什么要用Redis
  • 操作技巧
  • 2022-05-25 18:24
  • 13 0 0
+1
热搜关键词
今日要闻
换一批
数据库表连接的简单解释
22
SQL 行转列，列转行
20
Redis是单线程的，但Redis为什么这么快？
29
一起看懂Redis两种持久化方式的原理
32
为什么要用Redis
13
热点排行
1
一起看懂Redis两种持久化方式的原理
2
Redis是单线程的，但Redis为什么这么...
3
数据库表连接的简单解释
4
SQL 行转列，列转行
5
为什么要用Redis
6
基于redis.properties文件的配置及说...
7
《mysql必知必会》读书笔记--安全管...
8
c3p0
9
redis源码分析之事务Transaction（上）
10
分表优化：千万级数据的插入方法
  • 浏览量(PV):
  • 访客数(UV):
  • IP数:
  • 浏览量(PV):
  • 访客数(UV):
  • IP数:
  • 浏览量(PV):
  • 访客数(UV):
  • IP数:
  • 浏览量(PV):
  • 访客数(UV):
  • IP数:

ICP备案号：晋ICP备14005118号-3 Powered by APKBJ Copyright
© 2007- www.apkbj.com, All Rights Reserved 联系我们