浅谈油田oracle数据库开发中的SQL优化

2008

前言
几年来我厂针对油气田管理先后开发了地质应用软件、矿 级日报、月报系统、天然气管理系统等一些实际生产应用软件。 目前我厂大型数据信息统计、生产动态分析、措施效果追踪等 难度较大的工作大部分都是利用开发数据库中的数据信息, 这就 涉及到如何更好的管理和有效的利用数据库中的数据来提高我 们的工作效率。从大多数系统的应用实例来看,随着数据库中数 据的增加,系统的响应速度就成为系统需要解决的最主要的问题 之一。系统优化中一个很重要的方面就是SQL 语句的优化。据 统计,约有90 %的性能问题是由于程序员或用户使用了不恰当的 查询语句造成的,因此SQL 语句的质量对整个系统效率有重大关 系。对于海量数据来说,劣质SQL 语句和优质SQL 语句之间的 速度差别可以达到上百倍,甚至更多。
2.2.1 我们经常会遇到的是SQL 语句中根本没有 WHERE 条件子句。这样ORACLE系统在执行这条 SQL语句时就会整表扫描,大大降低查询效率。
2008

2.2.2 SQL 语句的条件中使用NULL 、IS NOT NULL或是不 相等的条件,将使索引失效,系统也会全表扫描,降低效率,如:
2008

2.2.3索引字段被包含在了表达式中,使索引失效,例如: select max(distinct ny) from dba05;
select * from daa01 where upper(qkdy) = 'PTHBB';
update dba05 set skjd= skjd*100 where jh='7P70-66’;
SELECT JH from daa01 不能共享的原因:大小写不同
SELECT jh FROM DAA01 不能共享的原因:空格数不一致
2008

②两条语句必须使用相同的名字的绑定变量。 SELECT jh ,kgrq from DAA02 where kzrq = :rq.year该语句不能与下面的语句共享: SELECT jh ,kgrq from people where kzrq = :rq.value 原因:绑定变量变化 SELECT jh ,kgrq from people where kzrq = ’2008’ 原因:绑定变量换成了常量
2008

2、SQL语句的优化方法
2.1共享SQL语句
根据ORACEL 中SQL 语句的执行过程可知,如果能够共享 共享池中的SQL语句将是比较优化的选择。要共享SQL语句需 满足以下两个条件。
①当前被执行的语句和共享池中的语句必须完全相同,例如:
SELECT JH FROM DAA01 只能与 SELECT JH FROM DAA01 共享, 即需要完全一致, 下面的SQL 语句就不能与之实现共享:
此外,SQL 的性能优化是一个复杂的过程,以上着重从 实现的角度讨论了SQL 语句优化,实际上要想根本解决查询 优化问题还会涉及数据库的结构设计与资源配置、网络的 流量控制以及操作系统的总体设计等等。
谢谢

2008 1、ORACLE 中SQL 语句的执行过程

如何知道一个SQL 语句的优劣,首先要了解一下 ORACLE 中SQL 语句的执行过程。
一个SQL 语句在ORACEL 中执行大概分为以下6 个过程:
①计算语句值； ②共享池中有无与此语句字符完全匹配的语句； ③准备要运行的SQL 语句； ④为新语句在共享池中创建空间； ⑤将语句存放在共享池中； ⑥执行准备好的SQL 语句。
由此可以看出,在使用动态查询时,尽量使用一个 绑定变量, 然后具体赋值, 这样可以使用共享SQL ,减 少编译次数,加快SQL 的执行速度。
2008

2.2 正确的使用索引
索引可以加快查询的速度,是提高SQL 语句执行速 度的较好方法。但如果SQL 语句编写不当,索引将起不 到任何作用,反而会影响系统的性能。引起索引失效有 以下几种方式:
类似这样的语句,应该将WHERE 子句中的左侧只 写索引字段,而在等号右侧使用表达式,这样就可以利用 索引,达到优化的目的。
2008

2.2.4使用LIKE 条件, %放在值之前,使索引失效,例如:
select * from daa01 where jh like '%7P10%';
SELECT jb FROM daa01 WHERE bz IS NULL; SELECT jb FROM daa01 WHERE mqjb !=’31’;
以上第一句是因为NULL值并没有被定义。在SQL语句 中使用NULL会有很多的麻烦。因此建议开发人员在建表时， 把需要索引的列设成NOT NULL。如果被设置为索引的列 在某些行中存在NULL值，就不会使用这个索引。
2008
2.2.7比较不匹配的数据类型

比较不匹配的数据类型也是比较难于发现的性能问题之一。
注意下面查询的例子，假设xqkdm是一个VARCHAR2类型的 字段，并且在该字段上有索引。
下面的语句将执行全表扫描。
select jh,cyjh,cw,mqjb
from daa01 where xqkdm=5101; Oracle可以自动把where子句变成
如果将字符串值改为: '7P10%'将不会使索引失效, 达到优化的效果。
2008

2.2.5 SELECT子句中尽量避免使用数字
例如: select 1,jh fromdaa01;
因为ORACLE系统在SQL解析过程中,将数 字转换为所有列名,这个过程需要通过查询数据字 典完成,耗费很多时间。应该在子句中直接将所有 的字段列名写出,这样就是比较优化的做法。
浅谈油田oracle数据库开发中的SQL优化
2008

摘要
大庆油田作为一个大型企业正在向信息化 产业化方向发展, Oracle 数据库的建设和应用 在油田勘探开发的效果评价、效果分析等各个 方面具有重大意义, 尤其是数据库的后台处理非 常重要, 它关系到前台客户对数据库应用的质量 和速度, 特别是以数据库、网络为基础的Web 应用都需要后台数据库的大力支持。本文着重 从实现的角度讨论了我厂大型数据库管理系统 ORACLE 中SQL 语句优化语句时,ORACEL 先将其解析然后将该语句存放在全局区域的 共享池中,并可以被所有的数据库用户共享。 当再次执行一个SQL 语句时,如果它和之前执 行过的语句完全相同, ORACEL 将直接从共 享池中获得被解析的语句。
①计算语句值； ②共享池中有无与此语句字符完全匹配的语句； ③准备要运行的SQL 语句； ④为新语句在共享池中创建空间； ⑤将语句存放在共享池中； ⑥执行准备好的SQL 语句。
2008
结束语

从以上讨论看出同一查询的不同SQL 语句写法会给查 询性能带来显著的差异。以上提到的是一些在软件开发和 数据库管理中最基本的提高SQL语句效率的方法,其中的关 键就是合理的建立并充分利用索引,但是在更多的情况下,往 往需要反复试验比较不同的语句以得到最佳方案。索引使 查询速度得到了很大的提升,但同时索引也占用了额外的硬 盘空间(当然现在一般硬盘空间不成问题),而且往表中插入 新记录时索引也要随着更新，这也需要一定时间。有些表 如果经常insert,而较少select,就不用加索引了。不然每次写 入数据都要重新改写索引,花费时间。
to_number(xqkdm)=5101， 这样就限制了索引的使用,改成以下查询就可以使用索引:
select jh,cyjh,cw,mqjb
from daa01 where xqkdm=’5101’; 特别注意：不匹配的数据类型之间比较会让Oracle自动限 制索引的使用，即便对这个查询执行Explain Plan也不能让您 明白为什么做了一次“全表扫描”。
2008

2.2.6 用where子句替代having 字句
避 免 使 用 HAVING 子 句 ， HAVING 只 会 在 检 索 出所有记录之后才对结果集进行过滤。这个处 理需要排序,总计等操作。如果能通过WHERE子 句限制记录的数目，那就能减少这方面的开销， 因此速度较快。