Hello All,
I am a suave in the oracle SQL tuning, Could you please help me fine tune the below query, please find the details which have been requested in other threads, right now this particular sql is running for 13 mins and it causing issues during our data load through ETL it would be great if we could reduce this to the maximum, appreciate your time and help on this.
Database Version:
Oracle 10g -- NLSRTL version 10.2.0.3.0
Below is the query:
SELECT
SUM(T5.ITEM_COST) as ITEM_COST,
TRIM(T1.ORGANIZATION_ID) as ORGANIZATION_ID,
TRIM(T1.INVENTORY_ITEM_ID) as INVENTORY_ITEM_ID
FROM
MTL_SYSTEM_ITEMS_B T1,
BOM_BILL_OF_MATERIALS T3,
BOM_INVENTORY_COMPONENTS T4,
CST_ITEM_COSTS T5,
MTL_SYSTEM_ITEMS_B T6
WHERE
T1.ITEM_TYPE = 'ATO ITEM' AND
T1.ORGANIZATION_ID = T3.ORGANIZATION_ID AND
T1.INVENTORY_ITEM_ID = T3.ASSEMBLY_ITEM_ID AND
T3.BILL_SEQUENCE_ID = T4.BILL_SEQUENCE_ID AND
T4.COMPONENT_ITEM_ID = T5.INVENTORY_ITEM_ID AND
T3.ORGANIZATION_ID = T5.ORGANIZATION_ID AND
T5.COST_TYPE_ID = 1062 AND
T4.COMPONENT_ITEM_ID = T6.INVENTORY_ITEM_ID AND
T6.ORGANIZATION_ID = 85 AND
NVL(T6.ITEM_TYPE, 'Unpecified') NOT IN ('ATO MODEL','ATO OPTION CLASS')
GROUP BY
T1.ORGANIZATION_ID,
T1.INVENTORY_ITEM_ID;
Explain Plan:
OPERATION OPTIONS OBJECT_NAME
SELECT STATEMENT
HASH GROUP BY
NESTED LOOPS
HASH JOIN
TABLE ACCESS BY INDEX ROWID BOM_COMPONENTS_B
NESTED LOOPS
HASH JOIN
TABLE ACCESS FULL MTL_SYSTEM_ITEMS_B
TABLE ACCESS FULL BOM_STRUCTURES_B
INDEX RANGE SCAN BOM_COMPONENTS_B_N2
TABLE ACCESS BY INDEX ROWID CST_ITEM_COSTS
INDEX SKIP SCAN CST_ITEM_COSTS_N2
TABLE ACCESS BY INDEX ROWID MTL_SYSTEM_ITEMS_B
INDEX UNIQUE SCAN MTL_SYSTEM_ITEMS_B_U1
These are the parameters relevant to the optimizer:
SQL> show parameter optimizer
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
optimizer_dynamic_sampling integer 2
optimizer_features_enable string 10.2.0.3
optimizer_index_caching integer 0
optimizer_index_cost_adj integer 100
optimizer_mode string ALL_ROWS
optimizer_secure_view_merging boolean FALSE
SQL> show parameter db_file_multi
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 8
SQL> show parameter db_block_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_block_size integer 8192
SQL> show parameter cursor_sharing
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cursor_sharing string EXACT
I cannot able to provice TKPROF information since i don't have access to that file, SQLPLUS Auto tracing is throwing some error, same is the issue with the DBMS_PLAN.XPLAN_CURSOR. please let me know if you need any more information
Thanks & Regards,
Sam.