ANBOB™

sql中如果使用了parallel hint 或是force parallel query 想并行查询的特性，如果那样做了就一定能保证 query optimizer选择并行的执行计划么？你知识那样做实际上产生什么样的影响么？

之前的一篇about parallel时忽略了这一点。

做一个简单的测试。

anbob@OEM12C>create table testpx as select rownum id,rpad(rownum,10) c1,rpad('x',100) c2 
from all_objects where rownum<=1E4;
Table created.

anbob@OEM12C>alter table testpx add constraints pk_testpx primary key(id);
Table altered.

anbob@OEM12C>exec dbms_stats.gather_table_stats(user,'TESTPX',-
method_opt=>'for all columns size 1',cascade=>true);
PL/SQL procedure successfully completed.

anbob@OEM12C>show parameter parallel_max
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
parallel_max_servers                 integer     160

anbob@OEM12C>explain plan for select /*+parallel(t 4)*/count(*) from testpx t;
Explained.

anbob@OEM12C>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 3411016230
---------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |     1 |     9   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |           |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_TESTPX | 10000 |     9   (0)| 00:00:01 |
--------------------------------------------------------------------------
9 rows selected.

notice:
从执行计划看出这个query 并未直正的用到parallel，你可能会怀疑是我当前的SESSION会话环境有问题，我也想到了查询了其它一个表是可以并行的，排除了session 环境，并以v$px_process等其它

view得到肯定。是ORACLE 忽略了parallel hint?Jonathan Lewis 也说过通常是不会的，optimizer不会忽略hint，除非你写错了hint语法或用错了hint.下面我加大parallel 并行度再次执行刚才的查询.

anbob@OEM12C>explain plan for select /*+parallel(t 30)*/count(*) from testpx t;
Explained.

anbob@OEM12C>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------
Plan hash value: 233662428
--------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |     2   (0)| 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 |          | 10000 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|   6 |       TABLE ACCESS FULL| TESTPX   | 10000 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------

Notice:
会发现这次optimizer用上了并行，为什么会这样?也许你注意到了Cost列，有原来的IFFS时的9变成了PX+TFS后的2(不同的环境这个值也可能不同)，具体parallel 3时的Cost是多少？有兴趣的可以做

10053 EVENT对比，Parallel hint前不是告诉并要求ORACLE执行一个并行parallel,只是告诉optimizer用一个比例的内部算法去计算parallel时的cost.如果在考虑了那个比例后，parallel Cost仍然是

昂贵的，同样不会选择parallel.

在并行查询中还有一种方法，可以不用hint，ALTER SESSION FORCE PARALLEL QUERY PARALLEL N;

看到FORCE也许你又会想，这次肯定是强制走索引了吧?是么?接着看我的一个简单的测试，基础表还用上面的testpx表。

anbob@OEM12C> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 2;
Session altered.

anbob@OEM12C>explain plan for SELECT SUM(id) FROM testpx WHERE id<1000;
Explained.

anbob@OEM12C>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
Plan hash value: 3042498370

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     1 |     4 |     3   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |           |     1 |     4 |            |          |
|*  2 |   INDEX RANGE SCAN| PK_TESTPX |   999 |  3996 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("ID"<1000)

14 rows selected.

Notice:
在session 级启用了force parallel,但是注意到query还是没有用parallel。用同样的方法，增加并行度从2到10，执行相同的查询再观察一下执行计划。

anbob@OEM12C> ALTER SESSION FORCE PARALLEL QUERY PARALLEL 10;
Session altered.

anbob@OEM12C>explain plan for SELECT SUM(id) FROM testpx WHERE id<1000;
Explained.

anbob@OEM12C>select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 3676534107

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name      | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |           |     1 |     4 |     2   (0)| 00:00:01 |        |      |            |
|   1 |  SORT AGGREGATE           |           |     1 |     4 |            |          |        |      |            |
|   2 |   PX COORDINATOR          |           |       |       |            |          |        |      |            |
|   3 |    PX SEND QC (RANDOM)    | :TQ10000  |     1 |     4 |            |          |  Q1,00 | P->S | QC (RAND)  |
|   4 |     SORT AGGREGATE        |           |     1 |     4 |            |          |  Q1,00 | PCWP |            |
|   5 |      PX BLOCK ITERATOR    |           |   999 |  3996 |     2   (0)| 00:00:01 |  Q1,00 | PCWC |            |
|*  6 |       INDEX FAST FULL SCAN| PK_TESTPX |   999 |  3996 |     2   (0)| 00:00:01 |  Q1,00 | PCWP |            |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   6 - filter("ID"<1000)

Notice:
现在用上了parallel,cost 也从3变为了2，说明force parallel query并不是真正的让oracle force用并行查询也是让optimizer降低了做算的成本(并行度越高可能成本越低)。在选择执行计划是optimizer仍然是自由的，可以选择没有并行的执行计划。

Summary：
如论你是使用force parallel query,还是parallel hint都并不是强行绑架optimizer执行并行，只是影响到了估算的cost，固化执行计划可以使用outline,profile，baseline等技术，但是那样就无法让query optimizer选择最优cost最小的执行计划。

打赏

微信扫一扫，打赏作者吧～