下面的sql，2000多条数据，3分钟出结果。不加workshop_id秒出结果。比较两者的执行计划，发现慢sql对MTL_ORGANIZATIONS做了merge，通过修改CUX_INV_ITEM_REQ_SOURCE_V的定义，加hint：no_merge(mo)禁止视图合并解决问题。
SELECT COUNT(1) FROM CUX_INV_ITEM_REQ_SOURCE_V T
WHERE T.organization_id =120
AND T.complete_date >= TO_DATE('2016-02-25','YYYY-MM-DD')
AND T.complete_date <= TO_DATE('2016-02-25','YYYY-MM-DD')
and t.workshop_id = 10567; SELECT COUNT(1) FROM CUX_INV_ITEM_REQ_SOURCE_V T
WHERE T.organization_id =120
AND T.complete_date >= TO_DATE('2016-02-25','YYYY-MM-DD')
AND T.complete_date <= TO_DATE('2016-02-25','YYYY-MM-DD')
and t.workshop_id = 10567;
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 783 (1)|
| 1 | SORT AGGREGATE | | 1 | | |
| 2 | VIEW | VM_NWVW_1 | 1 | | 783 (1)|
| 3 | HASH GROUP BY | | 1 | 703 | 783 (1)|
| 4 | TABLE ACCESS BY INDEX ROWID | FND_CURRENCIES | 1 | 7 | 1 (0)|
| 5 | NESTED LOOPS | | 3 | 2109 | 782 (1)|
|* 6 | HASH JOIN | | 3 | 2088 | 779 (1)|
| 7 | MERGE JOIN CARTESIAN | | 5 | 3315 | 776 (1)|
| 8 | NESTED LOOPS OUTER | | 13 | 8268 | 757 (1)|
| 9 | NESTED LOOPS | | 13 | 8047 | 756 (1)|
| 10 | NESTED LOOPS | | 11 | 6677 | 745 (1)|
| 11 | NESTED LOOPS | | 153 | 91494 | 592 (1)|
| 12 | NESTED LOOPS | | 1 | 589 | 563 (1)|
| 13 | NESTED LOOPS | | 1 | 547 | 561 (1)|
| 14 | NESTED LOOPS ANTI | | 1 | 516 | 561 (1)|
| 15 | NESTED LOOPS | | 1 | 508 | 561 (1)|
| 16 | NESTED LOOPS | | 1 | 488 | 561 (1)|
| 17 | NESTED LOOPS | | 1 | 472 | 561 (1)|
| 18 | NESTED LOOPS | | 1 | 452 | 561 (1)|
| 19 | NESTED LOOPS | | 1 | 431 | 560 (1)|
| 20 | NESTED LOOPS OUTER | | 1 | 413 | 559 (1)|
| 21 | NESTED LOOPS | | 1 | 389 | 559 (1)|
| 22 | NESTED LOOPS | | 2 | 650 | 353 (1)|
| 23 | MERGE JOIN CARTESIAN | | 7 | 2037 | 339 (1)|
| 24 | NESTED LOOPS | | 1 | 257 | 6 (0)|
| 25 | NESTED LOOPS | | 1 | 227 | 4 (0)|
| 26 | NESTED LOOPS | | 1 | 219 | 4 (0)|
| 27 | NESTED LOOPS | | 1 | 74 | 3 (0)|
| 28 | NESTED LOOPS | | 1 | 57 | 3 (0)|
| 29 | NESTED LOOPS | | 1 | 45 | 2 (0)|
| 30 | NESTED LOOPS | | 1 | 16 | 1 (0)|
|* 31 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 8 | 0 (0)|
| 32 | TABLE ACCESS BY INDEX ROWID| MTL_PARAMETERS | 1 | 8 | 1 (0)|
|* 33 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | | 0 (0)|
| 34 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS_TL | 1 | 29 | 1 (0)|
|* 35 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | | 0 (0)|
|* 36 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 12 | 1 (0)|
|* 37 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)|
|* 38 | INDEX UNIQUE SCAN | CUX_WIP_WORKSHOP_MAINTAIN_U1 | 1 | 17 | 0 (0)|
|* 39 | TABLE ACCESS BY INDEX ROWID | HR_LOCATIONS_ALL | 1 | 145 | 1 (0)|
|* 40 | INDEX UNIQUE SCAN | HR_LOCATIONS_PK | 1 | | 0 (0)|
|* 41 | INDEX UNIQUE SCAN | HR_LOCATIONS_ALL_TL_PK | 1 | 8 | 0 (0)|
|* 42 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 30 | 2 (0)|
|* 43 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 2 | | 1 (0)|
| 44 | BUFFER SORT | | 58 | 1972 | 337 (1)|
|* 45 | MAT_VIEW ACCESS FULL | CUX_INV_ITEM_REQ_ORDER_MV | 58 | 1972 | 333 (1)|
|* 46 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_ALL | 1 | 34 | 2 (0)|
|* 47 | INDEX RANGE SCAN | CUX_OE_ORDER_HEADERS_N1 | 1 | | 1 (0)|
|* 48 | TABLE ACCESS BY INDEX ROWID | CUX_OM_SO_FULFIL_LINES | 1 | 64 | 119 (0)|
|* 49 | INDEX RANGE SCAN | CUX_OM_SO_FULFIL_LINES_N1 | 158 | | 2 (0)|
|* 50 | INDEX UNIQUE SCAN | MTL_ITEM_SUB_DEFAULTS_U1 | 1 | 24 | 0 (0)|
|* 51 | INDEX UNIQUE SCAN | CUX_WIP_TASK_ORDERS_U1 | 1 | 18 | 1 (0)|
|* 52 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | 21 | 1 (0)|
|* 53 | INDEX UNIQUE SCAN | OE_TRANSACTION_TYPES_TL_U1 | 1 | 20 | 0 (0)|
|* 54 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | 16 | 0 (0)|
|* 55 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 20 | 0 (0)|
|* 56 | INDEX UNIQUE SCAN | CUX_INV_ITEM_REQ_LINES_U2 | 446 | 3568 | 0 (0)|
|* 57 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 31 | 0 (0)|
|* 58 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 42 | 2 (0)|
|* 59 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 2 | | 1 (0)|
|* 60 | INDEX RANGE SCAN | MTL_ONHAND_QUANTITIES_N6 | 7387 | 66483 | 29 (0)|
|* 61 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | 9 | 1 (0)|
|* 62 | INDEX UNIQUE SCAN | CST_ITEM_COSTS_U1 | 1 | 12 | 1 (0)|
|* 63 | INDEX RANGE SCAN | PA_PROJECTS_U1 | 1 | 17 | 1 (0)|
| 64 | BUFFER SORT | | 1 | 27 | 775 (1)|
|* 65 | TABLE ACCESS FULL | HR_ORGANIZATION_INFORMATION | 1 | 27 | 1 (0)|
|* 66 | TABLE ACCESS FULL | GL_LEDGERS | 2 | 66 | 3 (0)|
|* 67 | INDEX UNIQUE SCAN | FND_CURRENCIES_U1 | 1 | | 0 (0)|
---------------------------------------------------------------------------------------------------------------------------------SELECT COUNT(1) FROM CUX_INV_ITEM_REQ_SOURCE_V T
WHERE T.organization_id =120
AND T.complete_date >= TO_DATE(‘2016-02-25’,’YYYY-MM-DD’)
AND T.complete_date <= TO_DATE(‘2016-02-25’,’YYYY-MM-DD’);
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 223 | 2178 (1)|
| 1 | SORT AGGREGATE | | 1 | 223 | |
| 2 | NESTED LOOPS | | 40 | 8920 | 2178 (1)|
| 3 | NESTED LOOPS | | 30 | 6450 | 2178 (1)|
| 4 | NESTED LOOPS | | 23 | 4761 | 2178 (1)|
| 5 | NESTED LOOPS | | 21 | 3948 | 2178 (1)|
| 6 | NESTED LOOPS | | 18 | 3222 | 2160 (1)|
| 7 | NESTED LOOPS | | 18 | 3132 | 2160 (1)|
|* 8 | HASH JOIN | | 19 | 3192 | 2141 (1)|
| 9 | NESTED LOOPS OUTER | | 20 | 2760 | 2138 (1)|
| 10 | NESTED LOOPS OUTER | | 20 | 2520 | 2138 (1)|
| 11 | NESTED LOOPS ANTI | | 20 | 2420 | 2137 (1)|
| 12 | NESTED LOOPS | | 20 | 2260 | 2137 (1)|
|* 13 | HASH JOIN | | 20 | 2180 | 2137 (1)|
|* 14 | TABLE ACCESS BY INDEX ROWID | CUX_OM_SO_FULFIL_LINES | 1 | 52 | 119 (0)|
| 15 | NESTED LOOPS | | 2212 | 1920 | 1891 (1)|
| 16 | NESTED LOOPS | | 14 | 616 | 449 (1)|
|* 17 | MAT_VIEW ACCESS FULL | CUX_INV_ITEM_REQ_ORDER_MV | 58 | 1276 | 333 (1)|
|* 18 | TABLE ACCESS BY INDEX ROWID | OE_ORDER_HEADERS_ALL | 1 | 22 | 2 (0)|
|* 19 | INDEX RANGE SCAN | CUX_OE_ORDER_HEADERS_N1 | 1 | | 1 (0)|
|* 20 | INDEX RANGE SCAN | CUX_OM_SO_FULFIL_LINES_N1 | 158 | | 2 (0)|
| 21 | VIEW | MTL_ORGANIZATIONS | 1 | 13 | 246 (1)|
| 22 | HASH GROUP BY | | 1 | 337 | 246 (1)|
| 23 | NESTED LOOPS | | 16 | 5392 | 245 (0)|
| 24 | NESTED LOOPS | | 14 | 4550 | 231 (0)|
| 25 | NESTED LOOPS | | 192 | 60672 | 39 (0)|
| 26 | MERGE JOIN CARTESIAN | | 1 | 307 | 10 (0)|
| 27 | TABLE ACCESS BY INDEX ROWID | FND_CURRENCIES | 1 | 7 | 1 (0)|
| 28 | NESTED LOOPS | | 1 | 277 | 8 (0)|
| 29 | NESTED LOOPS | | 1 | 270 | 7 (0)|
| 30 | NESTED LOOPS | | 1 | 237 | 6 (0)|
| 31 | NESTED LOOPS | | 1 | 210 | 4 (0)|
| 32 | NESTED LOOPS | | 1 | 202 | 4 (0)|
| 33 | NESTED LOOPS | | 1 | 57 | 3 (0)|
| 34 | NESTED LOOPS | | 1 | 45 | 2 (0)|
| 35 | NESTED LOOPS | | 1 | 16 | 1 (0)|
|* 36 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 8 | 0 (0)|
| 37 | TABLE ACCESS BY INDEX ROWID| MTL_PARAMETERS | 1 | 8 | 1 (0)|
|* 38 | INDEX UNIQUE SCAN | MTL_PARAMETERS_U1 | 1 | | 0 (0)|
| 39 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS_TL | 1 | 29 | 1 (0)|
|* 40 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | | 0 (0)|
|* 41 | TABLE ACCESS BY INDEX ROWID | HR_ALL_ORGANIZATION_UNITS | 1 | 12 | 1 (0)|
|* 42 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | | 0 (0)|
|* 43 | TABLE ACCESS BY INDEX ROWID | HR_LOCATIONS_ALL | 1 | 145 | 1 (0)|
|* 44 | INDEX UNIQUE SCAN | HR_LOCATIONS_PK | 1 | | 0 (0)|
|* 45 | INDEX UNIQUE SCAN | HR_LOCATIONS_ALL_TL_PK | 1 | 8 | 0 (0)|
| 46 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 27 | 2 (0)|
|* 47 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | | 1 (0)|
|* 48 | TABLE ACCESS BY INDEX ROWID | GL_LEDGERS | 1 | 33 | 1 (0)|
|* 49 | INDEX UNIQUE SCAN | GL_LEDGERS_U2 | 1 | | 0 (0)|
|* 50 | INDEX UNIQUE SCAN | FND_CURRENCIES_U1 | 1 | | 0 (0)|
| 51 | BUFFER SORT | | 1 | 30 | 9 (0)|
|* 52 | TABLE ACCESS BY INDEX ROWID | HR_ORGANIZATION_INFORMATION | 1 | 30 | 2 (0)|
|* 53 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 2 | | 1 (0)|
|* 54 | INDEX RANGE SCAN | MTL_ONHAND_QUANTITIES_N6 | 7387 | 66483 | 29 (0)|
|* 55 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | 9 | 1 (0)|
|* 56 | INDEX UNIQUE SCAN | CST_ITEM_COSTS_U1 | 1 | 12 | 1 (0)|
|* 57 | INDEX UNIQUE SCAN | HR_ORGANIZATION_UNITS_PK | 1 | 4 | 0 (0)|
|* 58 | INDEX UNIQUE SCAN | CUX_INV_ITEM_REQ_LINES_U2 | 4 | 32 | 0 (0)|
|* 59 | INDEX RANGE SCAN | PA_PROJECTS_U1 | 1 | 5 | 1 (0)|
|* 60 | INDEX UNIQUE SCAN | MTL_ITEM_SUB_DEFAULTS_U1 | 1 | 12 | 0 (0)|
|* 61 | TABLE ACCESS FULL | HR_ORGANIZATION_INFORMATION | 4 | 120 | 3 (0)|
|* 62 | INDEX UNIQUE SCAN | CUX_WIP_TASK_ORDERS_U1 | 1 | 6 | 1 (0)|
|* 63 | INDEX UNIQUE SCAN | CUX_WIP_WORKSHOP_MAINTAIN_U1 | 1 | 5 | 0 (0)|
|* 64 | INDEX UNIQUE SCAN | MTL_SYSTEM_ITEMS_B_U1 | 1 | 9 | 1 (0)|
|* 65 | INDEX RANGE SCAN | HR_ORGANIZATION_INFORMATIO_FK2 | 1 | 19 | 0 (0)|
|* 66 | INDEX UNIQUE SCAN | OE_TRANSACTION_TYPES_TL_U1 | 1 | 8 | 0 (0)|
|* 67 | INDEX UNIQUE SCAN | HR_ALL_ORGANIZATION_UNTS_TL_PK | 1 | 8 | 0 (0)|
-------------------------------------------------------------------------------------------------------------------------------CUX_INV_ITEM_REQ_SOURCE_V的定义：
SELECT oh.header_id oe_header_id
,oh.org_id
,hou.name org_name
,ot.name order_type
,oh.order_number
,oh.cust_po_number
,oh.source_document_id contract_id
,oh.attribute9 oe_batch_number
,ch.complete_date
,ch.delivery_date
,fl.fulfil_line_id
,fl.organization_id
,mo.organization_name
,fl.line_num
,fl.inventory_item_id
,msi.segment1 item_code
,msi.description item_desc
,mis.subinventory_code default_subinventory
,msi.primary_uom_code uom_code
,fl.quantity
,fl.workshop_id
,wm.workshoptitle
,wt.taskid
,wt.taskno
,fl.project_id
,pp.segment1 project_code
,pp.name project_name
,fl.ordered_flag
,fl.ordered_date
FROM cux_om_so_fulfil_lines fl
,oe_order_headers_all oh
-- ,cux_inv_item_req_order_mv ch --物化视图,5分钟更新一次
,mtl_system_items_b msi
,cux_wip_workshop_maintain wm
,cux_wip_task_orders wt
,pa_projects_all pp
,hr_operating_units hou
,mtl_organizations mo
,mtl_item_sub_defaults mis
,oe_transaction_types_tl ot
WHERE oh.header_id = fl.oe_header_id
AND oh.order_source_id = 1001 --造易来源
AND oh.order_type_id = ot.transaction_type_id
AND ot.language = 'ZHS'
-- AND oh.source_document_id = ch.id
AND fl.supply_type = 'INVENTORY' --库存件
AND fl.inventory_item_id = msi.inventory_item_id
AND msi.organization_id = 82
AND fl.inventory_item_id = mis.inventory_item_id(+)
AND mis.default_type(+) = 2
AND fl.organization_id = mis.organization_id(+)
AND fl.workshop_id = wm.workshop_id
AND fl.taskno_id = wt.taskno_id
AND fl.project_id = pp.project_id(+)
AND oh.org_id = hou.organization_id
AND fl.organization_id = mo.organization_id
AND fl.cancelled_flag = 'N' --未取消
AND fl.ordered_flag = 'Y' --已下单
AND fl.inbound_flag = 'N' --未扫码
AND fl.deliver_flag = 'N' --未处理
AND NOT EXISTS (SELECT 1
FROM cux_inv_item_req_lines rl
WHERE rl.fulfil_line_id = fl.fulfil_line_id
AND rl.cancelled_flag = 'N');
扫一扫
824
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
