Oracle Sql 语句优化技巧总结一

1.  共享SQL

 

说明：为了不重复解析相同的SQL语句,在第一次解析之后,ORACLE将SQL语句存放在内存中.   这块位于系统全局区域SGA(system global area)的共享池(shared buffer pool)中的内存可以被所有的数据库用户共享. 因此,当你执行一个SQL语句(有时被称为一个游标)时,如果它和之前的执行过的语句完全相同, ORACLE就能很快获得已经被解析的语句以及最好的执行路径. ORACLE的这个功能大大地提高了SQL的执行性能并节省了内存的使用. 可惜的是ORACLE只对简单的表提供高速缓冲(cache buffering),这个功能并不适用于多表连接查询.数据库管理员必须在init.ora中为这个区域设置合适的参数,当这个内存区域越大,就可以保留更多的语句,当然被共享的可能性也就越大了. 当你向ORACLE提交一个SQL语句,ORACLE会首先在这块内存中查找相同的语句.       /*这里需要注明的是,ORACLE对两者采取的是一种严格匹配,要达成共享,SQL语句必须完全相同(包括空格,换行等). */   --共享的语句必须满足三个条件:   A. 字符级的比较:   B. 两个语句所指的对象必须完全相同:   C. 两个SQL语句中必须使用相同的名字的绑定变量(bind variables)

2.  优化步骤总结

说明：根据查询sql的各字段、表及where条件判断的顺序来调整并进行改进。

 

1.     基表(最少数据量的表)放到from条件最后面，因为ORACLE的解析器按照从右到左的顺序   处理FROM子句中的表名。   2.表之间的连接关系写到where条件后的最前面，且一次过滤最多数据判断的条件放在最末尾   3.减少访问数据库的次数       在SQL*Plus ， SQL*Forms和Pro*C中重新设置ARRAYSIZE参数，       也可以增加每次数据库访问的检索数据量 ，建议值为200(set arraysize 200)   4.尽量使用DECODE函数来减少处理时间，使用DECODE函数可以避免   重复扫描相同记录或重复连接相同的表，尽量使用oracle内置函数来提高效率   5.TRUNCATE替代DELETE，尽量多使用commit操作，   这样可以释放回滚段上用于恢复数据的信息和被程序语句获得的锁及redo log buffer 中的占用空间;   6. EXISTS替代in,not EXISTS替代not in,EXISTS替换DISTINCT，有时还得参考执行计划而定   7.识别低效SQL语句   SELECT EXECUTIONS,DISK_READS,BUFFER_GETS,       　　       ROUND((BUFFER_GETS-DISK_READS)/BUFFER_GETS,2) Hit_radio,       　　      ROUND(DISK_READS/EXECUTIONS,2) Reads_per_run,SQL_TEXT         FROM V$SQLAREA         WHERE EXECUTIONS>0       　　     AND BUFFER_GETS > 0       　　     AND  (BUFFER_GETS-DISK_READS)/BUFFER_GETS < 0.8       　　 ORDER BY 4 DESC;   8.使用EXPLAIN PLAN 分析SQL语句来分析刚填写好的SQL执行计划，pl/sql和toad自带   9. SELECT子句中避免使用'*'字符，ORACLE在解析的过程中，   会将'*' 依次转换成所有的列名，这个工作是通过查询数据字典完成的， 这意味着将耗费更多的时间。   10. 删除重复记录最高效的方法   DELETE FROM EMP E   WHERE E.ROWID > (SELECT MIN(X.ROWID)                               FROM EMP X                               WHERE X.EMP_NO = E.EMP_NO                                and .......);   11. 使用表的别名(Alias)，当在SQL语句中连接多个表时，请使用表的别名并把别名前缀于每个Column上，   这样可以减少解析的时间并减少那些由Column歧义引起的语法错误   12.对存在索引表而且频繁增、修改和删除操作会导致20%左右的碎片产生，   应该时常重建该索引来减少索引碎片的增多，只需要alter index  <INDEXNAME> rebuild一下   13.sql语句用大写，因为oracle总是先解析sql语句，把小写的字母转换成大写后再执行   14.避免在索引列上使用计算和not(则会全表索引),用>=替代>，其他的该性质判断一样，   避免在索引列上使用IS NULL和IS NOT NULL，如果索引使用该关键字则会导致索引失效　   15.使用提示(Hints)来获取数据(ORACLE hints注释)，来提高查询语句的数度，但得注意使用规则。   16.避免改变索引列的类型,索引尽量避免!=,||,+,nvl,like 一起使用，(建议建立基于函数的索引)   17.避免使用耗费资源的操作(带有DISTINCT，UNION，MINUS，INTERSECT，ORDER  BY的SQL语句   会启动SQL引擎执行耗费资源的排序(SORT)功能.DISTINCT需要一次排序操作,而其他的至少需要   执行两次排序)   18.  如果索引列里面有NULL值，Optimizer将无法优化   19. 提高GROUP  BY 语句的效率， 通过将不需要的记录在GROUP  BY 之前过滤掉   20. 对于truncate删除表的操作，最好采用下面的方式进行。 ·  truncate table ....   ·  drop index.....   ·  insert data .....   ·  create index ...   ·  analyze table table_name compute statistics;

3.  优化技巧参考

SQL语句的优化的技巧很多，下面是一些常用的技巧列表，一般对于专门处理优化规则

必须熟悉下面的参考信息，而对于开发人员可以了解一下就可以了。

 

SQL优化技巧

 

注释

 

以更快的工作完成系统的工作

 

选择行数最少为目标，可以使SQL解释和操作更少。

 

分析连接的层次

 

逐一对连接进行分析，搞清具体的使用。

 

检查基础视图

 

如果用户访问了视图或与视图进行过连接，则应对视图进行彻底的检查，看视图是否还需要优化。

 

对于小表，可以进行全表扫描

 

通过全表扫描，可以使问题解决更加清楚。对于小表或可选择性差的索引，全表扫描更有效。

 

逐条检查执行规划

 

索引访问和嵌套连接（NL）可能并不是最优。对于特地的连接类型，查询不要返回太多的行。

 

对于运行时间较长的查询，执行下面数学运算：如：   l  某个运算希望3分钟完成；   l  查询需要将表so_lines和表so_headers连接起来。

 

验证下列条件：   l  so_headers 的选择性为5%;   l  so_lines 的选择性为15%;   l  so_headers = 1GB,so_lines=25GB   l  数据工作集（结果集）=3.04GB   l  所需吞感吐量=22MB/每秒   可看出，速度取决于系统的配置，要求3分钟查出结果的期望太高了。

 

监视磁盘读和缓冲区的获取操作连接

 

使用SET autotrace  on stat来设置 磁盘读和缓冲区获取的自动统计显示，如果这两值较高，表示查询的开销较大，则查询语句需要进行优化

 

连接：   l  检查外连接   l  以子查询替代连接

 

l  如果通过索引可以得到行的话，就要避免全表扫描；   l  如果你能使用的索引取到100行的话，就避免使用取到 10,000 行的索引；   l  选择连接要注意选择连接较少行的表

 

选择EXISTS 或 IN

 

根据执行计划后的显示来进行择优选择

 

判定式失败

 

在查询语句中，如果列包含有许多个绑定变量时，就可能发生判定式崩溃。例如，对某列使用col=DECODE(:b1, ‘ ‘, :b3, col ) 判定时，就表示：如果绑定变量b1为null，则用绑定变量b3代替。否则，表达式就采用 col = col 。 这样的语句将会阻止系统优化程序使用 col列的索引。当然也就产生全表扫描，从而影响性能