ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 并行特性对分区表执行计划的影响

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

原创 Linux操作系统 作者:gvora 时间:2009-08-31 16:47:17 0 删除 编辑

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

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

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

OWNER      TABLE_NAME                     DEGREE               CACHE
---------- ------------------------------ -------------------- ----------
LAST_ANALYZED  COMPRESS
-------------- --------
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

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

表已创建。

SQL> create index IDX_AUCTIONS_ID on MV_AUCTION (AUCTION_ID);

索引已创建。

SQL> create index IDX_CATID on MV_AUCTION (CATEGORY);

索引已创建。

SQL> create index IDX_SPUID on MV_AUCTION (SPU_ID);

索引已创建。

SQL> set autotrace on
SQL> select * from MV_AUCTION;

执行计划
----------------------------------------------------------
Plan hash value: 2641310786

--------------------------------------------------------------------------------

-------------------

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

--------------------------------------------------------------------------------

-------------------

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

1 |       |       |

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

1 |     1 |    16 |

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

1 |     1 |    16 |

--------------------------------------------------------------------------------

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

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

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


表已创建。

SQL> create index IDX_AUCTIONS_ID20090819 on MV_AUCTION_P (AUCTION_ID);

索引已创建。

SQL> create index IDX_CATID20090819 on MV_AUCTION_P (CATEGORY);

索引已创建。

SQL> create index IDX_SPUID20090819 on MV_AUCTION_P (SPU_ID);

索引已创建。

SQL> alter table MV_AUCTION_P parallel 8;

表已更改。

SQL> select * from MV_AUCTION_P;

执行计划
----------------------------------------------------------
Plan hash value: 1383468301

--------------------------------------------------------------------------------

--------------------------------------------------

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

--------------------------------------------------------------------------------

--------------------------------------------------

|   0 | SELECT STATEMENT     |              |     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 |     TABLE ACCESS FULL| MV_AUCTION_P |     1 |   742 |     2   (0)| 00:00

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

--------------------------------------------------------------------------------

--------------------------------------------------


Note
-----
   - dynamic sampling used for this statement

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

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

SQL> select /*+ PARALLEL(MV_AUCTION,8) */ * from MV_AUCTION;

执行计划
----------------------------------------------------------
Plan hash value: 56807953

--------------------------------------------------------------------------------

------------------------------------------------

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

--------------------------------------------------------------------------------

------------------------------------------------

|   0 | SELECT STATEMENT     |            |     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 |     TABLE ACCESS FULL| MV_AUCTION |     1 |   742 |     2   (0)| 00:00:0

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

--------------------------------------------------------------------------------

Note
-----
   - dynamic sampling used for this statement

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

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

请登录后发表评论 登录
全部评论

注册时间:2008-12-30

  • 博文量
    62
  • 访问量
    287748