Oracle SQL tuning 步骤

文章由LinuxBoy分享于2019-03-31 01:03:12热评（548）

Oracle SQL tuning 步骤

SQL是的全称是Structured Query Language(结构化查询语言)。SQL是一个在80年代中期被使用的工业标准数据库查询语言。不要把SQL语言与商业化产品如Microsoft SQL server或开源产品MySQL相混淆。所有的使用SQL缩略词的这些都是SQL标准的一部分。

更多Oracle相关信息见Oracle 专题页面 http://www.bkjia.com/topicnews.aspx?tid=12

一、SQL tuning之前的调整
下面这个粗略的方法能够节省数千小时乏味的SQL tuning，因为一旦调整它将影响数以百计的SQL查询。记住，你必须优先调整它，否则后
续的优化器参数改变或统计信息可能不会有助于你的SQL调整。

   记住，你应当总是优先考虑系统级别的SQL tuning，否则在SQL tuning之后再进行调整可能会使得你先前调整的SQL功亏一篑。

1、优化系统内核
   首先应当考虑调整磁盘和网络I/O子系统(象RAID，DASD带宽，网络等)去最小化I/O时间，网络包的大小以及调度频率。

2、调整优化器统计信息
   应当定期收集和存储优化器的统计信息以便优化器根据数据的分布生成最佳的执行计划。此外，直方图有助于优化表的连接以及为有倾斜的
    where 子句谓词信息做出正确的访问决定。

3、调整优化器参数
   下列优化器参数应当被调整
   optimizer_mode， optimizer_index_caching， optimizer_index_cost_adj

4、优化实例
   下列实例/会话级别参数将影响SQL性能
   db_block_size，db_cache_size， and OS parameters (db_file_multiblock_read_count， cpu_count， &c)，

5、使用索引或物化视图调整SQL访问负载
   Oracle 10g之后可以使用SQL Access advisor来为SQL生成索引或物化视图的建议。应当总是使用索引来优化SQL，特别是基于函数的索引。
    Oracle 11g的改进：
   Oracle 11g中新增的SQL Performance Analyzer (SPA)是一个从整体上加快SQL调整的新特性。

   通过SPA，一旦创建一个负载(称为SQL tuning set，或者STS)，Oracle将根据不同环境情况，使用复杂的预测模块重复的执行工作负载(使
   用回归测试方法)，来得到当前负载的最佳SQL执行计划。使用SPA，我们可以预测一个SQL负载基于系统变化造成的影响，以及预测象参数
   调整，系统schema调整，硬件调整，操作调整，Oracle升级之后当前SQL语句的响应时间。更多详细的细节请参考:Oracle 11g New Feature

   当运行环境，Oracle实例以及对象被调整之后，更多地关注则是数据库中的性能影响最大的单个单个的SQL语句。下面将针对单个SQL调整给
   出一些常规建议以提高 Oracle 性能。

二、Oracle SQL tuning的目标
   Oracle SQL tuning是一个复杂的课题。Oracle Tuning: The Definitive Reference 这整本书描述了关于SQL tuning的细节。尽管如此，
    为了提高系统系能，Oracle DBA应当遵从下面一些总的指导原则。

1、SQL tuning 目标
   是以最小的数据库访问次数提取更多地数据行来生成最佳的执行计划(尽可能最小化物理读(PIO)与逻辑读(LIO)。

   指导原则
       移除不必要的大型全表扫描
           大型表的全表扫描将产生庞大的系统I/O且使得整个数据库性能下降。优化专家首先会评估当前SQL查询所返回的行数。最常见的办
       法是为走全表扫描的大表增加索引。B树索引，位图索引，以及基于函数的索引等能够避免全表扫描。有时候，对一些不必要的全表扫
       描通过添加提示的方法来避免全表扫描。

       缓存小表全表扫描
           有时候全表扫描是最快的访问方式，管理员应当确保专用的数据缓冲区(keep buffer cache，nk buffer cache)对这些表可用。在
           Oracle 8 以后小表可以被强制缓存到 keep 池。

       使用最佳索引
           Oracle 访问对象有时候会有一个以上的索引选择。因此应当检查当前查询对象上的每一个索引以确保Oracle使用了最佳索引。

       物化聚合运算以静态化表统计
           Oracle 10g的特性之一SQL Access advisor 会给出索引建议以及物化视图的建议。物化视图可以预连接表和预摘要表数据。(译者
           按，即Oracle可以根据特定的更新方式来提前更新物化视图中的数据，而在查询时仅仅查询物化视图即可得到最终所需的统计数据
           结果。物化视图实际上是一张实体表)

   以上这些概括了SQL tuning的目标。然而看是简单，调整起来并不容易，因为这需要对Oracle SQL内部有一个彻底的了解。接下来让我们从
   整体上来认识 Oracle SQL 优化。

2、Oracle SQL 优化器
   Oracle DBA首先要查看的是当前数据库缺省的优化器模式。Oracle初始化参数提供很多基于成本优化的优化器模式以及之前废弃的基于规则
   的优化器模式(或hint)供选择。基于成本的优化器主要依赖于表对象使用analyze命令收集的统计信息。Oracle根据表上的统计信息得以决定
   并为当前的SQL生成最高效的执行计划。需要注意的是在一些场合基于成本优化器可能会做出不正确的决定。基于成本的优化器在不断的改进，
   但是依然有很多场合使用基于规则的优化器能够使得查询更高效。

   在Oracle 10g之前，Oracle 缺省的优化器模式是CHOOSE模式。在该模式下，如果表对象上缺乏统计信息则此时Oracle使用基于规则的优化
   器；如果统计信息存在则使用基于成本的优化器。使用CHOOSE模式存在的隐患即是对一些复杂得查询有些对象上有统计信息，而另一些对象
   缺乏统计信息。

   在Oracle 10g开始，缺省的优化器模式是 ALL_ROWS，这有助于全表扫描优于索引扫描。ALL_ROWS优化器模式被设计成最小化计算资源且有
   助于全表扫描。索引扫描(first_rows_n)增加了额外的I/O开销。但是他们能更快地返回数据。

因此，大多数OLTP系统选择first_rows，first_rows_100 或者 first_rows_10以使得Oracle使用索引扫描来减少读块数量。

   注意：从Oracle 9i R2开始，Oracle 性能调整指导指出了first_rows 优化器模式已经被废弃，且使用first_rows_n代替

   当仅有一些表包含CBO统计信息，而另一些缺乏统计信息时，Oracle使用基于成本的优化模式来预估其他表在运行时的统计信息(即动态采样
   )，这在很大程度上影响单个查询性能下降。

   总之，Oracle 数据库管理员应当总是将尝试改变优化器模式作为SQL tuning的第一步。Oracle SQL tuning的首要原则是避免可怕的全表扫
   描。一个特性之一是一个非高效的SQL语句为提高查询性能使用所有的索引此仍然为一个失败的SQL语句。

   当然，有些时候使用全表扫描是合适的，尤其是在做聚合操作象sum，avg等操作，因为为了获得结果，表上的绝大部分数据行必须被读入到
   缓存。SQL tuning 高手应当合理的评估每一个全表扫描并要核实使用索引能否提高性能。

   在大多数Oracle 系统，SQL语句检索的仅仅是表上数据一个子集。Oracle 优化器会检查使用索引是否会导致更多的I/O。然而，如果构建了
   一个低效的查询，基于成本的优化器难以选择最佳的数据访问路径，转而倾向于使用全表扫描。故Oracle数据库管理员应当总是审查那些走
   全表扫描的SQL语句。

   更多有关全表扫描的问题，以及选择正确的优化模式请："Oracle Tuning: The Definitive Reference"

推荐文章：

Oracle SQL tuning 步骤