第一部分 棉花数据库问题和分析
1.问题sql
数据库的版本是9i,问题sql有两个:
Sql1:
SELECT c_lotno FROM b_ctn_normal WHERE d_prodatetime BETWEEN to_date('2011-07-01', 'yyyy-mm-dd HH24:MI:SS') AND to_date('2012-07-03', 'yyyy-mm-dd HH24:MI:SS') AND n_madein = 65 AND rownum < 31 |
Sql2:
SELECT count(c_bale) FROM b_ctn_normal WHERE d_prodatetime BETWEEN to_date('2011-07-01', 'yyyy-mm-dd HH24:MI:SS') AND to_date('2012-07-03', 'yyyy-mm-dd HH24:MI:SS') |
这俩sql其实非常简单,就是一个按照时间的分页查询,一个查询时间范围内的总数据量。
但是这个表的数据量很大,41803656条数据,单表容量超过21G。因此查询非常慢,仅仅查询30条数据就需要耗费十几分钟。甚至查不出结果。
2 表概况
表b_ctn_normal是一个分区表,按照D_VERIFYDATETIME进行了range分区,分区的策略为2010年前每年一个分区,2010年后每月一个分区.该表的数据量为41803656条。
partition by range (D_VERIFYDATETIME) partition PART_20080101 values less than (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) partition PART_20090101 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))—后续的省略
|
另外该表上建了大量的索引,见表1:
3.表存在的问题
以下是索引的概况统计信息。
NDEX_NAME |
DISTINCT_KEYS |
NUM_ROWS |
SAMPLE_SIZE |
I_CTN_NORMAL_99 |
4 |
41805079.0630631 |
8459008 |
I_NORMAL_MADEIN1 |
17 |
41804897.3546108 |
9544621 |
I_CTN_NORMAL_66 |
80 |
41767143.7096454 |
9580744 |
I_CTN_NORMAL_77 |
86 |
41473125.0963043 |
9479665 |
I_CTN_NORMAL_22 |
366 |
41875654.4438373 |
9937607 |
I_CTN_NORMAL_11 |
889 |
41867424.9314059 |
11007070 |
I_CTN_NORMAL_55 |
1957 |
40169648.695544 |
9058949 |
I_NORMAL_UPLOADTIME1 |
17253 |
41866396.7193889 |
10087608 |
I_CTN_NORMAL_33 |
384472 |
41842227.8696896 |
10621842 |
I_NORMAL_D_COLORGRADETIME |
1485863 |
41727490.2734861 |
9119757 |
GLOBAL_INDEX_D_VERIFYDATETIME |
21162573 |
41804256 |
9592128 |
PRIMARY1_ID |
41804473 |
41804473 |
9540784 |
UNI_NORMAL_C_BALE1 |
41841809 |
41841809.1781587 |
7023237 |
【表1】索引概况
l 表不是很宽,但是竟然建了13个索引,而且8个索引可选性很差,每个索引都占据不少段空间,极大的浪费了存储空间。
l 索引没有分区,千万级别的数据量,本身查找索引就很耗时,因此应当对索引分区其高索引检索性能。
l 表的索引和表应该建立在不同的表空间分开存放,同时表的分区在不同的表空间存放。
l 分区的记录不均匀,分区不合理
分区的统计信息显示,大量的数据集中在了PART_20100101和PART_20090101分区,分区很不合理,大大削弱了分区表的作用。应该对分区进行细粒度的划分,均匀分布数据。
TABLE_NAME |
PARTITION_NAME |
NUM_ROWS |
SAMPLE_SIZE |
B_CTN_NORMAL |
PART_20100101 |
15580400 |
2883811 |
B_CTN_NORMAL |
PART_20090101 |
13007483 |
2420607 |
B_CTN_NORMAL |
PART_20101201 |
3809673 |
709735 |
B_CTN_NORMAL |
PART_20110101 |
2656138 |
494675 |
B_CTN_NORMAL |
PART_20101101 |
2641196 |
492471 |
B_CTN_NORMAL |
PART_20110201 |
1169697 |
217919 |
B_CTN_NORMAL |
PART_20100201 |
1106187 |
205854 |
B_CTN_NORMAL |
PART_20110401 |
662618 |
123426 |
B_CTN_NORMAL |
PART_20110301 |
271190 |
50600 |
B_CTN_NORMAL |
PART_20100501 |
205173 |
37933 |
B_CTN_NORMAL |
PART_20100401 |
194223 |
35804 |
B_CTN_NORMAL |
PART_20100601 |
154195 |
28641 |
B_CTN_NORMAL |
PART_20110501 |
137085 |
25587 |
B_CTN_NORMAL |
PART_20100301 |
105747 |
19575 |
B_CTN_NORMAL |
PART_20101001 |
64424 |
11960 |
B_CTN_NORMAL |
PART_20100701 |
33743 |
6206 |
B_CTN_NORMAL |
PART_20100801 |
4044 |
725 |
B_CTN_NORMAL |
PART_20100901 |
283 |
283 |
B_CTN_NORMAL |
PART_20111001 |
80 |
80 |
B_CTN_NORMAL |
PART_20080101 |
53 |
53 |
B_CTN_NORMAL |
PART_20111201 |
21 |
21 |
B_CTN_NORMAL |
PART_20120601 |
2 |
2 |
B_CTN_NORMAL |
PART_20120301 |
1 |
1 |
B_CTN_NORMAL |
PART_20110601 |
0 |
|
B_CTN_NORMAL |
PART_20110701 |
0 |
|
B_CTN_NORMAL |
PART_20120401 |
0 |
|
B_CTN_NORMAL |
PART_20120801 |
0 |
|
B_CTN_NORMAL |
PART_20120701 |
0 |
|
B_CTN_NORMAL |
PART_20120501 |
0 |
|
B_CTN_NORMAL |
PART_20120201 |
0 |
|
B_CTN_NORMAL |
PART_20120101 |
0 |
|
B_CTN_NORMAL |
PART_20111101 |
0 |
|
B_CTN_NORMAL |
PART_20110801 |
0 |
|
B_CTN_NORMAL |
PART_20110901 |
0 |
|
注:以上的统计信息都是最新收集的.
4.分析制定策略
结合问题sql发现, 两个查询共同依赖于d_prodatetime字段的过滤,但是该字段重复值很多,根据统计信息,该列的NUM_DISTINCT为456,因此只依靠索引没有意义,CBO不会选择索引而是全表扫描。执行这两个查询的时候对数据没有区分度,选择了4K万数据进行全表扫描,效率可写而至。
而该查询较为简单,经过仔细的分析并研究目前的分区策略,我认为最佳的策略是增加范围分区字段,将表重新分区,分区条件纳入d_prodatetime字段。这样查询时可以以d_prodatetime进行分区裁减从而减少扫描的数据量。需要分析字段的值的分布区间,平均分配到各分区。经过分析表的数据分布,从节省时间上考虑,就以每两个月为一个范围进行分区。
partition by range (D_VERIFYDATETIME, d_prodatetime) partition PART_20080101 values less than (TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) partition PART_20090101 values less than (TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'), TO_DATE(' 2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
|
考虑到sql1中有对n_madein的过滤,分析该字段,字段值总共为17种,为number类型,而且该字段值在字段D_VERIFYDATETIME, d_prodatetime表示的区间中分布很均匀,因此,在上述分区基础上增加list分区,使分区策略变为Rang-list复合分区:
partition by range (D_VERIFYDATETIME, d_prodatetime) subpartition by list(N_MADEIN) partition PART_20080101 values less than (range1,range2) ( subpartition p31 values(64), subpartition p32 values(37), subpartition p33 values(48), subpartition p34 values(55), …………… ), partition PART_20090101 values less than (range3,range4) ( subpartition p31 values(64), subpartition p32 values(37), subpartition p33 values(48), subpartition p34 values(55), ………….. ), )
|
这样,sql1在执行时,会首先根据d_prodatetime裁减掉部分数据,然后再根据N_MADEIN再次裁减掉一部分,这样sql1的性能应该会得到很大提升。而对于仅仅含有N_MAADIN的过滤条件的查询,都会进行分区裁减,减少数据量。具体性能提高多少,需要测试。
同时,之前的分区字段D_VERIFYDATETIME的粒度应该适当的减小。因为d_prodatetime的重复值较多,以之前的分区粒度,2010年前的是每年一个分区,这样会导致d_prodatetime分区后数据会很不均匀,若查询2010年之前的数据,则d_prodatetime裁减的效果会不好,因此需要考虑d_prodatetime的字段值,重新规划分区粒度。分区粒度的大小需要考虑到d_prodatetime的范围分布情况。通过分析决定和d_prodatetime字段使用同样的分区范围。
由于需要对表重新分区,因此需要重建表。如果在已有的分区策略下增加分区,则直接alter表即可,oracle提供了丰富的方法为不同的分区增加新的分区;但是修改分区策略,必须重建表。而表数据量巨大,单表超过20G,因此数据的加载成为头疼的问题,如果在生产环境,产生的日志也很巨大。
第二部分 试验过程结果及分析
为了能试验本文预测的效果,于是我在我本机腾出了30G的空间,将库置于非归档模式,然后用database link以insert append的方式直接加载数据。通过仔细的权衡,我创建的分区表如下(限于时间关系,将所有表分区和索引分区建在一个表空间内):
--ddl过长,省略(见附件)
(注:复合分区可以为二级分区创建一个template,从而减少建表DDL的篇幅)
考虑只有本文开头的两个查询问题突出,因此我只建立了俩索引,选择了全局范围分区索引。
--创建分区索引GLOBAL_INDEX_D_VERIFYDATETIME CREATE INDEX GLOBAL_INDEX_D_VERIFYDATETIME ON b_ctn_normal(D_VERIFYDATETIME) GLOBAL PARTITION BY RANGE(D_VERIFYDATETIME)( partition part_index_0 values less than(to_date('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_1 values less than(to_date('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_2 values less than(to_date('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_3 values less than(to_date('2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_4 values less than(to_date('2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_5 values less than(to_date('2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_6 values less than(to_date('2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_7 values less than(to_date('2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_8 values less than(to_date('2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_9 values less than(to_date('2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_10 values less than(to_date('2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_11 values less than(to_date('2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_12 values less than(to_date('2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_13 values less than(to_date('2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_14 values less than(to_date('2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_15 values less than(to_date('2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_16 values less than(to_date('2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_17 values less than(to_date('2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_18 values less than(to_date('2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_19 values less than(to_date('2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_20 values less than(to_date('2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_21 values less than(to_date('2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_22 values less than(to_date('2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_23 values less than(to_date('2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_24 values less than(to_date('2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_25 values less than(to_date('2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_26 values less than(to_date('2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_27 values less than(to_date('2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_28 values less than(to_date('2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index_29 values less than(maxvalue) ) |
注:分区索引也可以使用本地前缀索引,可以减少DDL篇幅
考虑到D_PRODATETIME的值较多,当查询来临时,oracle首先以查询where条件中的D_PRODATETIME进行裁减,到目标分区之后,如果有索引的话,应该可以进行INDEX RANGE SCAN进行扫描,因此先建立分区索引试试。同样选择了全局范围分区索引。
--创建分区索引GLOBAL_INDEX_D_PRODATETIME CREATE INDEX GLOBAL_INDEX_D_PRODATETIME ON b_ctn_normal(D_PRODATETIME) GLOBAL PARTITION BY RANGE(D_PRODATETIME)( partition part_index1_0 values less than(to_date('2008-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_1 values less than(to_date('2008-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_2 values less than(to_date('2008-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_3 values less than(to_date('2008-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_4 values less than(to_date('2008-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_5 values less than(to_date('2009-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_6 values less than(to_date('2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_7 values less than(to_date('2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_8 values less than(to_date('2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_9 values less than(to_date('2009-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_10 values less than(to_date('2009-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_11 values less than(to_date('2010-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_12 values less than(to_date('2010-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_13 values less than(to_date('2010-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_14 values less than(to_date('2010-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_15 values less than(to_date('2010-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_16 values less than(to_date('2010-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_17 values less than(to_date('2011-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_18 values less than(to_date('2011-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_19 values less than(to_date('2011-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_20 values less than(to_date('2011-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_21 values less than(to_date('2011-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_22 values less than(to_date('2011-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_23 values less than(to_date('2012-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_24 values less than(to_date('2012-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_25 values less than(to_date('2012-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_26 values less than(to_date('2012-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_27 values less than(to_date('2012-09-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_28 values less than(to_date('2012-11-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS')), partition part_index1_29 values less than(maxvalue) )) |
下面是加载数据以及实施步骤:
l 表数据加载
创建db link “ctn”.
Insert /*+append*/ into b_ctn_normal select * from b_ctn_normal@ctn;
加载速度还可以,大概30分钟加载完,加载数据量4400W。
l 分别按照上述的策略创建分区索引
GLOBAL_INDEX_D_VERIFYDATETIME和GLOBAL_INDEX_D_PRODATETIME
索引创建约为30分钟
l 执行sql1和sql2和原始库进行对比
Sql1的测试结果对比:
|
返回行数 |
Pl/sql查询 |
Sqlplus跟踪 |
优化前 |
30 |
>15分钟 |
00: 04: 31.62 |
优化后 |
30 |
<0.4秒 |
00: 00: 00.03 |
Sql2的测试结果对比:
|
返回行数 |
Pl/sql查询 |
Sqlplus跟踪 |
优化前 |
5529 |
? |
00: 05: 42.67 |
优化后 |
5529 |
<0.02秒 |
00: 00: 00.01 |
通过以上对比结果,显示新的分区策略带来了巨大的性能提升,显示了oracle分区技术的强大威力。原来十几分钟甚至返回不了结果的查询现在毫秒就返回数据。
下面分析优化后的执行计划:
Sql1执行计划:
通过执行计划可以看出,查询正确的使用了d_prodatetime字段进行了分区裁减,然后使用到了该列的分区索引,但是并没有使用n_madein进行裁减。于是改变了下查询条件,将查询的数据量增大:
SELECT c_lotno FROM b_ctn_normal WHERE d_prodatetime BETWEEN to_date('2011-07-01', 'yyyy-mm-dd HH24:MI:SS') AND to_date('2012-07-03', 'yyyy-mm-dd HH24:MI:SS') AND n_madein = 65 AND rownum < 5000 |
这次,查询使用了二级分区裁减。先是对一级分区进行裁减,然后又对二级分区进行裁减,最后对二级分区使用N_MADEIN进行全表扫描。执行计划显示,查询5000条数据时耗时增加了很多,因为扫描的数据量实在太大了,查询需要扫描很多分区。这样只能通过减少一次查询的数据量来保证性能。通过和开发人员确认,一次查询一般不用返回这么多数据。
Sql2执行计划:
执行计划已经显示的很明确,一级分区按照新分区的字段进行裁减,然后使用建立的分区索引,性能很高。
虽然新的分区策略显示了巨大的性能提升,有效的解决了性能问题,但是仔细分析一下,仍然存在一些问题:
u 分区较多,在4K万级别的表上,分区多达493个,这有些过分了。需要减少分区数量。目前的分区是每俩月一个分区,目前的数据分布比重新分区前均匀了很多,但是仍然存在不均匀现象,而且每俩月一个分区仍然较多。因此需要维持现在的范围分区字段不变,将现在的俩月一个分区的条件变化一下,分析数据的分布区间,制定一个不均匀的分区条件。如2010年8月的数据很多,那可以分别以2010-08-01~2010-08-15~2010-08-30为区间划分。如果2010年9-12月数据很少,那么可以将9-12月合并为一个分区。尽可能的均匀划分分区记录数,也减少分区数量。
u 评估二级分区的必要性。总的分区数是1级分区和二级分区的乘积,为M*N的关系。二级分区的增加,大大增加了分区数。分析发现,有接近一半的二级分区是空闲的,并没有记录装入,浪费了大量的空间。而且目前的sql并没有使用到二级分区裁减,因此需要评估二级分区带来的性能提高。然后考虑是否将二级分区去掉只采用范围分区。去掉二级分区,目前对性能是没有坏处的,而且未来如果用到对N_MADEIN字段的裁剪,直接alter表即可增加二级分区,不用重建。因此建议去掉。
l 总结
分区是处理大表的首要应对策略,但是分区字段的选取和分区的方法需要仔细权衡,一般第一想到的分区字段都是合理的,但是一些隐含的字段没有考虑到,未来数据量上去了,这些隐含的条件造成的性能问题就暴露出来了,因此还是需要全面的分析。
对表进行了分区,相应的也要对索引进行分区,这样可以裁减掉部分索引,然后裁减掉记录,虽然是海量数据,但是却拥有极高的查询速度。记得在一本书上看过,作者说,正是因为有了分区技术,oracle才敢号称是海量数据库。
Reference:
【http://docs.oracle.com/cd/B19306_01/server.102/b14231/partiti.htm#i1009216】