论文部分内容阅读
[摘 要] Oracle数据库的性能调整相当重要,但难度也较大。如何提高Oracle数据库的检索效率,是数据库使用过程中的一项重要任务。本文基于此,对Oracle数据库的检索优化与应用进行了初步研究,从而更好地实现了数据库的调优。
[关键词] 数据库 检索优化 oracle
数据库管理员在数据库建立时,根据应用的需要合理设计分配表空间以及存储参数、内存使用初始化参数,对以后的数据库性能有很大的益处。只有认真分析Oracle运行过程当中出现的各种性能问题,才能保证Oracle数据库高效可靠地运行。因为数据库的性能调整是一个系统工程,涉及的方面很多,不能仅仅根据一个时间点的情况就断定数据库运行性能的好与坏。如何有效地进行调整,数据库管理员需要经过反反复复的过程。这些都需要在大量的实践工作中不断地积累经验。据统计,90%的性能问题是由于程序员或用户使用了不恰当的检索语句造成的,而不良的SQL语句往往来自于不恰当的索引设计、不充分的条件和不可优化的where子句。在进行恰当的优化后,运行速度就会明显的提高。因此,如何设计高效合理的检索语句就显得非常重要。本文以Oracle9i数据库应用实例为基础,结合数据库理论,介绍检索优化技术在现实系统中的运用。
一、在系统设计开发阶段调整数据库
为了充分利用Oracle数据库的功能特性,在设计信息系统时,数据库设计人员需要根据业务情况(如访问量或客户端数量)和现有资源状况(如数据库服务器的配置)考虑系统结构和数据库的逻辑结构的设计:
1、调整应用程序结构设计。
即应用程序采用的是传统的C/S两层体系结构,还是B/W/D三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。
2、恰当使用分区、索引及存档功能。
如果某种业务的数据量增长非常快,可以考虑存放该业务的数据库表是否使用Oracle数据库的分区功能;对于经常访问的数据库表是否需要建立索引;对于经常访问但是当业务流程完成后不再变动的数据可采用放入历史档案的方法来实现应用系统中访问尽可能少的数据量。
3、恰当编写访问数据的SQL语句。
良好的SQL语句可以被数据库重复使用而减少分析时间;恰当的使用索引可使访问的数据块大大减少从而减少响应时间。应用程序的执行最终将归结为数据库中的SQL语句执行,因此SQL语句的执行效率决定了Oracle数据库的性能。Oracle公司推荐使用Oracle语句优化器(Oracle Optimizer)和行锁管理器(row-level manager)来调整优化SQL语句。
4、调整硬盘I/O
这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上,做到硬盘之间I/O负载均衡。在磁盘比较富裕的情况下还应该遵循以下原则:
将表和索引分开;
创造用户表空间,与系统表空间(system)分开磁盘;
创建表和索引时指定不同的表空间;
创建回滚段专用的表空间,防止空间竞争影响事务的完成;
创建临时表空间用于排序操作,尽可能的防止数据库碎片存在于多个表空间中。
二、在数据库运行阶段调整数据库
数据库运行阶段调整数据库包括两个方面:操作系统级的调整;数据库级的调整。
1、操作系统级的调整
实施操作系统级调整的主要目的是减少内存交换,减少分页,使SGA(System Globle Area)可留驻内存。
(1)减少内存交换
内存交换(swapping)可能会造成很大的内存开销,应将它最小化。运行在Solaris Unix操作系统上的Oracle数据库,可利用vmstat或sar命令来检查交换,查看到系统级内存和硬盘I/O的使用情况,调整unix数据缓冲池的大小、每个进程所能使用的内存大小等参数。
(2)控制分页
少量的内存分页不会太显著地影响系统的性能,因为应用程序不必全部放在内存中。但是分页过多将会造成系统性能下降。为了检测过多的分页,可在快速响应或空闲期间运行测量,并与响应迟缓时的测量进行比较。可通过以下办法来解决:
使用vmstat或sar-p监控分页;
安装更多的内存;
将一些工作移到另一系统中;
配置系统核心使用更少的内存;
保持SGA在单个共享内存段中。
(3)使SGA(System Globle Area)留驻内存
SGA是对数据库数据进行快速访问的一个系统全局区,若SGA本身需要频繁地进行释放、分配,则不可能达到快速访问数据的目的,因此,要求SGA驻留内存。这时,我们可以重新配置UNIX核心,调整一些操作系统参数以达到增加共享内存的目的。
2、数据库级的调整
每一个Oracle实例都是由一组Oracle后台进程和SGA的一个内存区组成的。这组后台进程会自动的读写数据库的数据文件,因此,数据库性能可以被这些因素所影响:SGA各部分的分配是否合理,使用效率是否正常;I/O和锁竞争是否较多。
(1)SGA的分配及使用效率
分配给每个实例的内存,即SGA的使用效率如何,会大大影响数据库系统的性能。SGA由下列部分组成:共享池、数据块缓冲区、重做日志缓冲区、大池组成。以共享池为例:
共享池存放库缓存(存储共享SQL和PL/SQL区)和数据字典缓存(数据库对象信息)以及会话期间信息(对于MTS)。由于这些信息是应用程序需要经常访问的,因此这些信息需要保持高的命中率。可以通过以下语句来确认共享池数据的命中率:
库缓存:
select gethitratio from v$librarycache 应大于90%
select sum(reloads)/sum(pins) from v$librarycache应小于1%
数据字典缓存:
select sum(getmisses)/sum(gets)from v$rowcache应小于15%
由于程序设计人员的水平参差不齐,可能存在大的匿名块,这会导致SQL不能重用,因此需要找出大的匿名块以转换为存储过程达到重用:
select*from v$sqlarea where command_type=47 and length(sql_text)>500
而对于一些应用系统非常频繁使用的SQL对象如存储过程、函数、包等,可以通过钉在内存中的方式来防止由于共享池太小被移出:
exec dbms_shared_pool.keep(对象名)
(2) I/O和资源竞争
由于有众多的进程要写数据文件,因此需要通过I/O调整来解决I/O瓶颈问题。如果在设计阶段有效地考虑了表空间的合理分配,就能有效地在一定程度上减少I/O竞争。在数据库运行时,由于数据的动态增长,原来分配给表或索引的空间已经用完,Oracle会自动分配空间给这些数据库对象。而这个动态分配会对系统性能有所影响。在系统设计和试运行阶段数据量相对较小,效率低下的SQL可能并不会影响系统响应时间,但当系统数据量增长到一定程度时,需要在系统运行时监控并找出是哪些SQL不能有效使用索引或缺少索引,并进行相应调整:建立索引;修改SQL写法。
三、数据库检索优化的方法
本文重点说明改善用户检索计划的解决方案,下面介绍改善用户检索计划的方法。
1、避免使用不兼容的数据类型。
如float和int、char和varchar是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:SELECT name FROM employee WHERE salary>10000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为10000是个整型数。我们应当在编程时将整型转化成为money型,而不要等到运行时再转化。
2、合理使用索引
索引是数据库中重要的数据结构,它的根本目的就是为了提高检索效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处,其使用原则如下:
●在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高检索效率,反而会严重降低更新速度。
●如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。服务器上,索引可能失效或者因为频繁操作而使得读取效率降低,如果一个使用索引的检索不明不白地慢下来,可以试着用工具检查索引的完整性,必要时进行修复。另外,当数据库表更新大量数据后,删除并重建索引可以提高检索速度。
3、避免或简化排序
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序的步骤。以下是一些影响因素:
●索引中不包括一个或几个待排序的列;
●group by或order by子句中列的次序与索引的次序不一样;
●排序的列来自不同的表。为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的)。如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等。
4、用外联接提高表连接的检索速度
在作表连接(常用于视图)时,常使用以下方法来检索数据:
SELECT PAY_NO,PROJECT_NAME
FROM A
WHERE A.PAY_NO NOT IN (SELECT PAY_
NO FROM B WHERE VALUE>=120000);
但是若表A有10000条记录,表B有10000条记录,则要用掉30分钟才能查完,主要因为NOT IN要进行一条一条的比较,共需要10000*10000次比较后,才能得到结果。该用外联接后,可以缩短到1分左右的时间:
SELECT PAY_NO,PROJECT_NAME
FROM A,B
WHERE A.PAY_NO=B.PAY_NO(+)
AND B.PAY_NO IS NULL
AND B.VALUE>=12000;
5、避免相关子检索
一个列的标签同时在主检索和where子句中的检索中出现,那么很可能当主检索中的列值改变之后,子检索必须重新检索一次。检索嵌套层次越多,效率越低,因此应当尽量避免子检索。如果子检索不可避免,那么要在子检索中过滤掉尽可能多的行。
6、使用临时表加速检索
把表的一个子集进行排序并创建临时表,有时能加速检索。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。例如:SELECT cust.name,rcvbles.balance,……other columns FROM cust,rcvbles WHERE cust.customer_id=rcvlbes.customer_id AND rcvblls.balance>0 AND cust.postcode>“98000”ORDER BY cust.name如果这个检索要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个临时文件中,并按客户的名字进行排序:SELECT cust.name,rcvbles.balance,……other columns FROM cust,rcvbles WHERE cust.customer_id=rcvlbes.customer_id AND rcvblls.balance>0 ORDER BY cust.name INTO TEMP cust_with_balance然后以下面的方式在临时表中检索:SELECT*FROM cust_with_balance WHERE postcode>“98000”临时表中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以检索工作量可以得到大幅减少。注意:临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下,注意不要丢失数据。
7、避免相关子检索
一个列的标签同时在主检索和where子句中的检索中出现,那么很可能当主检索中的列值改变之后,子检索必须重新检索一次。检索嵌套层次越多,效率越低,因此应当尽量避免子检索。如果子检索不可避免,那么要在子检索中过滤掉尽可能多的行。
8、用排序来取代非顺序存取
非顺序磁盘存取是最慢的操作,表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况,使得我们在写应用程序时很容易写出要求存取大量非顺序页的检索。有些时候,用数据库的排序能力来替代非顺序的存取能改进检索。
四、结束语
数据库的检索在数据库的操作中占了相当大的比重,提高数据库的效率,主要在于提高数据库的检索。通过以上对数据库检索优化的探讨,我们知道对于数据库的检索优化重点就在于SQL语句的执行效率,因此抓住了这个重点,有针对性得进行进一步的实践,反复比较就可以得出最优的方案。
参 考 文 献
[1]科里M.J.著,张福德 译Oracle数据库性能优化技术,学苑出版社,1994
[2]黄开枝,Oracle9i数据库性能调整与优化,清华大学出版社,2005年6月
[3]唐汉明,翟振兴,兰丽华,关宝军,申宝柱,人民邮电出版社,2008年4月
[4]李俊民,精通SQL—结构化检索语言详解(第2版),人民邮电出版社,2008年8月■
[关键词] 数据库 检索优化 oracle
数据库管理员在数据库建立时,根据应用的需要合理设计分配表空间以及存储参数、内存使用初始化参数,对以后的数据库性能有很大的益处。只有认真分析Oracle运行过程当中出现的各种性能问题,才能保证Oracle数据库高效可靠地运行。因为数据库的性能调整是一个系统工程,涉及的方面很多,不能仅仅根据一个时间点的情况就断定数据库运行性能的好与坏。如何有效地进行调整,数据库管理员需要经过反反复复的过程。这些都需要在大量的实践工作中不断地积累经验。据统计,90%的性能问题是由于程序员或用户使用了不恰当的检索语句造成的,而不良的SQL语句往往来自于不恰当的索引设计、不充分的条件和不可优化的where子句。在进行恰当的优化后,运行速度就会明显的提高。因此,如何设计高效合理的检索语句就显得非常重要。本文以Oracle9i数据库应用实例为基础,结合数据库理论,介绍检索优化技术在现实系统中的运用。
一、在系统设计开发阶段调整数据库
为了充分利用Oracle数据库的功能特性,在设计信息系统时,数据库设计人员需要根据业务情况(如访问量或客户端数量)和现有资源状况(如数据库服务器的配置)考虑系统结构和数据库的逻辑结构的设计:
1、调整应用程序结构设计。
即应用程序采用的是传统的C/S两层体系结构,还是B/W/D三层体系结构。不同的应用程序体系结构要求的数据库资源是不同的。
2、恰当使用分区、索引及存档功能。
如果某种业务的数据量增长非常快,可以考虑存放该业务的数据库表是否使用Oracle数据库的分区功能;对于经常访问的数据库表是否需要建立索引;对于经常访问但是当业务流程完成后不再变动的数据可采用放入历史档案的方法来实现应用系统中访问尽可能少的数据量。
3、恰当编写访问数据的SQL语句。
良好的SQL语句可以被数据库重复使用而减少分析时间;恰当的使用索引可使访问的数据块大大减少从而减少响应时间。应用程序的执行最终将归结为数据库中的SQL语句执行,因此SQL语句的执行效率决定了Oracle数据库的性能。Oracle公司推荐使用Oracle语句优化器(Oracle Optimizer)和行锁管理器(row-level manager)来调整优化SQL语句。
4、调整硬盘I/O
这一步是在信息系统开发之前完成的。数据库管理员可以将组成同一个表空间的数据文件放在不同的硬盘上,做到硬盘之间I/O负载均衡。在磁盘比较富裕的情况下还应该遵循以下原则:
将表和索引分开;
创造用户表空间,与系统表空间(system)分开磁盘;
创建表和索引时指定不同的表空间;
创建回滚段专用的表空间,防止空间竞争影响事务的完成;
创建临时表空间用于排序操作,尽可能的防止数据库碎片存在于多个表空间中。
二、在数据库运行阶段调整数据库
数据库运行阶段调整数据库包括两个方面:操作系统级的调整;数据库级的调整。
1、操作系统级的调整
实施操作系统级调整的主要目的是减少内存交换,减少分页,使SGA(System Globle Area)可留驻内存。
(1)减少内存交换
内存交换(swapping)可能会造成很大的内存开销,应将它最小化。运行在Solaris Unix操作系统上的Oracle数据库,可利用vmstat或sar命令来检查交换,查看到系统级内存和硬盘I/O的使用情况,调整unix数据缓冲池的大小、每个进程所能使用的内存大小等参数。
(2)控制分页
少量的内存分页不会太显著地影响系统的性能,因为应用程序不必全部放在内存中。但是分页过多将会造成系统性能下降。为了检测过多的分页,可在快速响应或空闲期间运行测量,并与响应迟缓时的测量进行比较。可通过以下办法来解决:
使用vmstat或sar-p监控分页;
安装更多的内存;
将一些工作移到另一系统中;
配置系统核心使用更少的内存;
保持SGA在单个共享内存段中。
(3)使SGA(System Globle Area)留驻内存
SGA是对数据库数据进行快速访问的一个系统全局区,若SGA本身需要频繁地进行释放、分配,则不可能达到快速访问数据的目的,因此,要求SGA驻留内存。这时,我们可以重新配置UNIX核心,调整一些操作系统参数以达到增加共享内存的目的。
2、数据库级的调整
每一个Oracle实例都是由一组Oracle后台进程和SGA的一个内存区组成的。这组后台进程会自动的读写数据库的数据文件,因此,数据库性能可以被这些因素所影响:SGA各部分的分配是否合理,使用效率是否正常;I/O和锁竞争是否较多。
(1)SGA的分配及使用效率
分配给每个实例的内存,即SGA的使用效率如何,会大大影响数据库系统的性能。SGA由下列部分组成:共享池、数据块缓冲区、重做日志缓冲区、大池组成。以共享池为例:
共享池存放库缓存(存储共享SQL和PL/SQL区)和数据字典缓存(数据库对象信息)以及会话期间信息(对于MTS)。由于这些信息是应用程序需要经常访问的,因此这些信息需要保持高的命中率。可以通过以下语句来确认共享池数据的命中率:
库缓存:
select gethitratio from v$librarycache 应大于90%
select sum(reloads)/sum(pins) from v$librarycache应小于1%
数据字典缓存:
select sum(getmisses)/sum(gets)from v$rowcache应小于15%
由于程序设计人员的水平参差不齐,可能存在大的匿名块,这会导致SQL不能重用,因此需要找出大的匿名块以转换为存储过程达到重用:
select*from v$sqlarea where command_type=47 and length(sql_text)>500
而对于一些应用系统非常频繁使用的SQL对象如存储过程、函数、包等,可以通过钉在内存中的方式来防止由于共享池太小被移出:
exec dbms_shared_pool.keep(对象名)
(2) I/O和资源竞争
由于有众多的进程要写数据文件,因此需要通过I/O调整来解决I/O瓶颈问题。如果在设计阶段有效地考虑了表空间的合理分配,就能有效地在一定程度上减少I/O竞争。在数据库运行时,由于数据的动态增长,原来分配给表或索引的空间已经用完,Oracle会自动分配空间给这些数据库对象。而这个动态分配会对系统性能有所影响。在系统设计和试运行阶段数据量相对较小,效率低下的SQL可能并不会影响系统响应时间,但当系统数据量增长到一定程度时,需要在系统运行时监控并找出是哪些SQL不能有效使用索引或缺少索引,并进行相应调整:建立索引;修改SQL写法。
三、数据库检索优化的方法
本文重点说明改善用户检索计划的解决方案,下面介绍改善用户检索计划的方法。
1、避免使用不兼容的数据类型。
如float和int、char和varchar是不兼容的。数据类型的不兼容可能使优化器无法执行一些本来可以进行的优化操作。例如:SELECT name FROM employee WHERE salary>10000
在这条语句中,如salary字段是money型的,则优化器很难对其进行优化,因为10000是个整型数。我们应当在编程时将整型转化成为money型,而不要等到运行时再转化。
2、合理使用索引
索引是数据库中重要的数据结构,它的根本目的就是为了提高检索效率。现在大多数的数据库产品都采用IBM最先提出的ISAM索引结构。索引的使用要恰到好处,其使用原则如下:
●在经常进行连接,但是没有指定为外键的列上建立索引,而不经常连接的字段则由优化器自动生成索引。
●在频繁进行排序或分组(即进行group by或order by操作)的列上建立索引。
●在条件表达式中经常用到的不同值较多的列上建立检索,在不同值少的列上不要建立索引。比如在雇员表的“性别”列上只有“男”与“女”两个不同值,因此就无必要建立索引。如果建立索引不但不会提高检索效率,反而会严重降低更新速度。
●如果待排序的列有多个,可以在这些列上建立复合索引(compound index)。服务器上,索引可能失效或者因为频繁操作而使得读取效率降低,如果一个使用索引的检索不明不白地慢下来,可以试着用工具检查索引的完整性,必要时进行修复。另外,当数据库表更新大量数据后,删除并重建索引可以提高检索速度。
3、避免或简化排序
应当简化或避免对大型表进行重复的排序。当能够利用索引自动以适当的次序产生输出时,优化器就避免了排序的步骤。以下是一些影响因素:
●索引中不包括一个或几个待排序的列;
●group by或order by子句中列的次序与索引的次序不一样;
●排序的列来自不同的表。为了避免不必要的排序,就要正确地增建索引,合理地合并数据库表(尽管有时可能影响表的规范化,但相对于效率的提高是值得的)。如果排序不可避免,那么应当试图简化它,如缩小排序的列的范围等。
4、用外联接提高表连接的检索速度
在作表连接(常用于视图)时,常使用以下方法来检索数据:
SELECT PAY_NO,PROJECT_NAME
FROM A
WHERE A.PAY_NO NOT IN (SELECT PAY_
NO FROM B WHERE VALUE>=120000);
但是若表A有10000条记录,表B有10000条记录,则要用掉30分钟才能查完,主要因为NOT IN要进行一条一条的比较,共需要10000*10000次比较后,才能得到结果。该用外联接后,可以缩短到1分左右的时间:
SELECT PAY_NO,PROJECT_NAME
FROM A,B
WHERE A.PAY_NO=B.PAY_NO(+)
AND B.PAY_NO IS NULL
AND B.VALUE>=12000;
5、避免相关子检索
一个列的标签同时在主检索和where子句中的检索中出现,那么很可能当主检索中的列值改变之后,子检索必须重新检索一次。检索嵌套层次越多,效率越低,因此应当尽量避免子检索。如果子检索不可避免,那么要在子检索中过滤掉尽可能多的行。
6、使用临时表加速检索
把表的一个子集进行排序并创建临时表,有时能加速检索。它有助于避免多重排序操作,而且在其他方面还能简化优化器的工作。例如:SELECT cust.name,rcvbles.balance,……other columns FROM cust,rcvbles WHERE cust.customer_id=rcvlbes.customer_id AND rcvblls.balance>0 AND cust.postcode>“98000”ORDER BY cust.name如果这个检索要被执行多次而不止一次,可以把所有未付款的客户找出来放在一个临时文件中,并按客户的名字进行排序:SELECT cust.name,rcvbles.balance,……other columns FROM cust,rcvbles WHERE cust.customer_id=rcvlbes.customer_id AND rcvblls.balance>0 ORDER BY cust.name INTO TEMP cust_with_balance然后以下面的方式在临时表中检索:SELECT*FROM cust_with_balance WHERE postcode>“98000”临时表中的行要比主表中的行少,而且物理顺序就是所要求的顺序,减少了磁盘I/O,所以检索工作量可以得到大幅减少。注意:临时表创建后不会反映主表的修改。在主表中数据频繁修改的情况下,注意不要丢失数据。
7、避免相关子检索
一个列的标签同时在主检索和where子句中的检索中出现,那么很可能当主检索中的列值改变之后,子检索必须重新检索一次。检索嵌套层次越多,效率越低,因此应当尽量避免子检索。如果子检索不可避免,那么要在子检索中过滤掉尽可能多的行。
8、用排序来取代非顺序存取
非顺序磁盘存取是最慢的操作,表现在磁盘存取臂的来回移动。SQL语句隐藏了这一情况,使得我们在写应用程序时很容易写出要求存取大量非顺序页的检索。有些时候,用数据库的排序能力来替代非顺序的存取能改进检索。
四、结束语
数据库的检索在数据库的操作中占了相当大的比重,提高数据库的效率,主要在于提高数据库的检索。通过以上对数据库检索优化的探讨,我们知道对于数据库的检索优化重点就在于SQL语句的执行效率,因此抓住了这个重点,有针对性得进行进一步的实践,反复比较就可以得出最优的方案。
参 考 文 献
[1]科里M.J.著,张福德 译Oracle数据库性能优化技术,学苑出版社,1994
[2]黄开枝,Oracle9i数据库性能调整与优化,清华大学出版社,2005年6月
[3]唐汉明,翟振兴,兰丽华,关宝军,申宝柱,人民邮电出版社,2008年4月
[4]李俊民,精通SQL—结构化检索语言详解(第2版),人民邮电出版社,2008年8月■