dbms_sqltune包用于sql语句的调优,包括sql tuning advisor子程序、sql profile子程序、sql tuning set子程序、实时sql监控子程序、sql性能报告子程序。
简单来说,Sql tuning advisor是一组建议,能自动调整sql语句,使其性能更好。
使用的步骤为:
(1)使用create_tuning_task函数为一条语句或一组语句创建调优任务
(2)使用execute_tuning_task存储过程执行当前创建的调优任务
(3)由report_tuning_task显示调优结果
(4)使用script_tuning_task函数创建可以执行推荐建议的脚本
(一)sql tuning advisor
包括如下子程序:
CANCEL_TUNING_TASK :取消当前调优任务
CREATE_SQL_PLAN_BASELINE :为已存在的计划创建基线
CREATE_TUNING_TASK :Creates a tuning of a single statement or SQL tuning set for either the SQL Tuning Advisor
DROP_TUNING_TASK :删除调优任务
EXECUTE_TUNING_TASK :执行调优任务
IMPLEMENT_TUNING_TASK :导入由sql tuning advisor推荐的sql profile
INTERRUPT_TUNING_TASK :中断当前执行的调优任务
REPORT_AUTO_TUNING_TASK:显示自动调优任务的报告
REPORT_TUNING_TASK :显示调优结果
RESET_TUNING_TASK :重置当前执行的调优计划
RESUME_TUNING_TASK:重启以前中断的任务
SCRIPT_TUNING_TASK :创建脚本用于执行调优建议
SET_TUNING_TASK_PARAMETER:修改调优参数
1.创建调优任务
可以有多种输入方式,包括:单条语句文本、缓存中的语句、负载报告(快照)中的语句、sql tuning set、sql performance analyzer。
语法如下:
SQL text format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_text IN CLOB,
bind_list IN sql_binds := NULL,
user_name IN VARCHAR2 := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL ID format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
Workload Repository format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
begin_snap IN NUMBER,
end_snap IN NUMBER,
sql_id IN VARCHAR2,
plan_hash_value IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQLSET format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
sqlset_name IN VARCHAR2,
basic_filter IN VARCHAR2 := NULL,
object_filter IN VARCHAR2 := NULL,
rank1 IN VARCHAR2 := NULL,
rank2 IN VARCHAR2 := NULL,
rank3 IN VARCHAR2 := NULL,
result_percentage IN NUMBER := NULL,
result_limit IN NUMBER := NULL,
scope IN VARCHAR2 := SCOPE_COMPREHENSIVE,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL
plan_filter IN VARCHAR2 := 'MAX_ELAPSED_TIME',
sqlset_owner IN VARCHAR2 := NULL)
RETURN VARCHAR2;
SQL Performance Analyzer format:
DBMS_SQLTUNE.CREATE_TUNING_TASK(
spa_task_name IN VARCHAR2,
spa_task_owner IN VARCHAR2 := NULL,
spa_compare_exec IN VARCHAR2 := NULL,
basic_filter IN VARCHAR2 := NULL,
time_limit IN NUMBER := TIME_LIMIT_DEFAULT,
task_name IN VARCHAR2 := NULL,
description IN VARCHAR2 := NULL)
RETURN VARCHAR2;
-----------------
以下是我在以前一个项目中使用自动调优建议进行sql优化的例子:
使用sql tuning advisor自动生成调优建议。
如果某条sql有更优的计划,则系统自动生成sql profile,可以使用DBMS_SQLTUNE.REPORT_TUNING_TASK看到;如果统计信息过旧,调优建议中会提示进行统计信息的收集;如果缺少索引,调优建议中会包含创建索引的语句。
如执行以下语句:
spool sql_tuning
SET LONG 10000000 LONGCHUNKSIZE 1000000 LINESIZE 150 pagesize 0 serveroutput on size 1000000
variable stmt_task VARCHAR2(64);
EXEC :stmt_task1 := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => 'xxxxxx');
exec DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name =>:stmt_task1 );
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( :stmt_task1 ) from dual;
Spool off;
显示结果如下:
TASK_NAME STATUS
------------------------------ -----------
tuning_sql_test1 COMPLETED
DBMS_SQLTUNE.REPORT_TUNING_TAS
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : tuning_sql_test1
Tuning Task Owner : SYSTEM
Scope : COMPREHENSIVE
Time Limit(seconds) : 60
Completion Status : COMPLETED
Started at : 02/17/2014 17:29:00
Completed at : 02/17/2014 17:29:06
Number of SQL Profile Findings : 1
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 9ta2yn452fyj6
SQL Text : SELECT DT.TABLE_NAME, DT.OWNER FROM DBA_TABLES DT WHERE DT.OWNER
= 'SGPMS' AND TABLE_NAME IN ('A_RCVBL_PL_FLOW',
'A_RCVED_PL_FLOW', 'A_RCVBL_FLOW', 'A_RCVED_FLOW',
'A_CASH_ENTRY', 'A_OTHER_RCVPAY_ENTRY', 'PUB_A_RCVBL_FLOW',
'A_PC_TRAN', 'PUB_P_MSG_SEND', 'A_ACCT_BAL ',
'S_SUBSCIBE_CONT', 'PUB_A_GP_RCVBL_FLOW', 'A_OTHERINCOME_ENTRY',
'A_CASHCHK_FLOW', 'A_DEPOSIT_ENTRY', 'A_TRANSIT',
'A_GP_AGREEMENT', 'PUB_A_ACCT_YM', 'A_REFUND', 'A_ACCT',
'A_ACCT_TRAN', 'A_INNER_RCVPAY', 'A_ACCT_VER')
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划。
Recommendation (estimated benefit: 80.02%)
------------------------------------------
- 考虑接受推荐的 SQL 概要文件。
execute dbms_sqltune.accept_sql_profile(task_name => 'tuning_sql_test1',
replace => TRUE);
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original With Adjusted Cost
------------------------------
Plan hash value: 2987781044
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 85 | 14875 |
|* 1 | HASH JOIN RIGHT OUTER | | 85 | 14875 |
| 2 | TABLE ACCESS FULL | USER$ | 89 | 267 |
………………
|* 23 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 |
| 24 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 1 | 8 |
|* 25 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("CX"."OWNER#"="CU"."USER#"(+))
6 - access("T"."TS#"="TS"."TS#")
10 - access("KSPPI"."INDX"="KSPPCV"."INDX")
……
23 - access("T"."BOBJ#"="CO"."OBJ#"(+))
25 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
2- Using SQL Profile
--------------------
Plan hash value: 1758096801
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 66 | 11550 |
|* 1 | HASH JOIN | | 66 | 11550 |
| 2 | FIXED TABLE FULL | X$KSPPCV | 100 | 1300 |
……
|* 24 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | |
|* 25 | INDEX UNIQUE SCAN | I_OBJ1 | 1 | 5 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("KSPPI"."INDX"="KSPPCV"."INDX")
……
22 - access("T"."TS#"="S"."TS#"(+) AND "T"."FILE#"="S"."FILE#"(+) AND
"T"."BLOCK#"="S"."BLOCK#"(+))
24 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
25 - access("T"."BOBJ#"="CO"."OBJ#"(+))
-------------------------------------------------------------------------------
根据上面的提示,接受sql profile后就可以使语句性能提高80%。
以下图片是负载报告中sql相关的字段:
SQL> desc STAGE_TAB
Name Type Nullable Default Comments
------------------------ -------------------------- -------- ------- --------
NAME VARCHAR2(30) Y
OWNER VARCHAR2(30) Y
DESCRIPTION VARCHAR2(256) Y
SQL_ID VARCHAR2(13) Y
FORCE_MATCHING_SIGNATURE NUMBER Y
SQL_TEXT CLOB Y
PARSING_SCHEMA_NAME VARCHAR2(30) Y
BIND_DATA RAW(2000) Y
BIND_LIST PUBLIC.SQL_BIND_SET Y
MODULE VARCHAR2(48) Y
ACTION VARCHAR2(32) Y
ELAPSED_TIME NUMBER Y
CPU_TIME NUMBER Y
BUFFER_GETS NUMBER Y
DISK_READS NUMBER Y
DIRECT_WRITES NUMBER Y
ROWS_PROCESSED NUMBER Y
FETCHES NUMBER Y
EXECUTIONS NUMBER Y
END_OF_FETCH_COUNT NUMBER Y
OPTIMIZER_COST NUMBER Y
OPTIMIZER_ENV RAW(1000) Y
PRIORITY NUMBER Y
COMMAND_TYPE NUMBER Y
FIRST_LOAD_TIME VARCHAR2(19) Y
STAT_PERIOD NUMBER Y
ACTIVE_STAT_PERIOD NUMBER Y
OTHER CLOB Y
PLAN_HASH_VALUE NUMBER Y
PLAN PUBLIC.SQL_PLAN_TABLE_TYPE Y
SPARE1 NUMBER Y
SPARE2 NUMBER Y
SPARE3 BLOB Y
SPARE4 CLOB Y
使用方法举例:
下面是一个从负载报告(快照)创建调优任务,加载、执行、报告、接受调优任务,将调优结果导出到stage表中的一个完整例子。
variable sts_task varchar2(20);
DECLARE
baseline_cursor DBMS_SQLTUNE.SQLSET_CURSOR; --声明游标类型
BEGIN
--1、创建调优任务
dbms_sqltune.create_sqlset(sqlset_name=>'my_workload',
description=>'snapid from 368 to 373');
--2、获取负载报告的内容
OPEN baseline_cursor FOR
SELECT VALUE(p)
FROM TABLE(DBMS_SQLTUNE.SELECT_WORKLOAD_REPOSITORY(begin_snap => 360,end_snap => 373)) p;
--3、将负载报告的内容放入已经定义的STS中
DBMS_SQLTUNE.LOAD_SQLSET(sqlset_name => 'my_sql_tuningset',
populate_cursor => baseline_cursor);
--4、创建调优任务,将buffer_gets作为排序依据,在3600s内完成调优
:sts_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sqlset_name => 'my_workload',
rank1 => 'BUFFER_GETS',
time_limit => 3600,
description => 'tune my workload ordered by buffer gets');
--5、执行调优任务
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:sts_task);
END;
/
--6、显示调优结果
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY') FROM DUAL;
--SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task) from dual;
--SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'FINDINGS',5) from dual;
--7、生成调优脚本
SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK(:stmt_task) FROM DUAL;
--8、创建stage表,用于存放sql tuning set
--注意:db_version不能是当前系统的版本,否则报错ORA-15703、ORA-06512、ORA-06512
exec dbms_sqltune.create_stgtab_sqlset(table_name => 'STAGE_TAB',schema_name => 'SCOTT',db_version => dbms_sqltune.STS_STGTAB_10_2_VERSION);
--9、将sql tuning set导入到stage表
--这里需要注意区分sqlset_name,需要使用的是load_sqlset中的sqlset_name
--另外,执行调优任务后如果系统没有给出调优建议,下面的语句也会出错
exec dbms_sqltune.pack_stgtab_sqlset(sqlset_name => 'my_sql_tuningset',staging_table_name => 'STAGE_TAB',db_version => dbms_sqltune.STS_STGTAB_10_2_VERSION);
--10、删除调优任务
exec dbms_sqltune.drop_tuning_task(task_name => :sts_task);
--11、删除sql tuning set
exec dbms_sqltune.drop_sqlset(sqlset_name =>'my_workload');
(二)sql profile
sql profile通常是根据sql tuning advisor得到的。
使用方法为:
创建stage表,用于存储非sys用户的sql profile信息:CREATE_STGTAB_SQLPROF
将sql profile信息放入stage表:PACK_STGTAB_SQLPROF
删除sql profile:DROP_SQL_PROFILE
接受sql profile:accept_sql_profile
这个子程序的使用可能相对较少。主要是接受和删除操作。
总结一下:
sql tuning set就是将一系列我们感兴趣的sql语句按照特征存放在一起,方便调用和调优。
通过sql tuning advisor为单条sql、sql tuning set提出优化建议,部分建议中生成sql profile。Sql profile是对当前sql进行一系列调整,使其计划更优的方法。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26451536/viewspace-1176794/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/26451536/viewspace-1176794/