oracle优化方法论总述

ORACLE-sql-优化方法论  概述

第一部分

oracle sql优化的本质是基于对cbo和执行计划的深刻理解

三种方法总述

• 降低目标SQL语句的资源消耗

• 并行执行目标SQL语句

并行执行目标sql，即以额外的资源换取执行时间的缩短。

• 平衡系统的资源消耗

避免不必要的资源争用，比如合理调整报表、批处理等的执行时间，避免和业务发生争用。

第一种方法总述--------目标SQL语句的资源消耗

大体思路：

1. 改写sql，降低目标sql的资源消耗；----最常用的手段

2. 不改写sql，但通过调整执行计划或者调整相关表的数据来降低目标sql的资源消耗。

通常但也不绝对：

走索引的嵌套循环连接的执行效率 < 不走索引的全表扫描的哈希hash连接的执行效率

基于索引的优化的4种方式

方式一： 用合适的索引避免不必要的全表扫描----针对全表扫描和null等情况

举例：select * from table1 where id is null 

即使我们对id列创建索引，也不会走索引的。

对于普通的单键值B树索引而言，NULL值不入索引。

但是，对于复合索引，NULL值是入索引的。

处理：create index ix_table_id on table1 (id ,0) tablepace tablespacename;

方式二： 用合适的索引来避免不必要的排序----针对order by

排序操作通常需要扫描目标表的所有数据，所以耗费的资源随目标表的数据量递增而递增。

但是，索引是有序的。索引里的索引键值已经排好序了。

实战：

根据实际的执行计划，添加普通索引就好。

方式三： 函数索引，使无法走索引的变成走索引

例如：select * from table1 where name like "%abc";

这种%在前面的like  是没办法走索引的。

如果%变到后面，就可以走索引了，那么我们有什么办法把%移到后面还不改变原SQL。

方式：使用函数，reverse。把输入的内容顺序颠倒一下后输出。

举例：

create index ix_table_column on table( reverse(column) ) ;

方式四： 重新设计索引，避免不必要的全表扫描

嵌套循环连接和hash连接的两种方式。CBO在评估这两种表连接方式时，会选择其中的成本值最小的表连接方式。

使用分区表要注意，如果sql的where条件中没有限制分区的话，那么oracle即使能使用局部分区索引，也必须扫描完所有的分区才行。

另外，索引的成本，近似的理解成2。所以，如果50个分区的话，那么成本就是2*50=100；

那么，假设被驱动表返回的行数是1000行的话，那么嵌套循环连接的成本最小就是1000*100=100,000。

哈希连接的成本，进行比较，从而选择成本相对较低的执行计划。

因此，针对实际返回的情况，比如，我们实际返回的行数比较少的情况，实际是可用走嵌套循环的。那么上述的情况，只要确定分区范围，或者将索引设计成全局索引。都可以解决。

前提：分区表，不存在truncate表分区或者drop表分区的常规行为的话，也就不存在oracle里分区表普遍会面临的全局索引的维护问题。那么就可以将索引设计成全局索引。

sql优化的具体步骤

步骤一： 找出执行时间最长、资源消耗最多的top sql

根据awr和statspack报告，能比较清晰的定位问题；

步骤二： 查看top sql的执行计划，结合资源消耗情况和相关统计信息、trace文件来分析其执行计划是否合理

1. 如果还在shared pool中，可以使用display_crusor_9i.sql 和存储过程printsql来看真实的执行计划和资源消耗；

2. 如果从shared pool中age out了，那么，可以根据awr—sql报告（或者 dbms_xplan.display_awr 只能看到目标sql的历史执行计划）。或者statspack sql报告。来得到其历史执行计划和资源消耗。

3. 通过脚本sosi.txt 来得到上述 top sql中相关对象的统计信息来辅助诊断；

4. 通过开启10046/10053等事件，诊断分析。

综合上述，分析出次top sql的执行计划是否合理，是否存在性能问题。

步骤三： 通过上述的三种方法总述，进行sql的优化。

• 统计信息不准或是CBO计算成本方式的不足而导致的性能问题，可以重新收集统计信息或者手工修改统计信息，或者使用hint来加以解决

• sql写法问题，改写sql

• 不必要的全表扫描或者排序引起的性能问题，可以通过建立合适的索引（函数索引、位图索引等）来加以解决。

• sql的执行计划不稳定，使用hint、sql profile或者spm来解决。

• 表或者索引设计不良导致，重建。

• 如果上述无果，可以并行来缩短执行时间。

• 联系业务，更改sql的执行逻辑。

第二部分

sql优化要联系实际业务

比如，大表，不了解业务的情况，只是从sql优化的角度去优化sql，可能的问题：

1. 添加索引后，造成大量的单块读（db file sequential read）,因为索引本身很大，那么对于很分散的索引块而言，其缓存命中率会很低。

2. 如果表的99%是插入，1%是查询，那么加索引的优化就是得不尝试，更何况是大表。同时也要确定执行频率。当然，有可能我们加了索引后，也要过段时间才能出现负面效果，因为数据量可能过段时间才能变大起来。

3. 可以适当的利用已有的索引，确认我们的查询是否和现在的索引有关联。

第三部分

绑定变量

对于OLTP系统：

使用绑定变量来有效降低硬解析的数量是必须要做的。

详细看cursor和绑定变量的章节介绍

注意点：

trace文件，使用tkprof 翻译，这样可以方便的查看；

使用函数，因此CBO无法知道这个列所在过滤查询条件的可选择率，从而会采用默认的可选择率，这样会导致CBO评估返回行数过小，从而影响sql的表连接方式和连接顺序。

因此，sql语句有函数的情况，要重点排查。

分区表，加上限制，才能指定具体的分区去排查，否则就是全表扫描。