![c482f6d171e12a7535747c134f804d10.png](https://i-blog.csdnimg.cn/blog_migrate/861bb6a379de45ab6198af60e6d5d3d5.jpeg)
很多时候,在定位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 Informatio
从上面的调优报告可见,报告里已给出具体的调优操作方案,
如创建缺失索引,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;
/
欢迎光临天凯科技 - DBA服务平台dbs-service.cn
![a383a0fca1474c02ebc6483f78cb27c4.png](https://i-blog.csdnimg.cn/blog_migrate/2b47462e32b394f352a1199b86b5c228.png)
天凯DBS-数据库资源共享平台,平台有海量数据库实战案例资源,DBA视频教程,数据库软件工具等资源。十年DBA企业实战经验,工作项目中积累了不少经验与资源,在此,跟各位朋友分享,请关注我们网站,工作中碰到的案例,教程资源我们会持续更新,敬请留意!