本博客展示了SQL优化集的应用，通过SQL优化建议，实现了执行计划的改进，并提供了性能提升建议。包括使用SQL概要文件、创建索引、并行执行等策略来优化SQL查询性能。
同样是先选定SQL优化集，然后运行“SQL优化建议”
在结果页面，可以查看建议的改进操作所产生的新执行计划，以及与旧的执行计划的比较，但不能看到具体的建议操作。要查看，需要在SQL Developer中执行
由于输出较长，可将结果复制到文本文档。实例输出：
在结果页面，可以查看建议的改进操作所产生的新执行计划，以及与旧的执行计划的比较，但不能看到具体的建议操作。要查看，需要在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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/22621861/viewspace-2074506/
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
