Home » RDBMS Server » Performance Tuning » Query taking too long to run
Query taking too long to run [message #64941] Fri, 12 March 2004 07:21 Go to next message
avinash
Messages: 22
Registered: February 2001
Junior Member
Need help to optimize this query.
Running very slowly - not returning at times.
working queue table has indexes on line_status and allotted user.

The explain plan shows a full scan on this table.
the oracle database version is Oracle7 Server Release 7.3.4.5.0

SELECT /*+CHOOSE*/
working_queue.priority_level,
working_queue.order_id,
working_queue.order_line,
order.place_id_ship_to ship_to_id,
order.place_id_to_bill bill_to_id,
order.user_def_2 contract_num,
order.order_id_parent case_num,
order.customs_id cust_ref,
order.po,
order.cross_reference,
order_line.item_id_to_rcv,
item.product_name,
order_line.return_reason,
bill_place.name bill_to_customer,
ship_place.name ship_to_customer,
ship_place.country ship_to_country,
ship_place.state_prov ship_to_state,
DECODE(order_line.allow_adv_replace,
'Y',(order_line.qty_shipped-order_line.qty_received),
'N',(order_line.qty_authorized - order_line.qty_received),
(order_line.qty_authorized- order_line.qty_received))qtydue,
DECODE(to_char(order_LINE.SCHEDULE_DUE_DT,'MM/DD/YYYY'), '12/31/2088', 'TBD', NULL, to_char(order_LINE.CREATED_DT + order_LINE.DAYS_DUE_FRM_CUST, 'MM/DD/YY'), to_char(order_LINE.SCHEDULE_DUE_DT, 'MM/DD/YY') ) DATE_DUE,
-- (trunc(sysdate) - trunc(order.created_dt))age,
trunc(SYSDATE) - NVL(trunc(order_line.SCHEDULE_SHIP_DT),
DECODE(TO_CHAR(order_line.schedule_due_dt,'YYYYMMDD'), '20881231',trunc(SYSDATE),
trunc(order_LINE.CREATED_DT)) ) AGE,
((DECODE(order_line.allow_adv_replace,
'Y',(order_line.qty_shipped-order_line.qty_received),
'N',(order_line.qty_authorized - order_line.qty_received),
(order_line.qty_authorized- order_line.qty_received)))*
(order_line.price_of_credit))extcredit,
((DECODE(order_line.allow_adv_replace,
'Y',(order_line.qty_shipped-order_line.qty_received),
'N',(order_line.qty_authorized - order_line.qty_received),
(order_line.qty_authorized- order_line.qty_received)))*
(GET_PRICE(order_line.Item_Id_To_Rcv,'USD'))) extlist,
((DECODE(order_line.allow_adv_replace,
'Y',(order_line.qty_shipped-order_line.qty_received),
'N',(order_line.qty_authorized - order_line.qty_received),
(order_line.qty_authorized- order_line.qty_received)))*
(working_queue.standard_cost)) extstdcost,
working_queue.priority_code,
working_queue.sub_level relval,
working_queue.last_action_code,
working_queue.last_action_date,
working_queue.next_action_code,
working_queue.next_action_date,
working_queue.in_transit_date,
working_queue.woff_code,
working_queue.woff_sequence,
working_queue.waybill_carrier,
working_queue.charge_to_dept,
working_queue.bill_auth,
working_queue.allotted_user,
working_queue.prioritize,
working_queue.case_status,
working_queue.cracc_flag,
working_queue.wq_status,
working_queue.line_status art_status,
order_line.status org_status,
working_queue.created_user,
working_queue.last_updated_date,
working_queue.last_updated_user,
order_line.schedule_ship_dt,
order_line.currency,
bill_place.country bill_to_country,
bill_place.state_prov bill_to_state,
order_line.qty_received,
trading.quote_number quote,
source_order.foreign_source_order_nbr sales_order,
source_order_dtl_view.ordered_quantity * source_order_dtl_view.selling_price credit_amount
FROM order_line,
working_queue,
place ship_place,
place bill_place,
item,
trading@cca_iei,
source_order,
source_order_dtl_view@cca_iei,
source_order_line,
order
WHERE working_queue.wq_status='Y'
AND working_queue.prioritize='Y'
AND working_queue.line_status = 'OP'
and working_queue.allotted_user in ('JBORGHAR', 'LIMAY')
AND order_line.order_id= working_queue.order_id
AND order_line.order_line = working_queue.order_line
AND order_line.return_reason = 'TRDIN'
AND order.order_id = order_line.order_id
AND bill_place.place_id = order.place_id_to_bill
AND ship_place.place_id = order.place_id_ship_to
AND item .item_id = order_line.item_id_to_rcv
AND to_char(trading.rma_number(+)) = order.order_id
AND source_order.order_id(+) = order.order_id
and source_order_dtl_view.line_id (+)= source_order_line.foreign_line_id
and source_order_line.order_id = order_line.order_id
and source_order_line.order_line = order_line.order_line
Re: Query taking too long to run [message #64947 is a reply to message #64941] Tue, 16 March 2004 01:02 Go to previous messageGo to next message
badri
Messages: 7
Registered: October 2001
Junior Member
1. Try forcing INDEX usage, using the HINT.
2. Ensure you have updated the statistics.
3. You can also try changing the order of the tables in the FROM clause. (permutation & combination method)
4. check on db_file_multiblock_read_count
Re: Query taking too long to run [message #64960 is a reply to message #64947] Thu, 18 March 2004 00:29 Go to previous message
IA
Messages: 91
Registered: March 2004
Member
Try using the PARALLEL hint.

IA.
Previous Topic: urgent help required
Next Topic: Explain Plan - Query Performance Tuning
Goto Forum:
  


Current Time: Tue Sep 29 10:23:35 CDT 2020