Home » RDBMS Server » Performance Tuning » Please help me to tune this query
Please help me to tune this query [message #596551] Tue, 24 September 2013 13:04 Go to next message
ramya_162
Messages: 107
Registered: August 2013
Location: Banglore
Senior Member
Hi Experts,

The below query is taking 10 minutes to give output.
Please help me to tune this query.

WITH MARGIN AS (
SELECT sid, bid, 
    SUM(qnty* prc) quantity,
    SUM(qty*cst) profit,
    SUM(qty*stc)  volume
FROM sales_dtls
GROUP BY  sid, bid)
SELECT DISTINCT
    sup.sid,
    csh.cname,
    sls.sd_cost,
    mrg.mgn_cost,
    cst.doc,
    rps.rr_rate,
    csc.ccr_cost,
    cdc.ccr_rate,
    prt.prn_loc,
    mag.manu_stat,
    pfts.prn_stat,
    mgss.date_time_mgt,
    hst.hnr_no,
    MARGIN.quantity,
    MARGIN.profit,
    MARGIN.volume
FROM sup_dtls sup
    LEFT OUTER JOIN cash_dtls csh
    ON sup.sid = csh.sid
    LEFT OUTER JOIN sales_dtls sls 
    ON (sls.bid = csh.bid OR csh.bid IS NULL) AND sup.sid = sls.sid AND sls.s_no = 1
    INNER JOIN margin ON margin.sid=sup.sid AND margin.bid = sls.bid
    LEFT OUTER JOIN marg_dtls mrg ON sup.per_id = mrg.p_id
    LEFT OUTER JOIN cstr_dtls cst ON sup.cbs_id = cst.c_id
    LEFT OUTER JOIN reps_dtls rps ON rps.res_id = cst.r_id AND cst.pre = 2
    LEFT OUTER JOIN cstr_dtls csc ON sup.esc_id = csc.e_id
    LEFT OUTER JOIN cstr_dtls cdc ON sup.drd_id = cdc.d_id
    LEFT OUTER JOIN prft_dtls prt ON sup.ord_id = prt.o_id AND prt.pre = 1
    LEFT OUTER JOIN marg_dtls mag ON prt.san_id = mrg.s_id
    LEFT OUTER JOIN prft_dtls pfts ON sup.ord_id = pfts.o_id AND pfts.pre = 2
    LEFT OUTER JOIN marg_dtls mgss ON pfts.sal_id = mgss.s_id
    LEFT OUTER JOIN hist_dtls hst ON sup.ord_id = hst.o_id
    WHERE sup.date_time_supply >(SELECT MIN(last_dt) FROM dates_data)

Number of records in each table.

SELECT COUNT(*) FROM sup_dtls sup --36949

SELECT COUNT(*) FROM  cash_dtls csh--59741

SELECT COUNT(*) FROM sales_dtls sls --723168

SELECT COUNT(*) FROM  marg_dtls mrg --6425

SELECT COUNT(*) FROM  cstr_dtls cst --79760
    
SELECT COUNT(*) FROM reps_dtls rps--80264
    
SELECT COUNT(*) FROM prft_dtls prt --25452
    
SELECT COUNT(*) FROM hist_dtls hst --18276510


Please help me.

Thanks.
Re: Please help me to tune this query [message #596554 is a reply to message #596551] Tue, 24 September 2013 13:09 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
Since NOBODY can optimize SQL just by looking at it, we need a few more details.
http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:
1) DDL for all tables & indexes
2) EXPLAIN PLAN
3) output from SQL_TRACE & tkprof
Re: Please help me to tune this query [message #596558 is a reply to message #596551] Tue, 24 September 2013 13:29 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Whenever I see outer joins, I ask "why?" An outer join restricts the choices available to the optimizer hugely. Many programmers use them for no reason. Are you certain that you need them? Will you really lose rows that matter otherwise?

It is the same with DISTINCT: it can destroy performance, is often unnecessary, but programmers just throw it in. Are you really going to get duplicates? If so, should you handle them properly, or just throw them away?

If you really understand your data, you may be able to re-write that query into a form that will perform better.
Previous Topic: Howto avoid virtual circuit wait as application developer?
Next Topic: Select query taking time even after using PARALLEL hint
Goto Forum:
  


Current Time: Thu Mar 28 07:57:18 CDT 2024