Home » RDBMS Server » Performance Tuning » Query Tuning (Oracle 9.2, Windows)
 Query Tuning [message #443725] |
Wed, 17 February 2010 02:08  |
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 1685 times)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Query Tuning [message #444507 is a reply to message #444504] |
Mon, 22 February 2010 05:21  |
 |
Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...
|
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 #444560 is a reply to message #444508] |
Mon, 22 February 2010 09:41 |
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 |
cookiemonster
Messages: 13973
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.
|
|
|
|
Goto Forum:
Current Time: Fri Oct 31 14:33:25 CDT 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
