Home » RDBMS Server » Performance Tuning » Query Tuning (Oracle 9.2, Windows)
icon11.gif  Query Tuning [message #443725] Wed, 17 February 2010 02:08 Go to next message
visuorac
Messages: 10
Registered: February 2010
Location: chennai
Junior Member
Hi,

I want to tune this query ,please advise me.

SELECT
TO_CHAR(v2.uabscon_cancellation_date,'MON-YYYY')s_cancel_month,
COUNT(*) s_cont_cancelled,
SUM(v2.NumApps) s_appl_covered,
SUM(DECODE(
SIGN(TO_DATE('01-FEB-2009')-v2.compl_date),
1,1,
0
)*v2.NumApps
)s_prior_visit,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-FEB-2009',1,0)*v2.NumApps) s_visit_month_1,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-MAR-2009',1,0)*v2.NumApps) s_visit_month_2,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-APR-2009',1,0)*v2.NumApps) s_visit_month_3,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-MAY-2009',1,0)*v2.NumApps) s_visit_month_4,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JUN-2009',1,0)*v2.NumApps) s_visit_month_5,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JUL-2009',1,0)*v2.NumApps) s_visit_month_6,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-AUG-2009',1,0)*v2.NumApps) s_visit_month_7,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-SEP-2009',1,0)*v2.NumApps) s_visit_month_8,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-OCT-2009',1,0)*v2.NumApps) s_visit_month_9,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-NOV-2009',1,0)*v2.NumApps) s_visit_month_10,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-DEC-2009',1,0)*v2.NumApps) s_visit_month_11,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JAN-2010',1,0)*v2.NumApps) s_visit_month_12,
SUM(DECODE(
SIGN(TO_DATE('31-JAN-2010'||' 23:59:59','DD-MON-YYYY HH24:MI:SS')-v2.compl_date),
-1,1,
0
)*v2.NumApps
) s_post_visit,
SUM(DECODE(v2.compl_date,NULL,1,0)*v2.NumApps) s_no_visit
FROM
(
SELECT
/*+PARALLEL(svco1,4)*/
v.uabscon_cancellation_date,
v.NumApps,
svco1.max_ucbsvco_completion_date compl_date
FROM
(
SELECT

/*+PARALLEL(a,4)*/

a.uabscon_prem_code,
a.uabscon_cust_code,
a.uabscon_cancellation_date,
COUNT(*) NumApps
FROM
uimsmgr.uabscon a,
uimsmgr.uarserq b,
(
SELECT
/*+PARALLEL(b,4)*/
b.utrjapp_srvc_code,
b.utrjapp_styp_code,
b.utrjapp_visit_months,
b.utrjapp_li_ind,
c.utvsrvc_guarantee
FROM
uimsmgr.utrjapp b,
uimsmgr.utvsrvc c
WHERE
c.utvsrvc_bus_sector_id = 1
AND b.utrjapp_srvc_code = c.utvsrvc_code
AND b.utrjapp_visit_months <> 0
AND rownum<100
) japp1
WHERE
b.uarserq_cust_code = a.uabscon_cust_code
AND b.uarserq_prem_code = a.uabscon_prem_code
AND a.uabscon_status_ind = 'C'
AND a.uabscon_cancellation_date BETWEEN
TO_DATE('01-FEB-2009')
AND TO_DATE('31-JAN-2010'||' 23:59:59','DD-MON-YYYY HH24:MI:SS')
AND b.uarserq_srvc_code = japp1.utrjapp_srvc_code
AND b.uarserq_styp_code = japp1.utrjapp_styp_code
AND b.uarserq_serv_num > 0
-- No other active CONTRACT at the PREMISES
AND NOT EXISTS (
SELECT
'X'
FROM
uimsmgr.uabscon f
WHERE
f.uabscon_prem_code = a.uabscon_prem_code
AND f.uabscon_status_ind != 'P'
AND f.uabscon_status_ind != 'C'
AND rownum<100
)
-- Most recent quote
AND b.uarserq_quote_number = (
SELECT MAX(t.uabletq_quote_number)
FROM
uimsmgr.uabletq t
WHERE
t.uabletq_cust_code = a.uabscon_cust_code
AND t.uabletq_prem_code = a.uabscon_prem_code
AND rownum<100
)
AND rownum<100
GROUP BY
a.uabscon_prem_code,
a.uabscon_cust_code,
a.uabscon_cancellation_date
) v,
(
SELECT /*+PARALLEL(svco2,4)*/
svco2.ucbsvco_prem_code,
svco2.ucbsvco_cust_code,
MAX(svco2.ucbsvco_completion_date) max_ucbsvco_completion_date
FROM
uimsmgr.ucbsvco svco2
WHERE
svco2.ucbsvco_stus_code = 'C'
AND svco2.ucbsvco_sotp_code IN ('AS','FV')
AND rownum<100
GROUP BY
svco2.ucbsvco_prem_code,
svco2.ucbsvco_cust_code
) svco1
WHERE
svco1.ucbsvco_cust_code (+) = v.uabscon_cust_code
AND svco1.ucbsvco_prem_code (+) = v.uabscon_prem_code
AND rownum<100

) v2
GROUP BY
TO_CHAR(v2.uabscon_cancellation_date,'YYYYMM'),
TO_CHAR(v2.uabscon_cancellation_date,'MON-YYYY')
  • Attachment: Lat_Qry.sql
    (Size: 4.26KB, Downloaded 203 times)
Re: Query Tuning [message #443733 is a reply to message #443725] Wed, 17 February 2010 02:20 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888
and provide the requested information.

Read OraFAQ Forum Guide, "How to format your post?" section, to know how to format a query.

Regards
Michel
Re: Query Tuning [message #444477 is a reply to message #443725] Mon, 22 February 2010 03:39 Go to previous messageGo to next message
visuorac
Messages: 10
Registered: February 2010
Location: chennai
Junior Member
Please share your comments.
Re: Query Tuning [message #444483 is a reply to message #444477] Mon, 22 February 2010 03:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post what is requested.

Regards
Michel
Re: Query Tuning [message #444485 is a reply to message #444483] Mon, 22 February 2010 04:12 Go to previous messageGo to next message
visuorac
Messages: 10
Registered: February 2010
Location: chennai
Junior Member
I need to tune the above query.Please share your comments.
Re: Query Tuning [message #444492 is a reply to message #443733] Mon, 22 February 2010 04:23 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Wed, 17 February 2010 08:20
Read http://www.orafaq.com/forum/mv/msg/84315/433888/102589/#msg_433888
and provide the requested information.

Read OraFAQ Forum Guide, "How to format your post?" section, to know how to format a query.

Regards
Michel


We do not have enough information to be able to help. The link above tells you what information we need. Follow it.
Re: Query Tuning [message #444504 is a reply to message #444492] Mon, 22 February 2010 05:17 Go to previous messageGo to next message
visuorac
Messages: 10
Registered: February 2010
Location: chennai
Junior Member
I want to replace not exists in the above query.How can i replace? please share your comments.
Re: Query Tuning [message #444507 is a reply to message #444504] Mon, 22 February 2010 05:21 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please post what has been requested.
You can repeat the question indefinitvely, the answer will be the same until you post useful information.

Regards
Michel

[Updated on: Mon, 22 February 2010 05:22]

Report message to a moderator

Re: Query Tuning [message #444508 is a reply to message #444504] Mon, 22 February 2010 05:23 Go to previous messageGo to next message
Michel Cadot
Messages: 63802
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
visuorac wrote on Mon, 22 February 2010 12:17
I want to replace not exists in the above query.How can i replace? please share your comments.

You already asked this and got answers at:
http://www.orafaq.com/forum/m/443756/102589/#msg_443756
So you already had our comments.

Regards
Michel

[Updated on: Mon, 22 February 2010 05:23]

Report message to a moderator

Re: Query Tuning [message #444560 is a reply to message #444508] Mon, 22 February 2010 09:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Why do you believe that the NOT EXISTS part of the query is causing the problems?

What indexes are there on uimsmgr.uabscon?
Re: Query Tuning [message #444563 is a reply to message #443725] Mon, 22 February 2010 10:15 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Decided to have a quick look and having formatted it and read it I believe there is a far more fundamental problem with this query than performance. There's massive misuse of rownum going on that'll almost certainly mean it gives the wrong answer.
Here's the query formatted:
SELECT 
TO_CHAR(v2.uabscon_cancellation_date,'MON-YYYY')s_cancel_month,
COUNT(*) s_cont_cancelled,
SUM(v2.NumApps) s_appl_covered,
SUM(DECODE(SIGN(TO_DATE('01-FEB-2009')-v2.compl_date),
           1,1,
           0)*v2.NumApps)s_prior_visit,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-FEB-2009',1,0)*v2.NumApps) s_visit_month_1,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-MAR-2009',1,0)*v2.NumApps) s_visit_month_2,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-APR-2009',1,0)*v2.NumApps) s_visit_month_3,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-MAY-2009',1,0)*v2.NumApps) s_visit_month_4,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JUN-2009',1,0)*v2.NumApps) s_visit_month_5,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JUL-2009',1,0)*v2.NumApps) s_visit_month_6,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-AUG-2009',1,0)*v2.NumApps) s_visit_month_7,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-SEP-2009',1,0)*v2.NumApps) s_visit_month_8,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-OCT-2009',1,0)*v2.NumApps) s_visit_month_9,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-NOV-2009',1,0)*v2.NumApps) s_visit_month_10,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-DEC-2009',1,0)*v2.NumApps) s_visit_month_11,
SUM(DECODE(TRUNC(v2.compl_date,'MON'),'01-JAN-2010',1,0)*v2.NumApps) s_visit_month_12,
SUM(DECODE(SIGN(TO_DATE('31-JAN-2010'||' 23:59:59','DD-MON-YYYY HH24:MI:SS')-v2.compl_date),
           -1,1,
           0
          )*v2.NumApps
   ) s_post_visit,
SUM(DECODE(v2.compl_date,NULL,1,0)*v2.NumApps) s_no_visit
FROM
     (SELECT /*+PARALLEL(svco1,4)*/
             v.uabscon_cancellation_date,
             v.NumApps,
             svco1.max_ucbsvco_completion_date compl_date
             FROM
                  (SELECT /*+PARALLEL(a,4)*/
                          a.uabscon_prem_code,
                          a.uabscon_cust_code,
                          a.uabscon_cancellation_date,
                          COUNT(*) NumApps
                   FROM uimsmgr.uabscon a,
                        uimsmgr.uarserq b,
                        (SELECT /*+PARALLEL(b,4)*/
                                b.utrjapp_srvc_code,
                                b.utrjapp_styp_code,
                                b.utrjapp_visit_months,
                                b.utrjapp_li_ind,
                                c.utvsrvc_guarantee
                         FROM uimsmgr.utrjapp b,
                              uimsmgr.utvsrvc c
                         WHERE c.utvsrvc_bus_sector_id = 1
                         AND b.utrjapp_srvc_code = c.utvsrvc_code
                         AND b.utrjapp_visit_months <> 0
                         AND ROWNUM < 100
                        ) japp1
                   WHERE b.uarserq_cust_code = a.uabscon_cust_code 
                   AND b.uarserq_prem_code = a.uabscon_prem_code
                   AND a.uabscon_status_ind = 'C'
                   AND a.uabscon_cancellation_date BETWEEN TO_DATE('01-FEB-2009') 
                                                   AND TO_DATE('31-JAN-2010'||' 23:59:59','DD-MON-YYYY HH24:MI:SS')
                   AND b.uarserq_srvc_code = japp1.utrjapp_srvc_code
                   AND b.uarserq_styp_code = japp1.utrjapp_styp_code
                   AND b.uarserq_serv_num > 0
                   -- No other active CONTRACT at the PREMISES
                   AND NOT EXISTS (SELECT 'X'
                                   FROM uimsmgr.uabscon f 
                                   WHERE f.uabscon_prem_code = a.uabscon_prem_code 
                                   AND f.uabscon_status_ind != 'P'
                                   AND f.uabscon_status_ind != 'C'
                                   AND ROWNUM < 100
                                  )
                   -- Most recent quote
                   AND b.uarserq_quote_number = (SELECT MAX(t.uabletq_quote_number) 
                                                 FROM uimsmgr.uabletq t 
                                                 WHERE t.uabletq_cust_code = a.uabscon_cust_code 
                                                 AND t.uabletq_prem_code = a.uabscon_prem_code
                                                 AND ROWNUM < 100)
                   AND ROWNUM < 100
                   GROUP BY a.uabscon_prem_code,
                            a.uabscon_cust_code,
                            a.uabscon_cancellation_date
                  ) v,
                  (SELECT /*+PARALLEL(svco2,4)*/
                          svco2.ucbsvco_prem_code,
                          svco2.ucbsvco_cust_code,
                          MAX(svco2.ucbsvco_completion_date) max_ucbsvco_completion_date
                   FROM uimsmgr.ucbsvco svco2
                   WHERE svco2.ucbsvco_stus_code = 'C'
                   AND svco2.ucbsvco_sotp_code IN ('AS','FV')
                   AND ROWNUM < 100
                   GROUP BY svco2.ucbsvco_prem_code,
                            svco2.ucbsvco_cust_code
                  ) svco1
             WHERE svco1.ucbsvco_cust_code (+) = v.uabscon_cust_code
             AND svco1.ucbsvco_prem_code (+) = v.uabscon_prem_code
             AND ROWNUM < 100
     ) v2
GROUP BY
TO_CHAR(v2.uabscon_cancellation_date,'YYYYMM'),
TO_CHAR(v2.uabscon_cancellation_date,'MON-YYYY')

@visuorac - do you see how much easier that is to read than what you've posted at the top. This is why we tell you to use code tags.

So problems:
This:
(SELECT MAX(t.uabletq_quote_number) 
 FROM uimsmgr.uabletq t 
 WHERE t.uabletq_cust_code = a.uabscon_cust_code 
 AND t.uabletq_prem_code = a.uabscon_prem_code
 AND ROWNUM < 100)


Is obviously wrong. That's getting the max of the 1st 100 rows it finds. You presumably want the max of all the rows that match.

This:
AND NOT EXISTS (SELECT 'X'
                FROM uimsmgr.uabscon f 
                WHERE f.uabscon_prem_code = a.uabscon_prem_code 
                AND f.uabscon_status_ind != 'P'
                AND f.uabscon_status_ind != 'C'
                AND ROWNUM < 100)

Is pointless. Oracle stops a not exist lookup as soon as a match is found, limiting it by rownum accomplishes nothing.

In fact I strongly suspect every use of rownum in that query is wrong. Rownum is generally used to do top-n queries. I.e. queries that get the top n rows according to a particular ordering scheme:
SELECT *
FROM (select sal from emp order by sal desc)
where rownum <= 3;

gets the top 3 salaries in emp.

You do not have a single top-n query that I can see. Adding the rownum predicates might have speeded your query up but they will have ensured you get the wrong result at the same time.
Previous Topic: Improve Performance (Merged RL)
Next Topic: Migration to Oracle 10 Performance issues
Goto Forum:
  


Current Time: Sun Sep 25 12:57:02 CDT 2016

Total time taken to generate the page: 0.14151 seconds