在EM中使用SQL Tuning Advisor（SQL優化建議）優化SQL

同樣是先選定SQL優化集，然後執行“SQL優化建議”

在結果頁面，可以檢視建議的改進操作所產生的新執行計劃，以及與舊的執行計劃的比較，但不能看到具體的建議操作。要檢視，需要在SQL Developer中執行

點選(此處)摺疊或開啟

select dbms_sqltune.report_tuning_task(:task_name) from dual;

由於輸出較長，可將結果複製到文字文件。例項輸出：

點選(此處)摺疊或開啟

"GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SQL_TUNING_1459652706939
Tuning Task Owner : SYS
Workload Type : SQL Tuning Set
Scope : COMPREHENSIVE
Global Time Limit(seconds) : 1800
Per-SQL Time Limit(seconds) : 300
Completion Status : COMPLETED
Started at : 04/03/2016 11:05:23
Completed at : 04/03/2016 11:12:55
SQL Tuning Set (STS) Name : TOP_SQL_1459609199432
SQL Tuning Set Owner : SYS
Number of Statements in the STS : 5

-------------------------------------------------------------------------------
SUMMARY SECTION
-------------------------------------------------------------------------------
Global SQL Tuning Result Statistics
-------------------------------------------------------------------------------
Number of SQLs Analyzed : 5
Number of SQLs in the Report : 4
Number of SQLs with Findings : 4
Number of SQLs with SQL profiles recommended : 4
Number of SQLs with Index Findings : 2
Number of SQLs with Timeouts : 1

-------------------------------------------------------------------------------
SQLs with Findings Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
object ID SQL ID statistics profile(benefit) index(benefit) restructure
---------- ------------- ---------- ---------------- -------------- -----------
5 5avmzkym07mbs 86.12% 99.99%
2 0uy1f4214b2pq 87.18%
4 8zc0u0bk3t6uh 86.87%
3 0mvf2c00mtvfr 86.14% 64.35%

-------------------------------------------------------------------------------
Tables with New Potential Indices (ordered by schema, number of times, table)
-------------------------------------------------------------------------------
Schema Name Table Name Index Name Nb Time
--------------------------- --------------------------- -------------- --------
SH SALES IDX$$_002A0001 2

-------------------------------------------------------------------------------
DETAILS SECTION
-------------------------------------------------------------------------------
Statements with Results Ordered by Maximum (Profile/Index) Benefit, Object ID
-------------------------------------------------------------------------------
Object ID : 5
Schema Name: SH
SQL ID : 5avmzkym07mbs
SQL Text : select max(time_id) from sales

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
為此語句找到了效能更好的執行計劃。

Recommendation (estimated benefit: 86.12%)
------------------------------------------
- 考慮接受建議的 SQL 概要檔案, 以便對此語句使用並行執行。
execute dbms_sqltune.accept_sql_profile(task_name =>
'SQL_TUNING_1459652706939', object_id => 5, task_owner => 'SYS',
replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

與 DOP 8 並行執行此查詢會使原始計劃上的響應時間縮短 86.12%。但是, 啟用並行執行時要付出一些代價。它將增加語句的資源消耗 (預計為
11.03%), 這會導致系統吞吐量降低。此外, 由於在非常短的持續時間內消耗了這些資源, 因此如果沒有足夠可用的硬體容量,
併發語句的響應時間將受到負面影響。

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 1
Percent of total activity .54
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 12.99

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

2- Index Finding (see explain plans section below)
--------------------------------------------------
通過建立一個或多個索引可以改進此語句的執行計劃。

Recommendation (estimated benefit: 99.99%)
------------------------------------------
- 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
create index SH.IDX$$_002A0003 on SH.SALES("TIME_ID");

Rationale
---------
建立推薦的索引可以顯著地改進此語句的執行計劃。但是, 使用典型的 SQL 工作量執行 "訪問指導"
可能比單個語句更可取。通過這種方法可以獲得全面的索引建議案, 包括計算索引維護的開銷和附加的空間消耗。

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

1- Original
-----------
Plan hash value: 1047182207

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 31293 (1)| 00:06:16 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | TABLE ACCESS FULL| SALES | 14M| 112M| 31293 (1)| 00:06:16 |
----------------------------------------------------------------------------

2- Using New Indices
--------------------
Plan hash value: 2954725013

---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| IDX$$_002A0003 | 1 | 8 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

3- Using Parallel Execution
---------------------------
Plan hash value: 3130505568

----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 8 | 4343 (1)| 00:00:53 | | | |
| 1 | SORT AGGREGATE | | 1 | 8 | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 8 | | | Q1,00 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 8 | | | Q1,00 | PCWP | |
| 5 | PX BLOCK ITERATOR | | 14M| 112M| 4343 (1)| 00:00:53 | Q1,00 | PCWC | |
| 6 | TABLE ACCESS FULL| SALES | 14M| 112M| 4343 (1)| 00:00:53 | Q1,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
Object ID : 2
Schema Name: SH
SQL ID : 0uy1f4214b2pq
SQL Text : select * from (
select c.cust_first_name || ' ' || c.cust_last_name
, c.cust_gender
, p.prod_name
, p.prod_desc
, p.prod_list_price
, p.prod_min_price
, p.prod_total
, t.time_id
, ch.channel_desc
, prm.promo_name
, prm.promo_cost
, prm.promo_total
from sales s
left join products p
on s.prod_id = p.prod_id
left join customers c
on s.cust_id = c.cust_id
left join times t
on s.time_id = t.time_id
left join channels ch
on s.channel_id = ch.channel_id
left join promotions prm
on s.promo_id = prm.promo_id
where t.CALENDAR_YEAR = '2013'
order by p.prod_list_price desc
)
where rownum<11

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

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
為此語句找到了效能更好的執行計劃 2。選擇以下 SQL 概要檔案之一進行實施。

Recommendation (estimated benefit<=10%)
---------------------------------------
- 考慮接受推薦的 SQL 概要檔案。
execute dbms_sqltune.accept_sql_profile(task_name =>
'SQL_TUNING_1459652706939', object_id => 2, task_owner => 'SYS',
replace => TRUE);

Recommendation (estimated benefit: 87.18%)
------------------------------------------
- 考慮接受建議的 SQL 概要檔案, 以便對此語句使用並行執行。
execute dbms_sqltune.accept_sql_profile(task_name =>
'SQL_TUNING_1459652706939', object_id => 2, task_owner => 'SYS',
replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

與 DOP 8 並行執行此查詢會使 SQL 概要檔案計劃上的響應時間縮短 86.78%。但是, 啟用並行執行時要付出一些代價。它將增加語句的資源消耗
(預計為 5.79%), 這會導致系統吞吐量降低。此外, 由於在非常短的持續時間內消耗了這些資源, 因此如果沒有足夠可用的硬體容量,
併發語句的響應時間將受到負面影響。

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 1
Percent of total activity 1.09
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 25.98

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

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 優化程式不能合併位於執行計劃的行 ID 2 處的檢視。. 優化程式不能合併包含 "ORDER BY" 子句的檢視, 除非此語句為 "DELETE" 或
"UPDATE", 並且父查詢為此語句中的頂級查詢。.

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

1- Original With Adjusted Cost
------------------------------
Plan hash value: 4141466128

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 21580 | | 136K (1)| 00:27:23 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 1886K| 3882M| | 136K (1)| 00:27:23 |
|* 3 | SORT ORDER BY STOPKEY | | 1886K| 363M| 398M| 136K (1)| 00:27:23 |
|* 4 | HASH JOIN RIGHT OUTER | | 1886K| 363M| | 53842 (1)| 00:10:47 |
| 5 | TABLE ACCESS FULL | PROMOTIONS | 503 | 24647 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 1886K| 275M| | 53831 (1)| 00:10:46 |
| 7 | TABLE ACCESS FULL | PRODUCTS | 72 | 5688 | | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER| | 1894K| 133M| | 53823 (1)| 00:10:46 |
| 9 | TABLE ACCESS FULL | CHANNELS | 5 | 65 | | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN OUTER | | 1894K| 110M| 89M| 53815 (1)| 00:10:46 |
|* 11 | HASH JOIN | | 1886K| 68M| | 31369 (1)| 00:06:17 |
|* 12 | TABLE ACCESS FULL | TIMES | 365 | 4745 | | 20 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SALES | 14M| 351M| | 31309 (1)| 00:06:16 |
| 14 | TABLE ACCESS FULL | CUSTOMERS | 1626K| 35M| | 11875 (1)| 00:02:23 |
-------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
4 - access("S"."PROMO_ID"="PRM"."PROMO_ID"(+))
6 - access("S"."PROD_ID"="P"."PROD_ID"(+))
8 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID"(+))
10 - access("S"."CUST_ID"="C"."CUST_ID"(+))
11 - access("S"."TIME_ID"="T"."TIME_ID")
12 - filter("T"."CALENDAR_YEAR"='2013')

2- Using SQL Profile
--------------------
Plan hash value: 1525702549

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 21580 | | 132K (1)| 00:26:33 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 1886K| 3882M| | 132K (1)| 00:26:33 |
|* 3 | SORT ORDER BY STOPKEY | | 1886K| 363M| 398M| 132K (1)| 00:26:33 |
|* 4 | HASH JOIN RIGHT OUTER | | 1886K| 363M| | 49649 (1)| 00:09:56 |
| 5 | TABLE ACCESS FULL | PRODUCTS | 72 | 5688 | | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 1886K| 221M| | 49641 (1)| 00:09:56 |
| 7 | TABLE ACCESS FULL | CHANNELS | 5 | 65 | | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER | | 1886K| 197M| 54M| 49632 (1)| 00:09:56 |
| 9 | TABLE ACCESS FULL | CUSTOMERS | 1626K| 35M| | 11875 (1)| 00:02:23 |
|* 10 | HASH JOIN | | 777K| 64M| | 31414 (1)| 00:06:17 |
|* 11 | TABLE ACCESS FULL | TIMES | 365 | 4745 | | 20 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT OUTER| | 14M| 1039M| | 31354 (1)| 00:06:17 |
| 13 | TABLE ACCESS FULL | PROMOTIONS | 503 | 24647 | | 5 (0)| 00:00:01 |
| 14 | TABLE ACCESS FULL | SALES | 14M| 351M| | 31309 (1)| 00:06:16 |
---------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
4 - access("S"."PROD_ID"="P"."PROD_ID"(+))
6 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID"(+))
8 - access("S"."CUST_ID"="C"."CUST_ID"(+))
10 - access("S"."TIME_ID"="T"."TIME_ID")
11 - filter("T"."CALENDAR_YEAR"='2013')
12 - access("S"."PROMO_ID"="PRM"."PROMO_ID"(+))

3- Using Parallel Execution
---------------------------
Plan hash value: 1788017369

---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 21580 | | 17549 (1)| 00:03:31 | | | |
|* 1 | COUNT STOPKEY | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (ORDER) | :TQ10007 | 1886K| 3882M| | 17549 (1)| 00:03:31 | Q1,07 | P->S | QC (ORDER) |
| 4 | VIEW | | 1886K| 3882M| | 17549 (1)| 00:03:31 | Q1,07 | PCWP | |
|* 5 | SORT ORDER BY STOPKEY | | 1886K| 363M| 398M| 17549 (1)| 00:03:31 | Q1,07 | PCWP | |
| 6 | PX RECEIVE | | 10 | 21580 | | | | Q1,07 | PCWP | |
| 7 | PX SEND RANGE | :TQ10006 | 10 | 21580 | | | | Q1,06 | P->P | RANGE |
|* 8 | SORT ORDER BY STOPKEY | | 10 | 21580 | | | | Q1,06 | PCWP | |
|* 9 | HASH JOIN RIGHT OUTER | | 1886K| 363M| | 6011 (1)| 00:01:13 | Q1,06 | PCWP | |
| 10 | PX RECEIVE | | 1626K| 35M| | 1648 (1)| 00:00:20 | Q1,06 | PCWP | |
| 11 | PX SEND HASH | :TQ10004 | 1626K| 35M| | 1648 (1)| 00:00:20 | Q1,04 | P->P | HASH |
| 12 | PX BLOCK ITERATOR | | 1626K| 35M| | 1648 (1)| 00:00:20 | Q1,04 | PCWC | |
| 13 | TABLE ACCESS FULL | CUSTOMERS | 1626K| 35M| | 1648 (1)| 00:00:20 | Q1,04 | PCWP | |
| 14 | PX RECEIVE | | 1886K| 322M| | 4361 (1)| 00:00:53 | Q1,06 | PCWP | |
| 15 | PX SEND HASH | :TQ10005 | 1886K| 322M| | 4361 (1)| 00:00:53 | Q1,05 | P->P | HASH |
|* 16 | HASH JOIN RIGHT OUTER | | 1886K| 322M| | 4361 (1)| 00:00:53 | Q1,05 | PCWP | |
| 17 | PX RECEIVE | | 503 | 24647 | | 2 (0)| 00:00:01 | Q1,05 | PCWP | |
| 18 | PX SEND BROADCAST | :TQ10000 | 503 | 24647 | | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 19 | PX BLOCK ITERATOR | | 503 | 24647 | | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 20 | TABLE ACCESS FULL | PROMOTIONS | 503 | 24647 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 21 | HASH JOIN RIGHT OUTER | | 1886K| 233M| | 4358 (1)| 00:00:53 | Q1,05 | PCWP | |
| 22 | PX RECEIVE | | 72 | 5688 | | 2 (0)| 00:00:01 | Q1,05 | PCWP | |
| 23 | PX SEND BROADCAST | :TQ10001 | 72 | 5688 | | 2 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 24 | PX BLOCK ITERATOR | | 72 | 5688 | | 2 (0)| 00:00:01 | Q1,01 | PCWC | |
| 25 | TABLE ACCESS FULL | PRODUCTS | 72 | 5688 | | 2 (0)| 00:00:01 | Q1,01 | PCWP | |
|* 26 | HASH JOIN RIGHT OUTER | | 1886K| 91M| | 4356 (1)| 00:00:53 | Q1,05 | PCWP | |
| 27 | PX RECEIVE | | 5 | 65 | | 2 (0)| 00:00:01 | Q1,05 | PCWP | |
| 28 | PX SEND BROADCAST | :TQ10002 | 5 | 65 | | 2 (0)| 00:00:01 | Q1,02 | P->P | BROADCAST |
| 29 | PX BLOCK ITERATOR | | 5 | 65 | | 2 (0)| 00:00:01 | Q1,02 | PCWC | |
| 30 | TABLE ACCESS FULL | CHANNELS | 5 | 65 | | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
|* 31 | HASH JOIN | | 1886K| 68M| | 4353 (1)| 00:00:53 | Q1,05 | PCWP | |
| 32 | PX RECEIVE | | 365 | 4745 | | 3 (0)| 00:00:01 | Q1,05 | PCWP | |
| 33 | PX SEND BROADCAST | :TQ10003 | 365 | 4745 | | 3 (0)| 00:00:01 | Q1,03 | P->P | BROADCAST |
| 34 | PX BLOCK ITERATOR | | 365 | 4745 | | 3 (0)| 00:00:01 | Q1,03 | PCWC | |
|* 35 | TABLE ACCESS FULL| TIMES | 365 | 4745 | | 3 (0)| 00:00:01 | Q1,03 | PCWP | |
| 36 | PX BLOCK ITERATOR | | 14M| 351M| | 4345 (1)| 00:00:53 | Q1,05 | PCWC | |
|* 37 | TABLE ACCESS FULL | SALES | 14M| 351M| | 4345 (1)| 00:00:53 | Q1,05 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<11)
5 - filter(ROWNUM<11)
8 - filter(ROWNUM<11)
9 - access("S"."CUST_ID"="C"."CUST_ID"(+))
16 - access("S"."PROMO_ID"="PRM"."PROMO_ID"(+))
21 - access("S"."PROD_ID"="P"."PROD_ID"(+))
26 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID"(+))
31 - access("S"."TIME_ID"="T"."TIME_ID")
35 - filter("T"."CALENDAR_YEAR"='2013')
37 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."TIME_ID"))

-------------------------------------------------------------------------------
Object ID : 4
Schema Name: SH
SQL ID : 8zc0u0bk3t6uh
SQL Text : select * from (
select c.cust_first_name || ' ' || c.cust_last_name
, c.cust_gender
, p.prod_name
, p.prod_desc
, p.prod_list_price
, p.prod_min_price
, p.prod_total
, t.time_id
, ch.channel_desc
, prm.promo_name
, prm.promo_cost
, prm.promo_total
from sales s
left join products p
on s.prod_id = p.prod_id
left join customers c
on s.cust_id = c.cust_id
left join times t
on s.time_id = t.time_id
left join channels ch
on s.channel_id = ch.channel_id
left join promotions prm
on s.promo_id = prm.promo_id
where t.CALENDAR_YEAR = '2014'
order by p.prod_list_price desc
)
where rownum<11

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

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
為此語句找到了效能更好的執行計劃。

Recommendation (estimated benefit: 86.87%)
------------------------------------------
- 考慮接受建議的 SQL 概要檔案, 以便對此語句使用並行執行。
execute dbms_sqltune.accept_sql_profile(task_name =>
'SQL_TUNING_1459652706939', object_id => 4, task_owner => 'SYS',
replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

與 DOP 8 並行執行此查詢會使原始計劃上的響應時間縮短 86.88%。但是, 啟用並行執行時要付出一些代價。它將增加語句的資源消耗 (預計為
4.99%), 這會導致系統吞吐量降低。此外, 由於在非常短的持續時間內消耗了這些資源, 因此如果沒有足夠可用的硬體容量,
併發語句的響應時間將受到負面影響。

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 1
Percent of total activity .54
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 12.99

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

-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- 優化程式不能合併位於執行計劃的行 ID 2 處的檢視。. 優化程式不能合併包含 "ORDER BY" 子句的檢視, 除非此語句為 "DELETE" 或
"UPDATE", 並且父查詢為此語句中的頂級查詢。.

-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- 當前操作因超時而中斷。

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

1- Original
-----------
Plan hash value: 4141466128

-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 21580 | | 82165 (1)| 00:16:26 |
|* 1 | COUNT STOPKEY | | | | | | |
| 2 | VIEW | | 780K| 1606M| | 82165 (1)| 00:16:26 |
|* 3 | SORT ORDER BY STOPKEY | | 780K| 150M| 164M| 82165 (1)| 00:16:26 |
|* 4 | HASH JOIN RIGHT OUTER | | 780K| 150M| | 47801 (1)| 00:09:34 |
| 5 | TABLE ACCESS FULL | PROMOTIONS | 503 | 24647 | | 5 (0)| 00:00:01 |
|* 6 | HASH JOIN RIGHT OUTER | | 780K| 113M| | 47794 (1)| 00:09:34 |
| 7 | TABLE ACCESS FULL | PRODUCTS | 72 | 5688 | | 3 (0)| 00:00:01 |
|* 8 | HASH JOIN RIGHT OUTER| | 780K| 55M| | 47789 (1)| 00:09:34 |
| 9 | TABLE ACCESS FULL | CHANNELS | 5 | 65 | | 3 (0)| 00:00:01 |
|* 10 | HASH JOIN OUTER | | 780K| 45M| 37M| 47784 (1)| 00:09:34 |
|* 11 | HASH JOIN | | 777K| 28M| | 31369 (1)| 00:06:17 |
|* 12 | TABLE ACCESS FULL | TIMES | 365 | 4745 | | 20 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | SALES | 14M| 351M| | 31309 (1)| 00:06:16 |
| 14 | TABLE ACCESS FULL | CUSTOMERS | 1626K| 35M| | 11875 (1)| 00:02:23 |
-------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<11)
3 - filter(ROWNUM<11)
4 - access("S"."PROMO_ID"="PRM"."PROMO_ID"(+))
6 - access("S"."PROD_ID"="P"."PROD_ID"(+))
8 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID"(+))
10 - access("S"."CUST_ID"="C"."CUST_ID"(+))
11 - access("S"."TIME_ID"="T"."TIME_ID")
12 - filter("T"."CALENDAR_YEAR"='2014')

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

---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 21580 | | 10783 (1)| 00:02:10 | | | |
|* 1 | COUNT STOPKEY | | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | |
| 3 | PX SEND QC (ORDER) | :TQ10007 | 780K| 1606M| | 10783 (1)| 00:02:10 | Q1,07 | P->S | QC (ORDER) |
| 4 | VIEW | | 780K| 1606M| | 10783 (1)| 00:02:10 | Q1,07 | PCWP | |
|* 5 | SORT ORDER BY STOPKEY | | 780K| 150M| 164M| 10783 (1)| 00:02:10 | Q1,07 | PCWP | |
| 6 | PX RECEIVE | | 10 | 21580 | | | | Q1,07 | PCWP | |
| 7 | PX SEND RANGE | :TQ10006 | 10 | 21580 | | | | Q1,06 | P->P | RANGE |
|* 8 | SORT ORDER BY STOPKEY | | 10 | 21580 | | | | Q1,06 | PCWP | |
|* 9 | HASH JOIN RIGHT OUTER | | 780K| 150M| | 6009 (1)| 00:01:13 | Q1,06 | PCWP | |
| 10 | PX RECEIVE | | 503 | 24647 | | 2 (0)| 00:00:01 | Q1,06 | PCWP | |
| 11 | PX SEND BROADCAST | :TQ10002 | 503 | 24647 | | 2 (0)| 00:00:01 | Q1,02 | P->P | BROADCAST |
| 12 | PX BLOCK ITERATOR | | 503 | 24647 | | 2 (0)| 00:00:01 | Q1,02 | PCWC | |
| 13 | TABLE ACCESS FULL | PROMOTIONS | 503 | 24647 | | 2 (0)| 00:00:01 | Q1,02 | PCWP | |
|* 14 | HASH JOIN RIGHT OUTER | | 780K| 113M| | 6007 (1)| 00:01:13 | Q1,06 | PCWP | |
| 15 | PX RECEIVE | | 72 | 5688 | | 2 (0)| 00:00:01 | Q1,06 | PCWP | |
| 16 | PX SEND BROADCAST | :TQ10003 | 72 | 5688 | | 2 (0)| 00:00:01 | Q1,03 | P->P | BROADCAST |
| 17 | PX BLOCK ITERATOR | | 72 | 5688 | | 2 (0)| 00:00:01 | Q1,03 | PCWC | |
| 18 | TABLE ACCESS FULL | PRODUCTS | 72 | 5688 | | 2 (0)| 00:00:01 | Q1,03 | PCWP | |
|* 19 | HASH JOIN OUTER | | 780K| 55M| | 6005 (1)| 00:01:13 | Q1,06 | PCWP | |
| 20 | PX RECEIVE | | 777K| 37M| | 4355 (1)| 00:00:53 | Q1,06 | PCWP | |
| 21 | PX SEND HASH | :TQ10004 | 777K| 37M| | 4355 (1)| 00:00:53 | Q1,04 | P->P | HASH |
|* 22 | HASH JOIN RIGHT OUTER | | 777K| 37M| | 4355 (1)| 00:00:53 | Q1,04 | PCWP | |
| 23 | PX RECEIVE | | 5 | 65 | | 2 (0)| 00:00:01 | Q1,04 | PCWP | |
| 24 | PX SEND BROADCAST | :TQ10000 | 5 | 65 | | 2 (0)| 00:00:01 | Q1,00 | P->P | BROADCAST |
| 25 | PX BLOCK ITERATOR | | 5 | 65 | | 2 (0)| 00:00:01 | Q1,00 | PCWC | |
| 26 | TABLE ACCESS FULL | CHANNELS | 5 | 65 | | 2 (0)| 00:00:01 | Q1,00 | PCWP | |
|* 27 | HASH JOIN | | 777K| 28M| | 4353 (1)| 00:00:53 | Q1,04 | PCWP | |
| 28 | PX RECEIVE | | 365 | 4745 | | 3 (0)| 00:00:01 | Q1,04 | PCWP | |
| 29 | PX SEND BROADCAST | :TQ10001 | 365 | 4745 | | 3 (0)| 00:00:01 | Q1,01 | P->P | BROADCAST |
| 30 | PX BLOCK ITERATOR | | 365 | 4745 | | 3 (0)| 00:00:01 | Q1,01 | PCWC | |
|* 31 | TABLE ACCESS FULL| TIMES | 365 | 4745 | | 3 (0)| 00:00:01 | Q1,01 | PCWP | |
| 32 | PX BLOCK ITERATOR | | 14M| 351M| | 4345 (1)| 00:00:53 | Q1,04 | PCWC | |
|* 33 | TABLE ACCESS FULL | SALES | 14M| 351M| | 4345 (1)| 00:00:53 | Q1,04 | PCWP | |
| 34 | PX RECEIVE | | 1626K| 35M| | 1648 (1)| 00:00:20 | Q1,06 | PCWP | |
| 35 | PX SEND HASH | :TQ10005 | 1626K| 35M| | 1648 (1)| 00:00:20 | Q1,05 | P->P | HASH |
| 36 | PX BLOCK ITERATOR | | 1626K| 35M| | 1648 (1)| 00:00:20 | Q1,05 | PCWC | |
| 37 | TABLE ACCESS FULL | CUSTOMERS | 1626K| 35M| | 1648 (1)| 00:00:20 | Q1,05 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------

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

1 - filter(ROWNUM<11)
5 - filter(ROWNUM<11)
8 - filter(ROWNUM<11)
9 - access("S"."PROMO_ID"="PRM"."PROMO_ID"(+))
14 - access("S"."PROD_ID"="P"."PROD_ID"(+))
19 - access("S"."CUST_ID"="C"."CUST_ID"(+))
22 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID"(+))
27 - access("S"."TIME_ID"="T"."TIME_ID")
31 - filter("T"."CALENDAR_YEAR"='2014')
33 - filter(SYS_OP_BLOOM_FILTER(:BF0000,"S"."TIME_ID"))

-------------------------------------------------------------------------------
Object ID : 3
Schema Name: SH
SQL ID : 0mvf2c00mtvfr
SQL Text : select distinct time_id from sales

-------------------------------------------------------------------------------
FINDINGS SECTION (2 findings)
-------------------------------------------------------------------------------

1- SQL Profile Finding (see explain plans section below)
--------------------------------------------------------
為此語句找到了效能更好的執行計劃。

Recommendation (estimated benefit: 86.14%)
------------------------------------------
- 考慮接受建議的 SQL 概要檔案, 以便對此語句使用並行執行。
execute dbms_sqltune.accept_sql_profile(task_name =>
'SQL_TUNING_1459652706939', object_id => 3, task_owner => 'SYS',
replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);

與 DOP 8 並行執行此查詢會使原始計劃上的響應時間縮短 86.14%。但是, 啟用並行執行時要付出一些代價。它將增加語句的資源消耗 (預計為
10.87%), 這會導致系統吞吐量降低。此外, 由於在非常短的持續時間內消耗了這些資源, 因此如果沒有足夠可用的硬體容量,
併發語句的響應時間將受到負面影響。

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 1
Percent of total activity .27
Percent of samples with #Active Sessions > 2*CPU 0
Weekly DB time (in sec) 6.49

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

2- Index Finding (see explain plans section below)
--------------------------------------------------
通過建立一個或多個索引可以改進此語句的執行計劃。

Recommendation (estimated benefit: 64.35%)
------------------------------------------
- 考慮執行可以改進物理方案設計的訪問指導或者建立推薦的索引。
create index SH.IDX$$_002A0001 on SH.SALES("TIME_ID");

Rationale
---------
建立推薦的索引可以顯著地改進此語句的執行計劃。但是, 使用典型的 SQL 工作量執行 "訪問指導"
可能比單個語句更可取。通過這種方法可以獲得全面的索引建議案, 包括計算索引維護的開銷和附加的空間消耗。

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

1- Original
-----------
Plan hash value: 647064954

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6919 | 55352 | 31722 (2)| 00:06:21 |
| 1 | HASH UNIQUE | | 6919 | 55352 | 31722 (2)| 00:06:21 |
| 2 | TABLE ACCESS FULL| SALES | 14M| 112M| 31293 (1)| 00:06:16 |
----------------------------------------------------------------------------

2- Using New Indices
--------------------
Plan hash value: 1706113998

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6919 | 55352 | 11309 (5)| 00:02:16 |
| 1 | HASH UNIQUE | | 6919 | 55352 | 11309 (5)| 00:02:16 |
| 2 | INDEX FAST FULL SCAN| IDX$$_002A0001 | 14M| 112M| 10880 (1)| 00:02:11 |
----------------------------------------------------------------------------------------

3- Using Parallel Execution
---------------------------
Plan hash value: 2636409621

------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6919 | 55352 | 4396 (2)| 00:00:53 | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 6919 | 55352 | 4396 (2)| 00:00:53 | Q1,01 | P->S | QC (RAND) |
| 3 | HASH UNIQUE | | 6919 | 55352 | 4396 (2)| 00:00:53 | Q1,01 | PCWP | |
| 4 | PX RECEIVE | | 6919 | 55352 | 4396 (2)| 00:00:53 | Q1,01 | PCWP | |
| 5 | PX SEND HASH | :TQ10000 | 6919 | 55352 | 4396 (2)| 00:00:53 | Q1,00 | P->P | HASH |
| 6 | HASH UNIQUE | | 6919 | 55352 | 4396 (2)| 00:00:53 | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | | 14M| 112M| 4343 (1)| 00:00:53 | Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL| SALES | 14M| 112M| 4343 (1)| 00:00:53 | Q1,00 | PCWP | |
------------------------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------
"

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/22621861/viewspace-2074506/，如需轉載，請註明出處，否則將追究法律責任。

在EM中使用SQL Tuning Advisor（SQL優化建議）優化SQL

相關文章