mkwq
oracle 11g DBMS_sqlTUNE
- 1. 创建调优任务
- 1.1 语法
- 2. 执行调优任务
- 2.1语法
- 3. 查看调优报告
- 3.1 语法
- 4. 删除SQL调优任务
- 4.1 语法
- 5. 示例
- 6. 参考文档
1. 创建调优任务
1.1 语法
- 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, --limited/comprehensive，默认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,--limited/comprehensive，默认comprehensive值 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,--默认1800s 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,--limited/comprehensive，默认comprehensive值 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,--默认1800s 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,--limited/comprehensive，默认comprehensive值 time_limit IN NUMBER := TIME_LIMIT_DEFAULT,--默认1800s 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;
2. 执行调优任务
2.1语法
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name IN VARCHAR2,
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc IN VARCHAR2 := NULL)
RETURN VARCHAR2;
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(
task_name IN VARCHAR2,
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.argList := NULL,
execution_desc IN VARCHAR2 := NULL);
示例：
EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(:stmt_task);
3. 查看调优报告
3.1 语法
DBMS_SQLTUNE.REPORT_TUNING_TASK(
task_name IN VARCHAR2,
type IN VARCHAR2 := 'TEXT',
level IN VARCHAR2 := 'TYPICAL', --有 BASIC/TYPICAL/ALL 参数
section IN VARCHAR2 := ALL, --有SUMMARY/FINDINGS/PLAN/INFORMATION/ERROR/ALL 参数
object_id IN NUMBER := NULL,
result_limit IN NUMBER := NULL,
owner_name IN VARCHAR2 := NULL,
execution_name IN VARCHAR2 := NULL)
RETURN CLOB;
示例：
-- Get the whole report for the single statement case.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:stmt_task) from dual;
-- Show me the summary for the sts case.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'SUMMARY')
FROM DUAL;
-- Show me the findings for the statement I'm interested in.
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK(:sts_task, 'TEXT', 'TYPICAL', 'FINDINGS', 5) from dual;
4. 删除SQL调优任务
4.1 语法
DBMS_SQLTUNE.DROP_TUNING_TASK(
task_name IN VARCHAR2);
5. 示例
SQL_ID方式
--创建调优任务并执行
DECLARE
my_task_name VARCHAR2(30);
BEGIN
my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '18j0qammkwqfr',
time_limit => 3600,
task_name => 'SQL_TUNE_18j0qammkwqfr',
description => 'Task to tune a query');
DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => 'SQL_TUNE_18j0qammkwqfr');
END;
/
-- 查看调优任务状态
select t.task_name, t.ADVISOR_NAME, t.STATUS
from dba_advisor_tasks t
where t.task_name = 'SQL_TUNE_18j0qammkwqfr'
TASK_NAME ADVISOR_NAME STATUS
-------- ------------ --------
SQL_TUNE_18j0qammkwqfr SQL Tuning Advisor COMPLETED
-- 查看调优任务报告
select dbms_sqltune.report_tuning_task('SQL_TUNE_18j0qammkwqfr','TEXT','ALL') from dual;
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SQL_TUNE_18j0qammkwqfr
Tuning Task Owner : SYS
Tuning Task ID : 5671
Workload Type : Single SQL Statement
Execution Count : 1
Current Execution : EXEC_6034
Execution Type : TUNE SQL
Scope : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status : COMPLETED
Started at : 12/15/2018 21:24:39
Completed at : 12/15/2018 21:25:10
-------------------------------------------------------------------------------
Schema Name: SYS
SQL ID : 18j0qammkwqfr
SQL Text : update t_objects a
set (a.tablespace_name, a.flag) =
(select distinct b.tablespace_name, '2'
from t_tables b
where a.owner = b.owner
and a.object_name = b.table_name)
where exists (select distinct b.tablespace_name
from t_tables b
where a.owner = b.owner
and a.object_name = b.table_name)
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Alternative Plan Finding
---------------------------
Some alternative execution plans for this statement were found by searching
the system's real-time and historical performance data.
The following table lists these plans ranked by their average elapsed time.
See section "ALTERNATIVE PLANS SECTION" for detailed information on each
plan.
id plan hash last seen elapsed (s) origin note
-- ---------- -------------------- ------------ --------------- ----------------
1 1460256570 2018-12-15/21:09:22 54.846 Cursor cache not reproducible
Information
-----------
- All alternative plans other than the Original Plan could not be
reproduced in the current environment.
- The plan with id 1 could not be reproduced in the current environment.
For this reason, a SQL plan baseline cannot be created to instruct the
Oracle optimizer to pick this plan in the future.
-------------------------------------------------------------------------------
explain PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 3244764335
------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%cpu)| Time |
------------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 29227 | 2311K| | 280M (1)|936:39:02 |
| 1 | UPDATE | T_OBJECTS | | | | | |
|* 2 | HASH JOIN SEMI | | 29227 | 2311K| 1688K| 6712 (1)| 00:01:21 |
| 3 | TABLE ACCESS FULL | T_OBJECTS | 29227 | 1341K| | 71 (2)| 00:00:01 |
| 4 | VIEW | VW_SQ_1 | 801K| 25M| | 4804 (1)| 00:00:58 |
| 5 | TABLE ACCESS FULL| T_TABLES | 801K| 20M| | 4804 (1)| 00:00:58 |
| 6 | HASH unique | | 5 | 170 | | 4806 (1)| 00:00:58 |
|* 7 | TABLE ACCESS FULL | T_TABLES | 11 | 374 | | 4805 (1)| 00:00:58 |
------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$A419B674
3 - SEL$A419B674 / A@UPD$1
4 - SEL$683B0107 / VW_SQ_1@SEL$1E93CC81
5 - SEL$683B0107 / B@SEL$2
6 - SEL$1
7 - SEL$1 / B@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."OWNER"="ITEM_1" AND "A"."OBJECT_NAME"="ITEM_2")
7 - filter("B"."TABLE_NAME"=:B1 AND "B"."OWNER"=:B2)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
2 - (#keys=2; upd=6,7; cmp=1,3) "A"."OWNER"[VARCHAR2,30],
"ITEM_1"[VARCHAR2,30], "A"."OBJECT_NAME"[VARCHAR2,128], "ITEM_2"[VARCHAR2,30],
"SYS_ALIAS_2".ROWID[ROWID,10], "A"."FLAG"[VARCHAR2,2],
"A"."TABLESPACE_NAME"[VARCHAR2,30]
3 - "SYS_ALIAS_2".ROWID[ROWID,10], "A"."OWNER"[VARCHAR2,30],
"A"."OBJECT_NAME"[VARCHAR2,128], "A"."TABLESPACE_NAME"[VARCHAR2,30],
"A"."FLAG"[VARCHAR2,2]
4 - "ITEM_1"[VARCHAR2,30], "ITEM_2"[VARCHAR2,30]
5 - "B"."OWNER"[VARCHAR2,30], "B"."TABLE_NAME"[VARCHAR2,30]
6 - (#keys=2) "B"."TABLESPACE_NAME"[VARCHAR2,30], '2'[1]
7 - "B"."OWNER"[VARCHAR2,30], "B"."TABLE_NAME"[VARCHAR2,30],
"B"."TABLESPACE_NAME"[VARCHAR2,30]
-------------------------------------------------------------------------------
ALTERNATIVE PLANS SECTION
-------------------------------------------------------------------------------
Plan 1
------
Plan Origin :Cursor Cache
Plan Hash Value :1460256570
Executions :4
Elapsed Time :54.846 sec
CPU Time :54.336 sec
Buffer Gets :24506044
Disk Reads :452
Disk Writes :0
Notes:
1. Statistics shown are averaged over multiple executions.
2. The plan with id could not be reproduced in the current environment. For this reason, a SQL plan baseline cannot be created to instruct the Oracle optimizer to pick this plan in the future.
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | | | 7710K(100)| |
| 1 | UPDATE | T_OBJECTS | | | | |
| 2 | HASH JOIN RIGHT SEMI| | 25032 | 1980K| 225 (2)| 00:00:03 |
| 3 | VIEW | VW_SQ_1 | 25032 | 831K| 153 (1)| 00:00:02 |
| 4 | TABLE ACCESS FULL | T_TABLES | 25032 | 660K| 153 (1)| 00:00:02 |
| 5 | TABLE ACCESS FULL | T_OBJECTS | 29227 | 1341K| 71 (2)| 00:00:01 |
| 6 | HASH UNIQUE | | 1 | 34 | 154 (2)| 00:00:02 |
| 7 | TABLE ACCESS FULL | T_TABLES | 1 | 34 | 153 (1)| 00:00:02 |
-----------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$A419B674
3 - SEL$683B0107 / VW_SQ_1@SEL$1E93CC81
4 - SEL$683B0107 / B@SEL$2
5 - SEL$A419B674 / A@UPD$1
6 - SEL$1
7 - SEL$1 / B@SEL$1
-------------------------------------------------------------------------------
6. 参考文档
https://docs.oracle.com/cd/E11882_01/APPdev.112/e40758/d_sqltun.htm#CHDDCCBJ