• 售前

  • 售后

热门帖子
入门百科

Oracle SQL tuning 数据库优化步调分享(图文教程)

[复制链接]
天主的爱 显示全部楼层 发表于 2021-10-26 13:46:35 |阅读模式 打印 上一主题 下一主题
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开销。但是他们能更快地返回数据。


因此,大多数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"

三、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查询的按指定路径访问。

    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   

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

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

        SELECT /*+ first_rows */

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

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

    A standard join:  -->标准连接

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

    A nested query:  -->嵌套查询

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

    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值索引函数。
        (译者按,如 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.利用别名
        在参照列的地方总是利用表别名。
    --> Author : Robinson Cheng
    --> Blog   : http://blog.caogenba.net/robinson_0612

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x

帖子地址: 

回复

使用道具 举报

分享
推广
火星云矿 | 预约S19Pro,享500抵1000!
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

草根技术分享(草根吧)是全球知名中文IT技术交流平台,创建于2021年,包含原创博客、精品问答、职业培训、技术社区、资源下载等产品服务,提供原创、优质、完整内容的专业IT技术开发社区。
  • 官方手机版

  • 微信公众号

  • 商务合作