【www.chuban323.com--理学论文】
摘要:Oracle9i数据库的任务是存储和管理数据，而我们用户能看到的有关数据库特性就是它的性能，数据库的查询操作是影响一个应用系统响应时间的关键因素。科学合理地构造查询系统，是成功开发数据库应用系统非常重要的环节。本文主要介绍Oracle9i的查询优化程序及其组成，从基于索引、SQL优化、以及其它措施各个方面阐述了ORACLE 数据库查询效率提高的经验和方法。关键词: ORACLE数据库；SQL；数据库索引；查询优化
Query Optimization Method of ORACLE9i Database
Abstract: The basic tasks of a database are data storage and management，and the only characteristic of the database that terminal users can see is its capability, a database enquiry operation is the impact of the key factors of applications response time. Thus，the query system in a logic and reasonable way is the key to develop database application system successfully．So construct the query system reasonably is a very important link of develop the database application system successfully．This paper has introduced the experiences and methods of raising the query efficiency of ORACLE database from the following four aspects：hit ratio improving，large tables query optimizing，and tuning of SQL statement．www.lunwenwang.com 论文网在线
Key words: ORACLE database；SQL；Index； query optimization
引言：
1 ORACLE9i数据库常见的资源瓶颈类型
ORACLE9i数据库运行时，常见的瓶颈现象有以下几种类型:
内存瓶颈：当ORACLE9i进程需要更大的内存空间超过系统可提供时。
磁盘I／0瓶颈：由于内存缓冲区的空间不足，或者表空间及其文件在硬件上的分配不合理而导致进程对磁盘的频繁I／0读写。
CPU瓶颈：尽管多数情况下，都是由操作系统的内核来管理分配有效的CPU给ORACLE9i数据库进程使用，但是，仍然会出现过多的应用进程对CPU使用周期激烈竞争的现象。
ORACLE9i资源瓶颈。
对以上各种资源瓶颈，通常可以从多个方面进行调整，但是，当对ORACLE9i数据库某一方面进行调整后，必然会影响到其它方面的性能。所以，对ORACLE服务器进行性能优化调整，应该采取一种综合优化调整策略，本文重点阐述对数据库查询优化方法。
2 针对SGA 内存区的调整
首先进行Oracle实例调整。Oracle实例涉及到SGA 内存区和一组Oracle后台处理进程。对Oracle实例的调整就是对SGA 内存区和Oracle后台处理进程的调整。在对该问题的解决中，主要是针对SGA 内存区的调整。
2．1 SGA 内存区结构
SGA就是系统全局区，是指内存中允许多个进程相互通信的区域。在Oracle中，SGA对所有进程来说都是全局的可用的。图1为SGA结构图。缓冲区高速缓存是SGA 中为所有用户和系统进程保存数据的区域，任何数据在传递给一个调用的应用程序之前，都必须驻留在这片高速缓存中。这片区域是共享的，所以多个进程可以从这片高速缓存读取同样的数据块，而不必每次都从物理磁盘中读取。共享池是SGA 中的另一个区域，其中保存着关于待执行的SQL语句的信息。他由两部分组成：数据字典高速缓存，存放从数据字典中读取的信息以用于处理SQL请求；库高速缓存，存放需要执行的SQL语句信息，包括每个SQL语句的语法分析树和执行计划。如果多个用户要执行同样的SQL语句，那么语法分析树和执行计划就可以重复利用，省去了语法分析步骤的昂贵花费。
2.2 调整SGA 结构
一般来讲，在系统硬件支持的情况下，系统全局区越大越有利于数据库高效的运行。大的缓冲区高速缓存可以缓存更多的数据块，这样可以提高缓存命中率，节省物理磁盘读取的高昂代价；大的共享池意味着大的库高速缓存。库缓存的内存结构如图2所示。图2 库缓存的内存结构
库缓存越大，可以保存的SQL语法分析信息越多；此外，数据库中的一些对象，如表、索引、过程、触发器、软件包等也在首次执行后进驻库高速缓存。大的库缓存可以保证对这些对象的高命中率，从而节省解析和载入代价。作为一个通用的优化原则，我们在解决该问题时，适当增大了SGA 的容量，从而保证Oracle实例可以比较高效的运行。设置缓冲区高速缓存的容量为32 M ，设置共享池的大小为56M。运行rpt_lib.sql程序检查库高速缓存不足率(还没有运行该脚本)，表明库缓存足够。
2.3 Oracle数据库的物理结构和逻辑结构
Oracle数据库的物理结构从操作系统一级查看，就是由一个个的文件组成，从物理上可划分为：数据文件、日志文件、控制文件和参数文件。数据文件中存放了所有的数据信息；日志文件存放数据库运行期间产生的日志信息，它被重复覆盖使用，若不采用归档方式的话，已被覆盖的日志信息将无法恢复；控制文件记录了整个数据库的关键结构信息，它若被破坏，整个数据库将无法工作和恢复；参数文件中设置了很多Oracle数据库的配置参数，当数据库启动时，会读取这些信息。Oracle数据库的逻辑结构则是由一些数据库对象组成，这也是数据库管理员和应用开发人员经常用到和查看的逻辑对象，如：数据库表空问、表、索引、段、视图、存储过程、触发器等。数据库的逻辑存储结构(表空间等)决定 7数据库的物理空问是如髓使用的，其关系示意图如图3所示
3 查询优化的策略
3.1基于索引的优化
索引是数据库中一个常用而重要的数据库对象，而优化查询重要的方法是建立索引，在关系数据库系统的表上建立合适的索引，可以避免表扫描并减少因查询而造成的I／O开销，提高数据库数据查询的速度，改善数据库性能。但是创建索引会增加系统的时间和空间的开销。因此创建索引时必须要与实现应用系统的查询需求密切结合，才能达到优化查询的目的。 3.1.1建立必要的索引
判断索引必要性的最终标准则是判断这些索引是否对数据库的工作效率有所帮助。在实际应用的过程中，应该为优化工作做以下几点准备：
首先必须熟悉数据库应用程序中所有的SQL语句，并从中统计出常用且可能对性能有影响的部分语句；然后分析、归纳出作为Where条件子句的字段及其各种组合方式；在这一基础上可以初步判断出哪些表的哪些字段应该建立索引。其次，必须熟悉应用程序，要了解哪些表是数据操作频繁的表；哪些表经常与其他表进行连接；哪些表中的数据量可能很大；数据量大的表中各个字段的数据分布情况如何等等。对于满足上述条件的这些表，必须重点关注。因为建立在这些表上的索引，将对SQL语句的性能产生举足轻重的影响。
3.1.2 使用索引的一些规则
在大型应用开发或表较大的情况下，使用索引可以极大减少数据库读写次数，从而提高数据库访问速度，所以，索引在应用程序开发中经常被使用，但如何正确使用索引以发挥它的优势需遵循以下原则：
在主键(primly key)的索引方面，不应有超过25%的列成为主键，而只有很少的普通列，这会浪费索引空间在索引的使用效率方面，当选择数据少于全表的20%，并且表的大小超过ORACLE的5个数据块时，使用索引才会有效，否则用于索引的I/O加上用于数据的I/O就会大于做一次全表扫描的I/O.
另外，在数据分布不均匀的特殊情况下，选择性不高的索引也要建立。假设表SomeInfo中数据量很大，有一百万行，其中有一个字段Flag，取值范围为枚举值：[0，l，2，3，4，5，6，7]。按照前面所述索引建立的规则，该字段只有8种取值，索引值的重复率很高，索引选择性明显很低，通常被考虑为不必建立索引。然而，如果该字段上数据值的分布情况非常特殊，如表l所示：假定常用的查询中，查询Flag<6的情况既多又频繁，毫无疑问，如果能够建立索引，并且被应用，那么必将大大提高这种情况的查询效率。
使用索引尤其应当注意的是，在表连接操作时的驱动表／被驱动表的关系。ORACLE核心使用至底向上、从右至左的规则，如：FROM 子句中的最后一个表才是ORACLE用傲为驱动表的表；WHERE子句的最后一个条件中所台的列，它所属的表才是最先被引用的表。总之，在FROM子句中，将表名按被驱动表-驱动表排序，在WHERE子旬中，将条件语句按最少约束-最多约束排序。当指向被除行的索引所占空间超过总索引空间的20%时，就应删除并重建索引，以节省空间，提高性能。
3.1.3 让SQL语句用上合理的索引
建立必要的索引之后，并非意味着数据库性能的提高已经得到了令人满意的结果，还要针对某些具体的SQL语句进行分析：首先，确定索引是否真正得到了使用。不发挥作用的索引，正如建好的楼宇无人居住，这是对资源的浪费。不允许有闲置的索引，应竭力让那些仍没有使用索引的SQL语句发挥作用。
其次，判断索引是否利用得合理。特别是一些复杂的SQL语句，当其中Where子句包含多个带有索引的字段时，更应该注意索引的选择是否合理。错误的索引不会使数据库性能得到预期的提高，往往还会产生一些与愿望背道而驰的负面影响。下面，就如何使用合理索引的问题，引用实例进行说明
3.2 基于SQL语句的优化
由于Oracle9i是关系型数据库，SQL语句是面向结果而不是面向过程的查询语言，所以它包含一个基于开销的优化程序(Cost Based Optimizer)及一个基于规则的优化程序(Rule Based Optimizer)来对用户提交的及时查询提供一个最佳的执行策略，这个执行策略就是执行这个查询所需的一系列步骤。
3.2.1 查询优化程序中SQL语句的调整
要对查询语句进行优化，一个简单直接有效的方法是对SQL语句进行调整，减少计算量，提高查询的响应速度。对SQL语句进行调整时要遵循下列原则：
首先，选择运算尽可能先做，并在对同一个表进行多个选择运算时，选择影响较大的语句放在前面。
其次，在执行连接前对关系作适当的预处理，预处理的方法有两种，在连接属性上建立索引和对关系进行排序。
最后，应避免相关子查询。SQL概念上将位于WHERE子旬中的相关子查询处理成获取参数并且返回—个单独的值或值的集合(也可能为空集)的函数。它是按以下方式执行的：首先计算位于外层查询的FORM子句中关系的笛卡尔积，然后对该笛卡尔积的每个元组用位于WHERE子句中的谓词进行测试。这种执行方式技术上称为相关执行，相关执行方式效率不高，因为子查询要对应位于外层查询的每一个元组进行单独的计算，从而导致大量的随机磁盘I／O操作。所以在实际应用中，若可以用连接查询代替的子查询，则用连接查询实现；例如，有以下相关子查询语句：
SELECT sname
FROM student
WHERE exists(
SELECT *
FROM student_grade
WHERE score>90 and student.sno =student_grade.sno)
用连接查询实现以上子查询如下：
SELECT sname
FROM student，student_grade
WHERE score>90 AND student.sno =student_grade.sno)
如果子查询不可避免，那么要在子查询中过滤掉尽可能多的行。
3.2.2书写SQL的一些经验
(1)不要在索引的字段上面进行任何操作(包括函数操作)，例如SELECT *FROM EMP WHERE SAL*12>10000；要写成SELECT * FROM EMP WHERE SAL>10000／12：(2)尽量使用UNION替换掉OR的操作，如果可能，使用UNI0N ALL
(3)如果能用连接操作处理的语句，最好不要使用嵌套子查询
(4)如果返回的结果集超过表记录的40%以上，最好使用全表扫描，而不要使用索引，因为使用索引除了通过ROWID对表进行操作外，还增加了对索引的操作。
(5)如果不打算使用某个索引。对于字符类型的字段A(有索引)，可以用AII代替，对于数字类型子段B(有索引)，使用B+0就可以使基于开销的优化程序(CBO)不使用索引。
(6)尽量不要在含有索引的字段里面添加null，因为对null的判断将不使用索引。
(7)使用复合索引时要注意索引列在WHERE 中的位置，只有当复合索引中的第一列出现在WHERE 中时，才会成功的使用该复合索引。3.3其它优化手段
3.3.1使用存储过程
存储过程是SQL语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理，创建后便可转换为可执行代码，作为数据库的一个对象存储在数据库中，存储过程的代码驻留在服务器端，因而执行时不需要将应用程序代码向服务器端传送，可以大大减轻网络负载。同时，由于存储过程已编译为可执行代码，不需要每次执行时进行分析和优化工作，只需要从高速缓冲存储器中调用存储过程已编译好的二进制代码来执行，从而减少了预处理所花费的时间，提高了系统的效率。另一方面，使用存储过程还易于维护，且表的结构改变时，不影响客户端的应用程序。
3.3.2避免相关子查询
如果一个列的属性名同时在主查询和where子句查询中出现，那么很可能当主查询中的列值改变之后，子查询必须重新查询一次。查询嵌套层次越深，效率越低，因此，应尽量避免子查询，如果不可避免，那么要在子查询中过滤掉尽可能多的行。
3.3.3避免或简化排序
应当简化或避免对大型表中的数据进行重复排序，如果利用索引能以适当的次序自动产生输出时，优化器就能避免这种排序。以下是一些主要的影响因素：
(1)索引中不包括一个或几个待排序的列。
(2)group by或order by子句中列的次序与索引的次序不一样。
(3)排序的列来自不同的表。
为了避免不必要的排序，应正确增建索引，合理合并数据库表，在必要时对表进行反规范化处理。如果排序不可避免，那么应当试图简化它，如缩小排序的列的范围等。
4 结束语
数据库应用的性能调整是一个不断摸索、总结的过程，涉及的方面很多。优化数据库整体的应用性能是提高计算机系统处理速度的一种行之有效的办法，本文从索引优化，SQL语句以及oracle10g的新特性的优化等方面的优化进行讨论，结果表明优化后的数据库性能有所提高，但在实际应用中要根据系统的实际情况具体分析，对优化的方法反复实验，最后再确定最终的优化方法，这样才能达到较好的优化效果。实现快速、高效的数据查询和应用分析，同时也使硬件资源得到最充分的发挥。在实践中必须分析影响性能的各方面因素，针对不同的具体情况选择合理的优化措施；同时还需要不断了解Oracle提供的新技术并加以合理利用，从而更好地进行数据库的调优。
参考文献：
[l](美)Bulusu Lakshman.Oracle 9i PL／SQL开发人员指南.清华大学出版社.2004.
[2](美)Kevin loney Marlene Theriault.Oraele9iDBA手册.机械工业出版社.2002.
[3]Oracle91.6管理员指南.北京希望电子出版社.2003年1月.
[4]藤永昌.Oracle 9I数据库管理员大全[M].北京:清华大学出版社.2004.
[5]Oracle 网站.Oracle 10G技术文档[EB/OE].http://www.oracle.com/technology/documentation/ database10g.html.2005.基于oracle的sql优化如何