oracle 11g DBMS_SQLTUNE 包的使用方法介绍

最新推荐文章于 2023-04-18 22:59:49 发布

cuantangnie3915

最新推荐文章于 2023-04-18 22:59:49 发布

阅读量248

点赞数

文章标签：数据库

DBMS_SQLTUNE 包可以辅助我们进行 SQL 优化，提供一些 SQL 的优化建议。

使用流程如下：

1、创建优化任务

可以明文将要优化的 SQL 代入包中，注意在时间格式上增加了引号，如 date ''2015-11-18''。

declare
stmt_task VARCHAR2(64);
sts_task VARCHAR2(64);
--Create Tuning Task with SQL Text format
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_text => '
select count(1)
from (select /*+ index(a idx_acctflow_customtranstype)*/
transflowid
from elmp_trans_customaccountflow a
where a.customtransdate >= date ''2015-11-18''
and a.customtransdate < date ''2015-11-19'') a1
left join elmp_trans_customaccountflow a
on a1.transflowid = a.transflowid
left join elmp_trans_customdetail b
on a.customcode = b.customcode
left join elmp_trans_custom d
on d.customcode = b.customcode
left join elmp_trans_orderinfo e
on a.transflowid = e.transflowid
left join elmp_trans_chargedetail f
on a.transflowid = f.transflowid
left join elmp_trans_cashdetail g
on a.transflowid = g.transflowid
left join elmp_trans_bonusdetail z
on a.transflowid = z.transflowid
where (abs(a.customwtransamount) + abs(a.customctransamount) +
abs(a.customrtransamount) + abs(a.customstransamount)) != 0');
end;

2、执行优化任务

先查询出刚刚建立任务的名称

SELECT task_name, created, status FROM USER_ADVISOR_TASKS;

任务_2976 ..

begin
DBMS_SQLTUNE.EXECUTE_TUNING_TASK('任务_2976');
end;

等任务执行结束，再次查询，可以看到任务的状态为 COMPLETED

SELECT task_name, created, status FROM USER_ADVISOR_TASKS;

3、显示优化的结果

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('任务_2976') from dual;

GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 任务_2976
Tuning Task Owner : ACCT
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 1800
Completion Status : COMPLETED
Started at : 11/18/2015 14:05:23
Completed at : 11/18/2015 14:06:22

-------------------------------------------------------------------------------
Schema Name: ACCT
SQL ID : auvgjtavz7zst
SQL Text : select count(1)
from (select /*+ index(a idx_acctflow_customtranstype)*/
transflowid
from elmp_trans_customaccountflow a
where a.customtransdate >= date '2015-11-18'
and a.customtransdate < date '2015-11-19') a1
left join elmp_trans_customaccountflow a
on a1.transflowid = a.transflowid
left join elmp_trans_customdetail b
on a.customcode = b.customcode
left join elmp_trans_custom d
on d.customcode = b.customcode
left join elmp_trans_orderinfo e
on a.transflowid = e.transflowid
left join elmp_trans_chargedetail f
on a.transflowid = f.transflowid
left join elmp_trans_cashdetail g
on a.transflowid = g.transflowid
left join elmp_trans_bonusdetail z
on a.transflowid = z.transflowid
where (abs(a.customwtransamount) + abs(a.customctransamount) +
abs(a.customrtransamount) + abs(a.customstransamount)) !=
0

-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
为此语句找到了性能更好的执行计划。

Recommendation (estimated benefit: 66.94%)
------------------------------------------
- 考虑接受建议的 SQL 概要文件, 以便对此语句使用并行执行。
execute dbms_sqltune.accept_sql_profile(task_name => '任务_2976',
task_owner => 'ACCT', replace => TRUE, profile_type =>
DBMS_SQLTUNE.PX_PROFILE);

与 DOP 128 并行执行此查询会使原始计划上的响应时间缩短 66.94%。但是, 启用并行执行时要付出一些代价。它将增加语句的资源消耗 (预计为
4131.67%), 这会导致系统吞吐量降低。此外, 由于在非常短的持续时间内消耗了这些资源, 因此如果没有足够可用的硬件容量,
并发语句的响应时间将受到负面影响。

The following data shows some sampled statistics for this SQL from the past
week and projected weekly values when parallel execution is enabled.

Past week sampled statistics for this SQL
-----------------------------------------
Number of executions 0
Percent of total activity 0
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 0

Projected statistics with Parallel Execution
--------------------------------------------
Weekly DB time (in sec) 0

-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------

1- Original
-----------
Plan hash value: 2741320090

----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 9011 (1)| 00:01:49 | | |
| 1 | SORT AGGREGATE | | 1 | 87 | | | | |
|* 2 | HASH JOIN OUTER | | 2022 | 171K| 9011 (1)| 00:01:49 | | |
|* 3 | HASH JOIN RIGHT OUTER | | 2022 | 144K| 8942 (1)| 00:01:48 | | |
| 4 | INDEX FAST FULL SCAN | IDX_BONUSDETAIL_TRANSFLOWID | 4484 | 62776 | 6 (0)| 00:00:01 | | |
|* 5 | FILTER | | | | | | | |
| 6 | NESTED LOOPS OUTER | | 2022 | 116K| 8936 (1)| 00:01:48 | | |
|* 7 | INDEX SKIP SCAN | IDX_ACCTFLOW_CUSTOMTRANSTYPE | 2004 | 44088 | 2922 (1)| 00:00:36 | | |
| 8 | TABLE ACCESS BY GLOBAL INDEX ROWID| ELMP_TRANS_CUSTOMACCOUNTFLOW | 1 | 37 | 3 (0)| 00:00:01 | ROWID | ROWID |
|* 9 | INDEX UNIQUE SCAN | PK_CUSTOMACCOUNTFLOW | 1 | | 2 (0)| 00:00:01 | | |
| 10 | TABLE ACCESS FULL | ELMP_TRANS_ORDERINFO | 13059 | 178K| 68 (0)| 00:00:01 | | |
----------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("A"."TRANSFLOWID"="E"."TRANSFLOWID"(+))
3 - access("A"."TRANSFLOWID"="Z"."TRANSFLOWID"(+))
5 - filter(ABS("A"."CUSTOMWTRANSAMOUNT")+ABS("A"."CUSTOMCTRANSAMOUNT")+ABS("A"."CUSTOMRTRANSAMOUNT")+ABS("A"."CUSTOMSTRANSAMO
UNT")<>0)
7 - access("A"."CUSTOMTRANSDATE">=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A"."CUSTOMTRANSDATE" =TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
"A"."CUSTOMTRANSDATE"<to_date(' 2015-11-19="" 00:00:00',="" 'syyyy-mm-dd="" hh24:mi:ss'))="" ="" 9="" -="" access("transflowid"="A" ."transflowid"(+))

2- Using Parallel Execution
---------------------------
Plan hash value: 1059900845

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 87 | 2979 (1)| 00:00:36 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 87 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10004 | 1 | 87 | | | | | Q1,04 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 87 | | | | | Q1,04 | PCWP | |
|* 5 | HASH JOIN OUTER | | 2022 | 171K| 2979 (1)| 00:00:36 | | | Q1,04 | PCWP | |
|* 6 | HASH JOIN OUTER | | 2022 | 144K| 2977 (1)| 00:00:36 | | | Q1,04 | PCWP | |
| 7 | PX RECEIVE | | 2022 | 116K| 2974 (1)| 00:00:36 | | | Q1,04 | PCWP | |
| 8 | PX SEND HASH | :TQ10001 | 2022 | 116K| 2974 (1)| 00:00:36 | | | Q1,01 | P->P | HASH |
|* 9 | FILTER | | | | | | | | Q1,01 | PCWC | |
| 10 | NESTED LOOPS OUTER | | 2022 | 116K| 2974 (1)| 00:00:36 | | | Q1,01 | PCWP | |
| 11 | BUFFER SORT | | | | | | | | Q1,01 | PCWC | |
| 12 | PX RECEIVE | | | | | | | | Q1,01 | PCWP | |
| 13 | PX SEND ROUND-ROBIN | :TQ10000 | | | | | | | | S->P | RND-ROBIN |
|* 14 | INDEX SKIP SCAN | IDX_ACCTFLOW_CUSTOMTRANSTYPE | 2004 | 44088 | 2922 (1)| 00:00:36 | | | | | |
| 15 | TABLE ACCESS BY GLOBAL INDEX ROWID| ELMP_TRANS_CUSTOMACCOUNTFLOW | 1 | 37 | 0 (0)| 00:00:01 | ROWID | ROWID | Q1,01 | PCWP | |
|* 16 | INDEX UNIQUE SCAN | PK_CUSTOMACCOUNTFLOW | 1 | | 0 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 17 | PX RECEIVE | | 4484 | 62776 | 2 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 18 | PX SEND HASH | :TQ10002 | 4484 | 62776 | 2 (0)| 00:00:01 | | | Q1,02 | P->P | HASH |
| 19 | PX BLOCK ITERATOR | | 4484 | 62776 | 2 (0)| 00:00:01 | | | Q1,02 | PCWC | |
| 20 | INDEX FAST FULL SCAN | IDX_BONUSDETAIL_TRANSFLOWID | 4484 | 62776 | 2 (0)| 00:00:01 | | | Q1,02 | PCWP | |
| 21 | PX RECEIVE | | 13059 | 178K| 2 (0)| 00:00:01 | | | Q1,04 | PCWP | |
| 22 | PX SEND HASH | :TQ10003 | 13059 | 178K| 2 (0)| 00:00:01 | | | Q1,03 | P->P | HASH |
| 23 | PX BLOCK ITERATOR | | 13059 | 178K| 2 (0)| 00:00:01 | | | Q1,03 | PCWC | |
| 24 | TABLE ACCESS FULL | ELMP_TRANS_ORDERINFO | 13059 | 178K| 2 (0)| 00:00:01 | | | Q1,03 | PCWP | |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("A"."TRANSFLOWID"="E"."TRANSFLOWID"(+))
6 - access("A"."TRANSFLOWID"="Z"."TRANSFLOWID"(+))
9 - filter(ABS("A"."CUSTOMWTRANSAMOUNT")+ABS("A"."CUSTOMCTRANSAMOUNT")+ABS("A"."CUSTOMRTRANSAMOUNT")+ABS("A"."CUSTOMSTRANSAMOUNT")<>0)
14 - access("A"."CUSTOMTRANSDATE">=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."CUSTOMTRANSDATE"<to_date(' 2015-11-19="" 00:00:00', ="" ="" 'syyyy-mm-dd="" hh24:mi:ss')) filter("A"."CUSTOMTRANSDATE">=TO_DATE(' 2015-11-18 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."CUSTOMTRANSDATE"<to_date(' 2015-11-19="" 00:00:00', ="" ="" 'syyyy-mm-dd="" hh24:mi:ss')) 16 - access("TRANSFLOWID"="A"."TRANSFLOWID"(+))

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

SELECT DBMS_SQLTUNE.SCRIPT_TUNING_TASK('任务_2976') FROM DUAL;

如果 SQL 很大，也可以使用 SQL_ID 的方式来创建任务

先找到 SQL 对应的 SQL_ID

select sql_id, sql_text, sql_fulltext
from v$sql s
where s.SQL_FULLTEXT like
'%select count(1)
from (select /*+ index(a idx_acctflow_customtranstype)*/
transflowid
from elmp_trans_customaccountflow a
where a.customtransdate >= date ''2015-11-18''
and a.customtransdate < date ''2015-11-19'') a1
left join elmp_trans_customaccountflow a
on a1.transflowid = a.transflowid
left join elmp_trans_customdetail b
on a.customcode = b.customcode
left join elmp_trans_custom d
on d.customcode = b.customcode
left join elmp_trans_orderinfo e
on a.transflowid = e.transflowid
left join elmp_trans_chargedetail f
on a.transflowid = f.transflowid
left join elmp_trans_cashdetail g
on a.transflowid = g.transflowid
left join elmp_trans_bonusdetail z
on a.transflowid = z.transflowid
where (abs(a.customwtransamount) + abs(a.customctransamount) +
abs(a.customrtransamount) + abs(a.customstransamount)) != 0%';

0c16yqg8zruv4 select count(1) from (select /*+ index(a idx_acctflow_customtran

创建优化任务

declare
stmt_task VARCHAR2(64);
sts_task VARCHAR2(64);
begin
stmt_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '0c16yqg8zruv4');
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26506993/viewspace-1840499/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26506993/viewspace-1840499/

cuantangnie3915

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
oracle 11g DBMS_SQLTUNE 包的使用方法介绍

DBMS_SQLTUNE 包可以辅助我们进行 SQL 优化，提供一些 SQL 的优化建议。使用流程如下：1、创建优化任务可以明文将要优化的 SQL代入包中，注意在时间格式上增加了引号，如date ''...
复制链接

扫一扫