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 位用户发现它有用
                • 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 上一个 下一个