0 Replies Latest reply: Jun 8, 2014 11:13 PM by Vikas0204 RSS

    Query take to much time in 10g using VW_NSO_1 view in 11g

    Vikas0204

      I have a query like

      SELECT DISTINCT PM.PARTY_CODE, PM.PARTY_NAME FROM PURCHASE_ORDER PO, PURCHASE_ORDER_ITEM POI, PARTY_MASTER PM

      WHERE PO.PO_NO = POI.PO_NO AND PO.PO_DATE = POI.PO_DATE AND OPEN_PO='Y' AND PO.CCODE=:1 AND PO.FYCODE=:GLOBAL.MYFINY AND PM.PARTY_CODE=PO.SUPP_CODE AND PM.CCODE=PO.CCODE AND (PO.PO_NO,PO.PO_DATE,POI.ITEM_CODE,POI.QTY_ORD) NOT IN (SELECT DISTINCT PS.PO_NO,PS.PO_DATE,PD.ITEM_CODE,SUM(NVL(PD.QTY,0)) SCH_QTY FROM PURCHASE_ORDER_SCHEDULE PS, PURCHASE_SCHEDULE_DETAIL PD

      WHERE PS.SCHEDULE_NO = PD.SCHEDULE_NO AND PS.SCHEDULE_DATE = PD.SCHEDULE_DATE AND PS.CCODE = :2 AND PS.FYCODE=:GLOBAL.MYFINY GROUP BY PS.PO_NO,PS.PO_DATE,PD.ITEM_CODE) UNION SELECT PM.PARTY_CODE, PM.PARTY_NAME FROM WORK_ORDER_MASTER W, WORK_ORDER_RET_ITEM_DETAIL WD, PARTY_MASTER PM

      WHERE W.WORK_ORDER_NO = WD.WORK_ORDER_NO AND W.WORK_ORDER_DATE = WD.WORK_ORDER_dATE AND W.CCODE=:3 AND PM.PARTY_CODE=W.SUPPLIER_CODE AND PM.CCODE=W.CCODE AND (W.WORK_ORDER_NO,W.WORK_ORDER_DATE,WD.RET_ITEM_CODE,WD.RET_ITEM_QTY) NOT IN (SELECT DISTINCT PS.PO_NO,PS.PO_DATE,PD.ITEM_CODE,SUM(NVL(PD.QTY,0)) SCH_QTY FROM PURCHASE_ORDER_SCHEDULE PS, PURCHASE_SCHEDULE_DETAIL PD

      WHERE PS.SCHEDULE_NO = PD.SCHEDULE_NO AND PS.SCHEDULE_DATE = PD.SCHEDULE_DATE AND PS.CCODE = :4 AND PS.FYCODE = :5 GROUP BY PS.PO_NO,PS.PO_DATE,PD.ITEM_CODE);

       

      First i have tune it in 11g using sqltrpt.sql this gives me sql_profile_accept suggestion when i use it, its solves the issue and query is executed fast.

      this is the execution plan in 11g after tunning.

      1- Original

      -----------

      Plan hash value: 1571402389

       

       

      -----------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                                     | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

      -----------------------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                 |                                 |  3029 |   402K|       |   494   (3)| 00:00:06 |

      |   1 |  SORT UNIQUE                           |                                 |  3029 |   402K|   456K|   423   (9)| 00:00:06 |

      |   2 |   UNION-ALL                               |                                 |       |       |       |            |          |

      |*  3 |    HASH JOIN                             |                            |  3028 |   402K|       |   300   (2)| 00:00:04 |

      |*  4 |     TABLE ACCESS FULL            | PARTY_MASTER               |   410 | 11890 |       |     5   (0)| 00:00:01 |

      |   5 |     MERGE JOIN ANTI NA            |                            |  3028 |   316K|       |   295   (2)| 00:00:04 |

       

       

      |   6 |      SORT JOIN                             |                            |  3028 |   186K|   472K|   140   (2)| 00:00:02 |

      |*  7 |       HASH JOIN                           |                            |  3028 |   186K|       |    91   (2)| 00:00:02 |

      |*  8 |        TABLE ACCESS FULL         | PURCHASE_ORDER             |  1066 | 30914 |       |    22   (0)| 00:00:01 |

      |   9 |        TABLE ACCESS FULL         | PURCHASE_ORDER_ITEM        |  6056 |   201K|       |    68   (0)| 00:00:01 |

      |* 10 |      SORT UNIQUE                      |                            |  4667 |   200K|   536K|   155   (2)| 00:00:02 |

      |  11 |       VIEW                                   | VW_NSO_1                   |  4667 |   200K|       |   100   (2)| 00:00:02 |

      |  12 |        HASH GROUP BY               |                            |  4667 |   319K|   400K|   100   (2)| 00:00:02 |

      |* 13 |         HASH JOIN                       |                            |  4667 |   319K|       |    21   (5)| 00:00:01 |

      |* 14 |          TABLE ACCESS FULL      | PURCHASE_ORDER_SCHEDULE    |  1839 | 69882 |       |     9   (0)| 00:00:01 |

      |  15 |          TABLE ACCESS FULL      | PURCHASE_SCHEDULE_DETAIL   |  4678 |   146K|       |    11   (0)| 00:00:01 |

      |  16 |    NESTED LOOPS                    |                            |       |       |       |            |          |

      |  17 |     NESTED LOOPS                   |                            |     1 |    98 |       |     5   (0)| 00:00:01 |

      |  18 |      NESTED LOOPS                  |                            |     1 |    69 |       |     4   (0)| 00:00:01 |

      |  19 |       TABLE ACCESS FULL          | WORK_ORDER_RET_ITEM_DETAIL |     1 |    37 |       |     3   (0)| 00:00:01 |

      |* 20 |       TABLE ACCESS BY INDEX ROWID| WORK_ORDER_MASTER          |     1 |    32 |       |     1   (0)| 00:00:01 |

      |* 21 |        INDEX UNIQUE SCAN         | PK_WORK_ORDER_MASTER       |     1 |       |       |     0   (0)| 00:00:01 |

      |* 22 |         FILTER                              |                            |       |       |       |            |          |

      |  23 |          HASH GROUP BY             |                            |     1 |    75 |       |    22  (10)| 00:00:01 |

      |* 24 |           HASH JOIN                      |                            |   278 | 20850 |       |    21   (5)| 00:00:01 |

      |* 25 |            TABLE ACCESS FULL     | PURCHASE_ORDER_SCHEDULE    |   119 |  5117 |       |     9   (0)| 00:00:01 |

      |  26 |            TABLE ACCESS FULL     | PURCHASE_SCHEDULE_DETAIL   |  4678 |   146K|       |    11   (0)| 00:00:01 |

      |* 27 |      INDEX UNIQUE SCAN           | PK_PARTY_CODE              |     1 |       |       |     0   (0)| 00:00:01 |

      |* 28 |     TABLE ACCESS BY INDEX ROWID  | PARTY_MASTER               |     1 |    29 |       |     1   (0)| 00:00:01 |

      -----------------------------------------------------------------------------------------------------------------------

       

      and when i use manual sql tuning advisor its is not showing mwe any advice in 10g

       

      So, How can i solve it in 10g

       

      this is the execution plan in 10g

       

      1- Original

      -----------

      Plan hash value: 1979320185

       

       

      ----------------------------------------------------------------------------------------------------------------------

      | Id  | Operation                       | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |

      ----------------------------------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                |                            |       |       |       | 44728 (100)|          |

      |   1 |  SORT UNIQUE                    |                            |  3906 |   392K|   904K| 44636  (10)| 00:08:56 |

      |   2 |   UNION-ALL                     |                            |       |       |       |            |          |

      |*  3 |    FILTER                       |                            |       |       |       |            |          |

      |*  4 |     HASH JOIN                   |                            |  3905 |   392K|       |    62   (4)| 00:00:01 |

      |*  5 |      HASH JOIN                  |                            |  1376 | 89440 |       |    22   (5)| 00:00:01 |

      |*  6 |       TABLE ACCESS FULL         | PARTY_MASTER               |   400 | 12400 |       |     4   (0)| 00:00:01 |

      |*  7 |       TABLE ACCESS FULL         | PURCHASE_ORDER             |  1692 | 57528 |       |    17   (0)| 00:00:01 |

      |   8 |      TABLE ACCESS FULL          | PURCHASE_ORDER_ITEM        |  6106 |   226K|       |    39   (0)| 00:00:01 |

      |*  9 |     FILTER                      |                            |       |       |       |            |          |

      |  10 |      SORT GROUP BY              |                            |     1 |    79 |       |    19   (6)| 00:00:01 |

      |* 11 |       HASH JOIN                 |                            |  1856 |   143K|       |    18   (0)| 00:00:01 |

      |* 12 |        TABLE ACCESS FULL        | PURCHASE_ORDER_SCHEDULE    |  1856 | 79808 |       |     8   (0)| 00:00:01 |

      |  13 |        TABLE ACCESS FULL        | PURCHASE_SCHEDULE_DETAIL   |  4706 |   165K|       |    10   (0)| 00:00:01 |

      |  14 |    NESTED LOOPS                 |                            |     1 |   100 |       |     4   (0)| 00:00:01 |

      |  15 |     NESTED LOOPS                |                            |     1 |    69 |       |     3   (0)| 00:00:01 |

      |  16 |      TABLE ACCESS FULL          | WORK_ORDER_RET_ITEM_DETAIL |     1 |    37 |       |     2   (0)| 00:00:01 |

      |* 17 |      TABLE ACCESS BY INDEX ROWID| WORK_ORDER_MASTER          |     1 |    32 |       |     1   (0)| 00:00:01 |

      |* 18 |       INDEX UNIQUE SCAN         | PK_WORK_ORDER_MASTER       |     1 |       |       |     0   (0)|          |

      |* 19 |        FILTER                   |                            |       |       |       |            |          |

      |  20 |         SORT GROUP BY           |                            |     1 |    85 |       |    19   (6)| 00:00:01 |

      |* 21 |          HASH JOIN              |                            |   135 | 11475 |       |    18   (0)| 00:00:01 |

      |* 22 |           TABLE ACCESS FULL     | PURCHASE_ORDER_SCHEDULE    |   135 |  6615 |       |     8   (0)| 00:00:01 |

      |  23 |           TABLE ACCESS FULL     | PURCHASE_SCHEDULE_DETAIL   |  4706 |   165K|       |    10   (0)| 00:00:01 |

      |* 24 |     TABLE ACCESS BY INDEX ROWID | PARTY_MASTER               |     1 |    31 |       |     1   (0)| 00:00:01 |

      |* 25 |      INDEX UNIQUE SCAN          | PK_PARTY_CODE              |     1 |       |       |     0   (0)|          |

      ----------------------------------------------------------------------------------------------------------------------