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

最新推荐文章于 2023-06-21 15:51:12 发布
最新推荐文章于 2023-06-21 15:51:12 发布
阅读量600 收藏
点赞数
文章标签： oracle set autot

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

世界再美我始终如一
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle set autotrace traceonly,[数据库]sqlplus的autotrace开关
weixin_31021619的博客
04-04 550
[数据库]sqlplus的autotrace开关02020-07-29 09:00:11官方文档https://docs.oracle.com/cd/E11882_01/server.112/e16604/ch_eight.htm#i1037226set autotrace off表示关闭，是默认的(set autot off)set autotrace on表示显示结果、执行计划、统计信息(se...
oracle set autot,set autot on
weixin_34286410的博客
04-04 183
Usage: SET AUTOT[RACE] {OFF | ON | TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]]说明：前三个参数{OFF | ON | TRACE[ONLY]} 里必需选择一个，而且只能选择一个后两个参数[EXP[LAIN]] [STAT[ISTICS]]是可选的，也可以都不选择TRACE[ONLY]的含意是只显示explain和statisti...
oracle set autot,oracle set autot 的权限设置
weixin_33817140的博客
04-04 204
SQL> conn scott/tigerset Connected.SQL>SQL>SQL> set autot on;SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabledSP2-0611: Error enabling STATISTICS reportSQL> ...
oracle set autot,Oracle 开启autotrace功能
weixin_39953236的博客
04-04 183
默认的Oracle的sqlplus是不能够启动执行分析计划、统计执行效率的，启动如下功能只需要几步：1：通过sys用户sysdba角色登陆2：创建autotrace 角色，执行如下脚本$ORACLE_HOME\RDBMS\ADMIN\tracetab.sql在Oracle9i中需要运行$ORACLE_HOME\RDBMS\ADMIN\utlxplan.sql脚本生成plan_table表；在Ora...
oracle关闭跨节点并行,11g RAC数据库多节点并行操作开关 - PARALLEL_FORCE_LOCAL
weixin_39640773的博客
04-03 485
PARALLEL_FORCE_LOCALPARALLEL_FORCE_LOCAL如果指定为TRUE， 并行操作只会在当前instance里面并行，而不会垮多节点，在11g R2默认为FALSE, 如果想跨多个节点运行并行操作， 可以设置为 false 。例子：每周日 22:00 运行全量CBObeginexecute immediate 'alter session set parall...
阿里巴巴的Oracle DBA笔试题参考答案
最新发布
09-01
2. 查看SQL执行计划：使用set autot on，explain plan set statement_id = &item_id for &sql;，select * from table(dbms_xplan.display);等命令来查看SQL执行计划。 二、CBO和RBO CBO（Cost-Based Optimizer）和...
Oracle 11g新特性索引不可见
05-31
Oracle 11g 中引入了一个新特性，称为索引不可见（Index Invisible），它允许 DBA 将索引设置为不可见状态，使得优化器在选择查询计划时忽视该索引，而不影响索引的正常更新。 索引维护是 DBA 的一项重要工作。在...
oracle 定位sql语句
12-05
而在Oracle 9i中，workarea_size_policy参数决定了排序发生在PGA还是临时表空间，临时表空间主要为磁盘排序提供空间。 12. **PCTUSED和PCTFREE**：这两个参数分别表示行空间利用率的阈值，PCTUSED用于控制当空间...
偷来的ORACLE试题
12-24
Oracle数据库是世界上最流行的数据库管理系统之一，它包含了丰富的特性和功能，尤其在数据库管理、性能优化、备份恢复等方面具有显著优势。下面将详细解释标题和描述中提到的一些关键知识点。 一、SQL调优类： 1. ...
DBA笔试题.wps
12-13
这篇文档整合了热门的oracle DBA面试问题 一. SQL tuning 类 　1:列举几种表连接方式 　hash join/merge join/nest loop(cluster join)/index join 　2:不借助第三方工具，怎样查看sql的执行计划 　set autot ...
oracle set autot 的权限设置
cuanyanjie8781的博客
08-01 267
建plustrace权限。普通用户没有这个权限不能进行autot 2010-01-17 16:55 SQL> conn scott/...
PARALLEL_DEGREE_POLIC
12-29 113
The initialization parameterPARALLEL_DEGREE_POLICYcontrols whether automatic degree of parallelism (DOP), par...
Oracle11gr2新增PARALLEL_FORCE_LOCAL参数
ckawt40802的专栏
01-19 1633
在11.2以前，当用户发出并行查询，Oracle会将负载分配到所有的实例上，而在11.2中，Oracle提供了初始化参数，可以限制并行只运行在当前实例上。 RAC环境对并行查询的支持：http://yangtingkun.it...
11g RAC数据库多节点并行操作开关 - PARALLEL_FORCE_LOCAL
cuixie2370的博客
08-16 471
PARALLEL_FORCE_LOCAL PARALLEL_FORCE_LOCAL如果指定为TRUE， 并行操作只会在当前instance里面并行，而不会垮多节点，在11g R2默认为FALSE, 如果想跨多个...
oracle set autotrace的具体命\作用
cqcyoe268602的博客
04-07 454
在SQLPLUS里用,输入命令回车就行了(需要DBA权限) SET AUTOTRACE OFF ------------------- 不...
重庆思庄技术分享——参数PARALLEL_FORCE_LOCAL作用
m0_61458430的博客
06-21 326
Oracle默认的行为，当设置参数PARALLEL_FORCE_LOCAL为真时，Oracle解析器将会尝试将“select”和“update/delete/ insert”查询拆分成本地可执行的查询，但是不会将拆分查询发送到其他数据库服务器执行。PARALLEL_FORCE_LOCAL参数指定是否在可以执行并行查询的情况下，是否在本地进行查询。这将会强制Oracle在本地服务器上进行查询拆分，而不是在可用的远程服务器上执行。参数PARALLEL_FORCE_LOCAL作用。
oracle数据库并行策略,Oracle 并行原理与示例总结之2
weixin_33736210的博客
04-05 555
本帖最后由 leonarding 于 2013-1-26 19:15 编辑三针对PARALLEL_DEGREE_POLICY的三个值，分别演示它们的效果(1)自动并行度策略PARALLEL_DEGREE_POLICY是ORACLE 11G新特性，通过它可以设置并行度策略这个参数有三个值：manual手动方式，也是默认方式，oracle不会擅自调整并行度limited限制方式auto自动方式(2)实...
impdp 并行_expdp在rac下parallel并行报错解决
weixin_29062255的博客
12-24 633
其实这个是个很常见的问题，写下来做纪念吧。说明：而在11GR2后EXPDP 和 IMDP的WORKER进程在设置parallel参数时会在多个INSTANCE启动，所以DIRECTORY必须在共享磁盘上，如果没有设置共享磁盘需要指定cluster=no 来防止报错。今晚是个苦逼的日子明天需要导数据做ogg同步，因为数据量比较大，所以晚上导出，明天白天上班再导入到其它数据库，因此选用expdp做同步...
rac 控制并行进程的执
wll的专栏
10-10 2931
1、此参数用于在rac环境下限制并行只在运行节点并行操作 PARALLEL_FORCE_LOCAL Property Description Parameter type Boolean Default value false Modifiable ALTER SESSION, ALTER SYSTEM Range
oracle parallel参数（转）
gip666的博客
06-26 1790
在数据仓库中使用并行QUERY，需要考虑的参数 1. PARALLEL_DEGREE_POLICY PARALLEL_DEGREE_POLICY 有三种值 MANUAL,AUTO 和LIMITED.默认的值为MANUAL如果设置为AUTO，* Oracle会根据表的大小和操作类型，自动选择并行度。* 如果要求的并行度无法到达, 比如当前所有的parallel server都很繁忙。O...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值