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  |
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  |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Apr 19 22:31:11 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
