Oracle SQL tuning 数据库优化步骤分享(图文教程)-木庄网络博客

本文整理自网络，侵删。

SQL Turning 是Quest公司出品的Quest Central软件中的一个工具。Quest Central是一款集成化、图形化、跨平台的数据库管理解决方案，可以同时管理 Oracle、DB2 和 SQL server 数据库。

一、SQL Tuning for SQL Server简介

SQL语句的优化对发挥数据库的最佳性能非常关键。然而不幸的是，应用优化通常由于时间和资源的因素而被忽略。SQL Tuning (SQL优化)模块可以对比和评测特定应用中SQL语句的运行性能，提出智能化的优化建议，帮助用户改善应用的响应时间。SQL优化模块具有非介入式SQL采集、自动优化和专家建议等功能，全面改善SQL优化工作。

二、SQL Tuning for SQL Server的使用

1、打开Quest Database Management Solutions弹出窗口如图1所示

图1

2、在红色标记处打开SQL Tuning 优化SQL

（1）建立连接。

在Quest Central主界面上的“Database”树上选择“SQL Server”，然后在下方出现的“Tools”框中选择“SQL Tuning”选项，打开“Lanch SQL Tuning for SQL Server Connections”对话框（图2、图3）。我们在这里建立数据库服务器的连接，以后的分析工作都会在它上面完成。

图2 “建立连接”对话框

图3

双击“New Connection”图标，在弹出窗口中输入数据库的信息，单击“OK”，然后单击“Connect”即可。

（2）分析原始SQL语句，在单击“Connect”后将弹出一个新窗口，如图4

图4

在打开窗口的“Oriangal SQL”文本框内输入需要分析的原始SQL语句，红色标记处选择对应的数据库名，SQL语句代码如下：

图5 分析原始SQL语句

原始SQL语句

然后点击工具栏上的“Execute”按钮，执行原始的SQL语句，SQL Tuning会自动分析SQL的执行计划，并把分析结果显示到界面上（图5）。

（3）优化SQL。

现在我们点击工具栏上的“Optimize Statement”按钮,让SQL Tuning开始优化SQL，完成后，可以看到SQL Tuning产生了19条与原始SQL等价的优化方案（图6）。

图6 SQL优化方案

（4）获得最优SQL。

接下来，我们来执行上面产生的优化方案，以选出性能最佳的等效SQL语句。在列表中选择需要执行的优化方案(默认已全部选中)，然后点击工具栏上的“Execute”按钮旁边的下拉菜单，选择“Execute Selected”。等到所有SQL运行完成后,点击界面左方的“Tuning Resolution”按钮，
可以看到最优的SQL已经出来啦，运行时间竟然可以提高21%！（图7）

图7 “Tuning Resolution”界面

最优的SQL语句如下：

5）学习书写专家级的SQL语句。

优化后的SQL语句

SELECT dbo.Person_BasicInfo.*,
       dbo.Graduater_GraduaterRegist.RegistNO AS RegistNO,
       dbo.Graduater_GraduaterRegist.RegistTime AS BaoDaoTime,
       dbo.Graduater_GraduaterRegist.RegistMan AS RegistMan,
       dbo.Graduater_Business.ComeFrom AS ComeFrom,
       dbo.Graduater_Business.Code AS Code,
       dbo.Graduater_Business.Status AS Status,
       dbo.Graduater_Business.ApproveResult AS ApproveResult,
       dbo.Graduater_Business.NewCorp AS NewCorp,
       dbo.Graduater_Business.CommendNumber AS CommendNumber,
       dbo.Graduater_Business.EmployStatus AS EmployStatus,
       dbo.Graduater_Business.NewCommendTime AS NewCommendTime,
       dbo.Graduater_Business.GetSource AS GetSource,
       dbo.Graduater_Business.EmployTime AS EmployTime,
       dbo.Graduater_Business.Job AS Job,
       dbo.Graduater_Business.FillMan AS FillMan,
       dbo.Graduater_Business.FillTime AS FillTime,
       dbo.Graduater_Business.IsCommendOK AS IsCommendOK,
       dbo.Graduater_Business.ApproveUser AS ApproveUser,
       dbo.Graduater_Business.ApproveTime AS ApproveTime,
       dbo.Graduater_Business.RegistTime AS RegistTime,
       dbo.Graduater_Business.EmployCorp AS EmployCorp,
       dbo.Graduater_Business.JobRemark AS JobRemark,
       CASE WHEN dbo.Graduater_Business.ComeFrom = 'WS' THEN '网上登记'
            WHEN dbo.Graduater_Business.ComeFrom = 'HP' THEN '华普大厦'
            WHEN dbo.Graduater_Business.ComeFrom = 'JD' THEN '精典大厦'
            WHEN dbo.Graduater_Business.ComeFrom = 'MC' THEN '赛马场'
            WHEN ComeFrom = 'ZX' THEN '高指中心' END AS ComeFromName,
       dbo.Person_Contact.Address AS Address,
       dbo.Person_Contact.Zip AS Zip,
       dbo.Person_Contact.Telephone AS Telephone,
       dbo.Person_Contact.Mobile AS Mobile,
       dbo.Person_Contact.Email AS Email,
       dbo.Person_Contact.IM AS IM,
       dbo.Person_Skill.ForeignLanguage AS ForeignLanguage,
       dbo.Person_Skill.ForeignLanguageLevel AS ForeignLanguageLevel,
       dbo.Person_Skill.CantoneseLevel AS CantoneseLevel,
       dbo.Person_Skill.MandarinLevel AS MandarinLevel,
       dbo.Person_Skill.Language AS Language,
       dbo.Person_Skill.TechnicalTitle AS TechnicalTitle,
       dbo.Person_Skill.ComputerLevel AS ComputerLevel,
       dbo.Person_EmployPurpose.JobType AS JobType,
       dbo.Person_EmployPurpose.Vocation AS Vocation,
       dbo.Person_EmployPurpose.JobPlace AS JobPlace,
       dbo.Person_EmployPurpose.Salary AS Salary,
       dbo.Person_EmployPurpose.OnJobDate AS OnJobDate,
       dbo.Person_EmployPurpose.CorpType AS CorpType,
       dbo.Person_EmployPurpose.Job AS RequireJob,
       YEAR(GETDATE()) - YEAR(dbo.Person_BasicInfo.Birthday) AS Age,
       dbo.Graduater_Business.EmployType AS EmployType,
       dbo.Graduater_Business.EmployTypeCode AS EmployTypeCode,
       dbo.Graduater_Business.EmployCorpType AS EmployCorpType,
       CASE WHEN dbo.Graduater_Business.PrintStatus = '已打印' THEN '已打印'
            ELSE '未打印' END AS PrintStatus,
       dbo.Graduater_Business.PrintTime AS PrintTime,
       CASE WHEN dbo.Graduater_Business.EmployStatus = '是' THEN '已就业'
            ELSE '未就业' END AS EmployStatusView
  FROM dbo.Person_BasicInfo
       INNER JOIN dbo.Graduater_Business
          ON dbo.Person_BasicInfo.PersonID = dbo.Graduater_Business.PersonID
       LEFT OUTER JOIN dbo.Graduater_GraduaterRegist
         ON dbo.Graduater_Business.GradBusinessID = dbo.Graduater_GraduaterRegist.GraduaterGUID
       INNER JOIN dbo.Person_Contact
          ON dbo.Person_BasicInfo.PersonID = dbo.Person_Contact.PersonID
       INNER JOIN dbo.Person_Skill
          ON dbo.Person_BasicInfo.PersonID = dbo.Person_Skill.PersonID
       INNER JOIN dbo.Person_EmployPurpose
          ON dbo.Person_BasicInfo.PersonID = dbo.Person_EmployPurpose.PersonID
OPTION (FORCE ORDER)

（

通过上面的步骤，我们已经可以实现自动优化SQL语句，但更重要的是，我们还可以学习如何书写这样高性能的SQL语句。点击界面左方的“Compare Scenarios”按钮，我们可以比较优化方案和原始SQL中的任意2条SQL语句，SQL Tuning会将它们之间的不同之处以不同颜色表示出来，
还可以在下方的“执行计划”中，通过比较两条SQL语句的执行计划的不同，来了解其中的差异（图8）。

图8 “Compare Scenarios”界面

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开销。但是他们能更快地返回数据。