重庆思庄Oracle、Redhat认证学习论坛

标题: 在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_for... [打印本页]

作者: 郑全 时间: 2016-10-25 10:11
标题: 在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_for...
在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、Redhat认证学习论坛 (http://bbs.cqsztech.com/)