ORACLE数据库优化-分区

摘要:电信行业数据库的数据量非常庞大,仅仅一张表就可能达到几百G以上,如果不对这种大表进行优化,则数据访问的性能就会受到很大影响。该文主要讲解ORACLE分区技术,并利用分区技术来实现对大型数据库的大表进行优化。

关键词:性能调优;分区

中图分类号:T311 文献标识码:A文章编号:1009-3044(2010)17-4602-03

Oracle Database Performance Tuning-Partition

QI Wei-wei

(Laiwu Vocational and Technical College, Laiwu 271100, China)

Abstract: The database is very large in Telecommunications industry , only one table may reach above hundreds of G,if the big table is not optimized, the performance of data access will be greatly affected. This paper explain partition technology, and by using the partition technology for large database technology to realize the big table is optimized.

Key words: performance tuning; partition

1 Partition技术介绍

ORACLE的分区是一种处理超大型表、索引等的技术。通过将大表和索引按照分区规则分成可以管理的若干小块,从而避免了对每个表作为一个大的、单独的对象进行管理,为大量数据提供了可伸缩的性能。分区通过将操作分配给更小的存储单元,减少了需要进行管理操作的时间,并通过增强的并行处理提高了性能,通过屏蔽故障数据的分区,还增加了可用性。

在oracle 10gR2版本中,支持以下几种分区类型:1) 范围分区(range);2) 列表分区(list);3) 散列分区(hash);4) 组合分区(range-list 和 range-hash)。

范围分区(range):范围分区就是对数据表中的某个值的范围进行分区,根据某个值的范围,决定将该数据存储在哪个分区上。如根据业务记录的创建日期进行分区等。

散列分区(Hash):散列分区为通过指定分区编号来均匀分布数据的一种分区类型,因为通过在I/O设备上进行散列分区,使得这些分区大小一致。

列表分区(List):当你需要明确地控制如何将行映射到分区时,就使用列表分区方法。与范围分区和散列分区所不同,列表分区不支持多列分区。如果要将表按列分区,那么分区键就只能由表的一个单独的列组成,然而可以用范围分区或散列分区方法进行分区的所有的列,都可以用列表分区方法进行分区。

2 数据库分区设计优化方案

2.1 确定哪些大表需要进行分区

使用分区技术时,并不是对数据库中的所有表都进行分区,而只针对数据量比较大的一些大表才进行分区。根据分区的定义可知,分区其实就是将一个大数据段按规则划分成若干个小数据段,如果表对象本身很小,就失去了分区的意义。

根据经验,数据量大于1000万的表才需要做分区。

SQL> select owner, table_name, num_rows

from dba_tables

where num_rows > 10000000

and partitioned = 'NO';

说明:

在使用以上语句统计需要做分区的大表时,必须先收集数据库系统的统计信息。否则num_rows的数据不准确,无法正确表达出表对象的记录数。

2.2 讨论分区类型及分区字段的选择:

这一步至关重要,分区类型和分区字段的选择严重影响到数据表的访问性能。选择了错误的分区类型或分区字段给数据库性能带来的负面影响会比不做分区更大,因此在决定分区类型和分区字段时一定要与项目组讨论,按照业务需求及业务逻辑共同制定。

根据经验,选择分区类型的步骤:

1) 先确定该表中哪个字段在select语句的谓词中使用最频繁.此字段将做为分区字段。

因为分区的目的是将一个大表的数据段按规则分离成若干个小数据段,索引也分离为若干小索引段,在数据访问时,根据索引只需要访问其中的一个小索引段,最后访问其中的一个数据段,从而减小了需要访问的数据量,达到优化的目的。如果select语句的谓词中不包括分区字段,则必须访问完整个索引段,最后访问所有的小数据段,才能定位出需要访问的数据。

2) 根据分区字段的特点,确定分区类型。

如果该字段有明确的顺序先后关系,则该表合适做范围分区。如:时间;如果该字段没有明确的范围顺序关系,则是具有唯一值或若干值,则该表合适做列表分区。如:部门、分公司;如果该字段既无明确的范围顺序关系,也无具体值,而是些流水号,则该表合适做散列分区。如:批处理号、流水号。

2.3 数据表空间及索引表空间设计

表对象和索引对象的第一个规则是把表和索引分离。把表和相应的索引建立在不同的表空间中,最好在不同的磁盘上。这样可以避免在数据管理和查询时出现的许多I/O冲突。

在此优化方案中,我们将为每个分区创建一个对应的表空间,让表分区存放在不同的表空间中,达到不同分区间数据访问的分离。同时为每个索引分区也创建独立的索引分区表空间。

例:

SQL> create table appnostatus

(

appnochar(15) not null,

type char(2) not null,

utimedate,

updatetime char(20)

) partition by range (utime)

(

partition appnostatus_p201001 values less than (to_date('2010-02-01','yyyy-mm-dd')) tablespace zylife_data_p01,

partition appnostatus_p201002 values less than (to_date('2010-03-01','yyyy-mm-dd')) tablespace zylife_data_p02,

partition appnostatus_p201003 values less than (to_date('2010-04-01','yyyy-mm-dd')) tablespace zylife_data_p03,

partition appnostatus_p201004 values less than (to_date('2010-05-01','yyyy-mm-dd')) tablespace zylife_data_p04,

partition appnostatus_p201005 values less than (to_date('2010-06-01','yyyy-mm-dd')) tablespace zylife_data_p05,

partition appnostatus_p201006 values less than (to_date('2010-07-01','yyyy-mm-dd')) tablespace zylife_data_p06,

partition appnostatus_p201007 values less than (to_date('2010-08-01','yyyy-mm-dd')) tablespace zylife_data_p07,

partition appnostatus_p201008 values less than (to_date('2010-09-01','yyyy-mm-dd')) tablespace zylife_data_p08,

partition appnostatus_p201009 values less than (to_date('2010-10-01','yyyy-mm-dd')) tablespace zylife_data_p09,

partition appnostatus_p201010 values less than (to_date('2010-11-01','yyyy-mm-dd')) tablespace zylife_data_p10,

partition appnostatus_p201011 values less than (to_date('2010-12-01','yyyy-mm-dd')) tablespace zylife_data_p11,

partition appnostatus_p201012 values less than (to_date('2011-01-01','yyyy-mm-dd')) tablespace zylife_data_p12,

partition appnostatus_pdefault values less than (maxvalue)tablespace zylife_data_default

) enable row movement;

此优化方案中,将每一个分区都独立对应一个表空间,防止物理底层I/0冲突。

创建本地分区索引,并将索引分区的索引段也存放在不同的表空间。

SQL> create index idx_appnostatus_01 on appnostatus (utime,appno) local (

partition idx_appnostatus_01_p201001 tablespace zylife_index_p01,

partition idx_appnostatus_01_p201002 tablespace zylife_index_p02,

partition idx_appnostatus_01_p201003 tablespace zylife_index_p03,

partition idx_appnostatus_01_p201004 tablespace zylife_index_p04,

partition idx_appnostatus_01_p201005 tablespace zylife_index_p05,

partition idx_appnostatus_01_p201006 tablespace zylife_index_p06,

partition idx_appnostatus_01_p201007 tablespace zylife_index_p07,

partition idx_appnostatus_01_p201008 tablespace zylife_index_p08,

partition idx_appnostatus_01_p201009 tablespace zylife_index_p09,

partition idx_appnostatus_01_p201010 tablespace zylife_index_p10,

partition idx_appnostatus_01_p201011 tablespace zylife_index_p11,

partition idx_appnostatus_01_p201012 tablespace zylife_index_p12,

partition idx_appnostatus_01_default tablespace zylife_index_default

);

2.4 分区表执行计划的选择:

执行一个select语句,查看其执行计划:

SQL> select type from appnostatus where utime = :b1 and appno = :b2;

该语句的执行计划如下:

SELECT STATEMENT, GOAL = ALL_ROWS

COUNT STOPKEY

PARTITION RANGE SINGLE

TABLE ACCESS BY LOCAL INDEX ROWID

INDEX RANGE SCAN

可以看出,创建为分区表后,数据访问时只需要访问其中的某一个分区的数据,而其他的分区则不需要进行访问。对大表而言,可以减小很多的I/0操作,达到优化的目的。

3 总结

Oracle数据库的分区技术可以改善查询性能,仅搜索自己关心的分区,提高检索速度。同时可以把不同的分区分离至不同的磁盘上,以平衡I/0,改善整个系统的性能。除此之外,在数据维护方面,分区技术也有很大的优势。在进行历史数据转储时,只需要将需要转储的数据分区export备份出来转储至磁带中。而不需要将整张表全部export备份出来。在历史数据清理时,可以将历史数据所在的分区truncate或drop,而不影响表的其他数据,同时释放空间。

参考文献:

[1] Hart M,Jesse S.Oracle Database 10g高可用性实现方案――运用RAC、Flashback和Data Guard技术[M].刘永健,孔令梅,译.北京:清华大学出版社,2005.

[2] 张天慧.专家精讲:Oracle数据库管理与维护[M].北京:电子工业出版社,2009.

[3] 汪照东.Oracle 11g数据库管理与优化宝典[M].北京:电子工业出版社,2008.

[4] 安东尼尼.Oracle性能诊断艺术[M].北京:人民邮电出版社,2009.

[5] 陈吉平.构建0racle高可用环境[M].北京:电子工业出版社,2009.