总结:这个sql的优化思路是:首先确认 问题出在 mmt表的索引所描再回表上,立马想到的是消除mmt的索引扫描,索引加了 hint(full(mmt))sql5s可运行出来;但是觉得这个sql 还有优化空间,于是改写sql,将半连接改成left join,目的是利用sql中的等价条件,想让mmt表过滤的数据多一些,由于只对主表汇总,索不考虑为null的问题
,改完之后觉得过滤数据应该没问题了,但是运行不出来,于是考虑关联顺序问题,既然mmt驱动表的时候很慢就将它换成被驱动表,于是加了 hint(/*+ use_nl(OOL) leading(OOL)*/ )sql 0.5s出
select * from table(dbms_xplan.display);
explain plan for
--change before run long long time
SELECT
NVL(ABS(SUM(MMT.TRANSACTION_QUANTITY)), 0)
FROM MTL_MATERIAL_TRANSACTIONS MMT, OE_ORDER_LINES_ALL OOL
WHERE MMT.TRANSACTION_TYPE_ID = 33
AND MMT.TRANSACTION_ACTION_ID = 1
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2
AND MMT.TRX_SOURCE_LINE_ID = OOL.LINE_ID
AND MMT.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID
AND OOL.SOLD_TO_ORG_ID = 2057
AND EXISTS
(SELECT 1
FROM CUX_OE_SALEFORECAST_ALL S
WHERE OOL.ORIG_SYS_DOCUMENT_REF = S.PLANNING_NUMBER
AND OOL.ORG_ID = S.ORG_ID
AND ABS(MMT.TRANSACTION_QUANTITY) = ABS(S.PRIMARY_QUANTITY)
AND TRUNC(NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE))
BETWEEN to_date('11/01/2017 00:00:00','MM/DD/YYYY hh24:mi:ss')
AND to_date('11/10/2017 00:00:00','MM/DD/YYYY hh24:mi:ss')
AND TRUNC(MMT.TRANSACTION_DATE) =
NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE));
Plan hash value: 226097174 --INVENTORY_ITEM_ID, ORGANIZATION_ID, TRANSACTION_DATE
---TRANSACTION_ACTION_ID, TRANSACTION_TYPE_ID, TRANSACTION_SOURCE_TYPE_ID, TRANSACTION_DATE
-------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 126 | 635 (2)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 126 | | |
| 2 | NESTED LOOPS | | 1 | 126 | 635 (2)| 00:00:01 |
| 3 | NESTED LOOPS | | 9 | 126 | 635 (2)| 00:00:01 |
| 4 | NESTED LOOPS | | 9 | 675 | 622 (2)| 00:00:01 |
| 5 | SORT UNIQUE | | 2 | 72 | 9 (0)| 00:00:01 |
|* 6 | TABLE ACCESS STORAGE FULL | CUX_OE_SALEFORECAST_ALL | 2 | 72 | 9 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID BATCHED| MTL_MATERIAL_TRANSACTIONS | 5 | 195 | 611 (2)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | MTL_MATERIAL_TRANSACTIONS_N15 | 579 | | 428 (3)| 00:00:01 |
|* 9 | INDEX UNIQUE SCAN | OE_ORDER_LINES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 10 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL | 1 | 51 | 2 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
6 - storage(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO_DATE(' 2017-11-10
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01 00:00:00',
'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO_DATE(' 2017-11-10
00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - filter("TRX_SOURCE_LINE_ID" IS NOT NULL AND ABS("TRANSACTION_QUANTITY")=ABS("S"."PRIMARY_QUANTITY"))
8 - access("TRANSACTION_ACTION_ID"=1 AND "TRANSACTION_TYPE_ID"=33 AND "TRANSACTION_SOURCE_TYPE_ID"=2)
filter(TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))=NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))
9 - access("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID")
10 - filter("OOL"."SOLD_TO_ORG_ID"=2057 AND "INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID" AND
"ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID" AND "OOL"."ORIG_SYS_DOCUMENT_REF"="S"."PLANNING_NUMBER" AND
"OOL"."ORG_ID"="S"."ORG_ID")
-- add hint -5s
SELECT /*+ full(MMT)*/
NVL(ABS(SUM(MMT.TRANSACTION_QUANTITY)), 0)
FROM MTL_MATERIAL_TRANSACTIONS MMT, OE_ORDER_LINES_ALL OOL
WHERE MMT.TRANSACTION_TYPE_ID = 33
AND MMT.TRANSACTION_ACTION_ID = 1
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2
AND MMT.TRX_SOURCE_LINE_ID = OOL.LINE_ID
AND MMT.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID
AND OOL.SOLD_TO_ORG_ID = 2057
AND EXISTS
(SELECT 1
FROM CUX_OE_SALEFORECAST_ALL S
WHERE OOL.ORIG_SYS_DOCUMENT_REF = S.PLANNING_NUMBER
AND OOL.ORG_ID = S.ORG_ID
AND ABS(MMT.TRANSACTION_QUANTITY) = ABS(S.PRIMARY_QUANTITY)
AND TRUNC(NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE))
BETWEEN to_date('11/01/2017 00:00:00','MM/DD/YYYY hh24:mi:ss')
AND to_date('11/10/2017 00:00:00','MM/DD/YYYY hh24:mi:ss')
AND TRUNC(MMT.TRANSACTION_DATE) =
NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE));
Plan hash value: 2114840721
------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 126 | 611K (2)| 00:00:24 |
| 1 | SORT AGGREGATE | | 1 | 126 | | |
| 2 | NESTED LOOPS | | 1 | 126 | 611K (2)| 00:00:24 |
| 3 | NESTED LOOPS | | 9 | 126 | 611K (2)| 00:00:24 |
|* 4 | HASH JOIN | | 9 | 675 | 611K (2)| 00:00:24 |
| 5 | SORT UNIQUE | | 2 | 72 | 9 (0)| 00:00:01 |
|* 6 | TABLE ACCESS STORAGE FULL| CUX_OE_SALEFORECAST_ALL | 2 | 72 | 9 (0)| 00:00:01 |
|* 7 | TABLE ACCESS STORAGE FULL | MTL_MATERIAL_TRANSACTIONS | 47905 | 1824K| 611K (2)| 00:00:24 |
|* 8 | INDEX UNIQUE SCAN | OE_ORDER_LINES_U1 | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_LINES_ALL | 1 | 51 | 2 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access(ABS("TRANSACTION_QUANTITY")=ABS("S"."PRIMARY_QUANTITY") AND
TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))=NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))
6 - storage(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=
TO_DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=
TO_DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - storage("TRANSACTION_TYPE_ID"=33 AND "TRANSACTION_SOURCE_TYPE_ID"=2 AND "TRX_SOURCE_LINE_ID"
IS NOT NULL AND "TRANSACTION_ACTION_ID"=1)
filter("TRANSACTION_TYPE_ID"=33 AND "TRANSACTION_SOURCE_TYPE_ID"=2 AND "TRX_SOURCE_LINE_ID"
IS NOT NULL AND "TRANSACTION_ACTION_ID"=1)
8 - access("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID")
9 - filter("OOL"."SOLD_TO_ORG_ID"=2057 AND "INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID" AND
"ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID" AND "OOL"."ORIG_SYS_DOCUMENT_REF"="S"."PLANNING_NUMBER"
AND "OOL"."ORG_ID"="S"."ORG_ID")
--after change sql 0.5s
select * from table(dbms_xplan.display);
explain plan for
SELECT /*+ use_nl(OOL) leading(OOL) */
NVL(ABS(SUM(MMT.TRANSACTION_QUANTITY)), 0)
FROM MTL_MATERIAL_TRANSACTIONS MMT, OE_ORDER_LINES_ALL OOL
left join CUX_OE_SALEFORECAST_ALL s on OOL.ORIG_SYS_DOCUMENT_REF = S.PLANNING_NUMBER
AND OOL.ORG_ID = S.ORG_ID
WHERE MMT.TRANSACTION_TYPE_ID = 33
AND MMT.TRANSACTION_ACTION_ID = 1
AND MMT.TRANSACTION_SOURCE_TYPE_ID = 2
AND MMT.TRX_SOURCE_LINE_ID = OOL.LINE_ID
AND MMT.INVENTORY_ITEM_ID = OOL.INVENTORY_ITEM_ID
AND MMT.ORGANIZATION_ID = OOL.SHIP_FROM_ORG_ID
AND OOL.SOLD_TO_ORG_ID = 2057
AND ABS(MMT.TRANSACTION_QUANTITY) = ABS(S.PRIMARY_QUANTITY)
AND TRUNC(NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE))
BETWEEN to_date('11/01/2017 00:00:00','MM/DD/YYYY hh24:mi:ss')
AND to_date('11/10/2017 00:00:00','MM/DD/YYYY hh24:mi:ss')
AND TRUNC(MMT.TRANSACTION_DATE) =
NVL(S.RESERVATION_DATE_TO, S.PLANNING_DATE);
Plan hash value: 183599765
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 126 | 229K (1)| 00:00:09 |
| 1 | SORT AGGREGATE | | 1 | 126 | | |
| 2 | NESTED LOOPS | | 1 | 126 | 229K (1)| 00:00:09 |
| 3 | NESTED LOOPS | | 1 | 126 | 229K (1)| 00:00:09 |
|* 4 | HASH JOIN | | 1 | 87 | 229K (1)| 00:00:09 |
|* 5 | TABLE ACCESS STORAGE FULL| OE_ORDER_LINES_ALL | 132 | 6732 | 229K (1)| 00:00:09 |
|* 6 | TABLE ACCESS STORAGE FULL| CUX_OE_SALEFORECAST_ALL | 2 | 72 | 9 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | MTL_MATERIAL_TRANSACTIONS_N1 | 1 | | 3 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| MTL_MATERIAL_TRANSACTIONS | 1 | 39 | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OOL"."ORIG_SYS_DOCUMENT_REF"="S"."PLANNING_NUMBER" AND "OOL"."ORG_ID"="S"."ORG_ID")
5 - storage("OOL"."SOLD_TO_ORG_ID"=2057)
filter("OOL"."SOLD_TO_ORG_ID"=2057)
6 - storage(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO
_DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO
_DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - access("INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID" AND
"ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID")
filter(TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))=NVL("S"."RESERVATION_DATE_TO","S"."PLANNING
_DATE"))
8 - filter("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID" AND "TRANSACTION_TYPE_ID"=33 AND
"TRANSACTION_SOURCE_TYPE_ID"=2 AND "TRX_SOURCE_LINE_ID" IS NOT NULL AND "TRANSACTION_ACTION_ID"=1 AND
ABS("TRANSACTION_QUANTITY")=ABS("S"."PRIMARY_QUANTITY"));
再修改之后表的关联顺序发生了变化,使用了另外一个索引 N1,下面测试一下两个两个索引哪个相对性能好些;
--/*+ use_nl(OOL) leading(OOL)index(MMT,MTL_MATERIAL_TRANSACTIONS_N15)*/
Plan hash value: 3610194404
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 126 | 230K (1)| 00:00:10 |
| 1 | SORT AGGREGATE | | 1 | 126 | | |
| 2 | NESTED LOOPS | | 1 | 126 | 230K (1)| 00:00:10 |
| 3 | NESTED LOOPS | | 579 | 126 | 230K (1)| 00:00:10 |
|* 4 | HASH JOIN | | 1 | 87 | 229K (1)| 00:00:09 |
|* 5 | TABLE ACCESS STORAGE FULL| OE_ORDER_LINES_ALL | 132 | 6732 | 229K (1)| 00:00:09 |
|* 6 | TABLE ACCESS STORAGE FULL| CUX_OE_SALEFORECAST_ALL | 2 | 72 | 9 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | MTL_MATERIAL_TRANSACTIONS_N15 | 579 | | 428 (3)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID| MTL_MATERIAL_TRANSACTIONS | 1 | 39 | 611 (2)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("OOL"."ORIG_SYS_DOCUMENT_REF"="S"."PLANNING_NUMBER" AND "OOL"."ORG_ID"="S"."ORG_ID")
5 - storage("OOL"."SOLD_TO_ORG_ID"=2057)
filter("OOL"."SOLD_TO_ORG_ID"=2057)
6 - storage(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO_
DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
filter(TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))>=TO_DATE(' 2017-11-01
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND TRUNC(NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_DATE"))<=TO_
DATE(' 2017-11-10 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
7 - access("TRANSACTION_ACTION_ID"=1 AND "TRANSACTION_TYPE_ID"=33 AND
"TRANSACTION_SOURCE_TYPE_ID"=2)
filter(TRUNC(INTERNAL_FUNCTION("TRANSACTION_DATE"))=NVL("S"."RESERVATION_DATE_TO","S"."PLANNING_
DATE"))
8 - filter("TRX_SOURCE_LINE_ID"="OOL"."LINE_ID" AND "INVENTORY_ITEM_ID"="OOL"."INVENTORY_ITEM_ID"
AND "ORGANIZATION_ID"="OOL"."SHIP_FROM_ORG_ID" AND "TRX_SOURCE_LINE_ID" IS NOT NULL AND
ABS("TRANSACTION_QUANTITY")=ABS("S"."PRIMARY_QUANTITY"))
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/28380626/viewspace-2148356/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/28380626/viewspace-2148356/