SQL hint mean force? More about parallel(二)(不并行的场景)
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最小的执行计划。
对不起,这篇文章暂时关闭评论。