oracle 11g DBMS_SQLTUNE 包的使用方法介紹
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;
使用流程如下：
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- golang toolkits包的使用方法介紹Golang
- golang ssh包使用方法介紹Golang
- Oracle 11g系統調優之dbms_sqltune包的使用OracleSQL
- SQL調優工具包DBMS_SQLTUNE的使用方法SQL
- Oracle 11g UTL_FILE 包的使用方法Oracle
- DBMS_SQLTUNE使用方法SQL
- dbms_sqltune包的使用SQL
- DataGuard on Oracle 11g初步介紹(1)Oracle
- DataGuard on Oracle 11g初步介紹(2)Oracle
- DataGuard on Oracle 11g初步介紹(3)Oracle
- Audit 功能的使用方法介紹
- domutils 工具庫的使用方法介紹
- Lombok介紹及使用方法Lombok
- oracle統計資訊包--dbms_stats介紹Oracle
- Hanlp在ubuntu中的使用方法介紹HanLPUbuntu
- Lombok介紹，使用方法及原理Lombok
- Ldd命令介紹及使用方法
- oracle 11g自動收集統計資訊介紹Oracle
- pytorch 包介紹PyTorch
- vuex詳細介紹和使用方法Vue
- ORACLE 11G RAC--維護叢集的OCR命令介紹Oracle
- jar包和war包的介紹與區別JAR
- jar包、war包和ear包的介紹與區別JAR
- SAP Cloud SDK for JavaScript 的搭建和使用方法介紹CloudJavaScript
- javascript閉包概念介紹JavaScript
- Springmvc jar包介紹SpringMVCJAR
- oracle 11g adrci 工具使用方法Oracle
- Android中SharedPreferences使用方法介紹Android
- ORACLE MTS的介紹(zt)Oracle
- SAP UI5 navpopover Factory 的使用方法介紹UI
- ORACLE OWI介紹Oracle
- ORACLE ORION介紹Oracle
- oracle VPD介紹Oracle
- Oracle recyclebin介紹Oracle
- ORACLE鎖介紹Oracle
- Oracle ACE 介紹Oracle
- oracle job 介紹Oracle
- Oracle Spatial 介紹Oracle