长sql通过比较执行计划快速定位问题

下面的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');
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值