Oracle手工调用SQL TUNING调优神器

很多时候，在定位SQL的性能问题时，需要借助Oracle的调优工具进行分析，该工具从SQL本身的COST出发，利用动态采样数据，可以分析出目前执行计划的合理性，并提出访问路径的优化建议，由于该工具集成在EM后台里，有时候客户没有安装EM，或端口限制因素，导致无法调用，下面方案将通过原始方式调用SQL TUNNING包，从而可以很方便地使用该工具进行SQL性能定位!

先查语句的SQL_ID:

select sql_id,sql_text from v$sql where sql_text like 'select /*+ full(t)*/* from t where object_id=%'

记下sql_id直接调用下面PLSQL（记得在命令窗口下用sys执行）：

Set serveroutput On

declare

tuning_task varchar2(30);

begin

  tuning_task:=dbms_sqltune.create_tuning_task(sql_id => '0bu6uxy86b6gp');

  dbms_output.put_line(tuning_task);

end;

/

任务_20008

记下该任务名’任务_20008’

执行调优包：

exec dbms_sqltune.execute_tuning_task('任务_20008');

通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。

SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = '任务_20008';

查看调优报告：

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('任务_20008') FROM DUAL;

GENERAL INFORMATION SECTION

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

Tuning Task Name : sql_tuning_test

Tuning Task Owner : SYS

Workload Type : Single SQL Statement

Scope : COMPREHENSIVE

Time Limit(seconds): 60

Completion Status : COMPLETED

Started at : 03/18/2013 10:36:10

Completed at : 03/18/2013 10:36:10

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

Schema Name: SYS

SQL ID : 6rqdjwjwp53p0

SQL Text : select /*+ full(t)*/* from t where object_id=300

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

FINDINGS SECTION (1 finding)

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

1- SQL Profile Finding (see explain plans section below)

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

  为此语句找到了性能更好的执行计划。

  Recommendation (estimated benefit: 99.73%)

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

  - 考虑接受推荐的 SQL 概要文件。

    execute dbms_sqltune.accept_sql_profile(task_name => 'sql_tuning_test',

            task_owner => 'SYS', replace => TRUE);

  Validation results

已对 SQL profile 进行测试, 方法为执行其计划和原始计划并测量与计划相对应的执行统计信息。如果其中一个计划运行在很短的时间内就完成,

则另一计划可能只执行了一部分。

                           Original Plan With SQL Profile % Improved

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

  Completion Status: COMPLETE COMPLETE

  Elapsed Time (s): .005013 .00003 99.4 %

  CPU Time (s): .005099 0 100 %

  User I/O Time (s): 0 0

  Buffer Gets: 1118 3 99.73 %

  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: 1601196873

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

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

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

| 0 | SELECT STATEMENT | | 1 | 98 | 304 (1)| 00:00:04 |

|* 1 | TABLE ACCESS FULL| T | 1 | 98 | 304 (1)| 00:00:04 |

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

Predicate Information (identified by operation id):

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

1 - filter("OBJECT_ID"=300)

2- Using SQL Profile

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

Plan hash value: 1295815799

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

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

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

| 0 | SELECT STATEMENT | | 1 | 98 | 2 (0)| 00:00:01 |

| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 98 | 2 (0)| 00:00:01 |

|* 2 | INDEX RANGE SCAN | T_ID | 1 | | 1 (0)| 00:00:01 |

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

 

Predicate Information (identified by operation id):

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

 

   2 - access("OBJECT_ID"=300)

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

从上面的调优报告可见，报告里已给出具体的调优操作方案，

如创建缺失索引，SQL概要文件应用等关键步骤，用户可根据自身DB的情况选择执行即可！

下面也可以手工，删除调优任务（调优任务里的分析元数据，不处理也没问题）：

exec dbms_sqltune.drop_tuning_task(‘sql_tuning_test’);

删除SQL PROFILE:

先查看视图：

select * from dba_sql_profiles

SELECT name, created,category,sql_Text from dba_sql_profiles ORDER BY created DESC;

BEGIN

  DBMS_SQLTUNE.drop_sql_profile (

    name => 'SYS_SQLPROF_013df850fac40004',

    ignore => TRUE);

END;

/

天凯科技-运维资讯

专注高价值运维方案共享服务；网站具有丰富的数据库实战方案，系统运维方案，高价值精品视频教程等；
会员QQ群：532272497
客服QQ：282321952