您现在的位置:首页 --> Oracle --> 在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数
在ORACLE 12C RAC中使用in memory特性请注意parallel_degree_policy和parallel_force_local参数
浏览:1899次 出处信息
在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特性,不然只是执行计划中的启用,可能是假象。
建议继续学习:
- Linux服务器性能评估 (阅读:8152)
- 查看 CPU, Memory, I/O and NetFlow (阅读:6414)
- Linux 64位, MySQL, Swap & Memory 优化 (阅读:4460)
- memory prefetch浅析 (阅读:4787)
- RAC环境下Memory System Deconfigured (阅读:2214)
QQ技术交流群:445447336,欢迎加入!
扫一扫订阅我的微信号:IT技术博客大学习
扫一扫订阅我的微信号:IT技术博客大学习
<< 前一篇:给你的rman备份集加上密码锁
文章信息
- 作者:惜分飞 来源: 惜分飞
- 标签: memory parallel_degree_ parallel_force_l
- 发布时间:2015-05-29 20:03:57
建议继续学习
近3天十大热文
- [67] Oracle MTS模式下 进程地址与会话信
- [65] 如何拿下简短的域名
- [65] Go Reflect 性能
- [59] 图书馆的世界纪录
- [59] android 开发入门
- [59] 【社会化设计】自我(self)部分――欢迎区
- [58] IOS安全–浅谈关于IOS加固的几种方法
- [53] 视觉调整-设计师 vs. 逻辑
- [47] 读书笔记-壹百度:百度十年千倍的29条法则
- [46] 界面设计速成