ORACLE SQL TUNING ADVISOR 使用方法

转载 2017年11月14日 18:14:51

原文地址:http://blog.itpub.net/184303/viewspace-700658/


sql tunning advisor 使用的主要步骤:

  1 建立tunning task

  2 执行task

  3 显示tunning 结果

  4 根据建议来运行相应的调优方法
  
下面来按照这个顺序来实施一遍:

   1  建立测试表以及索引

SQL> CREATE TABLE test_sql_advisor AS SELECT OWNER,OBJECT_NAME,OBJECT_ID FROM DBA_OBJECTS;
 
Table created
 
SQL> select count(*) from test_sql_advisor;
 
  COUNT(*)
----------
    757229

   2  授权 SYSDBA权限登录

SQL> GRANT ADVISOR TO noap;
 
Grant succeeded
SQL> GRANT SELECT_CATALOG_ROLE TO noap;
 
Grant succeeded
SQL> GRANT EXECUTE ON DBMS_SQLTUNE TO noap;
 
Grant succeeded

 3  CREATE TASK

You can create tuning tasks from the following:
-  SQL statement selected by SQL identifier from the cursor cache
-  SQL Tuning Set containing multiple statements
-  Text of a single SQL statement
-  SQL statement selected by SQL identifier from the Automatic Workload Repository.

  上面翻译可以理解为建立调优任务可以通过以下几种方式:
  
   1 通过取得来自cursor cache 的sql_id来指定sql语句来建立任务
   2 sql调优的集合包括的多个语句来建立任务
   3 单一sql语句的文本来建立任务
   4 通过用awr中相应的sql_id来取得sql语句建立任务
   
建立任务主要用的是DBMS_SQLTUNE.CREATE_TUNING_TASK 这个函数,该函数存在重写,下面的贴出来接口
 1 基于SQL文本建立任务

-------------------- create_tuning_task - sql text format ------------------

  -- NAME:

  --     create_tuning_task - CRATE a TUNING TASK in order to tune a single SQL

  --     statement (sql text format)

  --

  -- DESCRIPTION

  --     This function is called to prepare the tuning of a single statement

  --     given its text.

  --     The function mainly creates an advisor task and sets its parameters.

  --

  -- PARAMETERS:

  --     sql_text    (IN) - text of a SQL statement

  --     bind_list   (IN) - a set of bind values

  --     user_name   (IN) - the username for who the statement will be tuned

  --     scope       (IN) - tuning scope (limited/comprehensive)

  --     time_limit  (IN) - maximum duration in second for the tuning session

  --     task_name   (IN) - optional tuning task name

  --     description (IN) - maximum of 256 SQL tuning session description

  --

  -- RETURNS:

  --     SQL tuning task unique name

  --

  -- EXCEPTIONS:

  --     To be done

  -----------------------------------------------------------------------------

  FUNCTION create_tuning_task(

    sql_text    IN CLOB,

    bind_list   IN sql_binds := NULL,

    user_name   IN VARCHAR2  := NULL,

    scope       IN VARCHAR2  := SCOPE_COMPREHENSIVE,

    time_limit  IN NUMBER    := TIME_LIMIT_DEFAULT,

    task_name   IN VARCHAR2  := NULL,

    description IN VARCHAR2  := NULL)

  RETURN VARCHAR2;

2 基于sql_id建立任务
--------------------- create_tuning_task - sql_id format --------------------

  -- NAME:

  --     create_tuning_task - sql_id format

  --

  -- DESCRIPTION

  --     This function is called to prepare the tuning of a single statement

  --     from the Cursor Cache given its identifier.

  --     The function mainly creates an advisor task and sets its parameters.

  --

  -- PARAMETERS:

  --     sql_id          (IN) - identifier of the statement

  --     plan_hash_value (IN) - hash value of the sql execution plan

  --     scope           (IN) - tuning scope (limited/comprehensive)

  --     time_limit      (IN) - maximum tuning duration in second

  --     task_name       (IN) - optional tuning task name

  --     description     (IN) - maximum of 256 SQL tuning session description

  --

  -- RETURNS:

  --     SQL tuning task unique name

  --

  -- EXCEPTIONS:

  --     To be done

  -----------------------------------------------------------------------------

  FUNCTION create_tuning_task(

    sql_id          IN VARCHAR2,

    plan_hash_value IN NUMBER   := NULL,

    scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,

    time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,

    task_name       IN VARCHAR2 := NULL,

    description     IN VARCHAR2 := NULL)

  RETURN VARCHAR2;

3 基于AWR快照间隔以及相应SQL_ID建立任务

-------------- create_tuning_task - workload repository format --------------

  -- NAME:

  --     create_tuning_task - workload repository format

  --

  -- DESCRIPTION

  --     This function is called to prepare the tuning of a single statement

  --     from the workload repository given a range of snapshot identifiers.

  --     The function mainly creates an advisor task and sets its parameters.

  --

  -- PARAMETERS:

  --     begin_snap      (IN) - begin snapshot identifier

  --     end_snap        (IN) - end snapshot identifier

  --     sql_id          (IN) - identifier of the statement

  --     plan_hash_value (IN) - plan hash value

  --     scope           (IN) - tuning scope (limited/comprehensive)

  --     time_limit      (IN) - maximum duration in second for tuning

  --     task_name       (IN) - optional tuning task name

  --     description     (IN) - maximum of 256 SQL tuning session description

  --

  -- RETURNS:

  --     SQL tuning task unique name

  --

  -- EXCEPTIONS:

  --     To be done

  -----------------------------------------------------------------------------

  FUNCTION create_tuning_task(

    begin_snap      IN NUMBER,

    end_snap        IN NUMBER,

    sql_id          IN VARCHAR2,

    plan_hash_value IN NUMBER   := NULL,

    scope           IN VARCHAR2 := SCOPE_COMPREHENSIVE,

    time_limit      IN NUMBER   := TIME_LIMIT_DEFAULT,

    task_name       IN VARCHAR2 := NULL,

    description     IN VARCHAR2 := NULL)

  RETURN VARCHAR2;

当然还有基于sqlset,以及SQL Performance Analyzer (SPA) task  建立任务的函数 这个以后再写专题吧

sample的建立是基于sql文本来做实验的,如下所示


DECLARE
  MY_TASK_NAME VARCHAR2(30);
   MY_SQLTEXT CLOB;
BEGIN
  MY_SQLTEXT :='SELECT * FROM TEST_OBJECT_TTX WHERE OBJECT_ID = :BND';
  MY_TASK_NAME := DBMS_SQLTUNE.CREATE_TUNING_TASK(SQL_TEXT => MY_SQLTEXT,
                  BIND_LIST=>SQL_BINDS(ANYDATA.CONVERTNUMBER(9)),
                  USER_NAME => 'NOAP',
                  SCOPE=>'COMPREHENSIVE',
                  TIME_LIMIT => 60,
                  TASK_NAME =>  'SQL_TUNING_TEST',
                  DESCRIPTION=>'TUNING TASK'
                  );

END;

建立后的状态为INITIAL 因为还没执行

4 EXECUTE TASK

SQL> BEGIN DBMS_SQLTUNE.EXECUTE_TUNING_TASK('SQL_TUNING_TEST'); END;
  2  /
 
PL/SQL procedure successfully completed


SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name ='SQL_TUNING_TEST';
 
STATUS
-----------
COMPLETED

5 查询建议结果

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_TEST') FROM DUAL;

该语句是一个CLOB字段的结果 点击自行查看 根据相应的建议优化sql

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : SQL_TUNING_TEST
Tuning Task Owner  : NOAP
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 06/24/2011 12:45:20
Completed at       : 06/24/2011 12:45:22
-------------------------------------------------------------------------------
Schema Name: NOAP
SQL ID     : 5k6fk8cynf60x
SQL Text   : SELECT * FROM TEST_SQL_ADVISOR WHERE OBJECT_ID = :BND
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Statistics Finding
---------------------
  尚未分析表 "NOAP"."TEST_SQL_ADVISOR"。
  Recommendation
  --------------
  - 考虑收集此表的优化程序统计信息。
    execute dbms_stats.gather_table_stats(ownname => 'NOAP', tabname =>
            'TEST_SQL_ADVISOR', estimate_percent =>
            DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
            AUTO');
  Rationale
  ---------
    为了选择好的执行计划, 优化程序需要此表的最新统计信息。
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 719217330
----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                 | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                      |     1 |    96 |     2   (0)| 00:03:18 |
|   1 |  TABLE ACCESS BY INDEX ROWID| TEST_SQL_ADVISOR     |     1 |    96 |     2   (0)| 00:03:18 |
|*  2 |   INDEX RANGE SCAN          | TEST_SQL_ADVISOR_IDX |     1 |       |     1   (0)| 00:01:39 |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("OBJECT_ID"=:BND)
-------------------------------------------------------------------------------

6 删除任务的方法

BEGIN  dbms_sqltune.drop_tuning_task('SQL_TUNING_TEST'); END;

7 可以用到的视图

            SELECT * FROM USER_ADVISOR_TASKS T WHERE TASK_NAME='SQL_TUNING_TEST';
            SELECT * FROM DBA_SQLTUNE_STATISTICS
            SELECT * FROM DBA_SQLTUNE_BINDS
            SELECT * FROM DBA_SQLTUNE_PLANS WHERE TASK_ID=13009


相关文章推荐

利用oracle sql tuning advisor 进行sql调优

sql tunning advisor 使用的主要步骤:   1 建立tunning task   2 执行task  3 显示tunning 结果  4 根据建议来运行相应的调优方法下面来按照这个顺...

oracle 10gADDM 和 SQL Tuning Advisor

  • 2008年09月02日 18:10
  • 347KB
  • 下载

SQL Tuning Advisor 使用11G的自动调优建议

ORACLE 提供了自动SQL优化的工具,当对DBA来说 可以节约下思考的时间,或许提供更多的参考价值...

SQL Tuning Advisor使用实例

在oracle10g之前,想要优化一个sql语句是比较麻烦,但是在oracle10g这个版本推出的SQL Tuning Advisor这个工具,能大大减少sql调优的工作量,不过要想使用SQL Tun...

初次使用SQL调优建议工具--SQL Tuning Advisor

在10g中,Oracle推出了自己的SQL优化辅助工具: SQL优化器(SQL Tuning Advisor :STA),它是新的DBMS_SQLTUNE包。使用STA一定要保证优化器是CBO模式下。...

Toad Oracle tuning advisor time out then change to use package DBMS_SQLTUNE

1. Advisor tuning results below GENERAL INFORMATION SECTION --------------------------------------...

SQL Tuning Advisor

试着回答: A) Access path analysis is not performed for the SQL statements.  B) SQL structure analysi...

Automatic SQL Tuning Advisor

在10gR2中,提供了$ORACLE_HOME/rdbms/admin/sqltrpt.sql脚本,用于抽取占用资源较多的sql,并可以为指定的sql生成执行计划,资源占用较多的sql分为两部分 1...
  • rgb_rgb
  • rgb_rgb
  • 2012年08月26日 19:09
  • 1532

SQL Tuning Advisor(SQL调优顾问,STA)

SQL调优化问需要一个或多个SQL语句作为输入,并调用自动优化器执行SQL调优。SQL调优顾问输出是以一种意见或者建议的形式,以及对每一项建议和期望效益的理由。该建议涉及对象的统计收集,新索引的创建,...

用 SQL Tuning Advisor (STA) 优化SQL语句

转自http://blog.csdn.net/tianlesoftware/article/details/5630888 在Oracle10g之前,优化SQL是个比较费力的技术活,不停的分析执行计...
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:ORACLE SQL TUNING ADVISOR 使用方法
举报原因:
原因补充:

(最多只允许输入30个字)