关于一个较大SQL执行时间非常长麻烦各位大师帮忙看一下
通过我的AWR和ASH发现一个SQL执行很大，对数据库和服务器都影响较大。还希望各位能帮忙看一下这个SQL该如何进行优化。其中wh1.IF_kc01这个表有4600W左右的数据。
还望麻烦各位SQL大师帮忙指点一下，我不太懂SQL调优，如果能告知处理步骤和方法将不胜感激！~
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : SADA179542013.4163899306
Tuning Task Owner : SYSTEM
Scope : COMPREHENSIVE
Time Limit(seconds) : 1800
Completion Status : COMPLETED
Started at : 01/09/2015 09:57:50
Completed at : 01/09/2015 09:58:11
Number of SQL Restructure Findings: 1
-------------------------------------------------------------------------------
Schema Name: WH6
SQL ID : 58u9yh20bc6fa
SQL Text : /* Formatted on 2015/1/9 9:56:27 (QP5 v5.256.13226.35510) */
CREATE TABLE X
AS
SELECT F.storerkey,
F.sku,
F.lot,
F.loc,
F.id,
SUM (F.qty) AS qty
FROM (SELECT D.orderkey,
(SELECT E.EXTERNORDERKEY
FROM ORDERS E
WHERE E.ORDERKEY = D.ORDERKEY
AND E.STORERKEY = D.STORERKEY)
AS KANBAN,
D.Storerkey,
D.sku,
D.lot,
D.Loc,
D.ID,
D.qty,
D.adddate
FROM PICKDETAIL D) F
WHERE NOT EXISTS
(SELECT 1
FROM (SELECT A.fromnumber
FROM wh1.IF_kc01 A
UNION
SELECT B.barcode
FROM wh1.IF_kc02 B) C
WHERE C.FROMNUMBER = F.kanban)
AND (F.Adddate + INTERVAL '8' HOUR) >
TO_DATE (TO_CHAR (SYSDATE - 7, 'YYYY-MM-DD')
|| ' 07:55:00',
'YYYY-MM-DD HH24:MI:SS')
AND (F.Adddate + INTERVAL '8' HOUR) < SYSDATE -
INTERVAL '3' MINUTE
AND EXISTS
(SELECT 1
FROM orders E
WHERE E.OHTYPE = 3
AND E.Storerkey = F.Storerkey
AND E.Orderkey = F.ORDERKEY
AND F.Storerkey = 'VWPL')
GROUP BY F.storerkey,
F.sku,
F.lot,
F.loc,
F.id
-------------------------------------------------------------------------------
FINDINGS SECTION (1 finding)
-------------------------------------------------------------------------------
1- Restructure SQL finding (see plan 1 in explain plans section)
----------------------------------------------------------------
An expensive "UNION" operation was found at line ID 9 of the execution plan.
Recommendation
--------------
- Consider using "UNION ALL" instead of "UNION", if duplicates are allowed
or uniqueness is guaranteed.
Rationale
---------
"UNION" is an expensive and blocking operation because it requires
elimination of duplicate rows. "UNION ALL" is a cheaper alternative,
assuming that duplicates are allowed or uniqueness is guaranteed.
-------------------------------------------------------------------------------
ADDITIONAL INFORMATION SECTION
-------------------------------------------------------------------------------
- The optimizer could not merge the view at line ID 8 of the execution plan.
The optimizer cannot merge a view that contains a set operator.
-------------------------------------------------------------------------------
EXPLAIN PLANS SECTION
-------------------------------------------------------------------------------
1- Original
-----------
Plan hash value: 2115035327
------------------------------------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------------
| 0 | CREATE TABLE STATEMENT | | 982 | 154K| | 550K(1)| 01:50:01 |
| 1 |LOAD AS SELECT | X | | | | | |
| 2 | HASH GROUP BY | | 982 | 154K|6568K| 549K(1)| 01:50:00 |
|*3 | FILTER | | | | | | |
|*4 | HASH JOIN ANTI | | 19637 |3087K|3120K| 507K(2)| 01:41:31 |
| 5 | VIEW | | 19835 |2886K| | 80223 (2)| 00:16:03 |
|*6 | FILTER | | | | | | |
|*7 | TABLE ACCESS FULL | PICKDETAIL | 19835 |2382K| | 80155 (2)| 00:16:02 |
| 8 | VIEW | | 45M| 515M| | 375K(1)| 01:15:08 |
| 9 | SORT UNIQUE | | 45M| 902M|2419M| 375K(2)| 01:15:08 |
|10 | UNION-ALL | | | | | | |
|11 | INDEX FAST FULL SCAN | IDX_IF_KC01_FROMNUMBER | 44M| 901M| | 54599 (1)| 00:10:56 |
|12 | INDEX FAST FULL SCAN | IDX_IF_KC02_BARCODE | 73854 |1298K| | 72 (2)| 00:00:01 |
|* 13 | FILTER | | | | | | |
|* 14 | TABLE ACCESS BY INDEX ROWID| ORDERS | 1 | 34 | | 2 (0)| 00:00:01 |
|* 15 | INDEX UNIQUE SCAN | PK_SYS_C004711 | 1 | | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( EXISTS (SELECT 0 FROM "ORDERS" "E" WHERE :B1=U'VWPL' AND "E"."ORDERKEY"=:B2 AND
TO_NUMBER("E"."OHTYPE")=3 AND "E"."STORERKEY"=:B3))
4 - access("F"."KANBAN"=SYS_OP_C2C("C"."FROMNUMBER"))
6 - filter(TO_DATE(TO_CHAR(SYSDATE@!-7,'YYYY-MM-DD')||' 07:55:00','YYYY-MM-DD
HH24:MI:SS')<SYSDATE@!-INTERVAL'+00 00:03:00' DAY(2) TO SECOND(0))
7 - filter(INTERNAL_FUNCTION("D"."ADDDATE")+INTERVAL'+00 08:00:00' DAY(2) TO
SECOND(0)<SYSDATE@!-INTERVAL'+00 00:03:00' DAY(2) TO SECOND(0) AND
INTERNAL_FUNCTION("D"."ADDDATE")+INTERVAL'+00 08:00:00' DAY(2) TO
SECOND(0)>TO_DATE(TO_CHAR(SYSDATE@!-7,'YYYY-MM-DD')||' 07:55:00','YYYY-MM-DD HH24:MI:SS'))
13 - filter(:B1=U'VWPL')
14 - filter(TO_NUMBER("E"."OHTYPE")=3 AND "E"."STORERKEY"=:B1)
15 - access("E"."ORDERKEY"=:B1)
-------------------------------------------------------------------------------
好厉害，但看不懂
我个人意见，可以试下sql tuning advisor.
页:
[1]