5 Replies Latest reply: Jun 16, 2014 9:07 AM by Mohamed Houri RSS

    how to improve query performance

    sam995972

      Hi All,

       

      oracle 11g

      Linux

       

      one of my query is taking 30 mins to execute, can any one suggest me how to reduce query execution timing?

       

      explain plan details:

       

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

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

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

      |   0 | SELECT STATEMENT                   |                          |   621 | 36018 |  1120K (19)| 04:21:32 |

      |   1 |  SORT UNIQUE NOSORT                |                          |     1 |    12 |     4  (25)| 00:00:01 |

      |*  2 |   FILTER                           |                          |       |       |            |          |

      |*  3 |    INDEX RANGE SCAN                | RE_I_IDX                 |     1 |    12 |     3   (0)| 00:00:01 |

      |   4 |  HASH GROUP BY                     |                          |   621 | 36018 |  1120K (19)| 04:21:32 |

      |*  5 |   FILTER                           |                          |       |       |            |          |

      |   6 |    MERGE JOIN CARTESIAN            |                          |  2199M|   118G|   931K  (2)| 03:37:24 |

      |   7 |     MERGE JOIN CARTESIAN           |                          |   297 | 15147 |    12   (0)| 00:00:01 |

      |   8 |      MERGE JOIN CARTESIAN          |                          |    11 |   561 |     6   (0)| 00:00:01 |

      |   9 |       NESTED LOOPS                 |                          |     1 |    46 |     5   (0)| 00:00:01 |

      |  10 |        NESTED LOOPS                |                          |     1 |    46 |     5   (0)| 00:00:01 |

      |  11 |         TABLE ACCESS BY INDEX ROWID| REQ1                     |     1 |    15 |     4   (0)| 00:00:01 |

      |* 12 |          INDEX RANGE SCAN          | REQ_RE_IDX               |     1 |       |     3   (0)| 00:00:01 |

      |* 13 |         INDEX UNIQUE SCAN          | S_PK                     |     1 |       |     0   (0)| 00:00:01 |

      |  14 |        TABLE ACCESS BY INDEX ROWID | SERVICE_PROVIDER_NAME    |     1 |    31 |     1   (0)| 00:00:01 |

      |  15 |       BUFFER SORT                  |                          |    11 |    55 |     5   (0)| 00:00:01 |

      |  16 |        INDEX FULL SCAN             | REQUEST_ITEM_PK          |    11 |    55 |     1   (0)| 00:00:01 |

      |  17 |      BUFFER SORT                   |                          |    27 |       |    11   (0)| 00:00:01 |

      |  18 |       INDEX FAST FULL SCAN         | WO_TYPE_PK               |    27 |       |     1   (0)| 00:00:01 |

      |  19 |     BUFFER SORT                    |                          |  7406K|    49M|  1120K (19)| 04:21:32 |

      |  20 |      INDEX FAST FULL SCAN          | REQITEM_PK               |  7406K|    49M|  3137   (2)| 00:00:44 |

       

       

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

       

      thanks,

      SAM.