parallel不起作用

有时候在执行sql的时候，为了加快执行的速度，可能会采取并发的方式去执行。为了减少影响，通常都是通过加parallel hint或者alter session force parallel QUERY parallel;但是可能sql并未按照预期的那样去进行并发处理

11.2.0.4

SYS@xb> create table t as select * from dba_objects;

Table created.

SYS@xb> create index idx_id on t(owner);

Index created.

SYS@xb> exec dbms_stats.gather_table_stats(user,'T',method_opt=>'for all columns size 1',cascade=>true);

PL/SQL procedure successfully completed.

加上并发hint

SYS@xb> select /*+ parallel(t 4)*/ count(*) from t where owner like 'S%';

  COUNT(*)
----------
     41973

SYS@xb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  6va6j0s526fc1, child number 0
-------------------------------------
select /*+ parallel(t 4)*/ count(*) from t where owner like 'S%'

Plan hash value: 2157616264

------------------------------------------------------------------
| Id  | Operation     | Name   | E-Rows |E-Bytes| Cost (%CPU)|
------------------------------------------------------------------
|   0 | SELECT STATEMENT  |    |        |       |     2 (100)|
|   1 |  SORT AGGREGATE   |    |      1 |     6 |        |
|*  2 |   INDEX RANGE SCAN| IDX_ID |   7360 | 44160 |     2   (0)|
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER" LIKE 'S%')
       filter("OWNER" LIKE 'S%')

可以看到虽然我在sql里加上了/*+ parallel(t 4)*/,但是执行计划并未采用并发，稍微把sql做下改动,去掉where条件

SYS@xb> select /*+ parallel(t 4)*/ count(*) from t;

  COUNT(*)
----------
     86353

SYS@xb> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST +PEEKED_BINDS +PARALLEL +PARTITION +COST +BYTES'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
SQL_ID  7nh87asa88rb5, child number 0
-------------------------------------
select /*+ parallel(t 4)*/ count(*) from t

Plan hash value: 3126468333

----------------------------------------------------------------------------------------------
| Id  | Operation          | Name     | E-Rows | Cost (%CPU)|    TQ  |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |    |    93 (100)|    |  |        |
|   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 |      |  86353 |    93   (0)|  Q1,00 | PCWC |        |
|*  6 |       TABLE ACCESS FULL| T    |  86353 |    93   (0)|  Q1,00 | PCWP |        |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   6 - access(:Z>=:Z AND :Z>=:Z)

这个时候的执行计划变成跟我预期的一样了，这是什么原理，parallel的hint怎么时好时坏。

做10053trace

select /*+ parallel(t 4)*/ count(*) from t

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T[T]
  Table: T  Alias: T
    Card: Original: 86353.000000  Rounded: 86353  Computed: 86353.00  Non Adjusted: 86353.00
  Access Path: TableScan
    Cost:  335.35  Resp: 93.15  Degree: 0
      Cost_io: 335.00  Cost_cpu: 12952950
      Resp_io: 93.06  Resp_cpu: 3598042
  Best:: AccessPath: TableScan
         Cost: 93.15  Degree: 4  Resp: 93.15  Card: 86353.00  Bytes: 0

这里优化器认为全表扫描的成本最低，所以选择了最好的tablescan，并发度为4

select /*+ parallel(t 4)*/ count(*) from t where owner like ‘S%’

SINGLE TABLE ACCESS PATH
  Single Table Cardinality Estimation for T[T]
  Column (#1): OWNER(
    AvgLen: 6 NDV: 24 Nulls: 0 Density: 0.041667
  Table: T  Alias: T
    Card: Original: 86353.000000  Rounded: 7360  Computed: 7360.20  Non Adjusted: 7360.20
  Access Path: TableScan
    Cost:  335.59  Resp: 93.22  Degree: 0
      Cost_io: 335.00  Cost_cpu: 21588250
      Resp_io: 93.06  Resp_cpu: 5996736
  Access Path: index (index (FFS))
    Index: IDX_ID
    resc_io: 57.00  resc_cpu: 20443312
    ix_sel: 0.000000  ix_sel_with_filters: 1.000000
  Access Path: index (FFS)
    Cost:  57.55  Resp: 57.55  Degree: 1
      Cost_io: 57.00  Cost_cpu: 20443312
      Resp_io: 57.00  Resp_cpu: 20443312
kkofmx: index filter:"T"."OWNER" LIKE 'S%'

  Access Path: index (IndexOnly)
    Index: IDX_ID
    resc_io: 19.00  resc_cpu: 1607507
    ix_sel: 0.085234  ix_sel_with_filters: 0.085234
    Cost: 1.90  Resp: 1.90  Degree: 1
  Best:: AccessPath: IndexRange
  Index: IDX_ID
         Cost: 1.90  Degree: 1  Resp: 1.90  Card: 7360.20  Bytes: 0

这里可以看到优化器列出了多种不同的访问路径和并发度的情形，主要包含tablescan,index ffs, index range scan,由于parallel hint只对tablescan生效，Cost_io: 335.00表示串行的io消耗，Resp_io: 93.06表示并发的响应消耗，cost_io / (0.9 * degree)，这里显示是Degree: 0，但实际上应该是4

根据以上可以判断，当对语句加上parallel hint的时候，优化器并不是必须就会采取并发的方式来执行，而是先计算出并发全表扫描的cost，然后计算index ffs和index range scan的cost，比较以后选择cost最低（Cost: 1.90）的那一种执行计划