由于客服数据库是一个提供基础服务的系统. 主要是查询, 新增, 修改客户信息. 主要的操作是查询.
客服数据库的使用特点是: 访问量大, 对系统响应时间要求高, 并且需要提供7x24的服务.
客服数据库的设计特点是: 表的数量少, 但是每个表的记录数很多. 查询SQL语句比较简单, 查询条件都可以用上合理的索引.
目前的查询SQL的执行的响应时间较短(秒级). 但是系统I/O量非常大, 平均占用80%的I/O带宽, 有时候峰值会达到100%的I/O带宽. 考虑到数据量在不断增长, 需要找到一个办法来解决系统的I/O压力较大以及这些大表的可维护性和可用性的问题. 用户考虑是否可以通过使用Oracle数据库提供的表分区功能来解决系统I/O大的问题. 应客户的要求，Oracle为客户对表分区方案进行评估.
在综合考虑了目前的客服数据库的特点以及主机的性能情况, 同时考虑到将来的数据增长. 此次项目需要解决以下主要问题:
1) 提高客服数据库的查询效率，降低对系统资源的使用率；尤其是要减缓数据库对系统I/O的压力.
2) 寻求长期的解决方案，以应对数据不断增长的局面；特别是在大表的可用性和可维护性方面.
需要进行分区的表及相关的SQL语句
为了在较短时间内检验表分区方案的可行性以及有效性, 我们根据用户提供的系统性能报告挑选了最影响系统性能的SQL语句加以分析. 根据用户提供的资料, 这些SQL语句占用了最多的系统资源. 特点是单次执行时间短(小于10秒), 但是执行次数多. 所以从整体来看, 这些SQL语句都是影响系统性能的主要因素.
表/索引的分区方案:
在综合考虑了客服数据库的压力情况以及常用的SQL语句的查询条件后, 我们看到这些查询条件有以下特点:
1. 都是等值查询条件和连接条件. 并且可以用上索引.
2. 基本上都是以MARK_NO作为查询条件或连接条件.
3. 查询条件比较简单.
因此, 我们决定使用HASH分区, 在关键列上创建LOCAL索引. 我们在测试系统的用户TEST下面创建了以下分区表和分区索引, 导入了数据:
|
表名 |
表分区方案(分区键值,分区数目) |
索引分区方案 |
|
T_P1 |
HASH (CITYNO) 1024 |
(CITYINO) LOCAL |
|
T_P2 |
HASH (MARK_NO) 1024 |
(MARK_NO) LOCAL |
|
T_P3 |
HASH (MARK_NO) 512 |
(MARK_NO) LOCAL |
|
T_P4 |
HASH (MARK_NO) 512 |
(MARK_NO) LOCAL |
|
T_P5 |
HASH (T_P5_NO) 1024 |
(T_P5_NO) LOCAL |
|
T_P6 |
HASH (CITYNO) 1024 |
(CITYINO) LOCAL |
以下是具体的命令:
create table T_P1
partition by hash(CITYINO)
partitions 1024 AS SELECT * FROM Testuser.T_P1 where 1=2;
create index ind_T_P1_CITYINO on T_P1(CITYINO) local ;
create table T_P2
partition by hash(mark_no)
partitions 1024 AS SELECT * FROM .T_P2 where 1=2;
create index ind_T_P2_partyno on T_P2(mark_no) local;
create table T_P3
partition by hash(mark_no)
partitions 512 as select * from .T_P3 where 1=2;
create index ind_prop_cust_partyno on T_P3(mark_no) local;
create table T_P4
partition by hash(busi_mark_no)
partitions 512 as select * from Testuser.T_P4 where 1=2;
create index ind_prop_insur_partyno on T_P4(busi_mark_no) local;
create table T_P5
partition by hash(T_P5_NO)
partitions 1024 as select * from TESTUSER.T_P5 where 1=2;
create index ind_T_P5_T_P5no on T_P5(T_P5_no) local;
create table T_P6
partition by hash(CITYINO)
partitions 1024 AS SELECT * FROM Testuser.T_P6 where 1=2;
create index ind_pol_ben_CITYINO on T_P6(CITYINO) local;
初步测试结果:
我们分别进行了以下SQL语句在没有分区和分区后的表上的测试.
select mark_no,client_no,business,branch_code from p_markno_deptno_view where mark_no=:1
执行计划的区别:
|
没有分区的执行计划 |
分区后的执行计划 |
|
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=16 Card=2 Bytes=54) 1 0 VIEW OF 'P_MARKNO_DEPTNO_VIEW' (Cost=16 Card=2 Bytes=54 )
2 1 SORT (UNIQUE) (Cost=16 Card=2 Bytes=60) 3 2 UNION-ALL 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T_P3' (Co st=4 Card=1 Bytes=30)
5 4 INDEX (RANGE SCAN) OF 'PK_T_P3' (UNIQUE) (Cost=3 Card=1)
6 3 TABLE ACCESS (BY INDEX ROWID) OF 'T_P4' (Cost=4 Card=1 Bytes=30)
7 6 INDEX (RANGE SCAN) OF 'PK_T_P4' (UNIQU E) (Cost=3 Card=1) |
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=12 Card=2 Bytes=54) 1 0 VIEW OF 'P_MARKNO_DEPTNO_VIEW' (Cost=12 Card=2 Bytes=54 )
2 1 SORT (UNIQUE) (Cost=12 Card=2 Bytes=58) 3 2 UNION-ALL 4 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PROPERTY_CUS T' (Cost=2 Card=1 Bytes=29)
5 4 INDEX (RANGE SCAN) OF 'IND_PROP_CUST_PARTYNO' (NON -UNIQUE) (Cost=1 Card=1)
6 3 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'PROPERTY_INS URED' (Cost=2 Card=1 Bytes=29)
7 6 INDEX (RANGE SCAN) OF 'IND_PROP_INSUR_PARTYNO' (NO N-UNIQUE) (Cost=1 Card=1) |
运行SQL语句的统计结果(consistent gets/运行时间):
|
|
不分区 |
分区后 |
|
mark_no='000240079776' |
10块/0.359秒 |
8块/ 0.203秒 |
|
mark_no='000239538081' |
10块/0.25秒 |
8块/ 0.203秒 |
|
mark_no='000238564517' |
10块/0.67秒 |
8块/ 0.52秒 |
select T_P1.mark_no from T_P2,T_P1 where T_P1.CITYINO= :1 and T_P1.mark_no=T_P2.mark_no and T_P2.lcd > :2 union
select T_P1.mark_no from T_P1 where T_P1.CITYINO= :3 and T_P1.lcd > :4
执行计划的区别:
|
没有分区 |
分区后 |
|
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=243 Card=66 Bytes=27 75)
1 0 SORT (UNIQUE) (Cost=243 Card=66 Bytes=2775) 2 1 UNION-ALL 3 2 NESTED LOOPS (Cost=194 Card=51 Bytes=2295) 4 3 TABLE ACCESS (BY INDEX ROWID) OF 'T_P1' (Cost=41 Card=51 Bytes=1224)
5 4 INDEX (RANGE SCAN) OF 'IDX_T_P1_CITYINO' (NON- UNIQUE) (Cost=4 Card=51)
6 3 TABLE ACCESS (BY INDEX ROWID) OF 'T_P2' (Cost=3 Ca rd=1 Bytes=21)
7 6 INDEX (UNIQUE SCAN) OF 'PK_T_P2' (UNIQUE) (Cost= 2 Card=1)
8 2 TABLE ACCESS (BY INDEX ROWID) OF 'T_P1' (Cost=41 C ard=15 Bytes=480)
9 8 INDEX (RANGE SCAN) OF 'IDX_T_P1_CITYINO' (NON-UN IQUE) (Cost=4 Card=51) |
Execution Plan ---------------------------------------------------------- 0 SELECT STATEMENT ptimizer=CHOOSE (Cost=1143 Card=592 Bytes= 23316)
1 0 SORT (UNIQUE) (Cost=1143 Card=592 Bytes=23316) 2 1 UNION-ALL 3 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_P2' (Cost= 2 Card=1 Bytes=22)
4 3 NESTED LOOPS (Cost=1130 Card=564 Bytes=22560) 5 4 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_P1' (Cost=2 Card=564 Bytes=10152)
6 5 INDEX (RANGE SCAN) OF 'IND_T_P1_CITYINO' (NO N-UNIQUE) (Cost=1 Card=225)
7 4 PARTITION HASH (ITERATOR) 8 7 INDEX (RANGE SCAN) OF 'IND_T_P2_PARTYNO' (NON- UNIQUE) (Cost=1 Card=20)
9 2 TABLE ACCESS (BY LOCAL INDEX ROWID) OF 'T_P1' (Cos t=2 Card=28 Bytes=756)
10 9 INDEX (RANGE SCAN) OF 'IND_T_P1_CITYINO' (NON-UN IQUE) (Cost=1 Card=225) |
运行SQL语句的统计结果(consistent gets/运行时间):
|
|
不分区 |
分区后 |
|
CITYINO='1170114932’,lcd>’20060101’(110rows) |
2948/6秒 |
1752块/8.969秒 |
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/747/viewspace-464601/ ，如需转载，请注明出处，否则将追究法律责任。
