精华内容
下载资源
问答
  • 现在我就向大家介绍这样一款工具:SQLTuning for SQL Server。 1. SQL Tuning 简介 SQL Turning是Quest公司出品的Quest Central软件中的一个工具。 QuestCentral(图1)是一款集成化、图形化、跨平台的数据库管理...
  • SQL语句是对数据库进行操作的惟一途径,对数据库系统的性能起着决定性的作用。对于同一条件下的SQL语句写法有很多,其中一些写法往往对性能又有很大影响。但是每个人掌握SQL语言的水平不同,如何才能保证写出高性能...
  • 该工具用于oracle数据库进行sql优化调优,还能根据awr报告等择取top sql进行批量优化,亦能给批量sql提供最少的优秀索引建立。本工具发明人Richard To, 资深ITPUB元老,从1996年开始设计SQL优化工具,拥有超过20年的...
  • Tosska Sql tuning expert for oracle 分析工具。ITPUB的元老杜老师 RichardTo, Dell的前首席工程师, 拥有超过20年的SQL优化经验.
  • 一款针对Oracle SQL语句优化的工具,对于某些耗时的查询语句有很好的优化效果。该软件的优化手段是引入Oracle的hint功能,手动给查询语句指定更优的执行计划来达到优化效果。
  • Tosska SQL Tuning Expert (TSE™) 是一款优秀的SQL 调优工具,无需用户参与即可优化 SQL 语句。该产品将为您提供最终的 SQL 性能解决方案,只需点击鼠标即可。在整个 SQL 调优过程中,您不必进行分析、猜测或手工...
  • Tosska SQL Tuning Expert (TSE™) 是一款优秀的SQL 调优工具,无需用户参与即可优化 SQL 语句。官方正版安装包,激活码需要自己找
  • 自动SQL优化工具Tosska SQL Tuning Expert for Oracle,帮助SQL开发人员解决SQL性能问题。
  • 使用ORACLE SQL Tuning advisor快速优化低效的SQL语句, 这里是一个使用范例,有9个步骤, 后面 其他语句,都按照这个步骤来做就可以了。
  • 一个SQL优化工具, 个人觉得非常不错。发明人是ITPUB的元老杜老师 RichardTo, Dell的前首席工程师, 拥有超过20年的SQL优化经验。只能用于Oracle优化。
  • tuning模式则将高负载的SQL语句直接扔给优化器,优化器来自动对其进行详细的分析,调试并给出建议,这是Oracle 提供的Automatic Tuning Optimizer,即自动调整优化器。Oracle 自动调整优化器通过SQL调优建
  • sql tuning

    2011-11-11 22:44:05
    sql tuning oracle sql tuning
  • 关于oracle的SQL Tuning Advisor全面的使用介绍,包括分析awr、cursor、sql_set、sql文本中获取sql并且建立、执行、获取结果的方法。并且介绍了sql_set,sql_profile的操作。里面涉及到的内容都有可执行的代码。
  • Sensible Effectivity - Environment friendly ApplyThe goal of SQL tuning is the advance of the prevailing execution plan. The authors talk about the elimination of brakes within the ...
  • 官方的Oracle 12C SQL 优化的Student Guide 和Activity Guide.
  • SQL Tuning使用指南

    2008-06-17 19:14:32
    SQL Tuning使用指南 QCO的SQL Tuning模块是一种功能强大的SQL语句分析、测试、优化工具,它可以贯穿Oracle数据库应用的整个生命周期,在开发阶段、试运行阶段和生产阶段帮助获得最佳SQL语句,以提高应用系统的...
  • SQL Tuning sqlServer,数据库教程, chm格式 英文版
  • 1.查看自动收集统计信息的任务及状态 1 sys@ora11g> select client_name,status from dba_autotask_client; 2 3 CLIENT_NAME STATUS 4 ------------------------------------- --------...7 tuning advisor EN.

    1.查看自动收集统计信息的任务及状态

     

    1 sys@ora11g> select client_name,status from dba_autotask_client;
    2 
    3 CLIENT_NAME STATUS
    4 ------------------------------------- --------
    5 auto optimizer stats collection ENABLED
    6 auto space advisor ENABLED
    7 tuning advisor ENABLED
    

     

    其中“auto optimizer stats collection”便是我们要寻找的自动收集统计信息的任务名称,它的状态目前是启用状态。

    2.禁止自动sql tuning advisor的任务
    我们可以使用DBMS_AUTO_TASK_ADMIN包完成这个任务。

     

     

     1 sys@ora11g> exec DBMS_AUTO_TASK_ADMIN.DISABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
     2 
     3 PL/SQL procedure successfully completed.
     4 
     5 sys@ora11g> select client_name,status from dba_autotask_client;
     6 
     7 CLIENT_NAME STATUS
     8 ------------------------------------- --------
     9 auto optimizer stats collection ENABLED
    10 auto space advisor ENABLED
    11 sql tuning advisor DISABLED

     

     

    此时“sql tuning advisor”任务已经被禁用,目的达到。

    3.启用sql tuning advisor的任务

     

     

     1 sys@ora11g> exec DBMS_AUTO_TASK_ADMIN.ENABLE(client_name => 'sql tuning advisor',operation => NULL,window_name => NULL);
     2 
     3 PL/SQL procedure successfully completed.
     4 
     5 sys@ora11g> select client_name,status from dba_autotask_client;
     6 
     7 CLIENT_NAME STATUS
     8 ------------------------------------- --------
     9 auto optimizer stats collection ENABLED
    10 auto space advisor ENABLED
    11 sql tuning advisor ENABLED

     

    展开全文
  • https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-tuning-advisor.html#GUID-35C7B77F-5CB1-45EC-A48D-10C1E47CC7BE -- sql tune advisor的步骤 1 create tuning task 2 execute tu...

    原文地址:

    https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/sql-tuning-advisor.html#GUID-35C7B77F-5CB1-45EC-A48D-10C1E47CC7BE

    -- sql tune advisor的步骤
    1 create tuning task
    2 execute tuning task
    3 report tuning task
    4 implement recommendations

    -- 创建SQL Tuning task ,其中SQL来源是写好的SQL

    SYS@test>conn hr/hr
    Connected.
    HR@test>DECLARE
    my_task_name VARCHAR2(30);
    my_sqltext CLOB;
    BEGIN
    my_sqltext := 'SELECT /*+ ORDERED */ * ' ||
    'FROM employees e, locations l, departments d ' ||
    'WHERE e.department_id = d.department_id AND ' ||
    'l.location_id = d.location_id AND ' ||
    'e.employee_id < :bnd';
    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
    sql_text => my_sqltext
    , bind_list => sql_binds(anydata.ConvertNumber(100))
    , user_name => 'HR'
    , scope => 'COMPREHENSIVE'
    , time_limit => 60
    , task_name => 'STA_SPECIFIC_EMP_TASK'
    , description => 'Task to tune a query on a specified employee'
    );
    END;
    /  2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19   20
    
    PL/SQL procedure successfully completed.
    
    HR@test>

    -- 查看TASK的状态

    COL TASK_ID FORMAT 999999
    COL TASK_NAME FORMAT a25
    COL STATUS_MESSAGE FORMAT a33
    SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM USER_ADVISOR_LOG;
    
    HR@test>SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM USER_ADVISOR_LOG;
    
    TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
    ------- ------------------------- ----------- ---------------------------------
       3659 STA_SPECIFIC_EMP_TASK     INITIAL
    
    HR@test>

    -- 也可以对task进行修改,并查看修改后的参数

    BEGIN
    DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER (
    task_name => 'STA_SPECIFIC_EMP_TASK'
    , parameter => 'TIME_LIMIT'
    , value => 300
    );
    END;
    /
    
    HR@test>COL PARAMETER_NAME FORMAT a25
    COL VALUE FORMAT a15
    SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
    FROM USER_ADVISOR_PARAMETERS
    WHERE TASK_NAME = 'STA_SPECIFIC_EMP_TASK'
    AND PARAMETER_VALUE != 'UNUSED'
    ORDER BY PARAMETER_NAME;HR@test>HR@test>  2    3    4    5
    
    PARAMETER_NAME            VALUE
    ------------------------- ---------------
    DAYS_TO_EXPIRE            30
    DEFAULT_EXECUTION_TYPE    TUNE SQL
    EXECUTION_DAYS_TO_EXPIRE  UNLIMITED
    JOURNALING                INFORMATION
    MODE                      COMPREHENSIVE
    SQL_LIMIT                 -1
    SQL_PERCENTAGE            1
    TARGET_OBJECTS            1
    TEST_EXECUTE              AUTO
    TIME_LIMIT                300
    
    10 rows selected.
    
    HR@test>

    -- 执行 task

    HR@test>exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_SPECIFIC_EMP_TASK');DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_SPECIFIC_EMP_TASK');
    
    PL/SQL procedure successfully completed.
    
    HR@test>
    
    HR@test>SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE
    FROM USER_ADVISOR_LOG;  2
    
    TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
    ------- ------------------------- ----------- ---------------------------------
       3659 STA_SPECIFIC_EMP_TASK     COMPLETED
    
    HR@test>

    -- 监控task的运行进度

    HR@test>SELECT STATUS
    FROM USER_ADVISOR_TASKS
    WHERE TASK_NAME = 'STA_SPECIFIC_EMP_TASK';  2    3
    
    STATUS
    -----------
    COMPLETED
    
    HR@test>
    
    
    VARIABLE my_tid NUMBER;
    EXEC :my_tid := 3659
    COL ADVISOR_NAME FORMAT a20
    COL SOFAR FORMAT 999
    COL TOTALWORK FORMAT 999
    SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
    ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
    FROM V$ADVISOR_PROGRESS
    WHERE TASK_ID = :my_tid;
    
    HR@test>VARIABLE my_tid NUMBER;
    EXEC :my_tid := 3659
    COL ADVISOR_NAME FORMAT a20
    COL SOFAR FORMAT 999
    COL TOTALWORK FORMAT 999
    SELECT TASK_ID, ADVISOR_NAME, SOFAR, TOTALWORK,
    ROUND(SOFAR/TOTALWORK*100,2) "%_COMPLETE"
    FROM V$ADVISOR_PROGRESS
    WHERE TASK_ID = :my_tid;HR@test>
    PL/SQL procedure successfully completed.
    
    HR@test>HR@test>HR@test>HR@test>  2    3    4
    
    TASK_ID ADVISOR_NAME         SOFAR TOTALWORK %_COMPLETE
    ------- -------------------- ----- --------- ----------
       3659 SQL Tuning Advisor       1         1        100
       3659 SQL Tuning Advisor       1         1        100
    
    HR@test>

    -- 显示SQL Tuning task 的结果

    SET LONG 1000  -- 加大到99999 可以显示全 
    SET LONGCHUNKSIZE 1000
    SET LINESIZE 100
    SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_SPECIFIC_EMP_TASK' )
    FROM DUAL;
    
    
    GENERAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    Tuning Task Name   : STA_SPECIFIC_EMP_TASK
    Tuning Task Owner  : HR
    Workload Type      : Single SQL Statement
    Execution Count    : 2
    Current Execution  : EXEC_3712
    Execution Type     : TUNE SQL
    Scope              : COMPREHENSIVE
    Time Limit(seconds): 300
    Completion Status  : COMPLETED
    Started at         : 03/21/2019 10:28:58
    Completed at       : 03/21/2019 10:28:59
    
    -------------------------------------------------------------------------------
    Schema Name: HR
    SQL ID     : dg7nfaj0bdcvk
    SQL Text   : SELECT /*+ ORDERED */ * FROM employees e, locations l,
                 departments d WHERE e.department_id = d.department_id AND
                 l.location_id = d.location_id AND e.employee_id < :bnd
    Bind Variables :
     1 -  (NUMBER):100
    
    -------------------------------------------------------------------------------
    FINDINGS SECTION (1 finding)
    -------------------------------------------------------------------------------
    
    1- Restructure SQL finding (see plan 1 in explain plans section)
    ----------------------------------------------------------------
      在执行计划的行 ID 2 处发现开销很大的笛卡尔积操作。
    
      Recommendation
      --------------
      - 考虑移去 "ORDERED" 提示。
    
    -------------------------------------------------------------------------------
    EXPLAIN PLANS SECTION
    -------------------------------------------------------------------------------
    
    1- Original
    -----------
    Plan hash value: 4162796140
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                      |               |     1 |   122 |     9   (0)| 00:00:01 |
    |*  1 |  HASH JOIN                            |               |     1 |   122 |     9   (0)| 00:00:01 |
    |   2 |   MERGE JOIN CARTESIAN                |               |    23 |  2323 |     6   (0)| 00:00:01 |
    |   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES     |     1 |    52 |     3   (0)| 00:00:01 |
    |*  4 |     INDEX RANGE SCAN                  | EMP_EMP_ID_PK |     1 |       |     2   (0)| 00:00:01 |
    |   5 |    BUFFER SORT                        |               |    23 |  1127 |     3   (0)| 00:00:01 |
    |   6 |     TABLE ACCESS FULL                 | LOCATIONS     |    23 |  1127 |     3   (0)| 00:00:01 |
    |   7 |   TABLE ACCESS FULL                   | DEPARTMENTS   |    27 |   567 |     3   (0)| 00:00:01 |
    -------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "L"."LOCATION_ID"="D"."LOCATION_ID")
       4 - access("E"."EMPLOYEE_ID"<:BND)
    
    -------------------------------------------------------------------------------

    -- 再次tuning 昨天的sts ,SQLT_WKLD_STS

    -- 创建task ,通过sts

    DECLARE my_task_name VARCHAR2(30);
    BEGIN
    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
    sqlset_name  => 'SQLT_WKLD_STS'
    , scope => 'COMPREHENSIVE'
    , time_limit => 60
    , task_name => 'STA_MY_STS'
    , description => 'Task to tune use a STS'
    );
    END;
    
    SH@test>DECLARE my_task_name VARCHAR2(30);
    BEGIN
    my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK (
    sqlset_name  => 'SQLT_WKLD_STS'
    , scope => 'COMPREHENSIVE'
    , time_limit => 60
    , task_name => 'STA_MY_STS'
    , description => 'Task to tune use a STS'
    );
    END;  2    3    4    5    6    7    8    9   10
     11  /
    
    PL/SQL procedure successfully completed.
    
    SH@test>
    

    -- 查看task的状态

    SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM USER_ADVISOR_LOG;
    
    TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
    ------- ------------------------- ----------- ---------------------------------
       3662 STA_MY_STS                INITIAL
    

    -- 执行task

    exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_MY_STS'); 
    SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM USER_ADVISOR_LOG;  
    
    SH@test>exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name=>'STA_MY_STS');
    
    PL/SQL procedure successfully completed.
    
    SH@test>
    SH@test>SELECT TASK_ID, TASK_NAME, STATUS, STATUS_MESSAGE FROM USER_ADVISOR_LOG;
    
    TASK_ID TASK_NAME                 STATUS      STATUS_MESSAGE
    ------- ------------------------- ----------- ---------------------------------
       3662 STA_MY_STS                COMPLETED
    
    SH@test>
    

    --显示sql tuning 的结果

    SET LONG 1000
    SET LONGCHUNKSIZE 1000
    SET LINESIZE 100
    SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'STA_MY_STS' ) FROM DUAL; -- 在sqlplus中显示不全 
    select dbms_sqltune.report_tuning_task(task_name => 'STA_MY_STS',owner_name => 'SH') from dual  -- 在plsql developer中查询出来
    
    
    GENERAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    Tuning Task Name                  : STA_MY_STS
    Tuning Task Owner                 : SH
    Workload Type                     : SQL Tuning Set
    Scope                             : COMPREHENSIVE
    Time Limit(seconds)               : 60
    Completion Status                 : COMPLETED
    Started at                        : 03/21/2019 11:08:14
    Completed at                      : 03/21/2019 11:08:30
    SQL Tuning Set (STS) Name         : SQLT_WKLD_STS
    SQL Tuning Set Owner              : SH
    Number of Statements in the STS   : 10
    
    -------------------------------------------------------------------------------
    SUMMARY SECTION
    -------------------------------------------------------------------------------
                          Global SQL Tuning Result Statistics
    -------------------------------------------------------------------------------
    Number of SQLs Analyzed                      : 10
    Number of SQLs in the Report                 : 5
    Number of SQLs with Findings                 : 5
    Number of SQLs with SQL profiles recommended : 1
    Number of SQLs with Index Findings           : 2
    Number of SQLs with SQL Restructure Findings : 1
    
    -------------------------------------------------------------------------------
        SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
    -------------------------------------------------------------------------------
    object ID  SQL ID        statistics profile(benefit) index(benefit) restructure
    ---------- ------------- ---------- ---------------- -------------- -----------
             5 37rpuv6jc0wbh                      99.81%         99.11%            
             7 bzmnj0nbvmz8t                                     98.85%            
             2 9fhs269dd284z                                                      1
    
    -------------------------------------------------------------------------------
     Tables with New Potential Indices (ordered by schema, number of times, table)
    -------------------------------------------------------------------------------
    Schema Name                 Table Name                  Index Name     Nb Time 
    --------------------------- --------------------------- -------------- --------
                             SH PRODUCTS                    IDX$$_0E4E0004        1
                                SALES                       IDX$$_0E4E0006        1
                                SALES                       IDX$$_0E4E0005        1
    
    -------------------------------------------------------------------------------
    DETAILS SECTION
    -------------------------------------------------------------------------------
     Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
    -------------------------------------------------------------------------------
    Object ID  : 5
    Schema Name: SH
    SQL ID     : 37rpuv6jc0wbh
    SQL Text   : select sum(quantity_sold) from sales s, products p
                 where s.prod_id = p.prod_id
                 and s.amount_sold > 20000 and p.prod_name= 'Linen Big Shirt'
    
    -------------------------------------------------------------------------------
    FINDINGS SECTION (2 findings)
    -------------------------------------------------------------------------------
    
    1- SQL Profile Finding (see explain plans section below)
    --------------------------------------------------------
      为此语句找到了性能更好的执行计划。
    
      Recommendation (estimated benefit: 99.81%)
      ------------------------------------------
      - 考虑接受推荐的 SQL 概要文件。
        execute dbms_sqltune.accept_sql_profile(task_name => 'STA_MY_STS',
                object_id => 5, task_owner => 'SH', replace => TRUE);
    
      Validation results
      ------------------
      已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,
      则另一计划可能只执行了一部分。
    
                               Original Plan  With SQL Profile  % Improved
                               -------------  ----------------  ----------
      Completion Status:            COMPLETE          COMPLETE
      Elapsed Time (s):             .069412           .000046      99.93 %
      CPU Time (s):                  .05263           .000046      99.91 %
      User I/O Time (s):            .013194                 0        100 %
      Buffer Gets:                     1635                 3      99.81 %
      Physical Read Requests:             5                 0        100 %
      Physical Write Requests:            0                 0 
      Physical Read Bytes:            59801                 0        100 %
      Physical Write Bytes:               0                 0 
      Rows Processed:                     1                 1 
      Fetches:                            1                 1 
      Executions:                         1                 1 
    
      Notes
      -----
      1. the original plan 的统计信息是 10 执行的平均值。
      2. the SQL profile plan 的统计信息是 10 执行的平均值。
    
    2- Index Finding (see explain plans section below)
    --------------------------------------------------
      通过创建一个或多个索引可以改进此语句的执行计划。
    
      Recommendation (estimated benefit: 99.11%)
      ------------------------------------------
      - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
        create index SH.IDX$$_0E4E0004 on SH.PRODUCTS("PROD_NAME","PROD_ID");
    
      - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
        create index SH.IDX$$_0E4E0005 on SH.SALES("AMOUNT_SOLD","PROD_ID");
    
      Rationale
      ---------
        创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
        可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。
    
    -------------------------------------------------------------------------------
    EXPLAIN PLANS SECTION
    -------------------------------------------------------------------------------
    
    1- Original With Adjusted Cost
    ------------------------------
    Plan hash value: 3590829658
    
    -------------------------------------------------------------------------------------------------------------
    | Id  | Operation                     | Name        | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT              |             |     1 |    42 |   527   (2)| 00:00:01 |       |       |
    |   1 |  SORT AGGREGATE               |             |     1 |    42 |            |          |       |       |
    |   2 |   NESTED LOOPS                |             |     1 |    42 |   527   (2)| 00:00:01 |       |       |
    |   3 |    NESTED LOOPS               |             |     1 |    42 |   527   (2)| 00:00:01 |       |       |
    |   4 |     PARTITION RANGE ALL       |             |     1 |    12 |   526   (2)| 00:00:01 |     1 |    28 |
    |*  5 |      TABLE ACCESS FULL        | SALES       |     1 |    12 |   526   (2)| 00:00:01 |     1 |    28 |
    |*  6 |     INDEX UNIQUE SCAN         | PRODUCTS_PK |     1 |       |     0   (0)| 00:00:01 |       |       |
    |*  7 |    TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 |    30 |     1   (0)| 00:00:01 |       |       |
    -------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       5 - filter("S"."AMOUNT_SOLD">20000)
       6 - access("S"."PROD_ID"="P"."PROD_ID")
       7 - filter("P"."PROD_NAME"='Linen Big Shirt')
    
    2- Using SQL Profile
    --------------------
    Plan hash value: 4243845115
    
    ---------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |                |     1 |    42 |   452   (1)| 00:00:06 |       |       |
    |   1 |  SORT AGGREGATE                    |                |     1 |    42 |            |          |       |       |
    |*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| SALES          |     4 |    48 |   452   (1)| 00:00:06 |       |       |
    |   3 |    NESTED LOOPS                    |                |     4 |   168 |   452   (1)| 00:00:06 |       |       |
    |*  4 |     TABLE ACCESS FULL              | PRODUCTS       |     1 |    30 |     3   (0)| 00:00:01 |       |       |
    |   5 |     PARTITION RANGE ALL            |                |       |       |            |          |     1 |    28 |
    |   6 |      BITMAP CONVERSION TO ROWIDS   |                |       |       |            |          |       |       |
    |*  7 |       BITMAP INDEX SINGLE VALUE    | SALES_PROD_BIX |       |       |            |          |     1 |    28 |
    ---------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter("S"."AMOUNT_SOLD">20000)
       4 - filter("P"."PROD_NAME"='Linen Big Shirt')
       7 - access("S"."PROD_ID"="P"."PROD_ID")
    
    3- Using New Indices
    --------------------
    Plan hash value: 3310752130
    
    ----------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ----------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                    |                |     1 |    42 |     4   (0)| 00:00:01 |       |       |
    |   1 |  SORT AGGREGATE                     |                |     1 |    42 |            |          |       |       |
    |   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| SALES          |     4 |    48 |     3   (0)| 00:00:01 | ROWID | ROWID |
    |   3 |    NESTED LOOPS                     |                |     4 |   168 |     4   (0)| 00:00:01 |       |       |
    |*  4 |     INDEX RANGE SCAN                | IDX$$_0E4E0004 |     1 |    30 |     1   (0)| 00:00:01 |       |       |
    |*  5 |     INDEX RANGE SCAN                | IDX$$_0E4E0005 |     4 |       |     2   (0)| 00:00:01 |       |       |
    ----------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - access("P"."PROD_NAME"='Linen Big Shirt')
       5 - access("S"."AMOUNT_SOLD">20000 AND "S"."PROD_ID"="P"."PROD_ID")
           filter("S"."PROD_ID"="P"."PROD_ID")
    
    -------------------------------------------------------------------------------
    Object ID  : 7
    Schema Name: SH
    SQL ID     : bzmnj0nbvmz8t
    SQL Text   : select * from sales where amount_sold = 4
    
    -------------------------------------------------------------------------------
    FINDINGS SECTION (1 finding)
    -------------------------------------------------------------------------------
    
    1- Index Finding (see explain plans section below)
    --------------------------------------------------
      通过创建一个或多个索引可以改进此语句的执行计划。
    
      Recommendation (estimated benefit: 98.85%)
      ------------------------------------------
      - 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
        create index SH.IDX$$_0E4E0006 on SH.SALES("AMOUNT_SOLD");
    
      Rationale
      ---------
        创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运行 "访问指导"
        可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护的开销和附加的空间消耗。
    
    -------------------------------------------------------------------------------
    EXPLAIN PLANS SECTION
    -------------------------------------------------------------------------------
    
    1- Original
    -----------
    Plan hash value: 1550251865
    
    ---------------------------------------------------------------------------------------------
    | Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    ---------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT    |       |   256 |  7424 |   526   (2)| 00:00:01 |       |       |
    |   1 |  PARTITION RANGE ALL|       |   256 |  7424 |   526   (2)| 00:00:01 |     1 |    28 |
    |*  2 |   TABLE ACCESS FULL | SALES |   256 |  7424 |   526   (2)| 00:00:01 |     1 |    28 |
    ---------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - filter("AMOUNT_SOLD"=4)
    
    2- Using New Indices
    --------------------
    Plan hash value: 2399532300
    
    -----------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                  | Name           | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
    -----------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                           |                |   256 |  7424 |     6   (0)| 00:00:01 |       |       |
    |   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| SALES          |   256 |  7424 |     6   (0)| 00:00:01 | ROWID | ROWID |
    |*  2 |   INDEX RANGE SCAN                         | IDX$$_0E4E0006 |   256 |       |     3   (0)| 00:00:01 |       |       |
    -----------------------------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       2 - access("AMOUNT_SOLD"=4)
    
    -------------------------------------------------------------------------------
    Object ID  : 2
    Schema Name: SH
    SQL ID     : 9fhs269dd284z
    SQL Text   : select * from sales where quantity_sold < 5
                 union select * from sales where quantity_sold> 500
    
    -------------------------------------------------------------------------------
    FINDINGS SECTION (1 finding)
    -------------------------------------------------------------------------------
    
    1- Restructure SQL finding (see plan 1 in explain plans section)
    ----------------------------------------------------------------
      在执行计划的行 ID 1 处发现开销很大的 "UNION" 操作。
    
      Recommendation
      --------------
      - 如果允许重复或可以保证唯一性, 则考虑用 "UNION ALL" 代替 "UNION"。
    
    -------------------------------------------------------------------------------
    EXPLAIN PLANS SECTION
    -------------------------------------------------------------------------------
    
    1- Original
    -----------
    Plan hash value: 4060794605
    
    -------------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name  | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |
    -------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |       |   918K|    25M|       |  8365   (1)| 00:00:02 |       |       |
    |   1 |  SORT UNIQUE          |       |   918K|    25M|    42M|  8365   (1)| 00:00:02 |       |       |
    |   2 |   UNION-ALL           |       |       |       |       |            |          |       |       |
    |   3 |    PARTITION RANGE ALL|       |   918K|    25M|       |   526   (2)| 00:00:01 |     1 |    28 |
    |*  4 |     TABLE ACCESS FULL | SALES |   918K|    25M|       |   526   (2)| 00:00:01 |     1 |    28 |
    |   5 |    PARTITION RANGE ALL|       |     1 |    29 |       |   526   (2)| 00:00:01 |     1 |    28 |
    |*  6 |     TABLE ACCESS FULL | SALES |     1 |    29 |       |   526   (2)| 00:00:01 |     1 |    28 |
    -------------------------------------------------------------------------------------------------------
     
    Predicate Information (identified by operation id):
    ---------------------------------------------------
     
       4 - filter("QUANTITY_SOLD"<5)
       6 - filter("QUANTITY_SOLD">500)
    
    -------------------------------------------------------------------------------
    Object ID  : 4
    Schema Name: SH
    SQL ID     : 35whmj2v6jq1z
    SQL Text   : DECLARE
                 c_sqlarea_cursor DBMS_SQLTUNE.SQLSET_CURSOR;
                 BEGIN
                 OPEN c_sqlarea_cursor FOR
                 SELECT VALUE(p)
                 FROM TABLE(
                 DBMS_SQLTUNE.SELECT_CURSOR_CACHE(
                 'AND parsing_schema_name = ''SH'' ')
                 ) p;
                 -- load the tuning set
                 DBMS_SQLTUNE.LOAD_SQLSET (
                 sqlset_name => 'SQLT_WKLD_STS'
                 , populate_cursor => c_sqlarea_cursor
                 );
                 END;
    
    -------------------------------------------------------------------------------
    ADDITIONAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    - 不支持的 SQL 语句类型。
    
    -------------------------------------------------------------------------------
    Object ID  : 10
    Schema Name: SH
    SQL ID     : gc81btz8wjs7s
    SQL Text   : BEGIN
                 DBMS_SQLTUNE.CREATE_SQLSET (
                 sqlset_name => 'SQLT_WKLD_STS'
                 , description => 'STS to store SQL from the private SQL area'
                 );
                 END
                 ;
    
    -------------------------------------------------------------------------------
    ADDITIONAL INFORMATION SECTION
    -------------------------------------------------------------------------------
    - 不支持的 SQL 语句类型。
    
    -------------------------------------------------------------------------------
    

    END

    展开全文
  • Sql tuning

    2010-06-22 17:23:47
    比较经典的SQL TUNING资料,不可错过 PPT
  • SQL Tuning

    2013-08-26 09:34:23
    SQL Tuning author Dan Tow outlines a timesaving method he's developed for finding the optimum execution plan--rapidly and systematically--regardless of the complexity of the SQL or the database ...
  • 自动SQL优化工具Tosska SQL Tuning Expert for Oracle,帮助SQL开发人员解决SQL性能问题。 官网下载https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/ 产品介绍...

    自动SQL优化工具Tosska SQL Tuning Expert for Oracle,帮助SQL开发人员解决SQL性能问题。

      

      官网下载 https://tosska.com/tosska-sql-tuning-expert-tse-oracle-free-download/

      产品介绍 https://tosska.com/tosska-sql-tuning-expert-tse-oracle/

      本工具发明人Richard To, 资深ITPUB元老,从1996年开始设计SQL优化工具,拥有超过20年的SQL优化经验。

     

      1. 打开软件,出现如下主界面

       

     

      2. 创建数据库连接,也可以稍后创建。填好连接信息,点击 “Connect” 按钮。

          如果您已经安装Oracle客户端,并且在Oracle客户端配置了TNS,可以在本窗口选择“TNS”作为"Connection Mode",然后在"Database Alias"中选择配置好的TNS作为数据库别名。

       

     

         如果您没有安装Oracle客户端或者不想安装Oracle客户端, 可以选择“Basic Type”作为"Connection Mode",只需数据库服务器IP, 端口和服务名即可。

      

         

      3. 输入有性能问题的SQL 

       

     

      4. 点击Tune按钮,自动生成大量的等价SQL并且开始执行。虽然测试还没有完成,我们已经可以看到 SQL 20 的性能提升了100%。

      

     

         让我们仔细看一下SQL 20, 它使用了两个Hints, 以最快的执行速度脱颖而出。原来的SQL要0.99秒,优化后的SQL执行时间接近0秒。

          由于这条SQL每天要在数据库中执行上万次,优化后可节省大约 165秒的数据库执行时间。

          

     

      最后,用等价的SQL 20 替换 应用程序源代码中有性能问题的SQL。重新编译应用程序,性能得到了提高。

     

      调优任务顺利完成!  

      

       Tosska SQL Tuning Expert Pro for Oracle,除了本文介绍的SQL调优基本功能之外,还能在不修改SQL源码的情况下,调整和稳定SQL执行计划;为批量SQL建议最优索引。

       详情请阅读以下文章:  

       SQL优化神器-Tosska SQL Tuning Expert Pro for Oracle

       SQL优化系列(二)- 查找性能差的SQL

       SQL优化系列(三)- 不修改源码,为批量SQL建议最佳索引

       SQL优化系列(四)- 不修改源码,调整SQL执行计划

    转自:https://www.cnblogs.com/dbexpert/p/8022663.html

    展开全文
  • SQL Tuning Expert for MySQL 是 Tosska 公司推出的针对MySQL的SQL优化工具。 该工具不仅让DBA或者SQL开发人员,轻松阅读和理解执行计划,而且能产生等价SQL,并找出最快的等价SQL. 官网下载 ...

    在这里插入图片描述

    1.概述

    SQL Tuning Expert for MySQL 是 Tosska 公司推出的针对MySQL的SQL优化工具。

    该工具不仅让DBA或者SQL开发人员,轻松阅读和理解执行计划,而且能产生等价SQL,并找出最快的等价SQL.

    官网下载 https://www.tosska.cn/tosska-sql-tuning-expert-for-mysql-tsem-free-download-zh/

    产品介绍 https://www.tosska.cn/tosska-sql-tuning-expert-tse-for-mysql-zh/

    下面开始介绍如何用工具优化SQL.

    1. 创建数据库连接, 也可以稍后创建。

      连接名可以随意填写,也可以用默认值,我个人喜欢用它来标识连接的是哪个数据库。

      填好连接信息,点击 “连接” 按钮。

    在这里插入图片描述
    2. 在SQL编

    展开全文
  • SQL Tuning --通过创建Index性能提升40倍

    千次阅读 2019-04-01 18:16:51
    上周四下午的时候收到Oracle DB的High Disk I/O Loading Session警示邮件,查找发现是如下这段SQL导致High Disk I/O: SELECT * FROM CLW3014 WHERE AUDIT_DATE = (SELECT MAX(AUDIT_DATE)AUDIT_DATE FROM CLW3014 ...
  • 原文地址:https://docs.oracle.com/en/database/oracle/oracle-database/12.2/tgsql/managing-sql-tuning-sets.html#GUID-91D1B886-A6D7-40B8-93D5-112B8C6E6AFE 其实,这个话题是一道OCM考试题,只是考试的时候,...
  • plsql developer显示效果: 按sqladvisor代码的改写的显示效果: 显示成原始查询的显示效果:
  • SQL Turning 是Quest公司出品...SQL Tuning (SQL优化)模块可以对比和评测特定应用中SQL语句的运行性能,提出智能化的优化建议,帮助用户改善应用的响应时间。SQL优化模块具有非介入式SQL采集、自动优化和专家建议等功能
  • Automatic SQL tuning consists of first identifying candidates for the tuning process, tuning the SQL statements, making recommendations, and automatically implementing any SQL profile recommendations....
  • 随着企业数据库的急剧膨胀和日益复杂,DBA为保证数据库性能所付出的努力与日俱增,手工或使用多种无法集成的管理工具,都会给日常管理和维护带来不必要的困难。...和 SQL server 数据库。Quest Central for Databases

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 18,834
精华内容 7,533
关键字:

sqltuning