Tuning The Sql Query

[orasolve]

Hi,

This is Bhargav and I am trying to tune this query that is being used in a discoverer report and the columns in the select statement are the ones that are to be displayed in the discoverer output.

Can somebody help me as top how to tune this query. Any sort of help is greatly appreciated , as this is of high priority.

Regards,

Bhargav.

select
  cost_orgs.organization_code || ' - ' || cost_orgs.organization_name cost_org,
  on_hand_orgs.organization_code || ' - ' || on_hand_orgs.organization_name on_hand_org,
  msi.segment1 item_name,
  msi.description item_desc,
  mcb.segment1 cost_cat_1,
  mcb.segment2 cost_cat_2,
  mcb.segment3 cost_cat_3,
  mcb.segment1 || '.' || mcb.segment2 || '.' || mcb.segment3 cost_category,
  upper(ctf1.cost_type) cost_type_1,
  cicf1.item_cost item_cost_1,
  cicf1.material_cost mtl_cost_1,
  upper(ctf2.cost_type) cost_type_2,
  cicf2.item_cost item_cost_2,
  cicf2.material_cost mtl_cost_2,
  sum(moh.transaction_quantity) on_hand_qty,
  decode(msi.planning_make_buy_code,1,'M','B') make_buy_flag
from
--  mtl_parameters mp,
  cst_item_costs cicf1,
  cst_cost_types ctf1,
  cst_item_costs cicf2,
  cst_cost_types ctf2,
  mtl_onhand_quantities moh,
  inv.mtl_categories_b mcb,
  inv.mtl_item_categories mic,
--  inv.mtl_category_sets_tl mcst,
  org_organization_definitions cost_orgs,
  org_organization_definitions on_hand_orgs,
  inv.mtl_system_items_b msi
where 1=1
and cost_orgs.organization_id = msi.organization_id
and cicf1.organization_id = msi.organization_id
and cicf1.inventory_item_id = msi.inventory_item_id
and ctf1.cost_type_id = cicf1.cost_type_id --this should be set to frozen
and cicf2.organization_id = msi.organization_id  --cross-product between sets will be resolved by Discoverer parameters
and cicf2.inventory_item_id = msi.inventory_item_id
and ctf2.cost_type_id = cicf2.cost_type_id
and moh.organization_id = on_hand_orgs.ORGANIZATION_ID
and moh.inventory_item_id = msi.inventory_item_id
and mic.organization_id (+) = msi.organization_id
and mic.inventory_item_id (+) = msi.inventory_item_id
and mic.category_set_id (+) = 1100000006 --inventory cost category
and mcb.category_id (+)= mic.category_id -- the inventory cost category consisists of mcb. segment1,2 and 3
--and mcst.CATEGORY_SET_ID = mic.CATEGORY_SET_ID
--and mcst.CATEGORY_SET_NAME = 'QC INVENTORY CATEGORY'
--and msi.inventory_item_id = 512
--and cicf1.COST_TYPE_ID = 1
--and cicf2.COST_TYPE_ID = 1101
--and msi.ORGANIZATION_ID = 89
group by
  cost_orgs.organization_code || ' - ' || cost_orgs.organization_name,
  on_hand_orgs.organization_code || ' - ' || on_hand_orgs.organization_name,
  msi.segment1,
  msi.description,
  mcb.segment1,
  mcb.segment2,
  mcb.segment3,
  mcb.segment1 || '.' || mcb.segment2 || '.' || mcb.segment3,
  upper(ctf1.cost_type),
  cicf1.item_cost,
  cicf1.material_cost,
  upper(ctf2.cost_type),
  cicf2.item_cost,
  cicf2.material_cost,
  decode(msi.planning_make_buy_code,1,'M','B')
 --order by msi.SEGMENT1

/

[amitpatel66]

Make use of HINTS in the query (HINT - INDEX,PARALLEL will be helpful)

[orasolve]

Hi Amit,

I am not so familiar with using Hints in the sql query.

Can you pls give a small example and tell me which hint to use in this scenario, where so many joins are there.

Will appreciate your response in the earliest possible time.

Thanks,

Bhargav.

[amitpatel66]

Hi Amit,

I am not so familiar with using Hints in the sql query.

Can you pls give a small example and tell me which hint to use in this scenario, where so many joins are there.

Will appreciate your response in the earliest possible time.

Thanks,

Bhargav.

Hi Bhargav,

You can make us of HINTS in your sql query to improve the performance of the Query.

There are many HINTS availabe with oracle that can be used but to make use of
the appropriate one will improve the performance.

See below example on making use of INDEX HINT in SQL Query:

SELECT /* INDEX(<table_alias>,<index_name>) */ FROM table_name table_alias
 

Find more about INDEX HINT here

Another HINT PARALLEL can be used for parallel execution of your Query.
See more about usage of PARALLEL HINT here