1 2 Previous Next 27 Replies Latest reply: May 18, 2014 1:32 AM by Lothar Flatz RSS

    steps to tune the query on explain plan

    969952

      Hi,

       

      Please find the belwo explain plan of a query and suggest me where and how can we tune the query.

      {code}

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

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

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

      |   0 | SELECT STATEMENT                |                             |   352K|    60M|       | 17677  (12)| 00:03:33 |

      |   1 |  TEMP TABLE TRANSFORMATION      |                             |       |       |       |            |          |

      |   2 |   LOAD AS SELECT                | SYS_TEMP_0FD9FCB72_F58FAD20 |       |       |       |            |          |

      |   3 |    VIEW                         |                             |    22G|  3684G|       |    96M(100)|320:57:19 |

      |   4 |     SORT UNIQUE                 |                             |    22G|  4837G|   436G|    96M  (1)|320:57:19 |

      |   5 |      CONCATENATION              |                             |       |       |       |            |          |

      |*  6 |       HASH JOIN                 |                             |  1792M|   392G|       | 52682  (26)| 00:10:33 |

      |   7 |        VIEW                     | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*  8 |         MAT_VIEW ACCESS FULL    | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*  9 |        HASH JOIN                |                             |  1792M|   372G|  9320K| 43943  (16)| 00:08:48 |

      |  10 |         VIEW                    | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |  11 |          MAT_VIEW ACCESS FULL   | STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |* 12 |         HASH JOIN RIGHT OUTER   |                             |   676K|   128M|    19M| 29255   (1)| 00:05:52 |

      |  13 |          VIEW                   | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 14 |           TABLE ACCESS FULL     | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 15 |          HASH JOIN              |                             |   195K|    34M|  9240K| 24001   (1)| 00:04:49 |

      |  16 |           VIEW                  | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |  17 |            MAT_VIEW ACCESS FULL | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |* 18 |           HASH JOIN OUTER       |                             |   114K|    17M|    12M| 21748   (1)| 00:04:21 |

      |* 19 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      |  20 |            VIEW                 | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 21 |             TABLE ACCESS FULL   | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 22 |       HASH JOIN                 |                             |    89M|    19G|       | 46498  (16)| 00:09:18 |

      |  23 |        VIEW                     | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |* 24 |         MAT_VIEW ACCESS FULL    | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |* 25 |        HASH JOIN                |                             |    89M|    18G|  9320K| 43943  (16)| 00:08:48 |

      |  26 |         VIEW                    | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |  27 |          MAT_VIEW ACCESS FULL   | STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |* 28 |         HASH JOIN RIGHT OUTER   |                             |   676K|   128M|    19M| 29255   (1)| 00:05:52 |

      |  29 |          VIEW                   | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 30 |           TABLE ACCESS FULL     | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 31 |          HASH JOIN              |                             |   195K|    34M|  9240K| 24001   (1)| 00:04:49 |

      |  32 |           VIEW                  | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |  33 |            MAT_VIEW ACCESS FULL | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |* 34 |           HASH JOIN OUTER       |                             |   114K|    17M|    12M| 21748   (1)| 00:04:21 |

      |* 35 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      |  36 |            VIEW                 | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 37 |             TABLE ACCESS FULL   | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 38 |       HASH JOIN                 |                             |  4480K|  1004M|       | 46189  (15)| 00:09:15 |

      |  39 |        VIEW                     | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |* 40 |         MAT_VIEW ACCESS FULL    | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |* 41 |        HASH JOIN                |                             |  4480K|   952M|  9320K| 43943  (16)| 00:08:48 |

      |  42 |         VIEW                    | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |  43 |          MAT_VIEW ACCESS FULL   | STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |* 44 |         HASH JOIN RIGHT OUTER   |                             |   676K|   128M|    19M| 29255   (1)| 00:05:52 |

      |  45 |          VIEW                   | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 46 |           TABLE ACCESS FULL     | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 47 |          HASH JOIN              |                             |   195K|    34M|  9240K| 24001   (1)| 00:04:49 |

      |  48 |           VIEW                  | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |  49 |            MAT_VIEW ACCESS FULL | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |* 50 |           HASH JOIN OUTER       |                             |   114K|    17M|    12M| 21748   (1)| 00:04:21 |

      |* 51 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      |  52 |            VIEW                 | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 53 |             TABLE ACCESS FULL   | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 54 |       HASH JOIN OUTER           |                             |   224K|    50M|    14M| 32597   (4)| 00:06:32 |

      |* 55 |        HASH JOIN                |                             | 64775 |    13M|  7696K| 28458   (5)| 00:05:42 |

      |* 56 |         HASH JOIN OUTER         |                             | 37855 |  7245K|  5416K| 26763   (5)| 00:05:22 |

      |* 57 |          HASH JOIN              |                             | 37675 |  4966K|       |  6436  (18)| 00:01:18 |

      |  58 |           VIEW                  | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |* 59 |            MAT_VIEW ACCESS FULL | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |* 60 |           HASH JOIN             |                             | 37675 |  4525K|  9320K|  4206  (27)| 00:00:51 |

      |  61 |            VIEW                 | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |  62 |             MAT_VIEW ACCESS FULL| STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |* 63 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      |  64 |          VIEW                   | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 65 |           TABLE ACCESS FULL     | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |  66 |         VIEW                    | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |  67 |          MAT_VIEW ACCESS FULL   | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |  68 |        VIEW                     | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 69 |         TABLE ACCESS FULL       | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 70 |       HASH JOIN OUTER           |                             | 11201 |  2570K|       | 23267   (6)| 00:04:40 |

      |* 71 |        HASH JOIN                |                             |  3239 |   702K|       | 20816   (6)| 00:04:10 |

      |* 72 |         HASH JOIN OUTER         |                             |  1893 |   362K|       | 19942   (7)| 00:04:00 |

      |* 73 |          HASH JOIN              |                             |  1884 |   248K|       |  6435  (18)| 00:01:18 |

      |* 74 |           HASH JOIN             |                             |  1884 |   226K|  9320K|  4206  (27)| 00:00:51 |

      |  75 |            VIEW                 | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |  76 |             MAT_VIEW ACCESS FULL| STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |* 77 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      |  78 |           VIEW                  | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |* 79 |            MAT_VIEW ACCESS FULL | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |  80 |          VIEW                   | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 81 |           TABLE ACCESS FULL     | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |  82 |         VIEW                    | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |  83 |          MAT_VIEW ACCESS FULL   | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |  84 |        VIEW                     | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 85 |         TABLE ACCESS FULL       | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |* 86 |       HASH JOIN OUTER           |                             |   560 |   128K|       | 23267   (6)| 00:04:40 |

      |* 87 |        HASH JOIN                |                             |   162 | 35964 |       | 20816   (6)| 00:04:10 |

      |* 88 |         HASH JOIN OUTER         |                             |    95 | 18620 |       | 19942   (7)| 00:04:00 |

      |* 89 |          HASH JOIN              |                             |    94 | 12690 |       |  6435  (18)| 00:01:18 |

      |* 90 |           HASH JOIN             |                             |    94 | 11562 |  9320K|  4206  (27)| 00:00:51 |

      |  91 |            VIEW                 | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |  92 |             MAT_VIEW ACCESS FULL| STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |* 93 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      |  94 |           VIEW                  | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |* 95 |            MAT_VIEW ACCESS FULL | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |  96 |          VIEW                   | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |* 97 |           TABLE ACCESS FULL     | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |  98 |         VIEW                    | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |  99 |          MAT_VIEW ACCESS FULL   | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 100 |        VIEW                     | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |*101 |         TABLE ACCESS FULL       | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |*102 |       HASH JOIN OUTER           |                             |    28 |  6580 |       | 23267   (6)| 00:04:40 |

      |*103 |        HASH JOIN                |                             |     8 |  1776 |       | 20816   (6)| 00:04:10 |

      |*104 |         HASH JOIN OUTER         |                             |     5 |   980 |       | 19942   (7)| 00:04:00 |

      |*105 |          HASH JOIN              |                             |     5 |   675 |       |  6435  (18)| 00:01:18 |

      |*106 |           HASH JOIN             |                             |     5 |   615 |  9320K|  4206  (27)| 00:00:51 |

      | 107 |            VIEW                 | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      | 108 |             MAT_VIEW ACCESS FULL| STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |*109 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      | 110 |           VIEW                  | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*111 |            MAT_VIEW ACCESS FULL | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      | 112 |          VIEW                   | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |*113 |           TABLE ACCESS FULL     | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      | 114 |         VIEW                    | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 115 |          MAT_VIEW ACCESS FULL   | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 116 |        VIEW                     | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |*117 |         TABLE ACCESS FULL       | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |*118 |       HASH JOIN OUTER           |                             |     1 |   235 |       | 23267   (6)| 00:04:40 |

      |*119 |        HASH JOIN OUTER          |                             |     1 |   174 |       |  9760  (12)| 00:01:58 |

      |*120 |         HASH JOIN               |                             |     1 |   161 |       |  7309  (16)| 00:01:28 |

      |*121 |          HASH JOIN              |                             |     1 |   135 |       |  6435  (18)| 00:01:18 |

      |*122 |           HASH JOIN             |                             |     1 |   123 |  9320K|  4206  (27)| 00:00:51 |

      | 123 |            VIEW                 | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      | 124 |             MAT_VIEW ACCESS FULL| STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |*125 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      | 126 |           VIEW                  | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*127 |            MAT_VIEW ACCESS FULL | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      | 128 |          VIEW                   | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 129 |           MAT_VIEW ACCESS FULL  | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 130 |         VIEW                    | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |*131 |          TABLE ACCESS FULL      | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      | 132 |        VIEW                     | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |*133 |         TABLE ACCESS FULL       | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |*134 |       HASH JOIN OUTER           |                             |     1 |   235 |       | 23267   (6)| 00:04:40 |

      |*135 |        HASH JOIN OUTER          |                             |     1 |   174 |       |  9760  (12)| 00:01:58 |

      |*136 |         HASH JOIN               |                             |     1 |   161 |       |  7309  (16)| 00:01:28 |

      |*137 |          HASH JOIN              |                             |     1 |   135 |       |  6435  (18)| 00:01:18 |

      |*138 |           HASH JOIN             |                             |     1 |   123 |  9320K|  4206  (27)| 00:00:51 |

      | 139 |            VIEW                 | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      | 140 |             MAT_VIEW ACCESS FULL| STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |*141 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      | 142 |           VIEW                  | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*143 |            MAT_VIEW ACCESS FULL | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      | 144 |          VIEW                   | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 145 |           MAT_VIEW ACCESS FULL  | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 146 |         VIEW                    | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |*147 |          TABLE ACCESS FULL      | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      | 148 |        VIEW                     | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |*149 |         TABLE ACCESS FULL       | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |*150 |       HASH JOIN OUTER           |                             |     1 |   235 |       | 23267   (6)| 00:04:40 |

      |*151 |        HASH JOIN OUTER          |                             |     1 |   174 |       |  9760  (12)| 00:01:58 |

      |*152 |         HASH JOIN               |                             |     1 |   161 |       |  7309  (16)| 00:01:28 |

      |*153 |          HASH JOIN              |                             |     1 |   135 |       |  6435  (18)| 00:01:18 |

      |*154 |           HASH JOIN             |                             |     1 |   123 |  9320K|  4206  (27)| 00:00:51 |

      | 155 |            VIEW                 | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      | 156 |             MAT_VIEW ACCESS FULL| STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |*157 |            MAT_VIEW ACCESS FULL | STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      | 158 |           VIEW                  | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*159 |            MAT_VIEW ACCESS FULL | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      | 160 |          VIEW                   | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 161 |           MAT_VIEW ACCESS FULL  | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 162 |         VIEW                    | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |*163 |          TABLE ACCESS FULL      | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      | 164 |        VIEW                     | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |*165 |         TABLE ACCESS FULL       | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |*166 |       HASH JOIN OUTER           |                             |     1 |   235 |       | 37689  (26)| 00:07:33 |

      |*167 |        HASH JOIN                |                             |     1 |   222 |       | 35237  (28)| 00:07:03 |

      |*168 |         HASH JOIN               |                             |     1 |   210 |       | 33008  (29)| 00:06:37 |

      |*169 |          HASH JOIN              |                             |  9781 |  1776K|       | 22622   (1)| 00:04:32 |

      |*170 |           FILTER                |                             |       |       |       |            |          |

      |*171 |            HASH JOIN OUTER      |                             |  5716 |   893K|    12M| 21748   (1)| 00:04:21 |

      |*172 |             MAT_VIEW ACCESS FULL| STAG_ANAME_MV               |   113K|    10M|       |  1085   (2)| 00:00:14 |

      | 173 |             VIEW                | ABC_CDE_INDEX_AST_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      |*174 |              TABLE ACCESS FULL  | ABC_CDE_INDEX_AST           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

      | 175 |           VIEW                  | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 176 |            MAT_VIEW ACCESS FULL | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |*177 |          VIEW                   | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      | 178 |           MAT_VIEW ACCESS FULL  | STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      | 179 |         VIEW                    | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*180 |          MAT_VIEW ACCESS FULL   | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      | 181 |        VIEW                     | ABC_CDE_INDEX_CHG_VW       |   818K|    10M|       |  2448   (1)| 00:00:30 |

      |*182 |         TABLE ACCESS FULL       | ABC_CDE_INDEX_CHG           |   818K|    10M|       |  2448   (1)| 00:00:30 |

      | 183 |   LOAD AS SELECT                | SYS_TEMP_0FD9FCB73_F58FAD20 |       |       |       |            |          |

      | 184 |    VIEW                         |                             |   145K|    18M|       |   187M  (1)|625:08:34 |

      | 185 |     SORT UNIQUE                 |                             |   145K|    22M|  1215G|   187M  (1)|625:08:34 |

      |*186 |      HASH JOIN                  |                             |  7487M|  1122G|       |   274K (98)| 00:54:59 |

      | 187 |       VIEW                      | STAG_MASTER_VW             | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*188 |        MAT_VIEW ACCESS FULL     | STAG_MASTER_MV              | 39985 |   468K|       |  2229   (1)| 00:00:27 |

      |*189 |       HASH JOIN                 |                             |  7487M|  1039G|  9320K|   245K (98)| 00:49:06 |

      | 190 |        VIEW                     | STAG_MANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      | 191 |         MAT_VIEW ACCESS FULL    | STAG_AANAME_MV              |   264K|  6208K|       |   974   (1)| 00:00:12 |

      |*192 |        HASH JOIN                |                             |   248K|    29M|  9240K|  2995   (1)| 00:00:36 |

      | 193 |         VIEW                    | STAG_NAMADDS_VW             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      | 194 |          MAT_VIEW ACCESS FULL   | STAG_NAMADDS_MV             |   248K|  6316K|       |   872   (1)| 00:00:11 |

      |*195 |         MAT_VIEW ACCESS FULL    | STAG_ANAME_MV               |   113K|    10M|       |  1075   (1)| 00:00:13 |

      | 196 |   SORT UNIQUE                   |                             |   352K|    60M|    72M| 17677  (12)| 00:03:33 |

      | 197 |    UNION-ALL                    |                             |       |       |       |            |          |

      |*198 |     VIEW                        |                             |   352K|    60M|       |  1883   (1)| 00:00:23 |

      | 199 |      TABLE ACCESS FULL          | SYS_TEMP_1234_5678 |   352K|    60M|       |  1883   (1)| 00:00:23 |

      |*200 |     FILTER                      |                             |       |       |       |            |          |

      |*201 |      HASH JOIN OUTER            |                             |     1 |   150 |       |  1982   (1)| 00:00:24 |

      |*202 |       HASH JOIN                 |                             |     1 |   142 |       |    97   (2)| 00:00:02 |

      | 203 |        VIEW                     |                             | 11540 | 92320 |       |    48   (0)| 00:00:01 |

      | 204 |         TABLE ACCESS FULL       | SYS_TEMP_3456_0987F0 | 11540 |  1510K|       |    48   (0)| 00:00:01 |

      | 205 |        VIEW                     |                             | 11540 |  1510K|       |    48   (0)| 00:00:01 |

      | 206 |         TABLE ACCESS FULL       | SYS_TEMP_0987F0_3456| 11540 |  1510K|       |    48   (0)| 00:00:01 |

      | 207 |       VIEW                      |                             |   352K|  2757K|       |  1883   (1)| 00:00:23 |

      | 208 |        TABLE ACCESS FULL        | SYS_TEMP_09kjhn0_3456|   352K|    60M|       |  1883   (1)| 00:00:23 |

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

      {code}

       

      Thanks.

        • 1. Re: steps to tune the query on explain plan
          Franck Pachot

          Hi,

          This is a plan with estimations only. It the estimations are right the this is probably the best plan. But it estimates to do a distinct on 22000000000 rows, that takes time of course.

          If you post the plan with actual number of rows after running the query, obtained as described in http://www.dbi-services.com/index.php/blog/entry/best-practice-to-send-an-oracle-execution-plan

          the I can check the plan.

          Can we see the query as well ?

          Regards,

          Franck.

          • 2. Re: steps to tune the query on explain plan
            rp0428
            Please find the belwo explain plan of a query and suggest me where and how can we tune the query.

            Query? What query? You didn't post any query.

             

            Please find, and read, the FAQ about how to post a tuning request and the information you need to provide.

             

            Hint: do you see ANY indexes in that length plan ANYWHERE?

            • 3. Re: steps to tune the query on explain plan
              969952

              Hi based on the explain plan.. can you please have a look and share your ideas to tune the query..

               

              Thanks.

              • 4. Re: steps to tune the query on explain plan
                JustinCave

                No one on the face of the earth can take a look at a query plan that contains only the CBO's estimates and, without looking at the query, understanding your data and data model, or getting actual execution values, say anything useful about how to tune the query.  Unless you're going to post that, as others have suggested, you're probably out of luck.

                 

                Realistically, a 200 line unformatted query plan is probably not the sort of thing that many folks are likely to spend a lot of time looking through in this sort of forum.  Maybe you'll get lucky and, with more information, someone will be able to see an obvious issue or find the problem sufficiently interesting to be willing to invest the time needed to help you out.  You would substantially increase the odds of assistance, though, if you could reproduce the problem you're having with a much smaller test case.

                 

                Justin

                • 5. Re: steps to tune the query on explain plan
                  Franck Pachot

                  Hi,

                  Based on the plan:

                  • either the estimations are right and the execution time is about 320 hours 57 minutes and 19 seconds. Then the idea is to avoid doing a distinct on 1792 million rows. Or to go to parallel query with a lot of CPU (hundreds of cores if you want less than an hour response time)
                  • or they are wrong and we cannot advise anything on it. Then the idea is to get the right numbers before we can advise anything.

                  Regards,

                  Franck.

                  • 6. Re: steps to tune the query on explain plan
                    969952

                    Hi Franck,

                     

                    The query contains around 850 lines so I can't post this query.. Max query i have optimized.. I have seen time as 00:01:54 and near to that. but some lines I have seen the time as below.. here am posting those results.. Please have a look and let m eknow can we tune the query at that point.. and what might be the reasons.

                     

                    {code}

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

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

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

                    |   0 | SELECT STATEMENT                                             |                              |  1400K|  1093M|       |   554K  (1)| 01:50:50 |

                    |   1 |  SORT ORDER BY                                               |                              |  1400K|  1093M|  1215M|   554K  (1)| 01:50:50 |

                    |   2 |   HASH UNIQUE                                                |                              |  1400K|  1093M|  1215M|   313K  (1)| 01:02:39 |

                    |*  3 |    HASH JOIN RIGHT OUTER                                     |                              |  1400K|  1093M|       | 72264   (1)| 00:14:28 |

                    |*  4 |     MAT_VIEW ACCESS FULL                                     | STAG_INFO_MV                |   240 |  6720 |       |    59   (0)| 00:00:01 |

                    |*  5 |     HASH JOIN RIGHT OUTER                                    |                              |   583K|   440M|       | 72199   (1)| 00:14:27 |

                    |*  6 |      MAT_VIEW ACCESS FULL                                    | STAG_INFYT_MV              |  3997 | 95928 |       |   205   (1)| 00:00:03 |

                    |*  7 |      HASH JOIN                                               |                              |   583K|   427M|       | 71991   (1)| 00:14:24 |

                    |*  8 |       HASH JOIN OUTER                                        |                              |   730 |   539K|       | 69769   (1)| 00:13:58 |

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

                    |* 10 |         HASH JOIN OUTER                                      |                              |     5 |  3715 |       | 69062   (1)| 00:13:49 |

                    |* 11 |          HASH JOIN                                           |                              |     5 |  3575 |       | 69003   (1)| 00:13:49 |

                    |  12 |           MERGE JOIN CARTESIAN                               |                              |   497 |   339K|       | 68601   (1)| 00:13:44 |

                    ...

                    ....

                    ....

                    till

                    | 812 |

                     

                    {code}

                     

                    I observed lines 0,1,2 ,3are taking long time.. please correct me if am wrong.. and let me know if there any steps to tune...

                     

                    This whole query contains Multiple SELECT ctatements, Join conditions and UNION operator.

                     

                    Thanks.

                    • 7. Re: steps to tune the query on explain plan
                      Franck Pachot

                      Can you check if you can replace the UNION with UNION ALL in order to avoid the distinct phase that is expensive ?

                      The plan you post shows estimation. Can you run the query as describe in Best practice for the sending of an Oracle execution plan - dbi services Blog - IT infrastructures & more in order to get the actual number of rows. First lines will be ok to start.

                      • 8. Re: steps to tune the query on explain plan
                        969952

                        Hi,

                         

                        Replaced UNION with UNION ALL but still there is no difference in the performance.. any other way to tunr this big query? Help me out.

                         

                        Thanks

                        • 9. Re: steps to tune the query on explain plan
                          Franck Pachot

                          Well, it was just a guess because you don't want to share the query nor the execution plan with actual cardinalities. The goal is probably to avoid the distinct.

                          • 10. Re: steps to tune the query on explain plan
                            969952

                            Hi,

                             

                            Please see the below explain plan and share your ideas.

                            {code}

                             

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

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

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

                            |   0 | SELECT STATEMENT               |                             |   126K|    21M|       |  6439  (13)| 00:01:18 |

                            |   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |       |            |          |

                            |   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9FCBAE_F58FAD20 |       |       |       |            |          |

                            |   3 |    VIEW                        |                             |  8183M|  1364G|       |    34M(100)|114:16:34 |

                            |   4 |     SORT UNIQUE                |                             |  8183M|  1691G|   153G|    34M  (1)|114:16:34 |

                            |   5 |      CONCATENATION             |                             |       |       |       |            |          |

                            |*  6 |       HASH JOIN                |                             |   669M|   138G|       | 34713  (15)| 00:06:57 |

                            |   7 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |*  8 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |*  9 |        HASH JOIN               |                             |   669M|   130G|    11M| 30050   (9)| 00:06:01 |

                            |  10 |         VIEW                   | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |  11 |          MAT_VIEW ACCESS FULL  | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |* 12 |         HASH JOIN              |                             |   203K|    36M|  9528K| 24172   (1)| 00:04:51 |

                            |  13 |          VIEW                  | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |  14 |           MAT_VIEW ACCESS FULL |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |* 15 |          HASH JOIN OUTER       |                             |   126K|    19M|    13M| 21810   (1)| 00:04:22 |

                            |* 16 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            |  17 |           VIEW                 | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 18 |            TABLE ACCESS FULL   | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 19 |       HASH JOIN                |                             |    33M|  7090M|       | 32402   (9)| 00:06:29 |

                            |  20 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 21 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 22 |        HASH JOIN               |                             |    33M|  6707M|    11M| 30050   (9)| 00:06:01 |

                            |  23 |         VIEW                   | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |  24 |          MAT_VIEW ACCESS FULL  | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |* 25 |         HASH JOIN              |                             |   203K|    36M|  9528K| 24172   (1)| 00:04:51 |

                            |  26 |          VIEW                  | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |  27 |           MAT_VIEW ACCESS FULL |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |* 28 |          HASH JOIN OUTER       |                             |   126K|    19M|    13M| 21810   (1)| 00:04:22 |

                            |* 29 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            |  30 |           VIEW                 | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 31 |            TABLE ACCESS FULL   | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 32 |       HASH JOIN                |                             |  1674K|   354M|       | 32286   (9)| 00:06:28 |

                            |  33 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 34 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 35 |        HASH JOIN               |                             |  1674K|   335M|    11M| 30050   (9)| 00:06:01 |

                            |  36 |         VIEW                   | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |  37 |          MAT_VIEW ACCESS FULL  | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |* 38 |         HASH JOIN              |                             |   203K|    36M|  9528K| 24172   (1)| 00:04:51 |

                            |  39 |          VIEW                  | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |  40 |           MAT_VIEW ACCESS FULL |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |* 41 |          HASH JOIN OUTER       |                             |   126K|    19M|    13M| 21810   (1)| 00:04:22 |

                            |* 42 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            |  43 |           VIEW                 | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 44 |            TABLE ACCESS FULL   | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 45 |       HASH JOIN                |                             | 83725 |    17M|       | 29222   (6)| 00:05:51 |

                            |  46 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 47 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 48 |        HASH JOIN               |                             | 83725 |    16M|  9528K| 26992   (7)| 00:05:24 |

                            |  49 |         VIEW                   | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |  50 |          MAT_VIEW ACCESS FULL  |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |* 51 |         HASH JOIN OUTER        |                             | 51802 |  9308K|  6800K| 25175   (7)| 00:05:03 |

                            |* 52 |          HASH JOIN             |                             | 51555 |  6192K|    11M|  4781  (32)| 00:00:58 |

                            |  53 |           VIEW                 | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |  54 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |* 55 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            |  56 |          VIEW                  | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 57 |           TABLE ACCESS FULL    | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 58 |       HASH JOIN                |                             |  4186 |   907K|       | 21391   (8)| 00:04:17 |

                            |* 59 |        HASH JOIN OUTER         |                             |  2590 |   495K|       | 20517   (8)| 00:04:07 |

                            |* 60 |         HASH JOIN              |                             |  2578 |   339K|       |  7011  (23)| 00:01:25 |

                            |* 61 |          HASH JOIN             |                             |  2578 |   309K|    11M|  4781  (32)| 00:00:58 |

                            |  62 |           VIEW                 | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |  63 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |* 64 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            |  65 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 66 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |  67 |         VIEW                   | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 68 |          TABLE ACCESS FULL     | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |  69 |        VIEW                    | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |  70 |         MAT_VIEW ACCESS FULL   |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |* 71 |       HASH JOIN                |                             |   209 | 46398 |       | 21391   (8)| 00:04:17 |

                            |* 72 |        HASH JOIN OUTER         |                             |   130 | 25480 |       | 20517   (8)| 00:04:07 |

                            |* 73 |         HASH JOIN              |                             |   129 | 17415 |       |  7011  (23)| 00:01:25 |

                            |* 74 |          HASH JOIN             |                             |   129 | 15867 |    11M|  4781  (32)| 00:00:58 |

                            |  75 |           VIEW                 | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |  76 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |* 77 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            |  78 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 79 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |  80 |         VIEW                   | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 81 |          TABLE ACCESS FULL     | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |  82 |        VIEW                    | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |  83 |         MAT_VIEW ACCESS FULL   |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |* 84 |       HASH JOIN                |                             |    10 |  2220 |       | 21391   (8)| 00:04:17 |

                            |* 85 |        HASH JOIN OUTER         |                             |     6 |  1176 |       | 20517   (8)| 00:04:07 |

                            |* 86 |         HASH JOIN              |                             |     6 |   810 |       |  7011  (23)| 00:01:25 |

                            |* 87 |          HASH JOIN             |                             |     6 |   738 |    11M|  4781  (32)| 00:00:58 |

                            |  88 |           VIEW                 | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |  89 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |* 90 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            |  91 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |* 92 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |  93 |         VIEW                   | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |* 94 |          TABLE ACCESS FULL     | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |  95 |        VIEW                    | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |  96 |         MAT_VIEW ACCESS FULL   |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |* 97 |       HASH JOIN OUTER          |                             |     1 |   222 |       | 21391   (8)| 00:04:17 |

                            |* 98 |        HASH JOIN               |                             |     1 |   161 |       |  7884  (20)| 00:01:35 |

                            |* 99 |         HASH JOIN              |                             |     1 |   135 |       |  7011  (23)| 00:01:25 |

                            |*100 |          HASH JOIN             |                             |     1 |   123 |    11M|  4781  (32)| 00:00:58 |

                            | 101 |           VIEW                 | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            | 102 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |*103 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            | 104 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |*105 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            | 106 |         VIEW                   | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            | 107 |          MAT_VIEW ACCESS FULL  |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            | 108 |        VIEW                    | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |*109 |         TABLE ACCESS FULL      | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |*110 |       HASH JOIN OUTER          |                             |     1 |   222 |       | 21391   (8)| 00:04:17 |

                            |*111 |        HASH JOIN               |                             |     1 |   161 |       |  7884  (20)| 00:01:35 |

                            |*112 |         HASH JOIN              |                             |     1 |   135 |       |  7011  (23)| 00:01:25 |

                            |*113 |          HASH JOIN             |                             |     1 |   123 |    11M|  4781  (32)| 00:00:58 |

                            | 114 |           VIEW                 | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            | 115 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |*116 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            | 117 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |*118 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            | 119 |         VIEW                   | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            | 120 |          MAT_VIEW ACCESS FULL  |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            | 121 |        VIEW                    | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |*122 |         TABLE ACCESS FULL      | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |*123 |       HASH JOIN OUTER          |                             |     1 |   222 |       | 21391   (8)| 00:04:17 |

                            |*124 |        HASH JOIN               |                             |     1 |   161 |       |  7884  (20)| 00:01:35 |

                            |*125 |         HASH JOIN              |                             |     1 |   135 |       |  7011  (23)| 00:01:25 |

                            |*126 |          HASH JOIN             |                             |     1 |   123 |    11M|  4781  (32)| 00:00:58 |

                            | 127 |           VIEW                 | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            | 128 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |*129 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            | 130 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |*131 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            | 132 |         VIEW                   | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            | 133 |          MAT_VIEW ACCESS FULL  |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            | 134 |        VIEW                    | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |*135 |         TABLE ACCESS FULL      | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |*136 |       HASH JOIN                |                             |     1 |   222 |       | 38054  (33)| 00:07:37 |

                            |*137 |        HASH JOIN               |                             |     1 |   210 |       | 35825  (35)| 00:07:10 |

                            |*138 |         HASH JOIN              |                             | 10185 |  1850K|       | 22684   (1)| 00:04:33 |

                            |*139 |          FILTER                |                             |       |       |       |            |          |

                            |*140 |           HASH JOIN OUTER      |                             |  6302 |   984K|    13M| 21810   (1)| 00:04:22 |

                            |*141 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT               |   125K|    11M|       |  1086   (2)| 00:00:14 |

                            | 142 |            VIEW                | SIT_IDX_MIS_VW        |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            |*143 |             TABLE ACCESS FULL  | SIT_IDX_MIS           |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                            | 144 |          VIEW                  | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            | 145 |           MAT_VIEW ACCESS FULL |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |*146 |         VIEW                   | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            | 147 |          MAT_VIEW ACCESS FULL  | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            | 148 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |*149 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            | 150 |   LOAD AS SELECT               | SYS_TEMP_0FD9FCBAF_F58FAD20 |       |       |       |            |          |

                            | 151 |    VIEW                        |                             |   158K|    20M|       |   240M  (1)|800:25:16 |

                            | 152 |     SORT UNIQUE                |                             |   158K|    24M|  1556G|   240M  (1)|800:25:16 |

                            |*153 |      HASH JOIN                 |                             |  9587M|  1437G|       |   349K (98)| 01:09:59 |

                            | 154 |       VIEW                     | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |*155 |        MAT_VIEW ACCESS FULL    | STAG_MASTER_MAT              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                            |*156 |       HASH JOIN                |                             |  9587M|  1330G|    11M|   312K (99)| 01:02:34 |

                            | 157 |        VIEW                    | STAG_MNAME_VW              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            | 158 |         MAT_VIEW ACCESS FULL   | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                            |*159 |        HASH JOIN               |                             |   256K|    30M|  9528K|  3073   (1)| 00:00:37 |

                            | 160 |         VIEW                   | STAG_MADDR_VW             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            | 161 |          MAT_VIEW ACCESS FULL  |STAG_MADDR_MAT             |   256K|  6516K|       |   872   (1)| 00:00:11 |

                            |*162 |         MAT_VIEW ACCESS FULL   | STAG_MNAME_MAT               |   125K|    11M|       |  1077   (1)| 00:00:13 |

                            | 163 |   SORT UNIQUE                  |                             |   126K|    21M|    26M|  6439  (13)| 00:01:18 |

                            | 164 |    UNION-ALL                   |                             |       |       |       |            |          |

                            |*165 |     VIEW                       |                             |   126K|    21M|       |   678   (1)| 00:00:09 |

                            | 166 |      TABLE ACCESS FULL         | SYS_TEMP_0FD9FCBAE_F58FAD20 |   126K|    21M|       |   678   (1)| 00:00:09 |

                            |*167 |     FILTER                     |                             |       |       |       |            |          |

                            |*168 |      HASH JOIN OUTER           |                             |     1 |   150 |       |   798   (1)| 00:00:10 |

                            |*169 |       HASH JOIN                |                             |     1 |   142 |       |   119   (1)| 00:00:02 |

                            | 170 |        VIEW                    |                             | 14334 |   111K|       |    59   (0)| 00:00:01 |

                            | 171 |         TABLE ACCESS FULL      | SYS_TEMP_0FD9FCBAF_F58FAD20 | 14334 |  1875K|       |    59   (0)| 00:00:01 |

                            | 172 |        VIEW                    |                             | 14334 |  1875K|       |    59   (0)| 00:00:01 |

                            | 173 |         TABLE ACCESS FULL      | SYS_TEMP_0FD9FCBAF_F58FAD20 | 14334 |  1875K|       |    59   (0)| 00:00:01 |

                            | 174 |       VIEW                     |                             |   126K|   990K|       |   678   (1)| 00:00:09 |

                            | 175 |        TABLE ACCESS FULL       | SYS_TEMP_0FD9FCBAE_F58FAD20 |   126K|    21M|       |   678   (1)| 00:00:09 |

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

                            {code}

                             

                            I can't post query here..

                             

                            Thanks.

                            • 11. Re: steps to tune the query on explain plan
                              Etbin

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

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

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

                              |   0 | SELECT STATEMENT               |                             |   126K|    21M|       |  6439  (13)| 00:01:18 |

                              |   1 |  TEMP TABLE TRANSFORMATION     |                             |       |       |       |            |          |

                              |   2 |   LOAD AS SELECT               | SYS_TEMP_0FD9FCBAE_F58FAD20 |       |       |       |            |          |

                              |   3 |    VIEW                        |                             |  8183M|  1364G|       |    34M(100)|114:16:34 |

                              |   4 |     SORT UNIQUE                |                             |  8183M|  1691G|   153G|    34M  (1)|114:16:34 |

                              |   5 |      CONCATENATION             |                             |       |       |       |            |          |

                              |*  6 |       HASH JOIN                |                             |   669M|   138G|       | 34713  (15)| 00:06:57 |

                              |   7 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |*  8 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |*  9 |        HASH JOIN               |                             |   669M|   130G|    11M| 30050   (9)| 00:06:01 |

                              |  10 |         VIEW                   | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |  11 |          MAT_VIEW ACCESS FULL  | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |* 12 |         HASH JOIN              |                             |   203K|    36M|  9528K| 24172   (1)| 00:04:51 |

                              |  13 |          VIEW                  | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |  14 |           MAT_VIEW ACCESS FULL | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |* 15 |          HASH JOIN OUTER       |                             |   126K|    19M|    13M| 21810   (1)| 00:04:22 |

                              |* 16 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              |  17 |           VIEW                 | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 18 |            TABLE ACCESS FULL   | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 19 |       HASH JOIN                |                             |    33M|  7090M|       | 32402   (9)| 00:06:29 |

                              |  20 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 21 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 22 |        HASH JOIN               |                             |    33M|  6707M|    11M| 30050   (9)| 00:06:01 |

                              |  23 |         VIEW                   | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |  24 |          MAT_VIEW ACCESS FULL  | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |* 25 |         HASH JOIN              |                             |   203K|    36M|  9528K| 24172   (1)| 00:04:51 |

                              |  26 |          VIEW                  | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |  27 |           MAT_VIEW ACCESS FULL | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |* 28 |          HASH JOIN OUTER       |                             |   126K|    19M|    13M| 21810   (1)| 00:04:22 |

                              |* 29 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              |  30 |           VIEW                 | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 31 |            TABLE ACCESS FULL   | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 32 |       HASH JOIN                |                             |  1674K|   354M|       | 32286   (9)| 00:06:28 |

                              |  33 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 34 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 35 |        HASH JOIN               |                             |  1674K|   335M|    11M| 30050   (9)| 00:06:01 |

                              |  36 |         VIEW                   | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |  37 |          MAT_VIEW ACCESS FULL  | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |* 38 |         HASH JOIN              |                             |   203K|    36M|  9528K| 24172   (1)| 00:04:51 |

                              |  39 |          VIEW                  | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |  40 |           MAT_VIEW ACCESS FULL | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |* 41 |          HASH JOIN OUTER       |                             |   126K|    19M|    13M| 21810   (1)| 00:04:22 |

                              |* 42 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              |  43 |           VIEW                 | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 44 |            TABLE ACCESS FULL   | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 45 |       HASH JOIN                |                             | 83725 |    17M|       | 29222   (6)| 00:05:51 |

                              |  46 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 47 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 48 |        HASH JOIN               |                             | 83725 |    16M|  9528K| 26992   (7)| 00:05:24 |

                              |  49 |         VIEW                   | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |  50 |          MAT_VIEW ACCESS FULL  | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |* 51 |         HASH JOIN OUTER        |                             | 51802 |  9308K|  6800K| 25175   (7)| 00:05:03 |

                              |* 52 |          HASH JOIN             |                             | 51555 |  6192K|    11M|  4781  (32)| 00:00:58 |

                              |  53 |           VIEW                 | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |  54 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |* 55 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              |  56 |          VIEW                  | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 57 |           TABLE ACCESS FULL    | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 58 |       HASH JOIN                |                             |  4186 |   907K|       | 21391   (8)| 00:04:17 |

                              |* 59 |        HASH JOIN OUTER         |                             |  2590 |   495K|       | 20517   (8)| 00:04:07 |

                              |* 60 |         HASH JOIN              |                             |  2578 |   339K|       |  7011  (23)| 00:01:25 |

                              |* 61 |          HASH JOIN             |                             |  2578 |   309K|    11M|  4781  (32)| 00:00:58 |

                              |  62 |           VIEW                 | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |  63 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |* 64 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              |  65 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 66 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |  67 |         VIEW                   | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 68 |          TABLE ACCESS FULL     | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |  69 |        VIEW                    | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |  70 |         MAT_VIEW ACCESS FULL   | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |* 71 |       HASH JOIN                |                             |   209 | 46398 |       | 21391   (8)| 00:04:17 |

                              |* 72 |        HASH JOIN OUTER         |                             |   130 | 25480 |       | 20517   (8)| 00:04:07 |

                              |* 73 |         HASH JOIN              |                             |   129 | 17415 |       |  7011  (23)| 00:01:25 |

                              |* 74 |          HASH JOIN             |                             |   129 | 15867 |    11M|  4781  (32)| 00:00:58 |

                              |  75 |           VIEW                 | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |  76 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |* 77 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              |  78 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 79 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |  80 |         VIEW                   | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 81 |          TABLE ACCESS FULL     | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |  82 |        VIEW                    | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |  83 |         MAT_VIEW ACCESS FULL   | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |* 84 |       HASH JOIN                |                             |    10 |  2220 |       | 21391   (8)| 00:04:17 |

                              |* 85 |        HASH JOIN OUTER         |                             |     6 |  1176 |       | 20517   (8)| 00:04:07 |

                              |* 86 |         HASH JOIN              |                             |     6 |   810 |       |  7011  (23)| 00:01:25 |

                              |* 87 |          HASH JOIN             |                             |     6 |   738 |    11M|  4781  (32)| 00:00:58 |

                              |  88 |           VIEW                 | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |  89 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |* 90 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              |  91 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |* 92 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |  93 |         VIEW                   | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |* 94 |          TABLE ACCESS FULL     | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |  95 |        VIEW                    | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |  96 |         MAT_VIEW ACCESS FULL   | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |* 97 |       HASH JOIN OUTER          |                             |     1 |   222 |       | 21391   (8)| 00:04:17 |

                              |* 98 |        HASH JOIN               |                             |     1 |   161 |       |  7884  (20)| 00:01:35 |

                              |* 99 |         HASH JOIN              |                             |     1 |   135 |       |  7011  (23)| 00:01:25 |

                              |*100 |          HASH JOIN             |                             |     1 |   123 |    11M|  4781  (32)| 00:00:58 |

                              | 101 |           VIEW                 | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              | 102 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |*103 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              | 104 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |*105 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              | 106 |         VIEW                   | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              | 107 |          MAT_VIEW ACCESS FULL  | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              | 108 |        VIEW                    | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |*109 |         TABLE ACCESS FULL      | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |*110 |       HASH JOIN OUTER          |                             |     1 |   222 |       | 21391   (8)| 00:04:17 |

                              |*111 |        HASH JOIN               |                             |     1 |   161 |       |  7884  (20)| 00:01:35 |

                              |*112 |         HASH JOIN              |                             |     1 |   135 |       |  7011  (23)| 00:01:25 |

                              |*113 |          HASH JOIN             |                             |     1 |   123 |    11M|  4781  (32)| 00:00:58 |

                              | 114 |           VIEW                 | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              | 115 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |*116 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              | 117 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |*118 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              | 119 |         VIEW                   | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              | 120 |          MAT_VIEW ACCESS FULL  | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              | 121 |        VIEW                    | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |*122 |         TABLE ACCESS FULL      | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |*123 |       HASH JOIN OUTER          |                             |     1 |   222 |       | 21391   (8)| 00:04:17 |

                              |*124 |        HASH JOIN               |                             |     1 |   161 |       |  7884  (20)| 00:01:35 |

                              |*125 |         HASH JOIN              |                             |     1 |   135 |       |  7011  (23)| 00:01:25 |

                              |*126 |          HASH JOIN             |                             |     1 |   123 |    11M|  4781  (32)| 00:00:58 |

                              | 127 |           VIEW                 | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              | 128 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |*129 |           MAT_VIEW ACCESS FULL | STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              | 130 |          VIEW                  | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |*131 |           MAT_VIEW ACCESS FULL | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              | 132 |         VIEW                   | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              | 133 |          MAT_VIEW ACCESS FULL  | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              | 134 |        VIEW                    | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |*135 |         TABLE ACCESS FULL      | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |*136 |       HASH JOIN                |                             |     1 |   222 |       | 38054  (33)| 00:07:37 |

                              |*137 |        HASH JOIN               |                             |     1 |   210 |       | 35825  (35)| 00:07:10 |

                              |*138 |         HASH JOIN              |                             | 10185 |  1850K|       | 22684   (1)| 00:04:33 |

                              |*139 |          FILTER                |                             |       |       |       |            |          |

                              |*140 |           HASH JOIN OUTER      |                             |  6302 |   984K|    13M| 21810   (1)| 00:04:22 |

                              |*141 |            MAT_VIEW ACCESS FULL| STAG_MNAME_MAT              |   125K|    11M|       |  1086   (2)| 00:00:14 |

                              | 142 |            VIEW                | SIT_IDX_MIS_VW              |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              |*143 |             TABLE ACCESS FULL  | SIT_IDX_MIS                 |  1898K|   110M|       | 13499   (1)| 00:02:42 |

                              | 144 |          VIEW                  | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              | 145 |           MAT_VIEW ACCESS FULL | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |*146 |         VIEW                   | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              | 147 |          MAT_VIEW ACCESS FULL  | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              | 148 |        VIEW                    | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |*149 |         MAT_VIEW ACCESS FULL   | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              | 150 |   LOAD AS SELECT               | SYS_TEMP_0FD9FCBAF_F58FAD20 |       |       |       |            |          |

                              | 151 |    VIEW                        |                             |   158K|    20M|       |   240M  (1)|800:25:16 |

                              | 152 |     SORT UNIQUE                |                             |   158K|    24M|  1556G|   240M  (1)|800:25:16 |

                              |*153 |      HASH JOIN                 |                             |  9587M|  1437G|       |   349K (98)| 01:09:59 |

                              | 154 |       VIEW                     | STAG_MASTER_VW              | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |*155 |        MAT_VIEW ACCESS FULL    | STAG_MASTER_MAT             | 39999 |   468K|       |  2229   (1)| 00:00:27 |

                              |*156 |       HASH JOIN                |                             |  9587M|  1330G|    11M|   312K (99)| 01:02:34 |

                              | 157 |        VIEW                    | STAG_MNAME_VW               |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              | 158 |         MAT_VIEW ACCESS FULL   | STAG_MNAME_MAT              |   328K|  7706K|       |   975   (1)| 00:00:12 |

                              |*159 |        HASH JOIN               |                             |   256K|    30M|  9528K|  3073   (1)| 00:00:37 |

                              | 160 |         VIEW                   | STAG_MADDR_VW               |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              | 161 |          MAT_VIEW ACCESS FULL  | STAG_MADDR_MAT              |   256K|  6516K|       |   872   (1)| 00:00:11 |

                              |*162 |         MAT_VIEW ACCESS FULL   | STAG_MNAME_MAT              |   125K|    11M|       |  1077   (1)| 00:00:13 |

                              | 163 |   SORT UNIQUE                  |                             |   126K|    21M|    26M|  6439  (13)| 00:01:18 |

                              | 164 |    UNION-ALL                   |                             |       |       |       |            |          |

                              |*165 |     VIEW                       |                             |   126K|    21M|       |   678   (1)| 00:00:09 |

                              | 166 |      TABLE ACCESS FULL         | SYS_TEMP_0FD9FCBAE_F58FAD20 |   126K|    21M|       |   678   (1)| 00:00:09 |

                              |*167 |     FILTER                     |                             |       |       |       |            |          |

                              |*168 |      HASH JOIN OUTER           |                             |     1 |   150 |       |   798   (1)| 00:00:10 |

                              |*169 |       HASH JOIN                |                             |     1 |   142 |       |   119   (1)| 00:00:02 |

                              | 170 |        VIEW                    |                             | 14334 |   111K|       |    59   (0)| 00:00:01 |

                              | 171 |         TABLE ACCESS FULL      | SYS_TEMP_0FD9FCBAF_F58FAD20 | 14334 |  1875K|       |    59   (0)| 00:00:01 |

                              | 172 |        VIEW                    |                             | 14334 |  1875K|       |    59   (0)| 00:00:01 |

                              | 173 |         TABLE ACCESS FULL      | SYS_TEMP_0FD9FCBAF_F58FAD20 | 14334 |  1875K|       |    59   (0)| 00:00:01 |

                              | 174 |       VIEW                     |                             |   126K|   990K|       |   678   (1)| 00:00:09 |

                              | 175 |        TABLE ACCESS FULL       | SYS_TEMP_0FD9FCBAE_F58FAD20 |   126K|    21M|       |   678   (1)| 00:00:09 |

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

                              just in case someone can spot something relevant (it wouldn't be fair not to share it once formatted)

                               

                              Regards

                               

                              Etbin

                              • 12. Re: steps to tune the query on explain plan
                                969952

                                how can we format like this while positing? can you please let m eknow.

                                • 13. Re: steps to tune the query on explain plan
                                  Etbin

                                  use advanced editor (select it on upper right of default editor)

                                  select text

                                  choose Courier New font family - it's a fixed width font.

                                   

                                  Regards

                                   

                                  Etbin

                                  • 14. Re: steps to tune the query on explain plan
                                    969952

                                    Franck/All,

                                     

                                    Posted the complete Explain plan here.. can you have a look and share your suggestion to tune this entire query...

                                     

                                    Thanks.

                                    1 2 Previous Next