浅谈Oracle数据库SQL优化

浅谈Oracle数据库SQL优化

何磊

浅谈Oracle数据库SQL优化

何磊

(中国移动有限公司贵州分公司贵州贵阳550004)

【摘要】Oracle数据库是当前应用最广泛的大型数据库之一，其性能优化直接关系到系统的运行效率。本文主要阐述了如何通过SQL调整来实现Oracle数据库性能的优化。

【关键词】Oracle数据库;SQL;性能优化

【中图分类号】TP311.138【文献标识码】A【文章编号】1009-9646(2009)04-0036-021

引言

随着Oracle数据库在各种大型商业应用系统中的广泛使用，Oracle数据库优化在系统实际应用中发挥着越来越重要的作用，数据库优化已成为数据库维护过程中的重要内容。

Oracle数据库可以从以下几个方面来进行优化。首先，在数据库设计阶段对其逻辑结构和物理结构进行优化设计，使之在满足需求条件的情况下，使系统性能达到最佳，系统开销达到最小，为下一步的调整创造良好的先决条件。然后可在此基础上进行网络调整、磁盘调整、实例调整、对象调整，最后进行SQL调整，最终实现数据库系统整体优化。本文主要从SQL调整分析如何实现对数据库的优化。

2OracleSQL调整的目标

2.1去掉不必要的大表全表扫描。一般情况下，可以通过添加索引的方法来去掉不必要的全表扫描。

2.2缓存小表全表扫描。如果全表扫描是最好的访问方式，可以通过将小表放入KEEP池中的方法将其缓存，以提高访问速度。

2.3检验优化索引的使用。检查每一个索引，确保Oracle使用正确的索引。

2.4检验优化的连接方法。

3OracleSQL调整的过程

3.1定位严重影响系统性能的SQL语句。从V$sqlarea视图和stats$sql_summary表中获得SQL语句的列表，这些列表可以根据以下参数进行排序。

3.1.1executions，定位执行频繁的查询。

3.1.2disk_reads，定位高磁盘读取量的查询。

3.1.3rows_processed，定位处理大量记录的查询。

3.1.4buffer_gets，定位高缓存读取量的查询。

3.1.5sorts，定位由排序造成速度下降的查询。

根据以上参数就可以确定具有高影响力的SQL语句。

3.2抽取和解释SQL语句。在指定SQL语句后，需要对它进行“解释”以确定现存的执行计划，并对它进行调整来确定这个执行计划是否进行改进。

3.3调整SQL语句。通过以上的步骤，接下来就可以使用以下方法对拥有非优化执行计划的SQL语句进行调整。

3.3.1提示。通过添加提示修改SQL执行计划，并代替使用存储框架的方法使执行计划变更持久化；Oracle提示可分为：优化器提示、表连接提示、表反连接提示、索引提示、并行提示、表访问提示、子查询中的提示。数据库管理员可根据具体情况使用以上提示，以达到SQL调整的目的。

3.3.2通过添加索引去掉全表扫描。不必要的全表扫描会造成大量不必要的输入输出，而且还会拖垮整个数据库。所以，首先我们应预防不必要的全表扫描出现。一般以下情况会导致不必要的全表扫描,在SQL调整过程中应注意：

A、表的最高峰值过高。如果一个表中删除了大量的纪录，最高峰值仍然保持在初始的位置，这将给CBO（基于成本的优化器）带来有关表大小的错误信息。所以应找到这些表并将最高峰值调低到适当的值。

B、不存在字段矩形图的不平衡索引分布。

C、使用倾向于全表扫描的CHOOSE或ALL_ROWS优化器模式。

D、将表设置为允许并行查询，这通常会导致CBO错误地

E、使用NULL条件的查询；可以考虑将NULL转换为其他特殊字符以避免全表扫描；下面的例子会调用全表扫描：

Select*fromabcwhereuser_nameisNULL。

可以通过将NULL值转换成直接量“N/A”，以查询“N/A”值的方式避免全表扫描。修改后：

updateabcsetuser_name=“N/A”whereuser_nameisNULL;

select*fromabcwhereuser_name=“N/A”;

F、对没有索引的字段查询；

G、带有LIKE条件的查询。在LIKE掩码的开始端使用“%”，那么这个查询将调用全表扫描。但LIKE掩码是由字符开始，该查询就可以使用现存的索引；例如，下面的语句将导致全表扫描：

Select*fromabcwhereuser_namelike“%lei”;

而语句Select*fromabcwhereuser_namelike“h%”可避免全表扫描。如果我们无法避免以“%”开始后跟字符的like掩码查询，可以使用reverse内置函数来创建基于函数的索引：

createindexuser_name_reverse_idxonabc(reverse(user_name));

下面使用reverse操作符查询语句做些改动：

select*fromabcwherereverse(user_name)like“iel%”;

这样后缀like操作符可以使用基于函数的索引。

H、带有NOTEQUALS条件的查询；

I、内置函数使索引无效。当WHERE子句中的一个索引字段被替换为一个内置函数时，原来的索引不再能够使用。例如：select*fromsowhereto_char(sts_date,“YYYY-MM”)=“2005-01”；

日期索引将不能使用。如果某函数经常用于某字段，可以在基础表中创建基于函数的索引，以避免使用全表扫描。基于函数的索引通常适用于TO_CHAR、SUBSTR、DECODE。

J、使用ALL_ROWS提示。ALL_ROWS优化器可以提高吞吐量，但它倾向于使用全表扫描；

K、使用并行提示。由于Oracle并行查询必须执行全表扫描来服务于多查询从动进程，所以在查询中使用并行提示通常会导致CBO调用全表扫描。我们在SQL查询中添加并行提示时要进行测试以证明并行查询确实比索引执行速度更快。

其次，如果在有序表中查询返回的记录数少于表记录数的40%，或者是在无序表中返回的记录数少于表记录数的7%，那么这个查询可以调整为使用索引来替代全表扫描。但在此之前首先要比较索引扫描的输入输出成本和全表扫描的输入输出成本，同时把多块读取以及可能的并行执行计算在内，然后决定是否去掉全表扫描。

最后，建立索引，实现优化目的。

3.3.3重新书写SQL语句；重新书写SQL语句可以改变表访问方式，以达到或得期望的执行计划。在RBO（基于规则的优化器）中，可以通过改变FORM子句中表的顺序来改变驱动表，或是改变WHERE子句中表达式出现的顺序来改变表访问方式。由于可以使用多种方法来重新书写查询，所以最普遍的SQL调整方法就是重新书写SQL源代码。但是我们在重新书写SQL语句以提高性能的时候，必须要确认重新书写的查询结果与原查询结果一致。例如：假定user_name字段不是唯一的。

Selectcount(*)fromabc

whereuser_namein(selectuser_namefromdef);

查询的输出结果：

count(*)

-------

2

为了避免进行了全表扫描，将子查询的表移入from子句，然后再where子句中添加条件，具体如下：

selectcount(*)fromabc,def

whereabc.user_name=def.user_name;

如果user_name在abc或def中不是唯一的，那么重新书写使用标准连接来代替非关联子查询将会导致重新书写的查询语句与原查询语句不一致。

查询的输出结果：

count(*)

-------

8

可见在没有保证唯一性的情况下，会有多条记录同外层查询的单条记录相连接，因而产生笛卡尔乘积，导致输出结果不正确。

3.3.4为查询中WHERE子句中提及的所有低基数字段添加位图索引。由于位图索引中进行的检索是在RAM中完成的，所以位图索引可以从本质上提高使用了小于1000个唯一数据值的数据列的查询速度。

3.3.5在PL/SQL中重新书写SQL。在PL/SQL中书写SQL，SQL将被替换为对PL/SQL软件包的调用，其中这个软件包中包含执行查询所需的存储过程。

3.4通过修改SQL源代码实现语句执行计划持久性。

4结束语

通过以上分析我们简要的了解了SQL调整实现Oracle数据库优化的过程及主要方法,但这仅仅是开始，因为Oracle数据库优化是一个相当艰巨而又系统的工程，每一个环节都相当复杂，不能仅仅根据一个时间点的情况就断定数据库运行性能的好与坏。对于广大的数据库管理员，只有经过不断的学习和在大量实践工作中不断地积累经验，才能实现对数据库的调优。参考文献

［1］(美)DonaldK.Burleson著，刘砚、黄春等译.Oracle高性能SQL调整［M］.北京：-机械工业出版社,2005

［2］(美)朗尼著李晓军等译.Oracle数据库管理员手册［M］.北京：-机械工业出版社,2000

［3］(美)MarleneTheriaultRachelCarmichaelJamesViscusi著乐嘉锦、王兰成译.Oracle数据库管理员基础教程［M］.北京：-机械工业出版社,2000

［4］(美)克瑞等著刘晓霞等译.Oracle优化技术［M］.北京：-机械工业出版社,1998