利用DBMS_SQLTUNE优化SQL
DBMS_SQLTUNE优化SQL是在oracle10g才出来的新特性,使用它能很大程度上方便对sql的分析和优化。执行DBMS_SQLTUNE包进行sql优化需要有advisor的权限:
stat>grant advisor to stat;
Grant succeeded.
使用DBMS_SQLTUNE包进行SQL优化,大致可以分为四个步骤:
创建优化任务
执行优化任务
显示优化任务的结果
按照建议执行优化
一、创建优化任务
使用dbms_sqltune.create_tuning_task函数来创建优化任务,该参数的声明 可以通过 desc dbms_sqltune.create_tuning_task 来查看。以下是一些主要的参数的意义。
Parameter Description
sql_text The text of a SQL statement
begin_snap Begin snapshot identifier
end_snap End snapshot identifier
sql_id The identifier of a SQL statement
bind_list An ordered list of bind values in ANYDATA type
plan_hash_value The hash value of the SQL execution plan
sqlset_name The SQL tuning set name
basic_filter The SQL predicate to filter the SQL from the SQL tuning set
object_filter The object filter
rank(i) An order-by clause on the selected SQL
result_percentage A percentage on the sum of a ranking measure
result_limit The top L(imit) SQL from the (filtered/ranked) SQL
user_name The username for whom the statement is to be tuned
scope Tuning scope (limited/comprehensive)
time_limit The maximum duration in seconds for the tuning session
task_name An optional tuning task name
description A task of the SQL tuning session to a maximum of 256 characters
函数的返回值为创建的任务名。下面我们创建一个叫sql_tuning_test的任务:
代码:--------------------------------------------------------------------------------
DECLARE
my_task_name VARCHAR2 (30);
my_sqltext CLOB;
BEGIN
my_sqltext := 'select * from tj_balance_cid_200907 where clientcode=''sadfasfds''';
my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
bind_list => null,
user_name => 'STAT',
scope => 'COMPREHENSIVE',
time_limit => 60,
task_name => 'vega_tuning_task',
description => 'Tuning Task'
);
END;
/
PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------
二、执行优化任务
通过调用dbms_sqltune.execute_tuning_task过程来执行前面创建好的优化任务。
exec dbms_sqltune.execute_tuning_task('sql_tuning_test');
PL/SQL procedure successfully completed.
--------------------------------------------------------------------------------
三、检查优化任务的状态
通过查看user_advisor_tasks/dba_advisor_tasks视图可以查看优化任务的当前状态。
代码:--------------------------------------------------------------------------------
SELECT status FROM USER_ADVISOR_TASKS WHERE task_name ='sql_tuning_test';
STATUS
-----------
COMPLETED
--------------------------------------------------------------------------------
四、得到优化任务执行的结果
通过dbms_sqltune.report_tning_task函数可以获得优化任务的结果。
代码:--------------------------------------------------------------------------------
SET LONG 999999
set serveroutput on size 999999
SET LINESIZE 1000
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('sql_tuning_test') FROM DUAL;
DBMS_SQLTUNE.REPORT_TUNING_TAS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : vega_tuning_task
Tuning Task Owner : STAT
Workload Type : Single SQL Statement
Execution Count : 2
Current Execution : EXEC_4345_1
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 08/04/2009 09:41:09
Completed at : 08/04/2009 09:41:10
-------------------------------------------------------------------------------
Schema Name: STAT
SQL ID : fgj1uaa7dkgt8
SQL Text : select * from tj_balance_cid_200907 where clientcode='sadfasfds'
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Index Finding (see explain plans section below)
--------------------------------------------------
通过创建一个或多个索引可以改进此语句的执行计划。
Recommendation (estimated benefit: 94.36%)
------------------------------------------
- 考虑运行可以改进物理方案设计的访问指导或者创建推荐的索引。
create index STAT.IDX$$_10F90001 on STAT.TJ_BALANCE_CID_200907("CLIENTCODE"
);
Rationale
---------
创建推荐的索引可以显著地改进此语句的执行计划。但是, 使用典型的 SQL 工作量运?
可能比单个语句更可取。通过这种方法可以获得全面的索引建议案, 包括计算索引维护
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 494025501
--------------------------------------------------------------------------------
| Id | Operation | Name | R
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
| 1 | PARTITION LIST ALL | |
| 2 | MAT_VIEW ACCESS BY LOCAL INDEX ROWID| TJ_BALANCE_CID_200907 |
|* 3 | INDEX RANGE SCAN | IDX_TJ_BALANCE_CID_200907_01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CLIENTCODE"='sadfasfds')
2- Using New Indices
--------------------
Plan hash value: 3010982149
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 |
| 1 | MAT_VIEW ACCESS BY GLOBAL INDEX ROWID| TJ_BALANCE_CID_200907 | 10 |
|* 2 | INDEX RANGE SCAN | IDX$$_10F90001 | 10 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("CLIENTCODE"='sadfasfds')
-------------------------------------------------------------------------------
然后我们就可以根据Recommendation部分的建议来执行优化操作了。
五、删除优化任务
通过调用dbms_sqltuen.drop_tuning_task可以删除已经存在的优化任务
代码:--------------------------------------------------------------------------------
exec dbms_sqltune.drop_tuning_task('sql_tuning_test');
PL/SQL procedure successfully completed.
最新文章
- java调用sqlldr导入csv文件数据到临时表
- iOS开发直播需要的准备
- Hive的安装
- EF ASP.NET MVC 更新出错:ObjectStateManager中已存在具有同一键的对象
- JSTL标签库中<;c:choose>;<;/c:choose>;不能放JSP页面<;!-- -->;注释
- LR11-更改licence
- FoxMail的Bug
- Visual Studio 新建项目报错"; this template attempted to load component assembly &#39;NuGet.VisualStudio.Interop, ….";.
- Python初学者笔记(3):输出列表中的奇数/奇数项,字符串中的偶数项,字符串大小写转换
- JProfiler 使用说明
- hdu 1151 Air Raid(二分图最小路径覆盖)
- 宁波Uber优步司机奖励政策(2月1日~2月7日)
- tyvj 1150 绳子围点 Pick定理 防溢出策略
- 你好,C++(2)1.3 C++世界版图1.4 如何学好C++
- MySQL flush tables with read lock
- Web系统如何做到读取客户电脑MAC等硬件信息且兼容非IE浏览器
- Jexus web server V5.6.1正式公布
- nagios总结二
- HTML <;td>; 标签的 colspan 属性
- MySQL时间操作的系统函数用法
热门文章
- (转)(C++)关于抽象基类和纯虚函数
- 使用Android ADT最新开发工具后,新建项目出现appcompat v7 他是什么?
- LUA 创建文件和文件夹
- Canvas实现环形进度条
- js-字符串方法
- (转)19 个 JavaScript 有用的简写技术
- 5) 十分钟学会android--ActionBar知识串烧
- 用python(2.7)自定义实现SQL的集合操作
- 部署Seafile服务
- 【Oracle】ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired