1 2 Previous Next 16 Replies Latest reply on Mar 22, 2019 9:34 AM by Jonathan Lewis

    Slow Query

    2980262

      Hi All,

       

      I am facing issue with below query, can you please provide some pointers to rewrite it, issue this has and make it faster.

       

       

      Database Details:
      
      SELECT banner FROM v$version;
      
      Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
      PL/SQL Release 12.1.0.2.0 - Production
      CORE 12.1.0.2.0 Production  
      TNS for Linux: Version 12.1.0.2.0 - Production
      NLSRTL Version 12.1.0.2.0 - Production
      
      SHOW parameter optimizer;
      
      NAME                                 TYPE    VALUE    
      ------------------------------------ ------- -------- 
      _optimizer_autostats_job             boolean FALSE    
      optimizer_adaptive_features          boolean FALSE    
      optimizer_adaptive_reporting_only    boolean TRUE     
      optimizer_capture_sql_plan_baselines boolean FALSE    
      optimizer_dynamic_sampling           integer 2        
      optimizer_features_enable            string  12.1.0.2 
      optimizer_index_caching              integer 0        
      optimizer_index_cost_adj             integer 80      
      optimizer_inmemory_aware             boolean FALSE    
      optimizer_mode                       string  ALL_ROWS 
      optimizer_secure_view_merging        boolean FALSE    
      optimizer_use_invisible_indexes      boolean FALSE    
      optimizer_use_pending_statistics     boolean FALSE    
      optimizer_use_sql_plan_baselines     boolean TRUE 
      
      SHOW parameter db_block_size;
      
      NAME          TYPE    VALUE 
      ------------- ------- ----- 
      db_block_size integer 8192 
      
      
      SELECT sname, pname, pval1, pval2 FROM sys.aux_stats$;
      
      
      
      SNAME                          PNAME                               PVAL1 PVAL2                                                                                                                                                                                                                                                          
      ------------------------------ ------------------------------ ---------- ------------------
      SYSSTATS_INFO                  STATUS                                    COMPLETED                                                                                                                                                                                                                                                      
      SYSSTATS_INFO                  DSTART                                    12-05-2005 03:41                                                                                                                                                                                                                                               
      SYSSTATS_INFO                  DSTOP                                     12-05-2005 03:41                                                                                                                                                                                                                                               
      SYSSTATS_INFO                  FLAGS                                   1                                                                                                                                                                                                                                                                
      SYSSTATS_MAIN                  CPUSPEEDNW                     687.167357                                                                                                                                                                                                                                                                
      SYSSTATS_MAIN                  IOSEEKTIM                              10                                                                                                                                                                                                                                                                
      SYSSTATS_MAIN                  IOTFRSPEED                           4096                                                                                                                                                                                                                                                                
      SYSSTATS_MAIN                  SREADTIM                                                                                                                                                                                                                                                                                                 
      SYSSTATS_MAIN                  MREADTIM                                                                                                                                                                                                                                                                                                 
      SYSSTATS_MAIN                  CPUSPEED                                                                                                                                                                                                                                                                                                 
      SYSSTATS_MAIN                  MBRC                                                                                                                                                                                                                                                                                                     
      SYSSTATS_MAIN                  MAXTHR                                                                                                                                                                                                                                                                                                   
      SYSSTATS_MAIN                  SLAVETHR                                                                                                                                                                                                                                                                                                 
      
      Explain Plan -
      
      
      Plan hash value: 2885918576
      
      -------------------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                                     | Name                         | E-Rows |E-Bytes|E-Temp | Cost (%CPU)|  OMem |  1Mem | Used-Mem |
      -------------------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT                              |                              |        |       |       |  2292K(100)|       |       |          |
      |   1 |  NESTED LOOPS                                 |                              |      1 |   101 |       |     4   (0)|       |       |          |
      |   2 |   NESTED LOOPS                                |                              |      1 |    83 |       |     3   (0)|       |       |          |
      |   3 |    NESTED LOOPS                               |                              |      1 |    54 |       |     2   (0)|       |       |          |
      |*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED       | MTL_CATEGORY_SETS_TL         |      1 |    32 |       |     1   (0)|       |       |          |
      |*  5 |      INDEX SKIP SCAN                          | MTL_CATEGORY_SETS_TL_U1      |    111 |       |       |     1   (0)|       |       |          |
      |*  6 |     INDEX RANGE SCAN                          | MTL_ITEM_CATEGORIES_U1       |      1 |    22 |       |     1   (0)|       |       |          |
      |   7 |    TABLE ACCESS BY INDEX ROWID                | MTL_CATEGORIES_B             |      1 |    29 |       |     1   (0)|       |       |          |
      |*  8 |     INDEX UNIQUE SCAN                         | MTL_CATEGORIES_B_U1          |      1 |       |       |     1   (0)|       |       |          |
      |*  9 |   INDEX UNIQUE SCAN                           | MTL_CATEGORIES_TL_U1         |      1 |    18 |       |     1   (0)|       |       |          |
      |  10 |  NESTED LOOPS                                 |                              |      1 |    36 |       |     2   (0)|       |       |          |
      |  11 |   NESTED LOOPS                                |                              |      1 |    36 |       |     2   (0)|       |       |          |
      |  12 |    TABLE ACCESS BY INDEX ROWID BATCHED        | CST_COST_TYPES               |      1 |    19 |       |     1   (0)|       |       |          |
      |* 13 |     INDEX RANGE SCAN                          | CST_COST_TYPES_U2            |      1 |       |       |     1   (0)|       |       |          |
      |* 14 |    INDEX UNIQUE SCAN                          | CST_ITEM_COSTS_U1            |      1 |       |       |     1   (0)|       |       |          |
      |  15 |   TABLE ACCESS BY INDEX ROWID                 | CST_ITEM_COSTS               |      1 |    17 |       |     1   (0)|       |       |          |
      |  16 |  NESTED LOOPS                                 |                              |      1 |   101 |       |     4   (0)|       |       |          |
      |  17 |   NESTED LOOPS                                |                              |      1 |    83 |       |     3   (0)|       |       |          |
      |  18 |    NESTED LOOPS                               |                              |      1 |    54 |       |     2   (0)|       |       |          |
      |* 19 |     TABLE ACCESS BY INDEX ROWID BATCHED       | MTL_CATEGORY_SETS_TL         |      1 |    32 |       |     1   (0)|       |       |          |
      |* 20 |      INDEX SKIP SCAN                          | MTL_CATEGORY_SETS_TL_U1      |    111 |       |       |     1   (0)|       |       |          |
      |* 21 |     INDEX RANGE SCAN                          | MTL_ITEM_CATEGORIES_U1       |      1 |    22 |       |     1   (0)|       |       |          |
      |  22 |    TABLE ACCESS BY INDEX ROWID                | MTL_CATEGORIES_B             |      1 |    29 |       |     1   (0)|       |       |          |
      |* 23 |     INDEX UNIQUE SCAN                         | MTL_CATEGORIES_B_U1          |      1 |       |       |     1   (0)|       |       |          |
      |* 24 |   INDEX UNIQUE SCAN                           | MTL_CATEGORIES_TL_U1         |      1 |    18 |       |     1   (0)|       |       |          |
      |* 25 |  HASH JOIN RIGHT OUTER                        |                              |    236K|    81M|       | 14748   (1)|  1079K|  1079K|  923K (0)|
      |  26 |   TABLE ACCESS BY INDEX ROWID BATCHED         | FND_LOOKUP_VALUES            |     27 |  1647 |       |     1   (0)|       |       |          |
      |* 27 |    INDEX RANGE SCAN                           | FND_LOOKUP_VALUES_U1         |     27 |       |       |     1   (0)|       |       |          |
      |* 28 |   HASH JOIN RIGHT OUTER                       |                              |    236K|    67M|       | 14744   (1)|  1557K|  1557K|  898K (0)|
      |  29 |    VIEW                                       | MTL_MATERIAL_STATUSES_VL     |      9 |   495 |       |     2   (0)|       |       |          |
      |  30 |     NESTED LOOPS                              |                              |      9 |   333 |       |     2   (0)|       |       |          |
      |  31 |      TABLE ACCESS BY INDEX ROWID BATCHED      | MTL_MATERIAL_STATUSES_TL     |      9 |   225 |       |     1   (0)|       |       |          |
      |* 32 |       INDEX SKIP SCAN                         | MTL_MATERIAL_STATUSES_TL_PK  |      9 |       |       |     1   (0)|       |       |          |
      |* 33 |      INDEX UNIQUE SCAN                        | MTL_MATERIAL_STATUSES_B_PK   |      1 |    12 |       |     1   (0)|       |       |          |
      |  34 |    NESTED LOOPS OUTER                         |                              |    236K|    54M|       | 14739   (1)|       |       |          |
      |* 35 |     FILTER                                    |                              |        |       |       |            |       |       |          |
      |* 36 |      HASH JOIN RIGHT OUTER                    |                              |    236K|    45M|       |  7706   (1)|  3501K|  2075K| 3105K (0)|
      |  37 |       VIEW                                    |                              |  29984 |   849K|       |   212   (3)|       |       |          |
      |  38 |        HASH GROUP BY                          |                              |  29984 |   497K|       |   212   (3)|  4002K|  1563K| 2844K (0)|
      |* 39 |         TABLE ACCESS BY INDEX ROWID BATCHED   | MTL_ONHAND_QUANTITIES_DETAIL |  47347 |   786K|       |   207   (1)|       |       |          |
      |* 40 |          INDEX RANGE SCAN                     | MTL_ONHAND_QUANTITIES_N7     |    757K|       |       |    26   (0)|       |       |          |
      |* 41 |       HASH JOIN RIGHT OUTER                   |                              |    236K|    39M|  3240K|  7490   (1)|  4587K|  1530K| 5651K (0)|
      |  42 |        VIEW                                   |                              |  47347 |  2681K|       |   644   (2)|       |       |          |
      |  43 |         HASH GROUP BY                         |                              |  47347 |  1572K|  2424K|   644   (2)|  6382K|  2620K| 4766K (0)|
      |* 44 |          TABLE ACCESS BY INDEX ROWID BATCHED  | MTL_ONHAND_QUANTITIES_DETAIL |  47347 |  1572K|       |   207   (1)|       |       |          |
      |* 45 |           INDEX RANGE SCAN                    | MTL_ONHAND_QUANTITIES_N7     |    757K|       |       |    26   (0)|       |       |          |
      |* 46 |        HASH JOIN OUTER                        |                              |    215K|    23M|    16M|  5158   (1)|    55M|  4904K|   58M (0)|
      |  47 |         NESTED LOOPS                          |                              |    215K|    14M|       |   324   (1)|       |       |          |
      |  48 |          TABLE ACCESS BY INDEX ROWID          | MTL_PARAMETERS               |      1 |     7 |       |     1   (0)|       |       |          |
      |* 49 |           INDEX UNIQUE SCAN                   | MTL_PARAMETERS_U1            |      1 |       |       |     1   (0)|       |       |          |
      |  50 |          TABLE ACCESS BY INDEX ROWID BATCHED  | MTL_SYSTEM_ITEMS_B           |    215K|    12M|       |   323   (1)|       |       |          |
      |* 51 |           INDEX SKIP SCAN                     | MTL_SYSTEM_ITEMS_B_N15       |    215K|       |       |     7   (0)|       |       |          |
      |  52 |         VIEW                                  |                              |    318K|    14M|       |  2845   (1)|       |       |          |
      |* 53 |          HASH JOIN                            |                              |    318K|    12M|       |  2845   (1)|  2061K|  2061K| 1454K (0)|
      |  54 |           TABLE ACCESS FULL                   | MTL_ABC_CLASSES              |     29 |   145 |       |     5   (0)|       |       |          |
      |  55 |           NESTED LOOPS                        |                              |    318K|    10M|       |  2837   (1)|       |       |          |
      |  56 |            TABLE ACCESS BY INDEX ROWID        | MTL_ABC_ASSIGNMENT_GROUPS    |      1 |    23 |       |     1   (0)|       |       |          |
      |* 57 |             INDEX UNIQUE SCAN                 | MTL_ABC_ASSIGNMENT_GROUPS_U2 |      1 |       |       |     1   (0)|       |       |          |
      |  58 |            TABLE ACCESS BY INDEX ROWID BATCHED| MTL_ABC_ASSIGNMENTS          |    318K|  4038K|       |  2836   (1)|       |       |          |
      |* 59 |             INDEX RANGE SCAN                  | MTL_ABC_ASSIGNMENTS_U1       |    318K|       |       |    12   (9)|       |       |          |
      |  60 |     TABLE ACCESS BY INDEX ROWID BATCHED       | MTL_ITEM_LOCATIONS           |      1 |    41 |       |     1   (0)|       |       |          |
      |* 61 |      INDEX RANGE SCAN                         | MTL_ITEM_LOCATIONS_U1        |      1 |       |       |     1   (0)|       |       |          |
      -------------------------------------------------------------------------------------------------------------------------------------------------------
      
      Outline Data
      -------------
      
        /*+
            BEGIN_OUTLINE_DATA
            IGNORE_OPTIM_EMBEDDED_HINTS
            OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
            DB_VERSION('12.1.0.2')
            OPT_PARAM('_b_tree_bitmap_plans' 'false')
            OPT_PARAM('_fast_full_scan_enabled' 'false')
            OPT_PARAM('_optimizer_use_feedback' 'false')
            OPT_PARAM('_px_adaptive_dist_method' 'off')
            OPT_PARAM('_optimizer_dsdir_usage_control' 0)
            OPT_PARAM('_optimizer_adaptive_plans' 'false')
            OPT_PARAM('_optimizer_strans_adaptive_pruning' 'false')
            OPT_PARAM('_optimizer_gather_feedback' 'false')
            OPT_PARAM('_optimizer_inmemory_table_expansion' 'false')
            OPT_PARAM('_optimizer_inmemory_gen_pushable_preds' 'false')
            OPT_PARAM('_optimizer_inmemory_autodop' 'false')
            OPT_PARAM('_optimizer_inmemory_access_path' 'false')
            OPT_PARAM('_optimizer_inmemory_bloom_filter' 'false')
            OPT_PARAM('_optimizer_nlj_hj_adaptive_join' 'false')
            OPT_PARAM('_optimizer_inmemory_minmax_pruning' 'false')
            OPT_PARAM('_optimizer_inmemory_cluster_aware_dop' 'false')
            OPT_PARAM('optimizer_index_cost_adj' 1)
            ALL_ROWS
            OUTLINE_LEAF(@"SEL$8C60ED75")
            MERGE(@"SEL$0D656E9E")
            MERGE(@"SEL$CF5359D5")
            OUTLINE_LEAF(@"SEL$45068F18")
            MERGE(@"SEL$97CCBC9E")
            OUTLINE_LEAF(@"SEL$B1F8E3A7")
            MERGE(@"SEL$3B673E5C")
            MERGE(@"SEL$9B0555CF")
            OUTLINE_LEAF(@"SEL$A065B7E5")
            MERGE(@"SEL$28")
            OUTLINE_LEAF(@"SEL$5")
            OUTLINE_LEAF(@"SEL$A3578F30")
            MERGE(@"SEL$5F9076AD")
            MERGE(@"SEL$9CF1E98E")
            OUTLINE_LEAF(@"SEL$335DD26A")
            MERGE(@"SEL$3")
            OUTLINE_LEAF(@"SEL$2F9EA193")
            MERGE(@"SEL$7B395E3F")
            OUTLINE(@"SEL$7")
            OUTLINE(@"SEL$0D656E9E")
            MERGE(@"SEL$1CF66C63")
            MERGE(@"SEL$42DFC41A")
            OUTLINE(@"SEL$CF5359D5")
            MERGE(@"SEL$9")
            OUTLINE(@"SEL$15")
            OUTLINE(@"SEL$97CCBC9E")
            MERGE(@"SEL$17")
            OUTLINE(@"SEL$18")
            OUTLINE(@"SEL$3B673E5C")
            MERGE(@"SEL$20")
            OUTLINE(@"SEL$9B0555CF")
            MERGE(@"SEL$00A1922E")
            MERGE(@"SEL$DC3B0B0A")
            OUTLINE(@"SEL$27")
            OUTLINE(@"SEL$28")
            OUTLINE(@"SEL$31")
            OUTLINE(@"SEL$5F9076AD")
            MERGE(@"SEL$35")
            OUTLINE(@"SEL$9CF1E98E")
            MERGE(@"SEL$33")
            OUTLINE(@"SEL$2")
            OUTLINE(@"SEL$3")
            OUTLINE(@"SEL$1")
            OUTLINE(@"SEL$7B395E3F")
            MERGE(@"SEL$30")
            MERGE(@"SEL$7AE4E971")
            MERGE(@"SEL$FF4A7D68")
            OUTLINE(@"SEL$10")
            OUTLINE(@"SEL$1CF66C63")
            MERGE(@"SEL$14")
            OUTLINE(@"SEL$42DFC41A")
            MERGE(@"SEL$12")
            OUTLINE(@"SEL$8")
            OUTLINE(@"SEL$9")
            OUTLINE(@"SEL$16")
            OUTLINE(@"SEL$17")
            OUTLINE(@"SEL$19")
            OUTLINE(@"SEL$20")
            OUTLINE(@"SEL$21")
            OUTLINE(@"SEL$00A1922E")
            MERGE(@"SEL$23")
            OUTLINE(@"SEL$DC3B0B0A")
            MERGE(@"SEL$25")
            OUTLINE(@"SEL$34")
            OUTLINE(@"SEL$35")
            OUTLINE(@"SEL$32")
            OUTLINE(@"SEL$33")
            OUTLINE(@"SEL$4")
            OUTLINE(@"SEL$30")
            OUTLINE(@"SEL$7AE4E971")
            MERGE(@"SEL$A35C1FEA")
            OUTLINE(@"SEL$FF4A7D68")
            MERGE(@"SEL$37")
            OUTLINE(@"SEL$13")
            OUTLINE(@"SEL$14")
            OUTLINE(@"SEL$11")
            OUTLINE(@"SEL$12")
            OUTLINE(@"SEL$22")
            OUTLINE(@"SEL$23")
            OUTLINE(@"SEL$24")
            OUTLINE(@"SEL$25")
            OUTLINE(@"SEL$6")
            OUTLINE(@"SEL$A35C1FEA")
            MERGE(@"SEL$29")
            OUTLINE(@"SEL$36")
            OUTLINE(@"SEL$37")
            OUTLINE(@"SEL$26")
            OUTLINE(@"SEL$29")
            INDEX_RS_ASC(@"SEL$2F9EA193" "MP1"@"SEL$29" ("MTL_PARAMETERS"."ORGANIZATION_ID"))
            INDEX_SS(@"SEL$2F9EA193" "MSIB"@"SEL$29" ("MTL_SYSTEM_ITEMS_B"."STYLE_ITEM_ID" "MTL_SYSTEM_ITEMS_B"."ORGANIZATION_ID"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2F9EA193" "MSIB"@"SEL$29")
            NO_ACCESS(@"SEL$2F9EA193" "CLASTABLE"@"SEL$6")
            NO_ACCESS(@"SEL$2F9EA193" "ONHSIW"@"SEL$4")
            NO_ACCESS(@"SEL$2F9EA193" "ONH"@"SEL$29")
            INDEX_RS_ASC(@"SEL$2F9EA193" "MTL_ITEM_LOCATIONS"@"SEL$30" ("MTL_ITEM_LOCATIONS"."INVENTORY_LOCATION_ID" 
                    "MTL_ITEM_LOCATIONS"."ORGANIZATION_ID"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2F9EA193" "MTL_ITEM_LOCATIONS"@"SEL$30")
            NO_ACCESS(@"SEL$2F9EA193" "MMS"@"SEL$4")
            INDEX_RS_ASC(@"SEL$2F9EA193" "FND_LOOKUP_VALUES"@"SEL$37" ("FND_LOOKUP_VALUES"."LOOKUP_TYPE" "FND_LOOKUP_VALUES"."VIEW_APPLICATION_ID" 
                    "FND_LOOKUP_VALUES"."LOOKUP_CODE" "FND_LOOKUP_VALUES"."SECURITY_GROUP_ID" "FND_LOOKUP_VALUES"."LANGUAGE" 
                    "FND_LOOKUP_VALUES"."ZD_EDITION_NAME"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2F9EA193" "FND_LOOKUP_VALUES"@"SEL$37")
            LEADING(@"SEL$2F9EA193" "MP1"@"SEL$29" "MSIB"@"SEL$29" "CLASTABLE"@"SEL$6" "ONHSIW"@"SEL$4" "ONH"@"SEL$29" "MTL_ITEM_LOCATIONS"@"SEL$30" 
                    "MMS"@"SEL$4" "FND_LOOKUP_VALUES"@"SEL$37")
            USE_NL(@"SEL$2F9EA193" "MSIB"@"SEL$29")
            USE_HASH(@"SEL$2F9EA193" "CLASTABLE"@"SEL$6")
            USE_HASH(@"SEL$2F9EA193" "ONHSIW"@"SEL$4")
            USE_HASH(@"SEL$2F9EA193" "ONH"@"SEL$29")
            USE_NL(@"SEL$2F9EA193" "MTL_ITEM_LOCATIONS"@"SEL$30")
            USE_HASH(@"SEL$2F9EA193" "MMS"@"SEL$4")
            USE_HASH(@"SEL$2F9EA193" "FND_LOOKUP_VALUES"@"SEL$37")
            SWAP_JOIN_INPUTS(@"SEL$2F9EA193" "ONHSIW"@"SEL$4")
            SWAP_JOIN_INPUTS(@"SEL$2F9EA193" "ONH"@"SEL$29")
            SWAP_JOIN_INPUTS(@"SEL$2F9EA193" "MMS"@"SEL$4")
            SWAP_JOIN_INPUTS(@"SEL$2F9EA193" "FND_LOOKUP_VALUES"@"SEL$37")
            INDEX_RS_ASC(@"SEL$A065B7E5" "MOQD"@"SEL$28" ("MTL_ONHAND_QUANTITIES_DETAIL"."IS_CONSIGNED"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$A065B7E5" "MOQD"@"SEL$28")
            USE_HASH_AGGREGATION(@"SEL$A065B7E5")
            INDEX_RS_ASC(@"SEL$5" "A"@"SEL$5" ("MTL_ABC_ASSIGNMENT_GROUPS"."ORGANIZATION_ID" "MTL_ABC_ASSIGNMENT_GROUPS"."ASSIGNMENT_GROUP_NAME"))
            INDEX_RS_ASC(@"SEL$5" "B"@"SEL$5" ("MTL_ABC_ASSIGNMENTS"."ASSIGNMENT_GROUP_ID" "MTL_ABC_ASSIGNMENTS"."INVENTORY_ITEM_ID"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "B"@"SEL$5")
            FULL(@"SEL$5" "D"@"SEL$5")
            LEADING(@"SEL$5" "A"@"SEL$5" "B"@"SEL$5" "D"@"SEL$5")
            USE_NL(@"SEL$5" "B"@"SEL$5")
            USE_HASH(@"SEL$5" "D"@"SEL$5")
            SWAP_JOIN_INPUTS(@"SEL$5" "D"@"SEL$5")
            INDEX_SS(@"SEL$A3578F30" "MTL_MATERIAL_STATUSES_TL"@"SEL$33" ("MTL_MATERIAL_STATUSES_TL"."STATUS_ID" "MTL_MATERIAL_STATUSES_TL"."LANGUAGE" 
                    "MTL_MATERIAL_STATUSES_TL"."ZD_EDITION_NAME"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$A3578F30" "MTL_MATERIAL_STATUSES_TL"@"SEL$33")
            INDEX(@"SEL$A3578F30" "MTL_MATERIAL_STATUSES_B"@"SEL$35" ("MTL_MATERIAL_STATUSES_B"."STATUS_ID" "MTL_MATERIAL_STATUSES_B"."ZD_EDITION_NAME"))
            LEADING(@"SEL$A3578F30" "MTL_MATERIAL_STATUSES_TL"@"SEL$33" "MTL_MATERIAL_STATUSES_B"@"SEL$35")
            USE_NL(@"SEL$A3578F30" "MTL_MATERIAL_STATUSES_B"@"SEL$35")
            INDEX_RS_ASC(@"SEL$335DD26A" "MOQD"@"SEL$3" ("MTL_ONHAND_QUANTITIES_DETAIL"."IS_CONSIGNED"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$335DD26A" "MOQD"@"SEL$3")
            USE_HASH_AGGREGATION(@"SEL$335DD26A")
            INDEX_SS(@"SEL$B1F8E3A7" "MTL_CATEGORY_SETS_TL"@"SEL$20" ("MTL_CATEGORY_SETS_TL"."CATEGORY_SET_ID" "MTL_CATEGORY_SETS_TL"."LANGUAGE" 
                    "MTL_CATEGORY_SETS_TL"."ZD_EDITION_NAME"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$B1F8E3A7" "MTL_CATEGORY_SETS_TL"@"SEL$20")
            INDEX(@"SEL$B1F8E3A7" "MIC2"@"SEL$18" ("MTL_ITEM_CATEGORIES"."ORGANIZATION_ID" "MTL_ITEM_CATEGORIES"."INVENTORY_ITEM_ID" 
                    "MTL_ITEM_CATEGORIES"."CATEGORY_SET_ID" "MTL_ITEM_CATEGORIES"."CATEGORY_ID"))
            INDEX_RS_ASC(@"SEL$B1F8E3A7" "MTL_CATEGORIES_B"@"SEL$25" ("MTL_CATEGORIES_B"."CATEGORY_ID" "MTL_CATEGORIES_B"."ZD_EDITION_NAME"))
            INDEX(@"SEL$B1F8E3A7" "MTL_CATEGORIES_TL"@"SEL$23" ("MTL_CATEGORIES_TL"."CATEGORY_ID" "MTL_CATEGORIES_TL"."LANGUAGE" 
                    "MTL_CATEGORIES_TL"."ZD_EDITION_NAME"))
            LEADING(@"SEL$B1F8E3A7" "MTL_CATEGORY_SETS_TL"@"SEL$20" "MIC2"@"SEL$18" "MTL_CATEGORIES_B"@"SEL$25" "MTL_CATEGORIES_TL"@"SEL$23")
            USE_NL(@"SEL$B1F8E3A7" "MIC2"@"SEL$18")
            USE_NL(@"SEL$B1F8E3A7" "MTL_CATEGORIES_B"@"SEL$25")
            USE_NL(@"SEL$B1F8E3A7" "MTL_CATEGORIES_TL"@"SEL$23")
            INDEX_RS_ASC(@"SEL$45068F18" "CST_COST_TYPES"@"SEL$17" ("CST_COST_TYPES"."COST_TYPE" "CST_COST_TYPES"."ORGANIZATION_ID" 
                    "CST_COST_TYPES"."ZD_EDITION_NAME"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$45068F18" "CST_COST_TYPES"@"SEL$17")
            INDEX(@"SEL$45068F18" "CIC"@"SEL$15" ("CST_ITEM_COSTS"."INVENTORY_ITEM_ID" "CST_ITEM_COSTS"."ORGANIZATION_ID" "CST_ITEM_COSTS"."COST_TYPE_ID"))
            LEADING(@"SEL$45068F18" "CST_COST_TYPES"@"SEL$17" "CIC"@"SEL$15")
            USE_NL(@"SEL$45068F18" "CIC"@"SEL$15")
            NLJ_BATCHING(@"SEL$45068F18" "CIC"@"SEL$15")
            INDEX_SS(@"SEL$8C60ED75" "MTL_CATEGORY_SETS_TL"@"SEL$9" ("MTL_CATEGORY_SETS_TL"."CATEGORY_SET_ID" "MTL_CATEGORY_SETS_TL"."LANGUAGE" 
                    "MTL_CATEGORY_SETS_TL"."ZD_EDITION_NAME"))
            BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$8C60ED75" "MTL_CATEGORY_SETS_TL"@"SEL$9")
            INDEX(@"SEL$8C60ED75" "MIC2"@"SEL$7" ("MTL_ITEM_CATEGORIES"."ORGANIZATION_ID" "MTL_ITEM_CATEGORIES"."INVENTORY_ITEM_ID" 
                    "MTL_ITEM_CATEGORIES"."CATEGORY_SET_ID" "MTL_ITEM_CATEGORIES"."CATEGORY_ID"))
            INDEX_RS_ASC(@"SEL$8C60ED75" "MTL_CATEGORIES_B"@"SEL$14" ("MTL_CATEGORIES_B"."CATEGORY_ID" "MTL_CATEGORIES_B"."ZD_EDITION_NAME"))
            INDEX(@"SEL$8C60ED75" "MTL_CATEGORIES_TL"@"SEL$12" ("MTL_CATEGORIES_TL"."CATEGORY_ID" "MTL_CATEGORIES_TL"."LANGUAGE" 
                    "MTL_CATEGORIES_TL"."ZD_EDITION_NAME"))
            LEADING(@"SEL$8C60ED75" "MTL_CATEGORY_SETS_TL"@"SEL$9" "MIC2"@"SEL$7" "MTL_CATEGORIES_B"@"SEL$14" "MTL_CATEGORIES_TL"@"SEL$12")
            USE_NL(@"SEL$8C60ED75" "MIC2"@"SEL$7")
            USE_NL(@"SEL$8C60ED75" "MTL_CATEGORIES_B"@"SEL$14")
            USE_NL(@"SEL$8C60ED75" "MTL_CATEGORIES_TL"@"SEL$12")
            END_OUTLINE_DATA
        */
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         4 - filter("CATEGORY_SET_NAME"='MZ PRODUCT HIERARCHY')
         5 - access("ZD_EDITION_NAME"='ORA$BASE')
             filter("ZD_EDITION_NAME"='ORA$BASE')
         6 - access("MIC2"."ORGANIZATION_ID"=85 AND "MIC2"."INVENTORY_ITEM_ID"=:B1 AND "CATEGORY_SET_ID"="MIC2"."CATEGORY_SET_ID")
         8 - access("MIC2"."CATEGORY_ID"="CATEGORY_ID" AND "ZD_EDITION_NAME"='ORA$BASE')
         9 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='ORA$BASE')
        13 - access("COST_TYPE"='Average' AND "ZD_EDITION_NAME"='ORA$BASE')
             filter("ZD_EDITION_NAME"='ORA$BASE')
        14 - access("CIC"."INVENTORY_ITEM_ID"=:B1 AND "CIC"."ORGANIZATION_ID"=:B2 AND "COST_TYPE_ID"="CIC"."COST_TYPE_ID")
        19 - filter("CATEGORY_SET_NAME"='MZ WMS NEST COEFF')
        20 - access("ZD_EDITION_NAME"='ORA$BASE')
             filter("ZD_EDITION_NAME"='ORA$BASE')
        21 - access("MIC2"."ORGANIZATION_ID"=85 AND "MIC2"."INVENTORY_ITEM_ID"=:B1 AND "CATEGORY_SET_ID"="MIC2"."CATEGORY_SET_ID")
        23 - access("MIC2"."CATEGORY_ID"="CATEGORY_ID" AND "ZD_EDITION_NAME"='ORA$BASE')
        24 - access("CATEGORY_ID"="CATEGORY_ID" AND "LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='ORA$BASE')
        25 - access("INVENTORY_LOCATION_TYPE"=TO_NUMBER("LOOKUP_CODE"))
        27 - access("LOOKUP_TYPE"='MTL_LOCATOR_TYPES' AND "ZD_EDITION_NAME"='V_20160910_0212')
             filter("ZD_EDITION_NAME"='V_20160910_0212')
        28 - access("STATUS_ID"="MMS"."STATUS_ID")
        32 - access("LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='ORA$BASE')
             filter(("LANGUAGE"=USERENV('LANG') AND "ZD_EDITION_NAME"='ORA$BASE'))
        33 - access("STATUS_ID"="STATUS_ID" AND "ZD_EDITION_NAME"='ORA$BASE')
        35 - filter(NVL("ONH"."ONHAND",0)+"XXOF_WRHS_INQ"."RESERVED_BKORDR"("MSIB"."INVENTORY_ITEM_ID","MSIB"."ORGANIZATION_ID")+"XXOF_WRHS_INQ"."PUR
                    CHASE_NORMAL"("MSIB"."INVENTORY_ITEM_ID","MSIB"."ORGANIZATION_ID")+0+"XXOF_WRHS_INQ"."PURCHASE_EXPEDITED"("MSIB"."INVENTORY_ITEM_ID","MSIB"."OR
                    GANIZATION_ID")+0>0)
        36 - access("MSIB"."INVENTORY_ITEM_ID"="ONH"."INVENTORY_ITEM_ID" AND "MSIB"."ORGANIZATION_ID"="ONH"."ORGANIZATION_ID")
        39 - filter("MOQD"."ORGANIZATION_ID"=86)
        40 - access("MOQD"."IS_CONSIGNED"=2)
        41 - access("MSIB"."INVENTORY_ITEM_ID"="ONHSIW"."INVENTORY_ITEM_ID" AND "MSIB"."ORGANIZATION_ID"="ONHSIW"."ORGANIZATION_ID")
        44 - filter("MOQD"."ORGANIZATION_ID"=86)
        45 - access("MOQD"."IS_CONSIGNED"=2)
        46 - access("CLASTABLE"."INVENTORY_ITEM_ID"="MSIB"."INVENTORY_ITEM_ID" AND "CLASTABLE"."ORGANIZATION_ID"="MSIB"."ORGANIZATION_ID")
        49 - access("MP1"."ORGANIZATION_ID"=86)
        51 - access("MSIB"."ORGANIZATION_ID"=86)
             filter("MSIB"."ORGANIZATION_ID"=86)
        53 - access("B"."ABC_CLASS_ID"="D"."ABC_CLASS_ID")
        57 - access("A"."ORGANIZATION_ID"=86 AND "A"."ASSIGNMENT_GROUP_NAME"='Putaway Classes')
        59 - access("A"."ASSIGNMENT_GROUP_ID"="B"."ASSIGNMENT_GROUP_ID")
        61 - access("INVENTORY_LOCATION_ID"="ONHSIW"."LOCATOR_ID")
      
      
      
      SQL Monitoring Report
      
      
      SQL Text
      ------------------------------
      SELECT /* + qb_name(xxqb_main) gather_plan_statistics */ item , item_description , product_grp_typ_fam , locators , storage_type , on_hand , date_of_maintenance , org , indicators , uom , abc_code , package_qty , item_cost , size_uom_lwh , commodity_category , eoq_category , weight , weight_uom , status_of_locator , subinventory_code , available_qty , reserve_qty , reserve_bkord , on_order_qty , rec_month_forecast , mz_wms_nest FROM ( WITH onhsiw AS ( SELECT mq.inventory_item_id ,
      mq.organization_id , mq.subinventory_code , mq.locator_id , MAX(mq.last_update_date) last_update_date , SUM(mq.transaction_quantity) onhand FROM apps.mtl_onhand_quantities mq GROUP BY mq.inventory_item_id , mq.organization_id , mq.subinventory_code , mq.locator_id ) SELECT /*+ opt_param('optimizer_index_cost_adj',1) */ item , item_description , product_grp_typ_fam , milk.concatenated_segments locators , flv.meaning storage_type , onhsiw.onhand on_hand , TO_CHAR(onhsiw.last_update_date,
      'YYYY-MM-DD HH24:MI:SS') date_of_maintenance , org , NULL indicators , uom , abc_code , package_qty , item_cost , size_uom_lwh , commodity_category , eoq_category , weight , weight_uom , mms.status_code status_of_locator , onhsiw.subinventory_code , apps.xxwms01iac399c.item_available_to_reserve(owt.inventory_item_id, owt.organization_id, onhsiw.subinventory_code , onhsiw.locator_id) available_qty , ( reserve_so + reserve_iso + reserve_pdc ) reserve_qty , reserve_bkord , ( po_normal + int_vendor
      + on_order_exp + intransit_iso ) on_order_qty , rec_month_forecast , mz_wms_nest FROM ( WITH clastable AS ( SELECT a.organization_id , b.inventory_item_id , a.assignment_group_name , d.abc_class_name FROM apps.mtl_abc_assignment_groups a , apps.mtl_abc_assignments b , apps.mtl_abc_classes d WHERE a.assignment_group_id = b.assignment_group_id AND b.abc_class_id = d.abc_class_id AND a.assignment_group_name = 'Putaway Classes' ) SELECT /*+ materialize */ tb.* , ( SELECT mc2.segment1 || '-' ||
      mc2.segment2 || '-' || mc2.segment3 FROM apps.mtl_item_categories mic2 , apps.mtl_category_sets_tl mcst2 , apps.mtl_categories mc2 WHERE 1 = 1 AND mic2.inventory_item_id = tb.inventory_item_id AND mic2.organization_id = 85 --msib.organization_id ---Master controlled-- AND mcst2.category_set_name = 'MZ PRODUCT HIERARCHY' AND mcst2.category_set_id = mic2.category_set_id AND mic2.category_id = mc2.category_id ) product_grp_typ_fam , abc_class_name abc_code , 'Pending' package_qty , ( SELECT
      round(cic.item_cost, 5) FROM apps.cst_cost_types cct , apps.cst_item_costs cic WHERE cct.cost_type_id = cic.cost_type_id AND cic.inventory_item_id = tb.inventory_item_id AND cic.organization_id = tb.organization_id AND cct.cost_type = 'Average' ) item_cost , apps.xxif01_common_utils.get_itemcat_con_value(tb.inventory_item_id, tb.organization_id, 'MZ WMS COMMODITY CD' , 'ORG') commodity_category , apps.xxif01_common_utils.get_itemcat_con_value(tb.inventory_item_id, tb.organization_id, 'MZ EOQ',
      'ORG') eoq_category , xxof_wrhs_inq.reserved_so(tb.inventory_item_id, tb.organization_id) reserve_so , xxof_wrhs_inq.reserved_iso(tb.inventory_item_id, tb.organization_id) reserve_iso , xxof_wrhs_inq.reserved_pdc(tb.inventory_item_id, tb.organization_id) reserve_pdc , xxof_wrhs_inq.month_forecast(tb.inventory_item_id, tb.organization_id) rec_month_forecast , ( SELECT mc2.segment1 || '-' || mc2.segment2 || '-' || mc2.segment3 FROM apps.mtl_item_categories mic2 , apps.mtl_category_sets_tl mcst2 ,
      apps.mtl_categories mc2 WHERE 1 = 1 AND mic2.inventory_item_id = tb.inventory_item_id AND mic2.organization_id = 85 AND mcst2.category_set_name = 'MZ WMS NEST COEFF' AND mcst2.category_set_id = mic2.category_set_id AND mic2.category_id = mc2.category_id ) mz_wms_nest FROM ( SELECT * FROM ( WITH onh AS ( SELECT mq.inventory_item_id , mq.organization_id , SUM(mq.transaction_quantity) onhand FROM apps.mtl_onhand_quantities mq GROUP BY mq.inventory_item_id , mq.organization_id ) SELECT
      msib.inventory_item_id , msib.organization_id , msib.segment1 item , msib.description item_description , nvl(onh.onhand, 0) on_hand , mp1.organization_code org , primary_uom_code uom , ( msib.dimension_uom_code || '-' || msib.unit_length || '-' || msib.unit_width || '-' || msib.unit_height ) size_uom_lwh , msib.unit_weight weight , msib.weight_uom_code weight_uom , xxof_wrhs_inq.reserved_bkordr(msib.inventory_item_id, msib.organization_id) reserve_bkord ,
      xxof_wrhs_inq.purchase_normal(msib.inventory_item_id, msib.organization_id) po_normal , xxof_wrhs_inq.purchase_expedited(msib.inventory_item_id, msib.organization_id) on_order_exp , 0 int_vendor , 0 intransit_iso FROM apps.mtl_system_items_b msib , apps.mtl_parameters mp1 , onh WHERE msib.inventory_item_id = onh.inventory_item_id (+) AND msib.organization_id = onh.organization_id (+) AND mp1.organization_id = msib.organization_id AND msib.organization_id = 86 ) main WHERE nvl(on_hand, 0) +
      reserve_bkord + po_normal + int_vendor + on_order_exp + intransit_iso > 0 ) tb , clastable WHERE clastable.inventory_item_id (+) = tb.inventory_item_id AND clastable.organization_id (+) = tb.organization_id ) owt , apps.mtl_item_locations_kfv milk , apps.mtl_material_statuses mms , apps.fnd_lookup_values flv , onhsiw WHERE milk.inventory_location_id (+) = onhsiw.locator_id AND milk.status_id = mms.status_id (+) AND flv.lookup_code (+) = milk.inventory_location_type AND flv.lookup_type (+) =
      'MTL_LOCATOR_TYPES' AND owt.inventory_item_id = onhsiw.inventory_item_id (+) AND owt.organization_id = onhsiw.organization_id (+) )
      
      
      Global Information
      ------------------------------
       Status              :  DONE (ALL ROWS)      
       Instance ID         :  1                    
       Session             :  (4961:65078) 
       SQL ID              :  06ufnhgsx0jgs        
       SQL Execution ID    :  16777216             
       Execution Started   :  03/19/2019 09:44:37  
       First Refresh Time  :  03/19/2019 09:44:41  
       Last Refresh Time   :  03/19/2019 10:10:26  
       Duration            :  1549s                
       Module/Action       :  SQL Developer/-      
       Service             :  EBSPRD               
       Program             :  SQL Developer        
       Fetch Calls         :  1104                 
      
      
      Global Stats
      ========================================================================================
      | Elapsed |   Cpu   |    IO    | Concurrency | PL/SQL  | Fetch | Buffer | Read | Read  |
      | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Time(s) | Calls |  Gets  | Reqs | Bytes |
      ========================================================================================
      |    1176 |     803 |      373 |        0.00 |      65 |  1104 |   357M | 272K |   2GB |
      ========================================================================================
      
      
      SQL Plan Monitoring Details (Plan Hash Value=2885918576)
      ===================================================================================================================================================================================================================
      | Id |                    Operation                    |             Name             |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Mem  | Activity |       Activity Detail        |
      |    |                                                 |                              | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | (Max) |   (%)    |         (# samples)          |
      ===================================================================================================================================================================================================================
      |  0 | SELECT STATEMENT                                |                              |         |       |      1515 |    +35 | 46745 |    46743 |       |       |       |     7.23 | Cpu (74)                     |
      |    |                                                 |                              |         |       |           |        |       |          |       |       |       |          | latch free (10)              |
      |  1 |   NESTED LOOPS                                  |                              |       1 |     4 |      1512 |    +38 | 46745 |    46743 |       |       |       |          |                              |
      |  2 |    NESTED LOOPS                                 |                              |       1 |     3 |      1512 |    +38 | 46745 |    46743 |       |       |       |          |                              |
      |  3 |     NESTED LOOPS                                |                              |       1 |     2 |      1512 |    +38 | 46745 |    46743 |       |       |       |          |                              |
      |  4 |      TABLE ACCESS BY INDEX ROWID BATCHED        | MTL_CATEGORY_SETS_TL         |       1 |     1 |      1512 |    +38 | 46745 |    46745 |       |       |       |     0.09 | Cpu (1)                      |
      |  5 |       INDEX SKIP SCAN                           | MTL_CATEGORY_SETS_TL_U1      |     111 |     1 |      1512 |    +38 | 46745 |       5M |       |       |       |     0.09 | Cpu (1)                      |
      |  6 |      INDEX RANGE SCAN                           | MTL_ITEM_CATEGORIES_U1       |       1 |     1 |      1512 |    +38 | 46745 |    46743 | 23906 | 187MB |       |     3.27 | Cpu (3)                      |
      |    |                                                 |                              |         |       |           |        |       |          |       |       |       |          | db file sequential read (35) |
      |  7 |     TABLE ACCESS BY INDEX ROWID                 | MTL_CATEGORIES_B             |       1 |     1 |      1512 |    +38 | 46743 |    46743 |       |       |       |          |                              |
      |  8 |      INDEX UNIQUE SCAN                          | MTL_CATEGORIES_B_U1          |       1 |     1 |      1512 |    +38 | 46743 |    46743 |       |       |       |     0.17 | Cpu (2)                      |
      |  9 |    INDEX UNIQUE SCAN                            | MTL_CATEGORIES_TL_U1         |       1 |     1 |      1512 |    +38 | 46743 |    46743 |       |       |       |          |                              |
      | 10 |   NESTED LOOPS                                  |                              |       1 |     2 |      1512 |    +38 | 46745 |    46742 |       |       |       |          |                              |
      | 11 |    NESTED LOOPS                                 |                              |       1 |     2 |      1512 |    +38 | 46745 |    46742 |       |       |       |          |                              |
      | 12 |     TABLE ACCESS BY INDEX ROWID BATCHED         | CST_COST_TYPES               |       1 |     1 |      1512 |    +38 | 46745 |    46745 |       |       |       |          |                              |
      | 13 |      INDEX RANGE SCAN                           | CST_COST_TYPES_U2            |       1 |     1 |      1512 |    +38 | 46745 |    46745 |       |       |       |          |                              |
      | 14 |     INDEX UNIQUE SCAN                           | CST_ITEM_COSTS_U1            |       1 |     1 |      1512 |    +38 | 46745 |    46742 |  9550 |  75MB |       |     0.86 | Cpu (2)                      |
      |    |                                                 |                              |         |       |           |        |       |          |       |       |       |          | db file sequential read (8)  |
      | 15 |    TABLE ACCESS BY INDEX ROWID                  | CST_ITEM_COSTS               |       1 |     1 |      1512 |    +38 | 46742 |    46742 |  7461 |  58MB |       |     0.52 | db file sequential read (6)  |
      | 16 |   NESTED LOOPS                                  |                              |       1 |     4 |      1040 |   +506 | 46745 |      465 |       |       |       |          |                              |
      | 17 |    NESTED LOOPS                                 |                              |       1 |     3 |      1040 |   +506 | 46745 |      465 |       |       |       |          |                              |
      | 18 |     NESTED LOOPS                                |                              |       1 |     2 |      1512 |    +38 | 46745 |      465 |       |       |       |          |                              |
      | 19 |      TABLE ACCESS BY INDEX ROWID BATCHED        | MTL_CATEGORY_SETS_TL         |       1 |     1 |      1512 |    +38 | 46745 |    46745 |       |       |       |     0.09 | Cpu (1)                      |
      | 20 |       INDEX SKIP SCAN                           | MTL_CATEGORY_SETS_TL_U1      |     111 |     1 |      1512 |    +38 | 46745 |       5M |       |       |       |     0.17 | Cpu (2)                      |
      | 21 |      INDEX RANGE SCAN                           | MTL_ITEM_CATEGORIES_U1       |       1 |     1 |      1040 |   +506 | 46745 |      465 |  4680 |  37MB |       |     0.60 | db file sequential read (7)  |
      | 22 |     TABLE ACCESS BY INDEX ROWID                 | MTL_CATEGORIES_B             |       1 |     1 |      1040 |   +506 |   465 |      465 |     3 | 24576 |       |          |                              |
      | 23 |      INDEX UNIQUE SCAN                          | MTL_CATEGORIES_B_U1          |       1 |     1 |      1040 |   +506 |   465 |      465 |       |       |       |          |                              |
      | 24 |    INDEX UNIQUE SCAN                            | MTL_CATEGORIES_TL_U1         |       1 |     1 |      1040 |   +506 |   465 |      465 |     2 | 16384 |       |          |                              |
      | 25 |   HASH JOIN RIGHT OUTER                         |                              |    236K | 14748 |      1546 |     +4 |     1 |    55192 |       |       |  945K |          |                              |
      | 26 |    TABLE ACCESS BY INDEX ROWID BATCHED          | FND_LOOKUP_VALUES            |      27 |     1 |         1 |     +4 |     1 |        7 |       |       |       |          |                              |
      | 27 |     INDEX RANGE SCAN                            | FND_LOOKUP_VALUES_U1         |      27 |     1 |         1 |     +4 |     1 |        7 |     1 |  8192 |       |          |                              |
      | 28 |    HASH JOIN RIGHT OUTER                        |                              |    236K | 14744 |      1546 |     +4 |     1 |    55192 |       |       |  920K |          |                              |
      | 29 |     VIEW                                        | MTL_MATERIAL_STATUSES_VL     |       9 |     2 |         1 |     +4 |     1 |        9 |       |       |       |          |                              |
      | 30 |      NESTED LOOPS                               |                              |       9 |     2 |         1 |     +4 |     1 |        9 |       |       |       |          |                              |
      | 31 |       TABLE ACCESS BY INDEX ROWID BATCHED       | MTL_MATERIAL_STATUSES_TL     |       9 |     1 |         1 |     +4 |     1 |        9 |       |       |       |          |                              |
      | 32 |        INDEX SKIP SCAN                          | MTL_MATERIAL_STATUSES_TL_PK  |       9 |     1 |         1 |     +4 |     1 |        9 |       |       |       |          |                              |
      | 33 |       INDEX UNIQUE SCAN                         | MTL_MATERIAL_STATUSES_B_PK   |       1 |     1 |      1546 |     +4 |     9 |        9 |       |       |       |          |                              |
      | 34 |     NESTED LOOPS OUTER                          |                              |    236K | 14739 |      1512 |    +38 |     1 |    55192 |       |       |       |          |                              |
      | 35 |      FILTER                                     |                              |         |       |      1518 |    +32 |     1 |    55192 |       |       |       |     0.43 | Cpu (5)                      |
      | 36 |       HASH JOIN RIGHT OUTER                     |                              |    236K |  7706 |      1546 |     +4 |     1 |     510K |       |       |    3M |          |                              |
      | 37 |        VIEW                                     |                              |   29984 |   212 |         1 |     +4 |     1 |    45447 |       |       |       |          |                              |
      | 38 |         HASH GROUP BY                           |                              |   29984 |   212 |         1 |     +4 |     1 |    45447 |       |       |    3M |          |                              |
      | 39 |          TABLE ACCESS BY INDEX ROWID BATCHED    | MTL_ONHAND_QUANTITIES_DETAIL |   47347 |   207 |         1 |     +4 |     1 |     110K |       |       |       |          |                              |
      | 40 |           INDEX RANGE SCAN                      | MTL_ONHAND_QUANTITIES_N7     |    758K |    26 |         4 |     +1 |     1 |     757K |  2358 |  18MB |       |     0.17 | db file sequential read (2)  |
      | 41 |        HASH JOIN RIGHT OUTER                    |                              |    236K |  7490 |      1546 |     +4 |     1 |     510K |       |       |    6M |          |                              |
      | 42 |         VIEW                                    |                              |   47347 |   644 |         1 |     +4 |     1 |    53894 |       |       |       |          |                              |
      | 43 |          HASH GROUP BY                          |                              |   47347 |   644 |         1 |     +4 |     1 |    53894 |       |       |    5M |          |                              |
      | 44 |           TABLE ACCESS BY INDEX ROWID BATCHED   | MTL_ONHAND_QUANTITIES_DETAIL |   47347 |   207 |         2 |     +3 |     1 |     110K |       |       |       |     0.09 | Cpu (1)                      |
      | 45 |            INDEX RANGE SCAN                     | MTL_ONHAND_QUANTITIES_N7     |    758K |    26 |         1 |     +4 |     1 |     757K |       |       |       |          |                              |
      | 46 |         HASH JOIN OUTER                         |                              |    216K |  5158 |      1546 |     +4 |     1 |     502K |       |       |   61M |     0.43 | Cpu (5)                      |
      | 47 |          NESTED LOOPS                           |                              |    216K |   324 |        29 |     +4 |     1 |     502K |       |       |       |          |                              |
      | 48 |           TABLE ACCESS BY INDEX ROWID           | MTL_PARAMETERS               |       1 |     1 |         1 |     +4 |     1 |        1 |       |       |       |          |                              |
      | 49 |            INDEX UNIQUE SCAN                    | MTL_PARAMETERS_U1            |       1 |     1 |        27 |     +4 |     1 |        1 |       |       |       |          |                              |
      | 50 |           TABLE ACCESS BY INDEX ROWID BATCHED   | MTL_SYSTEM_ITEMS_B           |    216K |   323 |        29 |     +4 |     1 |     502K | 35227 | 275MB |       |     2.32 | Cpu (1)                      |
      |    |                                                 |                              |         |       |           |        |       |          |       |       |       |          | db file sequential read (26) |
      | 51 |            INDEX SKIP SCAN                      | MTL_SYSTEM_ITEMS_B_N15       |    216K |     7 |        29 |     +4 |     1 |     502K |  1804 |  14MB |       |          |                              |
      | 52 |          VIEW                                   |                              |    318K |  2845 |      1417 |    +32 |     1 |     268K |       |       |       |          |                              |
      | 53 |           HASH JOIN                             |                              |    318K |  2845 |      1417 |    +32 |     1 |     268K |       |       |    1M |          |                              |
      | 54 |            TABLE ACCESS FULL                    | MTL_ABC_CLASSES              |      29 |     5 |         1 |    +32 |     1 |       29 |     3 | 104KB |       |          |                              |
      | 55 |            NESTED LOOPS                         |                              |    318K |  2837 |      1417 |    +32 |     1 |     268K |       |       |       |          |                              |
      | 56 |             TABLE ACCESS BY INDEX ROWID         | MTL_ABC_ASSIGNMENT_GROUPS    |       1 |     1 |         1 |    +32 |     1 |        1 |       |       |       |          |                              |
      | 57 |              INDEX UNIQUE SCAN                  | MTL_ABC_ASSIGNMENT_GROUPS_U2 |       1 |     1 |      1415 |    +32 |     1 |        1 |       |       |       |          |                              |
      | 58 |             TABLE ACCESS BY INDEX ROWID BATCHED | MTL_ABC_ASSIGNMENTS          |    318K |  2836 |      1417 |    +32 |     1 |     268K |  1699 |  13MB |       |     0.26 | Cpu (1)                      |
      |    |                                                 |                              |         |       |           |        |       |          |       |       |       |          | db file sequential read (2)  |
      | 59 |              INDEX RANGE SCAN                   | MTL_ABC_ASSIGNMENTS_U1       |    318K |    12 |      1417 |    +32 |     1 |     268K |   969 |   8MB |       |          |                              |
      | 60 |      TABLE ACCESS BY INDEX ROWID BATCHED        | MTL_ITEM_LOCATIONS           |       1 |     1 |      1512 |    +38 | 55192 |    53894 |   138 |   1MB |       |     0.26 | Cpu (3)                      |
      | 61 |       INDEX RANGE SCAN                          | MTL_ITEM_LOCATIONS_U1        |       1 |     1 |      1512 |    +38 | 55192 |    53894 |       |       |       |          |                              |
      ===================================================================================================================================================================================================================
      
      
      S
      
      
      
      
      
      
      
      
      
        • 1. Re: Slow Query
          3204324

          you have the hint 'optimizer_index_cost_adj' in your query and it mentions as 1 and it makes oracle think the "Index access will be cheaper than the full table scan which is not always the case

           

           

          --can you try running the same query by removing the hint--

          • 2. Re: Slow Query
            Cookiemonster76

            Do you think the query as posted is readable?

            Do you expect every individual on this forum who wants to help to format it themselves?

            • 3. Re: Slow Query
              Sven W.

              Create an execution plan where you also show the actual rows (A-rows) not only the estimated rows (E-rows).

               

              SELECT *

              FROM table(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST ALL +OUTLINE'));

              https://blogs.oracle.com/optimizer/how-to-generate-a-useful-sql-execution-plan

              • 4. Re: Slow Query
                Jonathan Lewis

                Four points to start -

                you have hint  gather_plan_statitics - which enables rowsource execution stats. This can introduce a massive overhead when you have lots of busy nested loop joins in place. This may be why the summary of the SQL Monitor report says elapsed time 1176 while the detail (operation zero) suggests 1550 seconds (1515 + 35). You don't need that hint when you can pull stats from the SQL Monitor.  (It's possible, though,  that the difference is simply the effect of client fetch time since you have 1,100 fetch calls for 45,000 rows).

                 

                As 3204324 points out, the query is running with optimizer_index_cost_adj = 1. This doesn't just make Oracle think that indexes have become much cheaper relative to tablescans, it also means that Oracle can't see much difference in the quality of the indexes on any given table - this may be why you've got a (potentially inefficient) skip scan at operation 5, which picks up 5M index entries, resulting in resulting in operation 4 visiting the table 5M times and discarding 4,953,000 or the rows it accesses (and the same again at operation 20). It may also be why, at operations 58/59 Oracle accesses 268K rows by index range scan from a single value driving a nested loop rather than doing a tablescan with hash join.

                 

                Operation 35 is a FILTER operation that looks like it's calling two PL/SQL functions to eliminate 90% of the 500K rows acquired by the hash join at operation 36.  Have theses pl/sql functions  been declared as deterministic ? Are they doing something inefficient. The report says 65 seconds on PL/SQL time, but there's a massive discrepancy between the Read Bytes in the summary (2GB) and the Read Bytes in the body of the plan (870M) - so I'm not inclined to trust the report completely (and the existence of all the inline scalar subqueries seems to have made some of the other numbers suspect).

                 

                 

                Without reading the code to examine in detail what it's doing and discovering how Oracle has transformed it, I would start by re-testing with the gather_plan_statistics hint taken out and the optimizer_index_cost_adj corrected.  If the result was still slow one of the strategies I'd consider is testing with without [edited] the scalar subqueries in the select list (temporarily) - with a view to thinking about creating non-mergeable views from them and doing a hash join into the body of the query, as it may be that the most significant part of the work load will be the large number of executions of those scalar subqueries.

                 

                Regards

                Jonathan Lewis

                • 5. Re: Slow Query
                  Randolf Geist

                  Jonathan Lewis wrote:

                   

                  Operation 35 is a FILTER operation that looks like it's calling two PL/SQL functions to eliminate 90% of the 500K rows acquired by the hash join at operation 36. Have theses pl/sql functions been declared as deterministic ? Are they doing something inefficient. The report says 65 seconds on PL/SQL time, but there's a massive discrepancy between the Read Bytes in the summary (2GB) and the Read Bytes in the body of the plan (870M) - so I'm not inclined to trust the report completely (and the existence of all the inline scalar subqueries seems to have made some of the other numbers suspect).

                   

                  Regards

                  Jonathan Lewis

                  Jonathan,

                   

                  just to add to what you've already pointed out (and possibly already referred to by "other numbers suspect"): It's a little bit odd that only approx. 15% of the time spent (assuming the 1100 seconds are in the right ballpark, even less assuming the 1500 seconds duration) is covered by the Real Time SQL Monitoring activity information - which is based on Active Session History. This is rather unusual and begs the question why all that activity wasn't captured in ASH... I have seen behaviour like this due to PDB/CDB bugs (the ASH activity somehow only shows up on ASH CDB level instead of on PDB level), but it could be anything else, like a very busy system where the data was already aged out of in-memory ASH etc. It's just something I would follow up on by manually querying ASH etc. to get a better understanding what is going on.

                   

                  Kind regards,

                  Randolf

                  1 person found this helpful
                  • 6. Re: Slow Query
                    Mohamed Houri

                    Hello Randolf

                     

                    Just to add that not all what is found in ASH is reported into SQL Monitoring report. For example time spent parsing is correctly reported in ASH via the IN_PARSE columns but not into the SQL Monitoring Duration. In fact SQL Monitoring report kicks in only when the query is in its execution phase (sql_exec_id, sql_exec_start).

                     

                    Best regards

                    Mohamed Houri

                    • 7. Re: Slow Query
                      Randolf Geist

                      Mohamed Houri wrote:

                       

                      Just to add that not all what is found in ASH is reported into SQL Monitoring report. For example time spent parsing is correctly reported in ASH via the IN_PARSE columns but not into the SQL Monitoring Duration. In fact SQL Monitoring report kicks in only when the query is in its execution phase (sql_exec_id, sql_exec_start).

                       

                       

                      Hi Mohamed,

                       

                      that's true but as you've pointed out the SQL Monitoring report only covers the execution phase, so still the question remains why that execution phase isn't properly covered by the ASH sampling which it usually is.

                       

                      Kind regards,

                      Randolf

                      • 8. Re: Slow Query
                        Jonathan Lewis

                        @randolf

                         

                        It did cross my mind that maybe the lost time is due to the filter predicate at operation 35

                         

                        NVL("ONH"."ONHAND",0) +

                            "XXOF_WRHS_INQ"."RESERVED_BKORDR"  ("MSIB"."INVENTORY_ITEM_ID","MSIB"."ORGANIZATION_ID") +

                            "XXOF_WRHS_INQ"."PURCHASE_NORMAL"  ("MSIB"."INVENTORY_ITEM_ID","MSIB"."ORGANIZATION_ID") + 0 +

                            "XXOF_WRHS_INQ"."PURCHASE_EXPEDITED"("MSIB"."INVENTORY_ITEM_ID","MSIB"."ORGANIZATION_ID") + 0 > 0

                         

                        It's 3 functions rather than the 2 I first thought - but if they call SQL maybe the code that raids ASH (in this version of Oracle) loses site of the SQL activity.  Maybe the ASH part of the code is looking for at SQL_ID rather than the TOP_SQL_ID  (columns names may be wrong - I don't have a database in front of me to check them).

                         

                        I may do a quick test when I next have access to a DB.

                         

                        Regards

                        Jonathan Lewis

                        • 9. Re: Slow Query
                          2980262

                          Thank You Jonathan and Randolf. I am checking on function part and test with in line views.

                           

                          As suggested I have removed hints and below are new details -

                           

                           

                          
                          
                          Global Stats
                          ========================================================================================
                          | Elapsed |   Cpu   |    IO    | Concurrency | PL/SQL  | Fetch | Buffer | Read | Read  |
                          | Time(s) | Time(s) | Waits(s) |  Waits(s)   | Time(s) | Calls |  Gets  | Reqs | Bytes |
                          ========================================================================================
                          |    1256 |     841 |      414 |        0.01 |      62 |  1112 |   373M | 385K |   3GB |
                          ========================================================================================
                          
                          
                          SQL Plan Monitoring Details (Plan Hash Value=3224886758)
                          =================================================================================================================================================================================================================
                          | Id |                   Operation                   |             Name             |  Rows   | Cost  |   Time    | Start  | Execs |   Rows   | Read  | Read  |  Mem  | Activity |       Activity Detail        |
                          |    |                                               |                              | (Estim) |       | Active(s) | Active |       | (Actual) | Reqs  | Bytes | (Max) |   (%)    |         (# samples)          |
                          =================================================================================================================================================================================================================
                          |  0 | SELECT STATEMENT                              |                              |         |       |      1201 |    +52 | 46798 |        0 |       |       |       |     6.65 | Cpu (80)                     |
                          |    |                                               |                              |         |       |           |        |       |          |       |       |       |          | latch free (2)               |
                          |  1 |   NESTED LOOPS                                |                              |       1 |     5 |           |        | 46798 |          |       |       |       |          |                              |
                          |  2 |    NESTED LOOPS                               |                              |       1 |     5 |           |        | 46798 |          |       |       |       |          |                              |
                          |  3 |     INDEX RANGE SCAN                          | MTL_ITEM_CATEGORIES_U1       |       1 |     3 |      1161 |    +19 | 46798 |        0 | 27857 | 218MB |       |     3.48 | db file sequential read (43) |
                          |  4 |     INDEX UNIQUE SCAN                         | MTL_CATEGORIES_B_U1          |       1 |     1 |           |        |       |          |       |       |       |          |                              |
                          |  5 |    TABLE ACCESS BY INDEX ROWID                | MTL_CATEGORIES_B             |       1 |     2 |           |        |       |          |       |       |       |          |                              |
                          |  6 |   NESTED LOOPS                                |                              |       1 |     3 |      1247 |     +9 | 46798 |    46795 |       |       |       |          |                              |
                          |  7 |    NESTED LOOPS                               |                              |       1 |     3 |      1247 |     +9 | 46798 |    46795 |       |       |       |          |                              |
                          |  8 |     TABLE ACCESS BY INDEX ROWID BATCHED       | CST_COST_TYPES               |       1 |     2 |      1247 |     +9 | 46798 |    46798 |       |       |       |          |                              |
                          |  9 |      INDEX RANGE SCAN                         | CST_COST_TYPES_U2            |       1 |     1 |      1247 |     +9 | 46798 |    46798 |       |       |       |          |                              |
                          | 10 |     INDEX UNIQUE SCAN                         | CST_ITEM_COSTS_U1            |       1 |     1 |      1247 |     +9 | 46798 |    46795 |  9226 |  72MB |       |     0.65 | db file sequential read (8)  |
                          | 11 |    TABLE ACCESS BY INDEX ROWID                | CST_ITEM_COSTS               |       1 |     2 |      1247 |     +9 | 46795 |    46795 |  4161 |  33MB |       |     0.24 | db file sequential read (3)  |
                          | 12 |   NESTED LOOPS                                |                              |       1 |     5 |           |        | 46798 |          |       |       |       |          |                              |
                          | 13 |    NESTED LOOPS                               |                              |       1 |     5 |         1 |  +1060 | 46798 |        0 |       |       |       |     0.08 | Cpu (1)                      |
                          | 14 |     INDEX RANGE SCAN                          | MTL_ITEM_CATEGORIES_U1       |       1 |     3 |           |        | 46798 |          |       |       |       |          |                              |
                          | 15 |     INDEX UNIQUE SCAN                         | MTL_CATEGORIES_B_U1          |       1 |     1 |           |        |       |          |       |       |       |          |                              |
                          | 16 |    TABLE ACCESS BY INDEX ROWID                | MTL_CATEGORIES_B             |       1 |     2 |           |        |       |          |       |       |       |          |                              |
                          | 17 |   HASH JOIN RIGHT OUTER                       |                              |    236K | 54834 |      1247 |     +9 |     1 |    55629 |       |       |  913K |          |                              |
                          | 18 |    TABLE ACCESS BY INDEX ROWID BATCHED        | FND_LOOKUP_VALUES            |      27 |     5 |         1 |     +9 |     1 |        7 |       |       |       |          |                              |
                          | 19 |     INDEX RANGE SCAN                          | FND_LOOKUP_VALUES_U1         |      27 |     2 |         1 |     +9 |     1 |        7 |       |       |       |          |                              |
                          | 20 |    FILTER                                     |                              |         |       |      1247 |     +9 |     1 |    55629 |       |       |       |          |                              |
                          | 21 |     HASH JOIN RIGHT OUTER                     |                              |    236K | 54826 |      1247 |     +9 |     1 |     511K |       |       |    3M |     0.08 | Cpu (1)                      |
                          | 22 |      VIEW                                     |                              |   29898 |  4291 |         1 |     +9 |     1 |    45567 |       |       |       |          |                              |
                          | 23 |       HASH GROUP BY                           |                              |   29898 |  4291 |         1 |     +9 |     1 |    45567 |       |       |    3M |          |                              |
                          | 24 |        TABLE ACCESS FULL                      | MTL_ONHAND_QUANTITIES_DETAIL |   47243 |  4286 |         1 |     +9 |     1 |     111K |       |       |       |          |                              |
                          | 25 |      HASH JOIN RIGHT OUTER                    |                              |    236K | 50532 |      1247 |     +9 |     1 |     511K |       |       |    1M |     0.16 | Cpu (2)                      |
                          | 26 |       VIEW                                    | MTL_MATERIAL_STATUSES_VL     |       9 |     4 |         1 |     +9 |     1 |        9 |       |       |       |          |                              |
                          | 27 |        NESTED LOOPS                           |                              |       9 |     4 |         1 |     +9 |     1 |        9 |       |       |       |          |                              |
                          | 28 |         TABLE ACCESS BY INDEX ROWID BATCHED   | MTL_MATERIAL_STATUSES_TL     |       9 |     3 |         1 |     +9 |     1 |        9 |       |       |       |          |                              |
                          | 29 |          INDEX SKIP SCAN                      | MTL_MATERIAL_STATUSES_TL_PK  |       9 |     1 |         1 |     +9 |     1 |        9 |       |       |       |          |                              |
                          | 30 |         INDEX UNIQUE SCAN                     | MTL_MATERIAL_STATUSES_B_PK   |       1 |     1 |      1247 |     +9 |     9 |        9 |       |       |       |          |                              |
                          | 31 |       HASH JOIN RIGHT OUTER                   |                              |    236K | 50525 |      1255 |     +1 |     1 |     511K |       |       |   46M |     0.08 | Cpu (1)                      |
                          | 32 |        TABLE ACCESS FULL                      | MTL_ITEM_LOCATIONS           |    453K |  4478 |         1 |     +9 |     1 |     449K |       |       |       |          |                              |
                          | 33 |        HASH JOIN RIGHT OUTER                  |                              |    236K | 42369 |      1247 |     +9 |     1 |     511K |       |       |    6M |     0.16 | Cpu (2)                      |
                          | 34 |         VIEW                                  |                              |   47243 |  4722 |         1 |     +9 |     1 |    54398 |       |       |       |          |                              |
                          | 35 |          HASH GROUP BY                        |                              |   47243 |  4722 |         1 |     +9 |     1 |    54398 |       |       |    5M |          |                              |
                          | 36 |           TABLE ACCESS FULL                   | MTL_ONHAND_QUANTITIES_DETAIL |   47243 |  4286 |         1 |     +9 |     1 |     111K |       |       |       |          |                              |
                          | 37 |         HASH JOIN OUTER                       |                              |    216K | 35959 |      1247 |     +9 |     1 |     502K |       |       |   75M |          |                              |
                          | 38 |          NESTED LOOPS                         |                              |    216K | 25852 |         1 |     +9 |     1 |     502K |       |       |       |          |                              |
                          | 39 |           TABLE ACCESS BY INDEX ROWID         | MTL_PARAMETERS               |       1 |     1 |         1 |     +9 |     1 |        1 |       |       |       |          |                              |
                          | 40 |            INDEX UNIQUE SCAN                  | MTL_PARAMETERS_U1            |       1 |     1 |         1 |     +9 |     1 |        1 |       |       |       |          |                              |
                          | 41 |           TABLE ACCESS BY INDEX ROWID BATCHED | MTL_SYSTEM_ITEMS_B           |    216K | 25851 |         8 |     +2 |     1 |     502K |       |       |       |     0.16 | Cpu (2)                      |
                          | 42 |            INDEX SKIP SCAN                    | MTL_SYSTEM_ITEMS_B_N15       |    216K |   589 |         1 |     +9 |     1 |     502K |       |       |       |          |                              |
                          | 43 |          VIEW                                 |                              |    318K |  8119 |      1158 |     +9 |     1 |     268K |       |       |       |          |                              |
                          | 44 |           HASH JOIN                           |                              |    318K |  8119 |      1158 |     +9 |     1 |     268K |       |       |    1M |          |                              |
                          | 45 |            TABLE ACCESS FULL                  | MTL_ABC_CLASSES              |      29 |     5 |         1 |     +9 |     1 |       29 |       |       |       |          |                              |
                          | 46 |            NESTED LOOPS                       |                              |    318K |  8110 |      1158 |     +9 |     1 |     268K |       |       |       |          |                              |
                          | 47 |             TABLE ACCESS BY INDEX ROWID       | MTL_ABC_ASSIGNMENT_GROUPS    |       1 |     1 |         1 |     +9 |     1 |        1 |       |       |       |          |                              |
                          | 48 |              INDEX UNIQUE SCAN                | MTL_ABC_ASSIGNMENT_GROUPS_U2 |       1 |     1 |      1157 |     +9 |     1 |        1 |       |       |       |          |                              |
                          | 49 |             TABLE ACCESS FULL                 | MTL_ABC_ASSIGNMENTS          |    318K |  8109 |      1163 |     +4 |     1 |     268K |   487 |  56MB |       |     0.49 | Cpu (1)                      |
                          |    |                                               |                              |         |       |           |        |       |          |       |       |       |          | db file scattered read (5)   |
                          =================================================================================================================================================================================================================
                          
                          
                          
                          
                          
                          
                          SQL_ID  8xzw776f122qk, child number 0
                          -------------------------------------
                          
                          
                          apps.mtl_onhand_quantities m
                          
                          Plan hash value: 3224886758
                          
                          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          | Id  | Pid | Ord | Operation                                   | Name                         | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | A-Time Self |Bufs Self |Reads Self|Write Self|A-Ti S-Graph |Bufs S-Graph |Reads S-Graph|Write S-Graph|LIO Ratio |TCF Graph |E-Rows*Sta|
                          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          |   0 |     |  50 | SELECT STATEMENT                            |                              |       |       |       |  2975K(100)|          | 00:00:00.00 |        0 |        0 |        0 |             |             |             |             |        0 |          |          |
                          |   1 |   0 |   5 |  NESTED LOOPS                               |                              |     1 |    51 |       |     5   (0)| 00:00:01 | 00:00:00.05 |        0 |        0 |        0 |             |             |             |             |          |          |    46838 |
                          |   2 |   1 |   3 |   NESTED LOOPS                              |                              |     1 |    51 |       |     5   (0)| 00:00:01 | 00:00:00.05 |        0 |        0 |        0 |             |             |             |             |          |          |    46838 |
                          |*  3 |   2 |   1 |    INDEX RANGE SCAN                         | MTL_ITEM_CATEGORIES_U1       |     1 |    22 |       |     3   (0)| 00:00:01 | 00:00:39.88 |      142K|    27894 |        0 | @           |             | @           |             |          |          |    46838 |
                          |*  4 |   2 |   2 |    INDEX UNIQUE SCAN                        | MTL_CATEGORIES_B_U1          |     1 |       |       |     1   (0)| 00:00:01 | 00:00:00.00 |        0 |        0 |        0 |             |             |             |             |          |          |        0 |
                          |   5 |   1 |   4 |   TABLE ACCESS BY INDEX ROWID               | MTL_CATEGORIES_B             |     1 |    29 |       |     2   (0)| 00:00:01 | 00:00:00.00 |        0 |        0 |        0 |             |             |             |             |          |          |        0 |
                          |   6 |   0 |  11 |  NESTED LOOPS                               |                              |     1 |    36 |       |     3   (0)| 00:00:01 | 00:00:00.09 |        0 |        0 |        0 |             |             |             |             |        0 |          |    46838 |
                          |   7 |   6 |   9 |   NESTED LOOPS                              |                              |     1 |    36 |       |     3   (0)| 00:00:01 | 00:00:00.07 |        0 |        0 |        0 |             |             |             |             |        0 |          |    46838 |
                          |   8 |   7 |   7 |    TABLE ACCESS BY INDEX ROWID BATCHED      | CST_COST_TYPES               |     1 |    19 |       |     2   (0)| 00:00:01 | 00:00:00.12 |     1113 |        0 |        0 |             |             |             |             |        0 |          |    46838 |
                          |*  9 |   8 |   6 |     INDEX RANGE SCAN                        | CST_COST_TYPES_U2            |     1 |       |       |     1   (0)| 00:00:01 | 00:00:00.18 |     1117 |        0 |        0 |             |             |             |             |        0 |          |    46838 |
                          |* 10 |   7 |   8 |    INDEX UNIQUE SCAN                        | CST_ITEM_COSTS_U1            |     1 |       |       |     1   (0)| 00:00:01 | 00:00:12.68 |    94788 |     9258 |        0 |             |             |             |             |        2 |          |    46838 |
                          |  11 |   6 |  10 |   TABLE ACCESS BY INDEX ROWID               | CST_ITEM_COSTS               |     1 |    17 |       |     2   (0)| 00:00:01 | 00:00:05.88 |    72377 |     4161 |        0 |             |             |             |             |        1 |          |    46835 |
                          |  12 |   0 |  16 |  NESTED LOOPS                               |                              |     1 |    51 |       |     5   (0)| 00:00:01 | 00:00:00.06 |        0 |        0 |        0 |             |             |             |             |          |          |    46838 |
                          |  13 |  12 |  14 |   NESTED LOOPS                              |                              |     1 |    51 |       |     5   (0)| 00:00:01 | 00:00:00.05 |        0 |        0 |        0 |             |             |             |             |          |          |    46838 |
                          |* 14 |  13 |  12 |    INDEX RANGE SCAN                         | MTL_ITEM_CATEGORIES_U1       |     1 |    22 |       |     3   (0)| 00:00:01 | 00:00:00.51 |      142K|        0 |        0 |             |             |             |             |          |          |    46838 |
                          |* 15 |  13 |  13 |    INDEX UNIQUE SCAN                        | MTL_CATEGORIES_B_U1          |     1 |       |       |     1   (0)| 00:00:01 | 00:00:00.00 |        0 |        0 |        0 |             |             |             |             |          |          |        0 |
                          |  16 |  12 |  15 |   TABLE ACCESS BY INDEX ROWID               | MTL_CATEGORIES_B             |     1 |    29 |       |     2   (0)| 00:00:01 | 00:00:00.00 |        0 |        0 |        0 |             |             |             |             |          |          |        0 |
                          |* 17 |   0 |  49 |  HASH JOIN RIGHT OUTER                      |                              |   236K|    80M|       | 54834   (2)| 00:00:03 | 00:00:00.18 |        0 |        0 |        0 |             |             |             |             |        0 |          |      472K|
                          |  18 |  17 |  18 |   TABLE ACCESS BY INDEX ROWID BATCHED       | FND_LOOKUP_VALUES            |    27 |  1647 |       |     5   (0)| 00:00:01 | 00:00:00.00 |        2 |        0 |        0 |             |             |             |             |        0 |          |       54 |
                          |* 19 |  18 |  17 |    INDEX RANGE SCAN                         | FND_LOOKUP_VALUES_U1         |    27 |       |       |     2   (0)| 00:00:01 | 00:00:00.00 |        8 |        1 |        0 |             |             |             |             |        0 |          |       54 |
                          |* 20 |  17 |  48 |   FILTER                                    |                              |       |       |       |            |          | 00:08:43.46 |      137M|      226K|        0 | @@@@@@@@@@@@| @@@@@@@@@@@@| @@@@@@@@@@  |             |     2458 |          |          |
                          |* 21 |  20 |  47 |    HASH JOIN RIGHT OUTER                    |                              |   236K|    67M|       | 54826   (2)| 00:00:03 | 00:00:00.94 |        0 |        0 |        0 |             |             |             |             |        0 |          |      472K|
                          |  22 |  21 |  21 |     VIEW                                    |                              | 29898 |   846K|       |  4291   (3)| 00:00:01 | 00:00:00.02 |        0 |        0 |        0 |             |             |             |             |        0 |          |    59796 |
                          |  23 |  22 |  20 |      HASH GROUP BY                          |                              | 29898 |   496K|       |  4291   (3)| 00:00:01 | 00:00:00.10 |        0 |        0 |        0 |             |             |             |             |        0 |          |    59796 |
                          |* 24 |  23 |  19 |       TABLE ACCESS FULL                     | MTL_ONHAND_QUANTITIES_DETAIL | 47243 |   784K|       |  4286   (3)| 00:00:01 | 00:00:00.21 |    31203 |        0 |        0 |             |             |             |             |        0 |          |    94486 |
                          |* 25 |  21 |  46 |     HASH JOIN RIGHT OUTER                   |                              |   236K|    60M|       | 50532   (2)| 00:00:02 | 00:00:00.44 |        0 |        0 |        0 |             |             |             |             |        0 |          |      472K|
                          |  26 |  25 |  26 |      VIEW                                   | MTL_MATERIAL_STATUSES_VL     |     9 |   495 |       |     4   (0)| 00:00:01 | 00:00:00.00 |        0 |        0 |        0 |             |             |             |             |        0 |          |       18 |
                          |  27 |  26 |  25 |       NESTED LOOPS                          |                              |     9 |   333 |       |     4   (0)| 00:00:01 | 00:00:00.00 |        0 |        0 |        0 |             |             |             |             |        0 |          |       18 |
                          |  28 |  27 |  23 |        TABLE ACCESS BY INDEX ROWID BATCHED  | MTL_MATERIAL_STATUSES_TL     |     9 |   225 |       |     3   (0)| 00:00:01 | 00:00:00.00 |        6 |        0 |        0 |             |             |             |             |        0 |          |       18 |
                          |* 29 |  28 |  22 |         INDEX SKIP SCAN                     | MTL_MATERIAL_STATUSES_TL_PK  |     9 |       |       |     1   (0)| 00:00:01 | 00:00:00.00 |        2 |        0 |        0 |             |             |             |             |        0 |          |       18 |
                          |* 30 |  27 |  24 |        INDEX UNIQUE SCAN                    | MTL_MATERIAL_STATUSES_B_PK   |     1 |    12 |       |     1   (0)| 00:00:01 | 00:00:00.00 |        8 |        0 |        0 |             |             |             |             |        0 |          |       18 |
                          |* 31 |  25 |  45 |      HASH JOIN RIGHT OUTER                  |                              |   236K|    48M|    22M| 50525   (2)| 00:00:02 | 00:00:01.37 |        0 |     1477 |        0 |             |             |             |             |        0 |          |      472K|
                          |  32 |  31 |  27 |       TABLE ACCESS FULL                     | MTL_ITEM_LOCATIONS           |   452K|    17M|       |  4478   (3)| 00:00:01 | 00:00:00.46 |    32491 |        0 |        0 |             |             |             |             |        0 |          |      905K|
                          |* 33 |  31 |  44 |       HASH JOIN RIGHT OUTER                 |                              |   236K|    39M|  3232K| 42369   (2)| 00:00:02 | 00:00:00.00 |        0 |        0 |        0 |             |             |             |             |        0 |          |      472K|
                          |  34 |  33 |  30 |        VIEW                                 |                              | 47243 |  2675K|       |  4722   (2)| 00:00:01 | 00:00:00.02 |        0 |        0 |        0 |             |             |             |             |        0 |          |    94486 |
                          |  35 |  34 |  29 |         HASH GROUP BY                       |                              | 47243 |  1568K|  2416K|  4722   (2)| 00:00:01 | 00:00:00.14 |        0 |        0 |        0 |             |             |             |             |        0 |          |    94486 |
                          |* 36 |  35 |  28 |          TABLE ACCESS FULL                  | MTL_ONHAND_QUANTITIES_DETAIL | 47243 |  1568K|       |  4286   (3)| 00:00:01 | 00:00:00.20 |    31198 |        0 |        0 |             |             |             |             |        0 |          |    94486 |
                          |* 37 |  33 |  43 |        HASH JOIN OUTER                      |                              |   215K|    23M|    16M| 35959   (1)| 00:00:02 | 00:00:02.31 |      798 |        0 |     4216 |             |             |             | @@@@@@@@@@@@|        0 |          |      432K|
                          |  38 |  37 |  35 |         NESTED LOOPS                        |                              |   215K|    14M|       | 25852   (1)| 00:00:02 | 00:00:00.17 |        0 |        0 |        0 |             |             |             |             |        0 |          |      432K|
                          |  39 |  38 |  32 |          TABLE ACCESS BY INDEX ROWID        | MTL_PARAMETERS               |     1 |     7 |       |     1   (0)| 00:00:01 | 00:00:00.00 |        2 |        0 |        0 |             |             |             |             |        1 |          |        2 |
                          |* 40 |  39 |  31 |           INDEX UNIQUE SCAN                 | MTL_PARAMETERS_U1            |     1 |       |       |     1   (0)| 00:00:01 | 00:00:00.00 |        2 |        0 |        0 |             |             |             |             |        1 |          |        2 |
                          |  41 |  38 |  34 |          TABLE ACCESS BY INDEX ROWID BATCHED| MTL_SYSTEM_ITEMS_B           |   215K|    12M|       | 25851   (1)| 00:00:02 | 00:00:02.75 |     1151K|        0 |        0 |             |             |             |             |        1 |          |      432K|
                          |* 42 |  41 |  33 |           INDEX SKIP SCAN                   | MTL_SYSTEM_ITEMS_B_N15       |   215K|       |       |   589   (2)| 00:00:01 | 00:00:01.83 |     3608 |     1804 |        0 |             |             |             |             |        0 |          |      432K|
                          |  43 |  37 |  42 |         VIEW                                |                              |   318K|    14M|       |  8119   (3)| 00:00:01 | 00:00:00.12 |        0 |        0 |        0 |             |             |             |             |        0 |          |      636K|
                          |* 44 |  43 |  41 |          HASH JOIN                          |                              |   318K|    12M|       |  8119   (3)| 00:00:01 | 00:00:00.31 |        0 |        0 |        0 |             |             |             |             |        0 |          |      636K|
                          |  45 |  44 |  36 |           TABLE ACCESS FULL                 | MTL_ABC_CLASSES              |    29 |   145 |       |     5   (0)| 00:00:01 | 00:00:00.00 |       30 |       13 |        0 |             |             |             |             |        0 |          |       58 |
                          |  46 |  44 |  40 |           NESTED LOOPS                      |                              |   318K|    10M|       |  8110   (3)| 00:00:01 | 00:00:00.09 |        0 |        0 |        0 |             |             |             |             |        0 |          |      636K|
                          |  47 |  46 |  38 |            TABLE ACCESS BY INDEX ROWID      | MTL_ABC_ASSIGNMENT_GROUPS    |     1 |    23 |       |     1   (0)| 00:00:01 | 00:00:00.00 |        2 |        0 |        0 |             |             |             |             |        1 |          |        2 |
                          |* 48 |  47 |  37 |             INDEX UNIQUE SCAN               | MTL_ABC_ASSIGNMENT_GROUPS_U2 |     1 |       |       |     1   (0)| 00:00:01 | 00:00:00.00 |        2 |        0 |        0 |             |             |             |             |        1 |          |        2 |
                          |* 49 |  46 |  39 |            TABLE ACCESS FULL                | MTL_ABC_ASSIGNMENTS          |   318K|  4038K|       |  8109   (3)| 00:00:01 | 00:00:07.94 |    52775 |    29296 |        0 |             |             | @           |             |        0 |          |      636K|
                          ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                          
                          
                          • 10. Re: Slow Query
                            Jonathan Lewis

                            I suggest the next test you do is to remove the scalar subqueries from the select list to see how much impact they are having on the run time. To make sure that Oracle doesn't change some other part of the plan as you do this make sure you include in the select list any columns used in correlated predicates in the subqueries. E.g.  if one of the scalar subqueries is "select t2.c2 from t2 where t2.c1 = big_query.c1" (where big_query.c1 is a column from the big join that's driiving the query) then make sure that big_query.c1 is in the select list when you do the test.

                             

                            If that shows no significant change in workload then find the definitions for those pl/sql functions, see if they are running SQL (I think they must be), and search v$sql for the relevant statements, to see if they are contributing the large amounts of time.

                             

                            Regards

                            Jonathan Lewis

                            • 11. Re: Slow Query
                              Randolf Geist

                              Jonathan Lewis wrote:

                               

                              @randolf

                               

                              It did cross my mind that maybe the lost time is due to the filter predicate at operation 35

                               

                              NVL("ONH"."ONHAND",0) +

                              "XXOF_WRHS_INQ"."RESERVED_BKORDR" ("MSIB"."INVENTORY_ITEM_ID","MSIB"."ORGANIZATION_ID") +

                              "XXOF_WRHS_INQ"."PURCHASE_NORMAL" ("MSIB"."INVENTORY_ITEM_ID","MSIB"."ORGANIZATION_ID") + 0 +

                              "XXOF_WRHS_INQ"."PURCHASE_EXPEDITED"("MSIB"."INVENTORY_ITEM_ID","MSIB"."ORGANIZATION_ID") + 0 > 0

                               

                              It's 3 functions rather than the 2 I first thought - but if they call SQL maybe the code that raids ASH (in this version of Oracle) loses site of the SQL activity. Maybe the ASH part of the code is looking for at SQL_ID rather than the TOP_SQL_ID (columns names may be wrong - I don't have a database in front of me to check them).

                               

                              I may do a quick test when I next have access to a DB.

                               

                              Regards

                              Jonathan Lewis

                              Jonathan,

                               

                              that's a good point and generally speaking sometimes ASH will show the SQL_ID of recursive queries (and sometimes not), so that might be the most obvious explanation for this particular case here why that is missing on execution plan line level but is included on global summary level (because Real-Time SQL Monitoring is clever enough to include that information by using the session identifier as relevant filter criteria for identifying corresponding ASH activity).

                               

                              Kind regards,

                              Randolf

                              • 12. Re: Slow Query
                                Randolf Geist

                                Interesting - the latter output looks suspiciously familiar to me - one of my scripts that extend / enrich DBMS_XPLAN.DISPLAY_CURSOR output (but it looks like some of the default columns like "A-Rows, A-Time" etc. are missing, did you remove them deliberately from the output?).

                                 

                                It looks like Jonathan was spot on - the majority of time is spent in the FILTER operation (almost 9 minutes) which also is responsible for the majority of logical and physical I/O. Clearly those PL/SQL functions are causing a lot of work - you would have to focus on them first. And the scalar subqueries are responsible for another 60 seconds - so these are then the next ones to tackle.

                                 

                                Kind regards,

                                Randolf

                                • 13. Re: Slow Query
                                  2980262

                                  yes sir, its your script and instead of all stats last i used advanced in parameter.

                                  • 14. Re: Slow Query
                                    2980262

                                    Hi Jonathan,

                                    Thank you for all your inputs. I am working on the suggestions but in mean time I tried below and PLSQL function looks no issue here. It looks like these functions are applied on whole result set instead of where these should get applied.

                                     

                                    I tried running below and it got completed in 86 seconds. Please have a look and suggest if we can restrict these function in their actual inline view i.e. tb in my case.

                                     

                                    SELECT /*+ gather_plan_statistics */ * FROM (SELECT msib.inventory_item_id
                                           , msib.organization_id
                                           , msib.segment1           item
                                           , msib.description        item_description
                                           , nvl(onh.onhand, 0) on_hand
                                           , mp1.organization_code   org
                                           , primary_uom_code        uom
                                           , ( msib.dimension_uom_code
                                               || '-'
                                               || msib.unit_length
                                               || '-'
                                               || msib.unit_width
                                               || '-'
                                               || msib.unit_height ) size_uom_lwh
                                           , msib.unit_weight        weight
                                           , msib.weight_uom_code    weight_uom
                                           , (SELECT xxof_wrhs_inq.reserved_bkordr(msib.inventory_item_id, msib.organization_id) FROM DUAL) reserve_bkord
                                           , (SELECT xxof_wrhs_inq.purchase_normal(msib.inventory_item_id, msib.organization_id) FROM DUAL) po_normal
                                           , (SELECT xxof_wrhs_inq.purchase_expedited(msib.inventory_item_id, msib.organization_id) FROM DUAL)on_order_exp
                                           , 0 int_vendor
                                           , 0 intransit_iso
                                    FROM apps.mtl_system_items_b   msib
                                         , apps.mtl_parameters       mp1
                                         , LATERAL ( SELECT mq.inventory_item_id
                                                            , mq.organization_id
                                                            , SUM(mq.transaction_quantity) onhand
                                                FROM apps.mtl_onhand_quantities mq
                                                WHERE msib.inventory_item_id = mq.inventory_item_id 
                                                      AND msib.organization_id = mq.organization_id 
                                                GROUP BY mq.inventory_item_id
                                                         , mq.organization_id
                                                   ) onh
                                    WHERE mp1.organization_id = msib.organization_id
                                          AND msib.organization_id = 86    )
                                          WHERE nvl(on_hand, 0) + reserve_bkord + po_normal + int_vendor + on_order_exp + intransit_iso > 0
                                    
                                    
                                    Plan hash value: 3817285129
                                    
                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                    | Id  | Operation                             | Name                         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                    |   0 | SELECT STATEMENT                      |                              |      1 |        |  45598 |00:01:07.94 |      46M|   4523 |       |       |          |
                                    |   1 |  FAST DUAL                            |                              |  45598 |      1 |  45598 |00:00:00.02 |       0 |      0 |       |       |          |
                                    |   2 |  FAST DUAL                            |                              |  45598 |      1 |  45598 |00:00:00.02 |       0 |      0 |       |       |          |
                                    |   3 |  FAST DUAL                            |                              |  45598 |      1 |  45598 |00:00:00.02 |       0 |      0 |       |       |          |
                                    |*  4 |  VIEW                                 |                              |      1 |  34740 |  45598 |00:01:07.94 |      46M|   4523 |       |       |          |
                                    |*  5 |   HASH JOIN                           |                              |      1 |  34740 |  45598 |00:00:01.88 |     596K|    322 |  3631K|  1761K| 3962K (0)|
                                    |   6 |    NESTED LOOPS                       |                              |      1 |  29898 |  45598 |00:00:00.23 |   15666 |      0 |       |       |          |
                                    |   7 |     TABLE ACCESS BY INDEX ROWID       | MTL_PARAMETERS               |      1 |      1 |      1 |00:00:00.01 |       2 |      0 |       |       |          |
                                    |*  8 |      INDEX UNIQUE SCAN                | MTL_PARAMETERS_U1            |      1 |      1 |      1 |00:00:00.01 |       1 |      0 |       |       |          |
                                    |   9 |     VIEW                              | VW_DCL_2131DCCF              |      1 |  29898 |  45598 |00:00:00.23 |   15664 |      0 |       |       |          |
                                    |  10 |      SORT GROUP BY                    |                              |      1 |  29898 |  45598 |00:00:00.22 |   15664 |      0 |  2746K|  1039K| 2440K (0)|
                                    |* 11 |       TABLE ACCESS FULL               | MTL_ONHAND_QUANTITIES_DETAIL |      1 |  47243 |    112K|00:00:00.12 |   15664 |      0 |       |       |          |
                                    |  12 |    TABLE ACCESS BY INDEX ROWID BATCHED| MTL_SYSTEM_ITEMS_B           |      1 |    215K|    501K|00:00:01.81 |     580K|    322 |       |       |          |
                                    |* 13 |     INDEX SKIP SCAN                   | MTL_SYSTEM_ITEMS_B_N15       |      1 |    215K|    501K|00:00:00.34 |    4534 |      1 |       |       |          |
                                    --------------------------------------------------------------------------------------------------------------------------------------------------------------------
                                    
                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------
                                    
                                       4 - filter(NVL("ON_HAND",0)+"RESERVE_BKORD"+"PO_NORMAL"+"INT_VENDOR"+"ON_ORDER_EXP"+"INTRANSIT_ISO">0)
                                       5 - access("MP1"."ORGANIZATION_ID"="MSIB"."ORGANIZATION_ID" AND "MSIB"."INVENTORY_ITEM_ID"="INVENTORY_ITEM_ID" AND 
                                                  "MSIB"."ORGANIZATION_ID"="ORGANIZATION_ID")
                                       8 - access("MP1"."ORGANIZATION_ID"=86)
                                      11 - filter(("MOQD"."ORGANIZATION_ID"=86 AND "MOQD"."IS_CONSIGNED"=2))
                                      13 - access("MSIB"."ORGANIZATION_ID"=86)
                                           filter("MSIB"."ORGANIZATION_ID"=86)
                                    
                                    
                                    
                                    
                                    1 2 Previous Next