Oracle SQL Tuning知识

Oracle SQL Tuning 目标：

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

优化器模式

ORACLE的优化器共有3种:

a. RULE (基于规则) b. COST (基于成本) c. CHOOSE (选择性)

       为了使用基于成本的优化器(CBO, Cost-Based Optimizer) , 你必须定期更新统计信息，以保证数据库中的对象统计信息(object statistics)的准确性.

       如果数据库的优化器模式设置为选择性(CHOOSE),那么实际的优化器模式将和是否运行过analyze命令有关. 如果table已经被analyze过, 优化器模式将自动成为CBO , 反之,数据库将采用RULE形式的优化器。

访问Table的方式

ORACLE 采用两种访问表中记录的方式:

a. 全表扫描 （TABLE ACCESS FULL）

 全表扫描就是顺序地访问表中每条记录. ORACLE采用一次读入多个数据块(database block)的方式优化全表扫描。使用FTS的前提条件：在较大的表上不建议使用全表扫描，除非取出数据的比较多，超过总量的5%-10%，或你想使用并行查询功能时。

b. 索引扫描

 你可以采用基于ROWID的访问方式情况,提高访问表的效率, ROWID包含了表中记录的物理位置信息.ORACLE采用索引(INDEX)实现了数据和存放数据的物理位置(ROWID)之间的联系. 通常索引提供了快速访问ROWID的方法,因此那些基于索引列的查询就可以得到性能上的提高.

其中ORACLE对索引又有两种访问模式.

a)索引唯一扫描 ( INDEX UNIQUE SCAN)

       大多数情况下, 优化器通过WHERE子句访问INDEX.

b)索引范围查询(INDEX RANGE SCAN)

       谓词(where限制条件)中使用了范围操作符(如>、<、<>、>=、<=、between)。在非唯一索引上，谓词"="也可能返回多行数据，所以在非唯一索引上都使用索引范围扫描。

       使用index rang scan的3种情况：

1. 在唯一索引列上使用了range操作符(> < <> >= <= between)

2. 在组合索引上，只使用部分列进行查询，导致查询出多行

3. 对非唯一索引列上进行的任何查询。

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

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

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

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

调整步骤
    很多人问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的执行计划

Oracle提供一个utlxplan.sql脚本来创建该表。执行该脚本并且为该表创建一个公共同义词。

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

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

    通过添加提示(hint)来修改SQL的执行计划；  一个添加提示的SQL语句，使得SQL查询按指定路径访问。 

    使用全局临时表来重写SQL；
    使用PL/SQL来重写SQL； 对于一些特定查询该方法能够有20倍左右的提升，将这些SQL封装到包含存储过程的包中去完成查询。
    使用提示来调整SQL。

    为便于测试，我们能够随时使用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模式自动获取最佳执行路径，等同于使用提示的功能。
    
    表连接顺序 ：当表连接的顺序可优化时，我们可以使用 ORDERED提示来强制表按照from子句中出现的先后顺序来进行连接
     
    first_rows_n提示 ：Oracle 有两个基于成本优化的提示，一个是first_rows_n，一个是all_rows。first_rows模式将尽可能在一查询到数据时就返回个客户端。而 all_rows 模式则为优化资源而设计，需要等到所有结果计算执行完毕才返回数据给客户端。
     
        SELECT /*+ first_rows */ ...

4、书写高效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值索引函数。
        (译者按，如 create index i_tb_col on tab(nvl(col,null)); create index i_tb_col on tab(col,0);)

    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.使用别名
        在参照列的地方总是使用表别名。