PLSQL_性能优化系列16_Oracle Tuning Analyze优化分析

 2014-12-23 Created By BaoXinjian

一、摘要

SQL是的全称是Structured Query Language(结构化查询语言)。SQL是一个在80年代中期被使用的工业标准数据库查询语言。

SQL性能优化是Oracle PLSQL调优的重要部分。所以需要仔细研究

本文转载乐沙弥同学文章，略作格式调整

二、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访问负载

    (1). Oracle 10g之后： 可以使用SQL Access advisor来为SQL生成索引或物化视图的建议。应当总是使用索引来优化SQL，特别是基于函数的索引。

    (2). 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)。

指导原则

(1). 移除不必要的大型全表扫描

大型表的全表扫描将产生庞大的系统I/O且使得整个数据库性能下降。

优化专家首先会评估当前SQL查询所返回的行数。

最常见的办法是为走全表扫描的大表增加索引。

B树索引，位图索引，以及基于函数的索引等能够避免全表扫描。有时候，对一些不必要的全表扫描通过添加提示的方法来避免全表扫描。

(2). 缓存小表全表扫描

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

(3). 使用最佳索引

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

(4). 物化聚合运算以静态化表统计

Oracle 10g的特性之一SQL Access advisor 会给出索引建议以及物化视图的建议。

物化视图可以预连接表和预摘要表数据。(译者按，即Oracle可以根据特定的更新方式来提前更新物化视图中的数据，而在查询时仅仅查询物化视图即可得到最终所需的统计数据结果。物化视图实际上是一张实体表)

(5). 总结

以上这些概括了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语句。

四、SQL调整战略步骤

很多人问SQL tuning从哪里着手。首先应当是从Library cache去根据他们的活动状况捕获SQL语句。

1. 寻找影响较大的SQL语句

我们可以根据SQL语句执行次数的多少进行排序来获得执行次数较多的SQL语句。

在v$sqlarea视图中executions 列以及表stats$sql_summary 或 dba_hist_sql_summary 能够去定位当前最频繁使用的SQL语句。注:也可以按照下列方式列出SQL语句。

• Rows processed - 处理的行数越多，则相应会有很高的I/O，也有可能耗用大量的临时表空间
• Buffer gets - Buffer gets过高可能表明资源被过度集中化查询，存在热块现象
• Disk reads - 高的磁盘读将引起过度的I/O
• Memory KB - 内存的分配大小可以鉴别该SQL语句是否在内存中使用了大量的表连接
• CPU secs - CPU的开销表明哪些SQL语句耗用了大量的CPU资源
• Sorts - 排序越多，则SQL性能越差，而且会占用大量的临时表空间
• Executions - 执行次数表明了当前SQL语句的频繁度，应当被首先考虑调整，因为这些语句影响了数据库的整体性能

2. 决定SQL的执行计划

每一个SQL语句都可以根据SQL_ID来获得其执行计划。有大量的第三方工具来获得SQL语句的执行计划。

而获得执行最常用的方式是使用Oracle自带的explain plan程序。

通过使用该程序，Oracle DBA能够在不执行SQL 语句的情形下解析并显示该SQL语句的执行计划。

查看SQL执行计划的输出，必须首先创建一个plan table. Oracle提供一个utlxplan.sql脚本来创建该表。执行该脚本并且为该表创建一个公共同义词。

sqlplus > @utlxplan
    Table created.
sqlplus > create public synonym plan_table for sys.plan_table;
    Synonym created.

大多数关系数据库使用解释程序将SQL语句作为输入，然后运行SQL优化器，输出访问的路径信息到一个plan_table。以便我们能够查看及调整其访问方式。下面的是一个复杂的SQL查询。

EXPLAIN PLAN SET STATEMENT_ID = 'test1' FOR
    SET STATEMENT_ID = 'RUN1'
    INTO plan_table
    FOR
    SELECT   'T'||plansnet.terr_code, 'P'||detplan.pac1
    || detplan.pac2 || detplan.pac3, 'P1', sum(plansnet.ytd_d_ly_tm),
     sum(plansnet.ytd_d_ty_tm),
     sum(plansnet.jan_d_ly),
     sum(plansnet.jan_d_ty),
    FROM plansnet, detplan
    WHERE
        plansnet.mgc = detplan.mktgpm
    AND
        detplan.pac1 in ('N33','192','195','201','BAI',
        'P51','Q27','180','181','183','184','186','188',
        '198','204','207','209','211')
    GROUP BY 'T'||plansnet.terr_code, 'P'||detplan.pac1 || detplan.pac2 || detplan.pac3;

这个语法使用管道输入到SQL优化器，解析SQL，存储执行计划信息到表plan_table，且RUN1作为鉴别当前SQL语句的标识符。注意，该查询并没有执行，它仅仅是创建了一个内部访问信息且输出到plan_table。plan 表包含下列字段。

• Operation - 表明当前语句完成的操作，通常包括table access, table merge, sort, or index operation
• Options - 补充说明operation，像full table, range table, join
• Object_name - 查询组件的名字
• Process ID - 查询组件的ID号
• Parent_ID - 查询组建的父ID，注意，有些查询会有一个相同的父ID

现在plan_table已经被填充，可以使用下面的查询来查看当前SQL语句的执行计划。

plan.sql - displays contents of the explain plan table
SET PAGES 9999;
SELECT  lpad(' ',2*(level-1))||operation operation,
                options,
                object_name,
                position
FROM plan_table
START WITH id=0
AND
statement_id = 'RUN1'
CONNECT BY prior id = parent_id
AND
statement_id = 'RUN1';

下面给出了当前语句执行计划信息以及各个部分的执行顺序。

SQL> @list_explain_plan
OPERATION
-------------------------------------------------------------------------------------
OPTIONS                           OBJECT_NAME                    POSITION
------------------------------ -------------------------------------------------------
SELECT STATEMENT
SORT
GROUP BY                                                       1
           CONCATENATION                                       1
NESTED LOOPS                                                   1
TABLE ACCESS FULL         PLANSNET                             1
TABLE ACCESS BY ROWID     DETPLAN                              2
              INDEX RANGE SCAN       DETPLAN_INDEX5            1
NESTED LOOPS

从上面的执行计划中得知当前的SQL语句存在表扫描现象。

去调整该SQL语句，我们应当寻找表where 子句中为planset上的列。

在这里我们看到了在where子句存在一个且属于表planset上的列mgc被用作连接条件。这说明一个基于表planset.mgs列上的索引是必要的。

Plan table并不能展现整个SQL语句的细节，但对于获得数据访问路径是非常有用的。SQL优化器知道每一个表的行数(基数)以及一些索引字段的状况。但并不了解数据的分布象如一个组件期待返回的行数。

3. 调整SQL语句

对于那些存在可优化的子执行计划，SQL应当按照下面的方式进行调整。

• 通过添加提示来修改SQL的执行计划
• 使用全局临时表来重写SQL
• 使用PL/SQL来重写SQL。对于一些特定查询该方法能够有20倍左右的提升。将这些SQL封装到包含存储过程的包中去完成查询。
• 使用提示来调整SQL
• 大多数SQL tuning工具中使用较多的莫过于使用提示。一个提示添加的SQL语句后使得SQL查询的按指定路径访问。

(1). Troubleshooting tip!

为便于测试，我们能够随时使用alter session命令来修改一个优化参数的值来观察调整前后的结果比较。使用新的 opt_param 提示能获得同样的效果。

    select /*+ opt_param('optimizer_mode','first_rows_10') */ col1, col2 . . .

    select /*+ opt_param('optimizer_index_cost_adj',20) */ col1, col2 . .

Oracle 发布了大量的SQL提示，而且提示随着Oracle版本的不同不断的增强和复杂化。

注意：提示通常用于调试SQL，最佳的办法是调整优化器的统计信息使的CBO模式自动获取最佳执行路径，等同于使用提示的功能。

我们来看看提高性能最常用的提示

Mode hints:  first_rows_10, first_rows_100

Oracle leading and ordered hints  Also see how to tune table join order with histograms

Dynamic sampling: dynamic_sampling

Oracle SQL undocumented tuning hints - Guru's only

The cardinality hint    

(2). 表连接顺序

当表连接的顺序可优化时，我们可以使用 ORDERED提示来强制表按照from子句中出现的先后顺序来进行连接

(3). first_rows_n提示

Oracle 有两个基于成本优化的提示，一个是first_rows_n，一个是all_rows。first_rows模式将尽可能在一查询到数据时就返回个客户端。

而 all_rows 模式则为优化资源而设计，需要等到所有结果计算执行完毕才返回数据给客户端。

SELECT /*+ first_rows */

4. 案例

同一个SQL语句有不同的写法。即简单的SQL查询能够以不同的方式来产生相同的结果集，但其执行效率和访问方式则千差万别。

下面的例子中的SQL语句使用了3种不同的写法来返回相同的结果

(1). A standard join:  -->标准连接

SELECT *
FROM STUDENT, REGISTRATION
WHERE STUDENT.student_id = REGISTRATION.student_id
AND REGISTRATION.grade = 'A';

(2). A nested query:  -->嵌套查询

SELECT *
FROM STUDENT
WHERE student_id =
        (SELECT student_id
            FROM REGISTRATION
            WHERE grade = 'A'
);

(3). A correlated subquery:  -->相关子查询

SELECT *
FROM STUDENT
WHERE
        0 <
        (SELECT count(*)
            FROM REGISTRATION
            WHERE grade = 'A'
            AND student_id = STUDENT.student_id
        );

我们应该根据基本的SQL原则来优化当前的SQL语句。

5. 书写高效SQL语句的技巧

下面给出一些编写高效SQL语句的总的指导原则，而不论Oracle优化器选择何种优化模式。这些看是简单的方式但是按照他们去做将收到事半功倍的效果(已经在实践中被证实)。

a.使用临时表重写复杂的子查询

Oracle 使用全局临时表以及WITH操作符去解决那些复杂的SQL子查询。尤其是那些where子句中的子查询，SELECT 字句标量子查询，FROM 子句的内联视图。

使用临时表实现SQL tuning(以及使用WITH的物化视图)能够使得性能得以惊人的提升。

b.使用MINUS 代替EXIST子查询

使用MINUS操作代替NOT IN 或NOT EXISTS将产生更高效的执行计划(译者按：此需要测试)。

c.使用SQL分析函数

Oracle 分析函数能够一次提取数据来做多维聚合运算(象ROLLUP,CUBE)以提高性能。

d.重写NOT EXISTS和查询作为外部连接NOT EXISTS 子查询

在一些案例中的NOT 查询(where 中一个列被定义为NULL值)，能够将其改写这个非相关子查询到IS NULL 的外部链接。如下例：

select book_key from book
where book_key NOT IN (select book_key from sales);

下面我们在where子句中使用了外部连接来替代原来的not exits，得到一个更高效的执行计划。

select b.book_key from book b, sales s
where b.book_key = s.book_key(+)
and s.book_key IS NULL;

e.索引NULL值列

如果你的SQL语句频繁使用到NULL值，应当考虑基于NULL值创建索引。为使该查询最优化，可以创建一个使用基于NULL值索引函数。

f.避免基于索引的运算

不要基于索引列做任何运算，除非你创建了一个相应的索引函数。或者重设设计列以使得where子句列上的谓词不需要转换。

        -->下面都是低效的SQL写法

        where salary*5            > :myvalue   

        where substr(ssn,7,4)     = "1234"

        where to_char(mydate,mon) = "january"

g.避免使用NOT IN 和HAVING

在合适的时候使用not exists子查询更高效。

h.避免使用LIKE谓词

在合适地时候，如果能够使用 = 运算应尽可能避免LIKE操作。

i.避免数据类型转换

如果一个where 子句列是数字型，则不要使用引号。而对一个字符索引列，总是使用引号。下面是数据类型混用的情形。

where cust_nbr = "123"

where substr(ssn,7,4) = 1234

j.使用decode与case

使用decode 与case 函数能够最小化查询表的次数。

k.不要害怕全表扫描

并不是所有的OLTP系统在使用索引时是最优化的。如果你的查询返回了表中的绝大部分数据，则全表扫描性能优于索引扫描。这取决于

一些因素包括你的配置(db_file_multiblock_read_count, db_block_size)，并行查询，以及表块和索引块在buffer cache中的数量。

l.使用别名

在参照列的地方总是使用表别名。

Thanks and Regards

转载：乐莎密 - http://blog.csdn.net/leshami/article/details/7422254