Home » RDBMS Server » Performance Tuning » Query Fetching data within Two hrs (9.2.0.8)
Query Fetching data within Two hrs [message #440305] |
Fri, 22 January 2010 05:15 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
jaganerp@gmail.com
Messages: 63 Registered: April 2008
|
Member |
|
|
Hi friend,
Please help me i am getting problem in Query Performance Tuning,
This query is taking more than one Hour, please let me how to come out from this problem,it's very urgent for my self
----------------------------------------
/*select count(*) from XXINV.XXINV_MTL_MTRL_TRX_RECEIPTS
select * from xxcb_debug_messages -- where message like '%XXINV.XXINV_MTL_MTRL_TRX_RECEIPTS%'
ORDER BY USER_ID DESC--ATTRIBUTE1 DESC */
SELECT /*+ star_transformation*/ '' oper_unit,
wrs.country sold_from_country,
wrf.country ship_from_country,
rsh.organization_id ship_from_org_id,
mps.organization_code ship_from_warehouse,
'' ship_from_VAT,
wrt.country ship_to_country,
mtl.organization_id ship_to_org_id,
mpt.organization_code ship_to_warehouse,
'' ship_to_VAT,
msi.segment1 Item_code,
msi.description item_description,
msi.attribute4 country_of_origin,
substr(msi.attribute1, 1, 8) HSC_code_8_digits,
substr(msi.attribute1, 1, 10) HSC_code_10_digits,
substr(msi.attribute1, 1, 12) HSC_code_12_digits,
wdd.shipped_quantity transfer_quantity,
mtl.subinventory_code subinventory,
msi.attribute6 net_weight,
msi.weight_uom_code weight_UOM,
nvl(msi.unit_volume, 0) item_volume,
msi.volume_uom_code volume_uom,
cst.item_cost cost_price,
cs2.item_cost unit_price,
'' total_price,
rts.currency_code currency_code,
trunc(mtl.transaction_date) physical_trx_date,
to_char(mtl.transaction_date, 'MM-YYYY') physical_trx_month,
trx.trx_number invoice_number,
trx.trx_date invoice_date,
oha.order_number order_number,
wdd.source_line_number line_number,
'' mode_of_transport,
'' transaction_type,
'' l_id,
'' l_code,
'' bill_to_site_use_id,
'' ship_to_site_use_id,
'Truck' freight_terms_code,
'' intermed_ship_to,
mtl.transaction_id mtl_transaction_id,
'' interface_line_attr5,
'' interface_line_attr7,
'' operating_org_id,
'' sales_org_id
from inv.mtl_system_items_b msi,
inv.mtl_parameters mps,
inv.mtl_parameters mpt,
po.rcv_shipment_headers rsh,
po.rcv_shipment_lines rsl,
apps.hr_organization_units_v wrf, -- Ship from country
apps.hr_organization_units_v wrt, -- Ship to country
apps.hr_organization_units_v wrs, -- Sold from country
hr.hr_all_organization_units aou,
po.po_requisition_headers_all rha,
ont.oe_order_headers_all oha,
ar.ra_customer_trx_all trx,
ar.ra_customer_trx_lines_all trl,
ar.ra_cust_trx_types_all ctt,
cst_item_costs cst,
cst_item_costs cs2,
ar.hz_locations lcs,
wsh.wsh_delivery_details wdd,
ont.oe_order_lines_all ola,
po.po_requisition_lines_all rla,
po.rcv_transactions rts,
inv.mtl_material_transactions mtl
where mtl.transaction_type_id = 61 -- Int Req Intr Rcpt
and mtl.transaction_date between '01-DEC-2009' and '31-DEC-2009'
-- trunc(p_enddate) -- By Jagan 19-01-10
-- and mtl.transaction_date between to_date(to_char(p_startdate,'dd-mon-yyyy')||' 00:00:00','dd-mon-yyyy hh24:mi:ss')
-- and to_date(to_char(p_enddate,'dd-mon-yyyy') ||' 23:59:59','dd-mon-yyyy hh24:mi:ss') -- BY Jagan 19-01-10
-- and mtl.organization_id = nvl(p_wrhs_org_id, mtl.organization_id)
and mpt.organization_id = mtl.organization_id
and rts.transaction_id = mtl.rcv_transaction_id
and rla.requisition_line_id = rts.requisition_line_id
-- and ola.source_document_line_id = rla.requisition_line_id
and wdd.source_header_id = ola.header_id
and oha.header_id=oha.header_id+0
and wdd.source_line_id = ola.line_id
-- and rha.requisition_header_id = rla.requisition_header_id
and rha.org_id = 624--p_unit_org_id
and mtl.inventory_item_id = msi.inventory_item_id
and mtl.organization_id = msi.organization_id
and mtl.organization_id = wrt.organization_id
and rsh.organization_id = cst.organization_id
and mtl.inventory_item_id = cst.inventory_item_id
and mtl.organization_id = cs2.organization_id
and mtl.inventory_item_id = cs2.inventory_item_id
and rts.shipment_header_id = rsh.shipment_header_id
and rts.shipment_line_id = rsl.shipment_line_id
--and rha.requisition_header_id = oha.source_document_id
and oha.header_id = ola.header_id
and ola.sold_from_org_id = wrs.organization_id
-- and to_char(ola.line_id) = trl.interface_line_attribute6(+)
and trl.interface_line_attribute6=ola.line_id --by Jagan 22-01-10
and trl.sales_order_line(+) is not null
and trl.sales_order=oha.order_number
--and trl.customer_trx_id = trx.customer_trx_id(+)
and trx.customer_trx_id=trl.customer_trx_id -- By jagan 22-01-10
--and ctt.cust_trx_type_id(+) = trx.cust_trx_type_id
and trx.cust_trx_type_id=ctt.cust_trx_type_id -- By jagan 22-01-10
--and ctt.org_id(+) = trx.org_id
and trx.org_id=oha.org_id -- By jagan 22-01-10
and rsh.organization_id = wrf.organization_id
and rsh.organization_id = mps.organization_id
and aou.organization_id = oha.org_id
--and lcs.location_id(+) = aou.location_id
and wrf.country <> wrt.country
and wdd.delivery_detail_id = --- 1.5
(SELECT MAX(wdd1.delivery_detail_id) --- 1.5
FROM wsh.wsh_delivery_details wdd1 --- 1.5
WHERE wdd1.source_header_id = ola.header_id --- 1.5
AND wdd1.source_line_id = ola.line_id) --- 1.5
and oha.order_source_id = 10
and ctt.cust_trx_type_id=51341
and cst.cost_type_id = 1
and cs2.cost_type_id = 1
order by item_code, Physical_trx_date, Order_number, Line_number;
--------------------
Regards
Jagan
jaganerp@gmail.com[/email]
Aim:jaganpatni
EDITED by CM: added code tags, please do so yourself next time, see the forum guide if you're not sure how.
[Updated on: Fri, 22 January 2010 05:53] by Moderator Report message to a moderator
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Aug 23 21:03:56 CDT 2024
|