OracleSQLtuning数据库优化步骤分享(图文教程)

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

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

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

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

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

   更多有关全表扫描的问题，以及选择正确的优化模式请："OracleTuning:TheDefinitiveReference"

三、SQL调整战略步骤
   很多人问SQLtuning从哪里着手。首先应当是从Librarycache去根据他们的活动状况捕获SQL语句。

1、寻找影响较大的SQL语句
   我们可以根据SQL语句执行次数的多少进行排序来获得执行次数较多的SQL语句。在v$sqlarea视图中executions列以及表stats$sql_summary
   或dba_hist_sql_summary能够去定位当前最频繁使用的SQL语句。注:也可以按照下列方式列出SQL语句。
      Rowsprocessed
         处理的行数越多，则相应会有很高的I/O，也有可能耗用大量的临时表空间

      Buffergets
         Buffergets过高可能表明资源被过度集中化查询，存在热块现象

      Diskreads
         高的磁盘读将引起过度的I/O

      MemoryKB
         内存的分配大小可以鉴别该SQL语句是否在内存中使用了大量的表连接

      CPUsecs
         CPU的开销表明哪些SQL语句耗用了大量的CPU资源

      Sorts
         排序越多，则SQL性能越差，而且会占用大量的临时表空间

      Executions
         执行次数表明了当前SQL语句的频繁度，应当被首先考虑调整，因为这些语句影响了数据库的整体性能

2、决定SQL的执行计划
   每一个SQL语句都可以根据SQL_ID来获得其执行计划。有大量的第三方工具来获得SQL语句的执行计划。而获得执行最常用的方式是使用Oracle
   自带的explainplan程序。通过使用该程序，OracleDBA能够在不执行SQL语句的情形下解析并显示该SQL语句的执行计划。

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

   sqlplus>@utlxplan
   Tablecreated.

   sqlplus>createpublicsynonymplan_tableforsys.plan_table;
   Synonymcreated.

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

   EXPLAINPLANSETSTATEMENT_ID="test1"FOR
   SETSTATEMENT_ID="RUN1"
   INTOplan_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),
   FROMplansnet,detplan
   WHERE
      plansnet.mgc=detplan.mktgpm
   AND
      detplan.pac1in("N33","192","195","201","BAI",
      "P51","Q27","180","181","183","184","186","188",
      "198","204","207","209","211")
   GROUPBY"T"||plansnet.terr_code,"P"||detplan.pac1||detplan.pac2||detplan.pac3;

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

      operation
         表明当前语句完成的操作，通常包括tableaccess,tablemerge,sort,orindexoperation

      options
         补充说明operation，像fulltable,rangetable,join

      object_name
         查询组件的名字

      ProcessID
         查询组件的ID号

      Parent_ID
         查询组建的父ID，注意，有些查询会有一个相同的父ID

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

      plan.sql-displayscontentsoftheexplainplantable
      SETPAGES9999;
      SELECT lpad("",2*(level-1))||operationoperation,
            options,
            object_name,
            position
      FROMplan_table
      STARTWITHid=0
      AND
      statement_id="RUN1"
      CONNECTBYpriorid=parent_id
      AND
      statement_id="RUN1";

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

   OPERATION
   -------------------------------------------------------------------------------------
   OPTIONS                          OBJECT_NAME                   POSITION
   -------------------------------------------------------------------------------------
   SELECTSTATEMENT
   SORT
   GROUPBY                                                     1
        CONCATENATION                                  1
   NESTEDLOOPS                                   1
   TABLEACCESSFULL        PLANSNET                  1
   TABLEACCESSBYROWID    DETPLAN                   2
          INDEXRANGESCAN      DETPLAN_INDEX5            1
   NESTEDLOOPS

   从上面的执行计划中得知当前的SQL语句存在表扫描现象。去调整该SQL语句，我们应当寻找表where子句中为planset上的列。在这里我们
   看到了在where子句存在一个且属于表planset上的列mgc被用作连接条件。这说明一个基于表planset.mgs列上的索引是必要的。

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

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

   通过添加提示来修改SQL的执行计划

   使用全局临时表来重写SQL

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

   使用提示来调整SQL

   大多数SQLtuning工具中使用较多的莫过于使用提示。一个提示添加的SQL语句后使得SQL查询的按指定路径访问。

   Troubleshootingtip! 
   为便于测试，我们能够随时使用altersession命令来修改一个优化参数的值来观察调整前后的结果比较。使用新的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模式自动获取最佳执行路径，等同于使用提示的功能。
   我们来看看提高性能最常用的提示

      Modehints: first_rows_10,first_rows_100
      Oracleleadingandorderedhints Alsoseehowtotunetablejoinorderwithhistograms

      Dynamicsampling:dynamic_sampling

      OracleSQLundocumentedtuninghints-Guru"sonly
      Thecardinalityhint   

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

   first_rows_n提示
      Oracle有两个基于成本优化的提示，一个是first_rows_n，一个是all_rows。first_rows模式将尽可能在一查询到数据时就返回个客
      户端。而all_rows模式则为优化资源而设计，需要等到所有结果计算执行完毕才返回数据给客户端。

      SELECT/*+first_rows*/

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

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

   Astandardjoin: -->标准连接

   SELECT*
   FROMSTUDENT,REGISTRATION
   WHERE
      STUDENT.student_id=REGISTRATION.student_id
   AND
      REGISTRATION.grade="A";

   Anestedquery: -->嵌套查询

   SELECT*
   FROMSTUDENT
   WHERE
      student_id=
      (SELECTstudent_id
         FROMREGISTRATION
         WHERE
        grade="A"
      );

   Acorrelatedsubquery: -->相关子查询

   SELECT*
   FROMSTUDENT
   WHERE
      0<
      (SELECTcount(*)
         FROMREGISTRATION
         WHERE
         grade="A"
         AND
         student_id=STUDENT.student_id
      );

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

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

   a.使用临时表重写复杂的子查询
      Oracle使用全局临时表以及WITH操作符去解决那些复杂的SQL子查询。尤其是那些where子句中的子查询，SELECT字句标量子查询，
      FROM子句的内联视图。使用临时表实现SQLtuning(以及使用WITH的物化视图)能够使得性能得以惊人的提升。

   b.使用MINUS代替EXIST子查询
      使用MINUS操作代替NOTIN或NOTEXISTS将产生更高效的执行计划(译者按：此需要测试)。

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

   d.重写NOTEXISTS和查询作为外部连接NOTEXISTS子查询
      在一些案例中的NOT查询(where中一个列被定义为NULL值)，能够将其改写这个非相关子查询到ISNULL的外部链接。如下例：
      selectbook_keyfrombook
      where
      book_keyNOTIN(selectbook_keyfromsales);

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

      selectb.book_keyfrombookb,saless
      where
        b.book_key=s.book_key(+)
      and
        s.book_keyISNULL;

   e.索引NULL值列
      如果你的SQL语句频繁使用到NULL值，应当考虑基于NULL值创建索引。为使该查询最优化，可以创建一个使用基于NULL值索引函数。
      (译者按，如createindexi_tb_colontab(nvl(col,null));createindexi_tb_colontab(col,0);)

   f.避免基于索引的运算
      不要基于索引列做任何运算，除非你创建了一个相应的索引函数。或者重设设计列以使得where子句列上的谓词不需要转换。
      -->下面都是低效的SQL写法
      wheresalary*5           >:myvalue  
      wheresubstr(ssn,7,4)    ="1234"
      whereto_char(mydate,mon)="january"

    g.避免使用NOTIN和HAVING 
      在合适的时候使用notexists子查询更高效。

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

   i.避免数据类型转换
      如果一个where子句列是数字型，则不要使用引号。而对一个字符索引列，总是使用引号。下面是数据类型混用的情形。
      wherecust_nbr="123"
      wheresubstr(ssn,7,4)=1234

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

   k.不要害怕全表扫描
      并不是所有的OLTP系统在使用索引时是最优化的。如果你的查询返回了表中的绝大部分数据，则全表扫描性能优于索引扫描。这取决于
      一些因素包括你的配置(db_file_multiblock_read_count,db_block_size)，并行查询，以及表块和索引块在buffercache中的数量。

   l.使用别名
      在参照列的地方总是使用表别名。
   -->Author:RobinsonCheng
   -->Blog  :http://blog.csdn.net/robinson_0612