Oracle 插入时并行并集，卷起，立方体降级 (11.2.0.4)

askTom 2017-05-26

244

问题描述

问题是这样的查询 :( 最后的表创建脚本)

  insert /*+no_append parallel(8)*/ into tmp_px0
  select count(distinct rn) from tmp_px1
  union all
  select count(distinct rn) from tmp_px2
  union all
  select count(distinct rn) from tmp_px3

使用2*8 * (联合子查询的数量) px服务器，而所有联合操作因此运行 (根据sql monitor(OEM) 和v $ px_session视图)
(尚未找到任何可靠的方法来检查已执行的查询中使用了多少并行服务器 (sql monitor中的并行选项卡除外)
要在当前运行的查询中检查这一点，请使用此)

  select sq.SQL_TEXT, ps.QCSID, ps.QCSERIAL#, count(*) from v$px_session ps
  join v$session s on ps.SID=s.SID and ps.SERIAL#=s.SERIAL#
  join (select SQL_ID, max(SQL_TEXT) as SQL_TEXT from v$sql group by SQL_ID) sq on sq.SQL_ID=s.SQL_ID
  group by sq.SQL_TEXT, ps.QCSID, ps.QCSERIAL#;

question is-是否有任何解决方法可以使用2*8总px进程而不是2*8 * (联合子查询的数量)，而无需将insert划分为多个插入 (并非总是可能的)

根据v $ px_session px服务器分配结果: 处理每个并集会在v $ px_session中增加16台服务器，因此在查询运行时使用的服务器总数会增加: 16-32-48
在执行结束时的sql_monitor (并行选项卡) 中，它们分为3组，每组2组8个进程 (全部不同)
由于这些组的数据库时间相等，因此似乎任何时候都只有16个处于活动状态

main problem is如果没有足够的可用px服务器或有资源管理器限制，
剩余的联合子查询将被串行执行-女巫可能需要很多时间，
一直以来，所有分配的并行服务器都被此查询锁定-因此该资源组的其他查询 (如果未正确设置资源管理器，则为整个服务器) 必须串行运行

它不会在没有插入的情况下为select重现: 运行每个union因此，在查询执行的所有时间都使用16个并行服务器 (每个2组8个进程)

  select /*+parallel(8)*/count(distinct rn) from tmp_px1
  union all
  select /*+parallel(8)*/count(distinct rn) from tmp_px2
  union all
  select /*+parallel(8)*/count(distinct rn) from tmp_px3;

但是用物化子查询和rollup再现，cube (里面使用临时物化子查询)
当填充服务器的内部临时表计数以相同的方式增长时: 16-32-48

  with tmp as (select /*+materialize*/ * from (
              select count(distinct rn) from tmp_px1
              union all
              select count(distinct rn) from tmp_px2
              union all
              select count(distinct rn) from tmp_px3
              ))
  select /*+parallel(8)*/* from tmp;

它不取决于dml是否并行 (已启用并行dml/no_append hint的附加提示):
并行dml为insert添加8个添加px服务器，因此总数增长: 24-40-56

测试表的创建 (使它们足够大，因此单个选择运行至少几秒钟，因此有时间检查v $ px_session，而每个选择运行)

drop table tmp_px0;
drop table tmp_px1;
drop table tmp_px2;
drop table tmp_px3;
create table tmp_px0(rn number);
create table tmp_px1(rn number);
create table tmp_px2(rn number);
create table tmp_px3(rn number);

begin
  for ii in 1..10 loop
    insert into tmp_px1 select 10000000*ii+rownum as rn from dual connect by rownum<10000000;
    commit;
  end loop;
end;
call dbms_stats.gather_table_stats(ownname => 'TMP', tabname =>'TMP_PX1', degree => 8, cascade => true);

insert into tmp_px2 select * from tmp_px1;
commit;
call dbms_stats.gather_table_stats(ownname => 'TMP', tabname =>'TMP_PX2', degree => 8, cascade => true);
insert into tmp_px3 select * from tmp_px1;
commit;
call dbms_stats.gather_table_stats(ownname => 'TMP', tabname =>'TMP_PX3', degree => 8, cascade => true);

希望你给我一些建议
谢谢

专家解答

叶夫根尼，

您看到的行为在Oracle数据库11g中是预期的。

这是正在发生的事情。

当并行执行UNION ALL语句时，在11g中，每个分支一次由一组并行服务器进程执行一个。这意味着每个分支将使用一个新的并行化器。见https://blogs.oracle.com/datawarehousing/px-server-sets%2c-parallelizers%2c-dfo-trees%2c-parallel-groups%2c-lets-talk-about-terminology有关什么是并行化器的更多信息。

通常，第一个分支将开始执行，并分配2 * DOP并行服务器进程。分支完成后，将释放并行服务器进程。然后第二个分支将执行相同的操作，依此类推。当您运行语句的查询部分并查看SQL监视器报告中的并行选项卡时，您将看到同一组并行服务器进程为每个不同分支重新分配 (也就是说，如果相同的并行服务器进程在那个时候可用)。因此，没有并行服务器进程的累积，因为在某个时间点，将只分配2 * DOP并行服务器进程。

但是，当您引入并行DML时，情况会发生变化。当涉及事务时，在用户提交之前，不释放执行该语句所涉及的并行服务器进程。

因此，当并行DML语句涉及UNION时，所有执行将以相同的方式开始，第一个分支分配2 * DOP并行服务器进程。但是，当第一个分支完成时，并行服务器进程将不会释放。然后，第二个分支将分配另一个2 * DOP并行服务器进程，依此类推。

所以，有了3个分支，每个分支有2组并行服务器进程 (生产者和消费者) 和另一组并行服务器进程执行插入，你会看到 (3*2 * DOP) DOP并行服务器进程分配。

对于您的示例，DOP为8，并且UNION ALL语句中有3个分支，分配的并行服务器进程总数将为 (3*2*8) 8或56。

这正是我在11g上执行测试用例并查询v $ px_process或检查SQL Monitor中的并行选项卡时看到的内容https://sqlmaria.com/11g_px_allocation/(4组或组并行服务器进程)

SQL> SELECT * FROM V $ PX_PROCESS;

SERV STATUS           PID SPID                            SID    SERIAL#
---- --------- ---------- ------------------------ ---------- ----------
P009 AVAILABLE         34 4079
P034 AVAILABLE         59 4129
P006 AVAILABLE         31 4073
P037 AVAILABLE         62 4135
P039 AVAILABLE         64 4139
P003 AVAILABLE         28 4067
P007 AVAILABLE         32 4075
P018 AVAILABLE         43 4097
P044 AVAILABLE         69 4149
P005 AVAILABLE         30 4071
P050 AVAILABLE         75 4161
P051 AVAILABLE         76 4163
P015 AVAILABLE         40 4091
P055 AVAILABLE         80 4171
P054 AVAILABLE         79 4169
P028 AVAILABLE         53 4117
P021 AVAILABLE         46 4103
P043 AVAILABLE         68 4147
P004 AVAILABLE         29 4069
P031 AVAILABLE         56 4123
P010 AVAILABLE         35 4081
P016 AVAILABLE         41 4093
P017 AVAILABLE         42 4095
P025 AVAILABLE         50 4111
P020 AVAILABLE         45 4101
P000 AVAILABLE         18 4032
P036 AVAILABLE         61 4133
P008 AVAILABLE         33 4077
P002 AVAILABLE         27 4065
P040 AVAILABLE         65 4141
P022 AVAILABLE         47 4105
P042 AVAILABLE         67 4145
P029 AVAILABLE         54 4119
P026 AVAILABLE         51 4113
P033 AVAILABLE         58 4127
P011 AVAILABLE         36 4083
P049 AVAILABLE         74 4159
P045 AVAILABLE         70 4151
P032 AVAILABLE         57 4125
P046 AVAILABLE         71 4153
P041 AVAILABLE         66 4143
P027 AVAILABLE         52 4115
P014 AVAILABLE         39 4089
P047 AVAILABLE         72 4155
P053 AVAILABLE         78 4167
P024 AVAILABLE         49 4109
P035 AVAILABLE         60 4131
P013 AVAILABLE         38 4087
P052 AVAILABLE         77 4165
P019 AVAILABLE         44 4099
P023 AVAILABLE         48 4107
P038 AVAILABLE         63 4137
P030 AVAILABLE         55 4121
P012 AVAILABLE         37 4085
P048 AVAILABLE         73 4157
P001 AVAILABLE         19 4034

56 rows selected.

不幸的是，这是11g。

从Oracle Database 12c Release 1开始，我们并行执行UNION ALL语句的方式已完全改变，并引入了concurrent UNION ALL，如果OPTIMIZER_FEATURE_ENABLED设置为12.1或更高，则默认情况下处于打开状态。只会分配2 * DOP并行服务器进程，这正是我在12c中执行you testcase并查询v $ px_process或检查SQL Monitor中的parallel选项卡时看到的https://sqlmaria.com/12c_px_allocation/(1组并行服务器进程)

SQL> SELECT * FROM V $ PX_PROCESS;

服务状态PID SPID SID序列号IS_GV CON_ID
-
P000可用34 3904 FALSE 0
P001可用35 3906 FALSE 0
P002可用43 3926 FALSE 0
P003可用44 3928 FALSE 0
P004可用45 3930 FALSE 0
P005可用46 3932 FALSE 0
P006可用47 3934 FALSE 0
P007可用48 3936 FALSE 0
P008可用50 4061 FALSE 0
P009可用53 4063 FALSE 0
P00A可用54 4065 FALSE 0
P00B可用55 4067 FALSE 0
P00C可用56 4069 FALSE 0
P00D可用57 4071 FALSE 0
P00E可用58 4073 FALSE 0
P00F可用59 4076 FALSE 0

选择16行。

有关新并发联合的更多信息，请参见http://http://docs.oracle.com/database/122/VLDBG/types-parallelism.htm#VLDBG1472

oracle asktom

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

Oracle 插入时并行并集，卷起，立方体降级 (11.2.0.4)

问题描述

专家解答

评论