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 | |