并行特性对分区表执行计划的影响

最新推荐文章于 2021-04-05 10:22:57 发布

cog8220

最新推荐文章于 2021-04-05 10:22:57 发布

阅读量175

点赞数

文章标签：数据库

前一段时间在大分区表数据切换方案上线的时候遇到一个问题：在新方案应用之后，新方案的执行计划与旧方案执行计划相差甚远，导致业务应用延迟。同样的SQL语句，同样的表结构，为什么方案应用前后的执行计划会相差那么多呢，难道仅仅是因为新方案使用了视图来屏蔽底层表的变化吗？知识储备告诉我不是这样的：Oracle在执行SQL语句的时候会将视图名称转化为视图定义，因此执行计划是不会受到影响的。肯定是两张表在某些细节属性上存在不同。

通过查询DBA_TABLES数据字典表来比较两张表的属性：

>  owner, table_name, degree, cache, last_analyzed, compression
  2     dba_tables
  3   where table_name  'MV_AUC%';

OWNER      TABLE_NAME                     DEGREE               CACHE

LAST_ANALYZED  

ODS        MV_AUC                                   16              N
2009-6-20 12:42:42

ODS        MV_AUCTION20090804                        1             N

从上面的查询结果可以看出，以前的表并行度为16，而现在的表并行度只有1，因此可以推出以前的查询会通过16个进程并行查询表的16个分区，充分利用了分区的并行特性，查询效率很高，而新表不具备并行特性，查询时间是之前的16倍以上。定位到了问题的根源，解决起来也就容易多了。将新表的并行度改为16。 Alter table MV_AUCTION20090804 parallel 16; 至此，执行计划与之前一模一样，问题迎刃而解。

大家可能注意到了，两张表还存在一些区别，即以前的表曾经被分析过。通过实验验证，对于这种大型分区表（使用了并行的）而言，分析与否对执行计划并没太大影响，况且分析已经相当陈旧，因此可以忽略。

说来说去都是并行特性，那么它到底对执行计划有多大的影响呢？我们可以通过实验来体会一下它的神奇之处：

这里我们使用MV_AUC的表结构进行测试：首先创建表MV_AUCTION，缺省情况下表不具备并行特性，即该表的并行度为1

>   MV_AUCTION
  2  (
  3    .......

       .......

       .......

 23  )
 24    hash (ID)
 25  (
 26     HASH_P1,
 27     HASH_P2,
 28     HASH_P3,
 29     HASH_P4,
 30     HASH_P5,
 31     HASH_P6,
 32     HASH_P7,
 33     HASH_P8,
 34     HASH_P9,
 35     HASH_P10,
 36     HASH_P11,
 37     HASH_P12,
 38     HASH_P13,
 39     HASH_P14,
 40     HASH_P15,
 41     HASH_P16
 42  );

表已创建。

>   IDX_AUCTIONS_ID  MV_AUCTION (AUCTION_ID);

索引已创建。

>   IDX_CATID  MV_AUCTION (CATEGORY);

索引已创建。

>   IDX_SPUID  MV_AUCTION (SPU_ID);

索引已创建。

>  autotrace 
>  *  MV_AUCTION;

执行计划

Plan hash value: 2641310786





| Id  | Operation          | Name         |   | Bytes | Cost (%CPU)| Time
  | Pstart| Pstop |





|   0 |     |              |     1 |   742 |     2   (0)| 00:00:0

1 |       |       |

|   1 |   HASH |              |     1 |   742 |     2   (0)| 00:00:0

1 |     1 |    16 |

|   2 |     FULL| MV_AUCTION  |     1 |   742 |     2   (0)| 00:00:0

1 |     1 |    16 |

上面的执行计划显示的操作为全表扫描（TABLE ACCESS FULL），并且哈希分区的16个分区进行了单独扫描，并没有用到分区的并行特性。

接下来创建与表MV_AUCTION同样结构的表MV_AUCTION_P，这里我们设置它的并行度为8：

>   MV_AUCTION_P
  2  (
  3    .......

       .......

       .......

 23  )
 24    hash (ID)
 25  (
 26     HASH_P1,
 27     HASH_P2,
 28     HASH_P3,
 29     HASH_P4,
 30     HASH_P5,
 31     HASH_P6,
 32     HASH_P7,
 33     HASH_P8,
 34     HASH_P9,
 35     HASH_P10,
 36     HASH_P11,
 37     HASH_P12,
 38     HASH_P13,
 39     HASH_P14,
 40     HASH_P15,
 41     HASH_P16
 42  );


表已创建。

>   IDX_AUCTIONS_ID20090819  MV_AUCTION_P (AUCTION_ID);

索引已创建。

>   IDX_CATID20090819  MV_AUCTION_P (CATEGORY);

索引已创建。

>   IDX_SPUID20090819  MV_AUCTION_P (SPU_ID);

索引已创建。

>   MV_AUCTION_P ;

表已更改。

>  *  MV_AUCTION_P;

执行计划

Plan hash value: 1383468301





| Id  | Operation            | Name         |   | Bytes | Cost (%CPU)| Time
    | Pstart| Pstop |    TQ  |-| PQ Distrib |





|   0 |       |              |     1 |   742 |     2   (0)| 00:00

:01 |       |       |        |      |            |

|   1 |  PX COORDINATOR      |              |       |       |            |
    |       |       |        |      |            |

|   2 |   PX SEND QC (RANDOM)| :TQ10000     |     1 |   742 |     2   (0)| 00:00

:01 |       |       |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR |              |     1 |   742 |     2   (0)| 00:00

:01 |     1 |    16 |  Q1,00 | PCWC |            |

|   4 |       FULL| MV_AUCTION_P |     1 |   742 |     2   (0)| 00:00

:01 |     1 |    16 |  Q1,00 | PCWP |            |






Note

   - dynamic sampling used for this

可以看出，上面的执行计划跟先前的大不相同，同样为全表扫描，分区并行使得执行效率大大提高（PX COORDINATOR；PX SEND QC (RANDOM)；PX BLOCK ITERATOR）。

当然，我们可以通过使用HINT来达到同样的效果：

>   *  MV_AUCTION;

执行计划

Plan hash value: 56807953





| Id  | Operation            | Name       |   | Bytes | Cost (%CPU)| Time
  | Pstart| Pstop |    TQ  |-| PQ Distrib |





|   0 |       |            |     1 |   742 |     2   (0)| 00:00:0

1 |       |       |        |      |            |

|   1 |  PX COORDINATOR      |            |       |       |            |
  |       |       |        |      |            |

|   2 |   PX SEND QC (RANDOM)| :TQ10000   |     1 |   742 |     2   (0)| 00:00:0

1 |       |       |  Q1,00 | P->S | QC (RAND)  |

|   3 |    PX BLOCK ITERATOR |            |     1 |   742 |     2   (0)| 00:00:0

1 |     1 |    16 |  Q1,00 | PCWC |            |

|   4 |       FULL| MV_AUCTION |     1 |   742 |     2   (0)| 00:00:0

1 |     1 |    16 |  Q1,00 | PCWP |            |



Note

   - dynamic sampling used for this

不过，在应用并行特性的时候有一点需要特别注意：给表加并行度，会使全表扫描成本降低，有可能cbo会选择全表来代替索引扫描。最主要的是并行意味着需要更多的资源，除非你资源充足，通过启用并行来提高效率才是明智之选。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15203236/viewspace-613641/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15203236/viewspace-613641/

cog8220

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
并行特性对分区表执行计划的影响

前一段时间在大分区表数据切换方案上线的时候遇到一个问题：在新方案应用之后，新方案的执行计划与旧方案执行计划相差甚远，导致业务应用延迟。同样的SQL语句，同样的表结构，为什么方案应用前后的执行计划会相差那么多呢，难...
复制链接

扫一扫