前一段时间在大分区表数据切换方案上线的时候遇到一个问题:在新方案应用之后,新方案的执行计划与旧方案执行计划相差甚远,导致业务应用延迟。同样的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/