ACCESSDBMS_SQLTUNE使用方式

SQL调优工具包DBMS_SQLTUNE的动方式

oracle
提供了优化建议功能包DBMS_SQLTUNE,该包可以协助我们解析SQL,并提供优化建议。

老执行计划
alter session set statistics_level=all;
set serveroutput off
select * from test.emp where ename=’SCOTT’ and DEPTNO=20;
SELECT * FROM
table(dbms_xplan.display_cursor(NULL,NULL,’runstats_last’));

PLAN_TABLE_OUTPUT

SQL_ID  8k1gbrapm7zpd, child number 0

select * from test.emp where ename=’SCOTT’ and DEPTNO=20

Plan hash value: 3956160932


| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time  

| Buffers |

|   0 | SELECT STATEMENT  |      |      1 |        |      1 |00:00:00.01
|       4 |
|*  1 |  TABLE ACCESS FULL| EMP  |      1 |      1 |      1

|00:00:00.01 |       4 |

Predicate Information (identified by operation id):

   1 – filter((“ENAME”=’SCOTT’ AND “DEPTNO”=20))

脚就是因此DBMS_SQLTUNE优化该SQL
–1.予用户ADVISOR权限
grant ADVISOR  to test;

–2.创建sql tuning任务
conn test/test

DECLARE
 my_task_name VARCHAR2(30);
 my_sqltext   CLOB;
BEGIN
 my_sqltext := ‘select * from emp where ename= :name and DEPTNO=
:deptno’;
 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
         sql_text    => my_sqltext,
bind_list   =>
sql_binds(anydata.convertvarchar2(10),anydata.convertnumber(2)), 
         user_name   => ‘TEST’,
         scope       => ‘COMPREHENSIVE’,
         time_limit  => 60,
         task_name   => ‘test_sql_tuning’, 
         description => ‘Task to tune a query on emp’);
END;
/

参数说明:
bind_list:多单绑定变量以’,’逗号分隔。参数值一定要依据绑定变量对应的排列的路书写.
如果:emp.ename类型是VARCHAR2(10),那么将写成
 bind_list    =>sql_binds(anydata.convertvarchar2(10)),

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;

/*2014-4-8日增加 end*/

–3.翻看任务名 SELECT TASK_NAME 
FROM   DBA_ADVISOR_LOG 
WHERE  OWNER = ‘TEST’;

TASK_NAME

test_sql_tuning

–4.执行sql tuning任务
BEGIN
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>
‘test_sql_tuning’ );
END;
/

–5.查看sql tunning任务状态
SELECT status 
FROM   USER_ADVISOR_TASKS 
WHERE  task_name = ‘test_sql_tuning’;

STATUS

COMPLETED

–6.展示sql tunning结果
SET LONG 10000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(‘test_sql_tuning’)
FROM   DUAL;

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)

GENERAL INFORMATION SECTION

Tuning Task Name   : test_sql_tuning
Tuning Task Owner  : TEST
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 60
Completion Status  : COMPLETED
Started at         : 04/01/2014 16:45:16
Completed at       : 04/01/2014 16:45:17

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)


Schema Name: TEST
SQL ID     : 95fv6dbj64d0f
SQL Text   : select * from emp where ename= :name and DEPTNO= :deptno


FINDINGS SECTION (2 findings)

1- Statistics Finding

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)

  Table “TEST”.”EMP” was not analyzed.

  Recommendation
  ————–
  – Consider collecting optimizer statistics for this table.
    execute dbms_stats.gather_table_stats(ownname => ‘TEST’,
tabname =>

            ‘EMP’, estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE,

            method_opt => ‘FOR ALL COLUMNS SIZE AUTO’);

  Rationale
  ———

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)

    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: 66.67%)
  ——————————————
  – Consider running the Access Advisor to improve the physical schema
design

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)


    or creating the recommended index.
    create index TEST.IDX$$_00D80001 on
TEST.EMP(“ENAME”,”DEPTNO”);

  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.

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)


EXPLAIN PLANS SECTION

1- Original

Plan hash value: 3956160932


| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time    

|

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)

|   0 | SELECT STATEMENT  |      |     1 |    87 |     3   (0)| 00:00:01
|
|*  1 |  TABLE ACCESS FULL| EMP  |     1 |    87 |     3   (0)|

00:00:01 |

Predicate Information (identified by operation id):

   1 – filter(“ENAME”=:NAME AND “DEPTNO”=:DEPTNO)

2- Using New Indices

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)

Plan hash value: 2106247215

| Id  | Operation                   | Name           | Rows  | Bytes |

Cost (%CPU)| Time     |

|   0 | SELECT STATEMENT            |                |     1 |    87 |  
  1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    87 |  
  1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_00D80001 |     1 |       |

    1   (0)| 00:00:01 |

Predicate Information (identified by operation id):

DBMS_SQLTUNE.REPORT_TUNING_TASK(‘TEST_SQL_TUNING’)


   2 – access(“ENAME”=:NAME AND “DEPTNO”=:DEPTNO)


建议报告总结:
<1>收集EMP表的统计信息
execute dbms_stats.gather_table_stats(ownname => ‘TEST’, tabname
=>’EMP’, estimate_percent =>
DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt => ‘FOR ALL COLUMNS SIZE
AUTO’);

<2>创建索引
create index TEST.IDX$$_00D80001 on TEST.EMP(“ENAME”,”DEPTNO”); 

优化后实施计划

| Id  | Operation                   | Name           | Starts | E-Rows |

A-Rows |   A-Time   | Buffers |

|   0 | SELECT STATEMENT            |                |      1 |        |
     1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP            |      1 |      1 |
     1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN          | IDX$$_00D80001 |      1 |      1

|      1 |00:00:00.01 |       2 |

Predicate Information (identified by operation id):

   2 – access(“ENAME”=’SCOTT’ AND “DEPTNO”=20)

–7.到位后去除sql tunning任务
EXEC DBMS_SQLTUNE.DROP_TUNING_TASK(‘test_sql_tuning’);

–8.其他
–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’;

如上因oracle doc整理
http://docs.oracle.com/cd/E11882\_01/server.112/e41573/sql\_tune.htm\#PFGRF94856
转载:
http://blog.chinaunix.net/uid-23284114-id-4192029.html

相关文章