Sql Tuning Advisor 使用方法

Sql Tuning Advisor
  1.使用步骤：
    

?创建优化任务

?执行优化任务

?查看建议

?确认建议（实施建议）

2.使用案例：
    2.1 创建优化任务：
    

DECLARE

  my_task_name VARCHAR2(30);

  my_sqltext CLOB;

BEGIN

  my_sqltext := 'SELECT * '   ||

                'FROM sales ' || 'WHERE prod_id = 10 AND ' || 'cust_id = 100 ';

  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(

                           sql_text => my_sqltext,

                           user_name => 'SH',

                           scope => 'COMPREHENSIVE',

                           time_limit => 60,

                           task_name => 'TEST_sql_tuning_task',

                           description => 'Sample Task');

END;



第二种方式：



DECLARE
  my_task_name VARCHAR2(30);
  my_sqltext   CLOB;
BEGIN
 select sql_fulltext into my_sqltext from v$sqlarea where sql_id='bhz4hquh8kf2a';
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => my_sqltext,
                                                  user_name   => 'UOP_CRM1',
                                                  scope       => 'COMPREHENSIVE',
                                                  time_limit  => 60,
                                                  task_name   => 'test_sql_tuning_task_ming',
                                                  description => 'Task to tune a query');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'test_sql_tuning_task_ming');
END;    

2.2 执行优化任务：
     

Execute dbms_sqltune.Execute_tuning_task (task_name => 'TEST_sql_tuning_task');

2.3  查看优化建议

set long 65536

set longchunksize 65536

set linesize 100

select dbms_sqltune.report_tuning_task('TEST_sql_tuning_task') from dual;

  
   

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

SQL ID  : 9bxw71yp99fr6

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

SQL Text: SELECT * FROM sales WHERE prod_id = 10 AND cust_id = 100

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

FINDINGS SECTION (5 findings)

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

1- Statistics Finding

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

  Index "SH"."SALES_PROMO_BIX" was not analyzed.

  Recommendation

    Consider collecting optimizer statistics for this index.

    execute dbms_stats.gather_index_stats(ownname => 'SH', indname =>

            'SALES_PROMO_BIX', estimate_percent =>

2.4  
删除任务的方法

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

2.5 可以用到的视图

            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

案例分析：


我们现在创建一个测试表，看此时正确的执行计划
13:11:53 scott@orcl> select * from t2 where empno=200;


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("EMPNO"=200)




Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1092  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed


我们使用hint来强制走一个错误的执行计划
13:11:58 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |    39 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("EMPNO"=200)




Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         47  consistent gets
          0  physical reads
          0  redo size
       1088  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
现在我们使用sqltune advisor来进行调整
创建TUNING_TASK并执行
declare
  l_task_name varchar2(30);
  l_sql       clob;
begin
  l_sql       := 'select /*+ full(t2) */ * from t2 where empno=200';
  l_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text    => l_sql,
                                                 user_name   => 'SCOTT',
                                                 scope       => 'COMPREHENSIVE',
                                                 time_limit  => 60,
                                                 task_name   => 'test01',
                                                 description => null);
end;
/


time_limit：执行的最长时间，默认是60。
scope：
LIMITED，用大概1秒时间去优化SQL语句，但是并不进行SQL Profiling分析。
COMPREHENSIVE，进行全面分析，包含SQL Profiling分析；比LIMITED用时更长。


**也可以用sql_id创建sql tunning任务，比用sql_text方便很多
FUNCTION CREATE_TUNING_TASK RETURNS VARCHAR2
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 SQL_ID                         VARCHAR2                IN
 PLAN_HASH_VALUE                NUMBER                  IN     DEFAULT
 SCOPE                          VARCHAR2                IN     DEFAULT
 TIME_LIMIT                     NUMBER                  IN     DEFAULT
 TASK_NAME                      VARCHAR2                IN     DEFAULT
 DESCRIPTION                    VARCHAR2                IN     DEFAULT


DECLARE
 my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         SQL_ID      => 'ddw7j6yfnw0vz',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'tunning_task_ddw7j6yfnw0vz', 
         description => 'Task to tune a query on  ddw7j6yfnw0vz');
END;
/ 


我们查看此时任务的状态
13:27:53 scott@orcl> select task_name,EXECUTION_START,EXECUTION_END,STATUS from DBA_ADVISOR_LOG where task_name like 'test%';


TASK_NAME                      EXECUTION_START     EXECUTION_END       STATUS
------------------------------ ------------------- ------------------- -----------
test01                                                                 INITIAL


执行sql tuning任务
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'test01' );
END;
/




展示sql tunning结果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('test01')
FROM   DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TASK('TEST01')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : test01
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 12/21/2014 13:29:11
Completed at       : 12/21/2014 13:29:15


-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 3bgc9fc2fp597
SQL Text   : select /*+ full(t2) */ * from t2 where empno=200


-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------


1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
  A potentially better execution plan was found for this statement.


  Recommendation (estimated benefit: 93.46%)
  ------------------------------------------
  - Consider accepting the recommended SQL profile.
    execute dbms_sqltune.accept_sql_profile(task_name => 'test01', task_owner
            => 'SCOTT', replace => TRUE);


  Validation results
  ------------------
  The SQL profile was tested by executing both its plan and the original plan
  and measuring their respective execution statistics. A plan may have been
  only partially executed if the other could be run to completion in less time.


                           Original Plan  With SQL Profile  % Improved
                           -------------  ----------------  ----------
  Completion Status:            COMPLETE          COMPLETE
  Elapsed Time (s):             .000378           .000098      74.07 %
  CPU Time (s):                 .000299           .000099      66.88 %
  User I/O Time (s):                  0                 0
  Buffer Gets:                       46                 3      93.47 %
  Physical Read Requests:             0                 0
  Physical Write Requests:            0                 0
  Physical Read Bytes:                0                 0
  Physical Write Bytes:               0                 0
  Rows Processed:                     1                 1
  Fetches:                            1                 1
  Executions:                         1                 1


  Notes
  -----
  1. Statistics for the original plan were averaged over 10 executions.
  2. Statistics for the SQL profile plan were averaged over 10 executions.


-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


1- Original With Adjusted Cost
------------------------------
Plan hash value: 1513984157


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |    39 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   |     1 |    39 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("EMPNO"=200)


2- Using SQL Profile
--------------------
Plan hash value: 2008370210


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("EMPNO"=200)


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


我们可以看到他提供的建议，执行sql_profile，我们根据他的建议执行这个profile
execute dbms_sqltune.accept_sql_profile(task_name => 'test01',task_owner=> 'SCOTT',replace => TRUE);


然后我们再来执行下原来的带hint的语句
select /*+ full(t2) */ * from t2 where empno=200;
13:39:32 scott@orcl> select /*+ full(t2) */ * from t2 where empno=200;


Elapsed: 00:00:00.01


Execution Plan
----------------------------------------------------------
Plan hash value: 2008370210


--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("EMPNO"=200)


Note
-----
   - SQL profile "SYS_SQLPROF_014a6b5a4a4a0000" used for this statement


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
       1092  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
可以看到使用了sql_profile 从而走了正确的执行计划


现在我们再来看看其他的情况，我们原来的表上没有索引，看看tune advisor能提供什么样的建议
13:42:44 scott@orcl> select * from t4 where empno=200;


Elapsed: 00:00:00.04


Execution Plan
----------------------------------------------------------
Plan hash value: 2560505625


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   100 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T4   |     1 |   100 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("EMPNO"=200)


Note
-----
   - dynamic sampling used for this statement (level=2)




Statistics
----------------------------------------------------------
         14  recursive calls
          0  db block gets
        114  consistent gets
         50  physical reads
          0  redo size
       1088  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed
此时表是没有索引的，走的全表扫描
我们使用sql_id的方式来创建task
13:45:41 scott@orcl> select sql_text,sql_id from v$sql where sql_text like 'select * from t4%';


SQL_TEXT                                                     SQL_ID
------------------------------------------------------------ -------------
select * from t4 where empno=200                             5avs113b5fn8v


DECLARE
 my_task_name VARCHAR2(30);
BEGIN
  my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         SQL_ID      => '5avs113b5fn8v',
         scope       => 'COMPREHENSIVE',
         time_limit  => 60,
         task_name   => 'tunning_task_5avs113b5fn8v', 
         description => 'Task to tune a query on  5avs113b5fn8v');
END;
/ 


启动这个task
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'tunning_task_5avs113b5fn8v' );
END;
/


查看report
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('tunning_task_5avs113b5fn8v')
FROM   DUAL;


DBMS_SQLTUNE.REPORT_TUNING_TASK('TUNNING_TASK_5AVS113B5FN8V')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : tunning_task_5avs113b5fn8v
Tuning Task Owner  : SCOTT
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 12/21/2014 13:48:02
Completed at       : 12/21/2014 13:48:03


-------------------------------------------------------------------------------
Schema Name: SCOTT
SQL ID     : 5avs113b5fn8v
SQL Text   : select * from t4 where empno=200


-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------


1- Statistics Finding
---------------------
  Table "SCOTT"."T4" was not analyzed.


  Recommendation
  --------------
  - Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
            'T4', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt
            => 'FOR ALL COLUMNS SIZE AUTO');


  Rationale
  ---------
    The optimizer requires up-to-date statistics for the table in order to
    select a good execution plan.


2- Index Finding (see explain plans section below)
--------------------------------------------------
  The execution plan of this statement can be improved by creating one or more
  indices.


  Recommendation (estimated benefit: 86.7%)
  -----------------------------------------
  - Consider running the Access Advisor to improve the physical schema design
    or creating the recommended index.
    create index SCOTT.IDX$$_00540001 on SCOTT.T4("EMPNO");


  Rationale
  ---------
    Creating the recommended indices significantly improves the execution plan
    of this statement. However, it might be preferable to run "Access Advisor"
    using a representative SQL workload as opposed to a single statement. This
    will allow to get comprehensive index recommendations which takes into
    account index maintenance overhead and additional space consumption.


-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------


1- Original
-----------
Plan hash value: 2560505625


--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   100 |    15   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T4   |     1 |   100 |    15   (0)| 00:00:01 |
--------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   1 - filter("EMPNO"=200)


2- Using New Indices
--------------------
Plan hash value: 3508715929


----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |   100 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T4             |     1 |   100 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_00540001 |     1 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------


   2 - access("EMPNO"=200)


-------------------------------------------------------------------------------
可以看到 sql_tune advisor提供了建议在empno 列上面创建索引，可见分析的还是很准确的




删除tune_tast
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK('test01');


其他
--sql tunning任务创建后，也可以修改参数
BEGIN
  DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(
    task_name => 'test_sql_tuning',
    parameter => 'TIME_LIMIT', value => 300);
END;
/


--查看SQL Tuning Advisor的进展（task执行很久）
col opname for a20
col ADVISOR_NAME for a20
SELECT SID,SERIAL#,USERNAME,OPNAME,ADVISOR_NAME,TARGET_DESC,START_TIME SOFAR, TOTALWORK 
FROM   V$ADVISOR_PROGRESS 
WHERE  USERNAME = 'TEST';

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/25462274/viewspace-2120751/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/25462274/viewspace-2120751/