Home » RDBMS Server » Performance Tuning » Long running SQL needs tuning (Oracle 10g)
| Long running SQL needs tuning [message #327021] |
Fri, 13 June 2008 07:48  |
hidnana
Messages: 87
Registered: November 2007
Location: chennai
|
Member |
|
|
We've an SQL procedure that hangs after running for hours. We've identified the SQL that's causing the bottleneck. Also I've attached the screnshot of th explain plan.
Pls. suggest me to improve the performance of this query.
===============================================
SELECT COUNT(DISTINCT reat_elg.prd_hld_id)
FROM tl_w_prd_hld prdhld , tl_w_prd_hld_role prdhldrl , tl_w_phlc_cnt_stat prdhldco , tl_w_reat_elg reat_elg
WHERE reat_elg.prd_hld_id = prdhld.prd_hld_id AND
prdhld.prd_hld_id = prdhldrl.prd_hld_id AND
prdhld.prd_hld_id = prdhldco.prd_hld_id AND
reat_elg.prd_hld_id != '-1' AND
reat_elg.eligible_ind = 'Y' AND
prdhldrl.eff_end_date IS NULL AND
prdhldrl.hms_legown_ind = 'Y' AND
prdhldco.legown_issue_ind = 'N' AND
prdhld.jntown_type_ind='N' AND
reat_elg.prd_hld_id IN
(SELECT distinct phr2.prd_hld_id
FROM tl_w_prd_hld_role phr2, tl_w_phlc_cnt_stat ph_loi
WHERE ph_loi.prd_hld_id=ph_loi.prd_hld_id AND
phr2.prd_hld_id = reat_elg.prd_hld_id AND
phr2.eff_end_date IS NULL AND
phr2.hms_legown_ind = 'Y' AND
(ph_loi.create_source,phr2.prd_hld_id) IN
(SELECT DISTINCT
(CASE
WHEN (ccntbl_ind='Y' OR ce_legown_issue_ind='N' OR sfc_cntbl_ind='' OR sfc_legown_issue_ind='') AND ce_hms_legown_ind='Y' THEN ce_create_source
WHEN ccntbl_ind='Y' AND ce_legown_issue_ind='N' AND ce_hms_legown_ind='Y' THEN ce_create_source
WHEN ((ccntbl_ind='N' OR ce_legown_issue_ind='Y') AND ce_hms_legown_ind='Y' ) AND (sfc_cntbl_ind='Y' AND sfc_legown_issue_ind='N' AND SFC_hms_legown_IND='Y') THEN sfc_create_source
WHEN ((ccntbl_ind='N' OR ce_legown_issue_ind='Y') AND ce_hms_legown_ind='Y' ) AND ((sfc_cntbl_ind='N' AND sfc_legown_issue_ind='Y') AND SFC_hms_legown_IND='Y') THEN sfc_create_source
WHEN ((ccntbl_ind='N' OR ce_legown_issue_ind='Y') AND ce_hms_legown_ind='Y' ) AND (sfc_cntbl_ind='Y' AND sfc_legown_issue_ind='N' AND SFC_hms_legown_IND='N') THEN ce_create_source
WHEN ((ccntbl_ind='N' OR ce_legown_issue_ind='Y') AND ce_hms_legown_ind='Y' ) AND ((sfc_cntbl_ind='N' AND sfc_legown_issue_ind='Y') AND SFC_hms_legown_IND='N') THEN ce_create_source
END
),
(CASE
WHEN (ccntbl_ind='Y' OR ce_legown_issue_ind='N' OR sfc_cntbl_ind='' OR sfc_legown_issue_ind='') AND ce_hms_legown_ind='Y' THEN CE_prd_hld_ID
WHEN ccntbl_ind='Y' AND ce_legown_issue_ind='N' AND ce_hms_legown_ind='Y' THEN ce_create_source
WHEN ((ccntbl_ind='N' OR ce_legown_issue_ind='Y') AND ce_hms_legown_ind='Y' ) AND (sfc_cntbl_ind='Y' AND sfc_legown_issue_ind='N' AND SFC_hms_legown_IND='Y') THEN sfc_prd_hld_id
WHEN ((ccntbl_ind='N' OR ce_legown_issue_ind='Y') AND ce_hms_legown_ind='Y' ) AND ((sfc_cntbl_ind='N' AND sfc_legown_issue_ind='Y') AND SFC_hms_legown_IND='Y') THEN sfc_prd_hld_id
WHEN ((ccntbl_ind='N' OR ce_legown_issue_ind='Y') AND ce_hms_legown_ind='Y' ) AND (sfc_cntbl_ind='Y' AND sfc_legown_issue_ind='N' AND SFC_hms_legown_IND='N') THEN ce_prd_hld_id
WHEN ((ccntbl_ind='N' OR ce_legown_issue_ind='Y') AND ce_hms_legown_ind='Y' ) AND ((sfc_cntbl_ind='N' AND sfc_legown_issue_ind='Y') AND SFC_hms_legown_IND='N') THEN ce_prd_hld_id
END
)
FROM
(
SELECT
cnteng.prd_hld_id ce_prd_hld_id,
cnteng.cntbl_ind ccntbl_ind,
cnteng.legown_issue_ind ce_legown_issue_ind,
cnteng.hms_legown_ind ce_hms_legown_ind,
cnteng.create_source ce_create_source,
slsfrc.prd_hld_id sfc_prd_hld_id,
slsfrc.cntbl_ind sfc_cntbl_ind,
slsfrc.legown_issue_ind sfc_legown_issue_ind,
slsfrc.hms_legown_ind sfc_hms_legown_ind,
slsfrc.create_source sfc_create_source
FROM
(SELECT ph_loi.prd_hld_id,ph_loi.cntbl_ind,ph_loi.legown_issue_ind,prdhldrl.hms_legown_ind,ph_loi.create_source FROM tl_w_phlc_cnt_stat ph_loi,tl_w_prd_hld_role prdhldrl
WHERE
ph_loi.prd_hld_id=prdhldrl.prd_hld_id
AND ph_loi.create_source!='slsfrc'
)cnteng,
(
SELECT ph_loi.prd_hld_id,ph_loi.cntbl_ind,ph_loi.legown_issue_ind,prdhldrl.hms_legown_ind,ph_loi.create_source FROM tl_w_phlc_cnt_stat ph_loi,tl_w_prd_hld_role prdhldrl
WHERE
ph_loi.prd_hld_id=prdhldrl.prd_hld_id
AND ph_loi.CREATE_SOURCE='slsfrc'
)slsfrc where cnteng.prd_hld_id=slsfrc.prd_hld_id
)
)
GROUP BY phr2.prd_hld_id
HAVING COUNT(*) > 1
);
================================================
[Mod-Edit: Frank removed explain-plan (jpg) as requested by OP; contained undisclosed table-names]
[Updated on: Sun, 15 June 2008 08:37] by Moderator Report message to a moderator |
|
|
|
| Re: Long running SQL needs tuning [message #327042 is a reply to message #327021] |
Fri, 13 June 2008 09:15  |
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
|
Senior Member |
|
|
| Quote: |
(SELECT ph_loi.prd_hld_id,ph_loi.cntbl_ind,ph_loi.legown_issue_ind,prdhldrl.hms_legown_ind,ph_loi.create_source FROM tl_w_phlc_cnt_stat ph_loi,tl_w_prd_hld_role prdhldrl
WHERE
ph_loi.prd_hld_id=prdhldrl.prd_hld_id
AND ph_loi.create_source!='slsfrc'
)cnteng,
(
SELECT ph_loi.prd_hld_id,ph_loi.cntbl_ind,ph_loi.legown_issue_ind,prdhldrl.hms_legown_ind,ph_loi.create_source FROM tl_w_phlc_cnt_stat ph_loi,tl_w_prd_hld_role prdhldrl
WHERE
ph_loi.prd_hld_id=prdhldrl.prd_hld_id
AND ph_loi.CREATE_SOURCE='slsfrc'
)slsfrc where cnteng.prd_hld_id=slsfrc.prd_hld_id
|
Why the same select is appearing twice ? Am I missing something here. Also why distinct and group by together ?
Regards
Raj
|
|
|
|
| Re: Long running SQL needs tuning [message #327069 is a reply to message #327021] |
Fri, 13 June 2008 11:16 |
 |
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
|
Senior Member |
|
|
HI, seems like you need to learn some basics of how to analyze performance of queries.
One thing you can do here is try to figure out where in your query most of your time is going. Your query is made up of pieces. You should try running these pieces in steps so that you can figure out at what point your response time goes down the toilet. For example:
How long does this take:
SELECT ph_Loi.prd_hld_Id,
ph_Loi.cnTbl_Ind,
ph_Loi.LegOwn_Issue_Ind,
prdhldrl.hms_LegOwn_Ind,
ph_Loi.Create_Source
FROM tl_w_phlc_cnt_sTat ph_Loi,
tl_w_prd_hld_Role prdhldrl
WHERE ph_Loi.prd_hld_Id = prdhldrl.prd_hld_Id
AND ph_Loi.Create_Source != 'slsfrc'
How long does this take:
SELECT ph_Loi.prd_hld_Id,
ph_Loi.cnTbl_Ind,
ph_Loi.LegOwn_Issue_Ind,
prdhldrl.hms_LegOwn_Ind,
ph_Loi.Create_Source
FROM tl_w_phlc_cnt_sTat ph_Loi,
tl_w_prd_hld_Role prdhldrl
WHERE ph_Loi.prd_hld_Id = prdhldrl.prd_hld_Id
AND ph_Loi.Create_Source = 'slsfrc'
How long does this take:
SELECT cnTeng.prd_hld_Id ce_prd_hld_Id,
cnTeng.cnTbl_Ind ccnTbl_Ind,
cnTeng.LegOwn_Issue_Ind ce_LegOwn_Issue_Ind,
cnTeng.hms_LegOwn_Ind ce_hms_LegOwn_Ind,
cnTeng.Create_Source ce_Create_Source,
slsfrc.prd_hld_Id sfc_prd_hld_Id,
slsfrc.cnTbl_Ind sfc_cnTbl_Ind,
slsfrc.LegOwn_Issue_Ind sfc_LegOwn_Issue_Ind,
slsfrc.hms_LegOwn_Ind sfc_hms_LegOwn_Ind,
slsfrc.Create_Source sfc_Create_Source
FROM (SELECT ph_Loi.prd_hld_Id,
ph_Loi.cnTbl_Ind,
ph_Loi.LegOwn_Issue_Ind,
prdhldrl.hms_LegOwn_Ind,
ph_Loi.Create_Source
FROM tl_w_phlc_cnt_sTat ph_Loi,
tl_w_prd_hld_Role prdhldrl
WHERE ph_Loi.prd_hld_Id = prdhldrl.prd_hld_Id
AND ph_Loi.Create_Source != 'slsfrc') cnTeng,
(SELECT ph_Loi.prd_hld_Id,
ph_Loi.cnTbl_Ind,
ph_Loi.LegOwn_Issue_Ind,
prdhldrl.hms_LegOwn_Ind,
ph_Loi.Create_Source
FROM tl_w_phlc_cnt_sTat ph_Loi,
tl_w_prd_hld_Role prdhldrl
WHERE ph_Loi.prd_hld_Id = prdhldrl.prd_hld_Id
AND ph_Loi.Create_Source = 'slsfrc') slsfrc
WHERE cnTeng.prd_hld_Id = slsfrc.prd_hld_Id
How long does this take:
SELECT DISTINCT (CASE
WHEN (ccnTbl_Ind = 'Y'
OR ce_LegOwn_Issue_Ind = 'N'
OR sfc_cnTbl_Ind = ''
OR sfc_LegOwn_Issue_Ind = '')
AND ce_hms_LegOwn_Ind = 'Y' THEN ce_Create_Source
WHEN ccnTbl_Ind = 'Y'
AND ce_LegOwn_Issue_Ind = 'N'
AND ce_hms_LegOwn_Ind = 'Y' THEN ce_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND (sfc_cnTbl_Ind = 'Y'
AND sfc_LegOwn_Issue_Ind = 'N'
AND sfc_hms_LegOwn_Ind = 'Y') THEN sfc_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND ((sfc_cnTbl_Ind = 'N'
AND sfc_LegOwn_Issue_Ind = 'Y')
AND sfc_hms_LegOwn_Ind = 'Y') THEN sfc_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND (sfc_cnTbl_Ind = 'Y'
AND sfc_LegOwn_Issue_Ind = 'N'
AND sfc_hms_LegOwn_Ind = 'N') THEN ce_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND ((sfc_cnTbl_Ind = 'N'
AND sfc_LegOwn_Issue_Ind = 'Y')
AND sfc_hms_LegOwn_Ind = 'N') THEN ce_Create_Source
END),
(CASE
WHEN (ccnTbl_Ind = 'Y'
OR ce_LegOwn_Issue_Ind = 'N'
OR sfc_cnTbl_Ind = ''
OR sfc_LegOwn_Issue_Ind = '')
AND ce_hms_LegOwn_Ind = 'Y' THEN ce_prd_hld_Id
WHEN ccnTbl_Ind = 'Y'
AND ce_LegOwn_Issue_Ind = 'N'
AND ce_hms_LegOwn_Ind = 'Y' THEN ce_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND (sfc_cnTbl_Ind = 'Y'
AND sfc_LegOwn_Issue_Ind = 'N'
AND sfc_hms_LegOwn_Ind = 'Y') THEN sfc_prd_hld_Id
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND ((sfc_cnTbl_Ind = 'N'
AND sfc_LegOwn_Issue_Ind = 'Y')
AND sfc_hms_LegOwn_Ind = 'Y') THEN sfc_prd_hld_Id
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND (sfc_cnTbl_Ind = 'Y'
AND sfc_LegOwn_Issue_Ind = 'N'
AND sfc_hms_LegOwn_Ind = 'N') THEN ce_prd_hld_Id
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND ((sfc_cnTbl_Ind = 'N'
AND sfc_LegOwn_Issue_Ind = 'Y')
AND sfc_hms_LegOwn_Ind = 'N') THEN ce_prd_hld_Id
END)
FROM (SELECT cnTeng.prd_hld_Id ce_prd_hld_Id,
cnTeng.cnTbl_Ind ccnTbl_Ind,
cnTeng.LegOwn_Issue_Ind ce_LegOwn_Issue_Ind,
cnTeng.hms_LegOwn_Ind ce_hms_LegOwn_Ind,
cnTeng.Create_Source ce_Create_Source,
slsfrc.prd_hld_Id sfc_prd_hld_Id,
slsfrc.cnTbl_Ind sfc_cnTbl_Ind,
slsfrc.LegOwn_Issue_Ind sfc_LegOwn_Issue_Ind,
slsfrc.hms_LegOwn_Ind sfc_hms_LegOwn_Ind,
slsfrc.Create_Source sfc_Create_Source
FROM (SELECT ph_Loi.prd_hld_Id,
ph_Loi.cnTbl_Ind,
ph_Loi.LegOwn_Issue_Ind,
prdhldrl.hms_LegOwn_Ind,
ph_Loi.Create_Source
FROM tl_w_phlc_cnt_sTat ph_Loi,
tl_w_prd_hld_Role prdhldrl
WHERE ph_Loi.prd_hld_Id = prdhldrl.prd_hld_Id
AND ph_Loi.Create_Source != 'slsfrc') cnTeng,
(SELECT ph_Loi.prd_hld_Id,
ph_Loi.cnTbl_Ind,
ph_Loi.LegOwn_Issue_Ind,
prdhldrl.hms_LegOwn_Ind,
ph_Loi.Create_Source
FROM tl_w_phlc_cnt_sTat ph_Loi,
tl_w_prd_hld_Role prdhldrl
WHERE ph_Loi.prd_hld_Id = prdhldrl.prd_hld_Id
AND ph_Loi.Create_Source = 'slsfrc') slsfrc
WHERE cnTeng.prd_hld_Id = slsfrc.prd_hld_Id)
How long does this take:
SELECT DISTINCT phr2.prd_hld_Id
FROM tl_w_prd_hld_Role phr2,
tl_w_phlc_cnt_sTat ph_Loi
WHERE ph_Loi.prd_hld_Id = ph_Loi.prd_hld_Id
AND phr2.prd_hld_Id = rEat_elg.prd_hld_Id
AND phr2.eff_End_Date IS NULL
AND phr2.hms_LegOwn_Ind = 'Y'
AND (ph_Loi.Create_Source,
phr2.prd_hld_Id) IN (SELECT DISTINCT (CASE
WHEN (ccnTbl_Ind = 'Y'
OR ce_LegOwn_Issue_Ind = 'N'
OR sfc_cnTbl_Ind = ''
OR sfc_LegOwn_Issue_Ind = '')
AND ce_hms_LegOwn_Ind = 'Y' THEN ce_Create_Source
WHEN ccnTbl_Ind = 'Y'
AND ce_LegOwn_Issue_Ind = 'N'
AND ce_hms_LegOwn_Ind = 'Y' THEN ce_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND (sfc_cnTbl_Ind = 'Y'
AND sfc_LegOwn_Issue_Ind = 'N'
AND sfc_hms_LegOwn_Ind = 'Y') THEN sfc_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND ((sfc_cnTbl_Ind = 'N'
AND sfc_LegOwn_Issue_Ind = 'Y')
AND sfc_hms_LegOwn_Ind = 'Y') THEN sfc_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND (sfc_cnTbl_Ind = 'Y'
AND sfc_LegOwn_Issue_Ind = 'N'
AND sfc_hms_LegOwn_Ind = 'N') THEN ce_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND ((sfc_cnTbl_Ind = 'N'
AND sfc_LegOwn_Issue_Ind = 'Y')
AND sfc_hms_LegOwn_Ind = 'N') THEN ce_Create_Source
END),
(CASE
WHEN (ccnTbl_Ind = 'Y'
OR ce_LegOwn_Issue_Ind = 'N'
OR sfc_cnTbl_Ind = ''
OR sfc_LegOwn_Issue_Ind = '')
AND ce_hms_LegOwn_Ind = 'Y' THEN ce_prd_hld_Id
WHEN ccnTbl_Ind = 'Y'
AND ce_LegOwn_Issue_Ind = 'N'
AND ce_hms_LegOwn_Ind = 'Y' THEN ce_Create_Source
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND (sfc_cnTbl_Ind = 'Y'
AND sfc_LegOwn_Issue_Ind = 'N'
AND sfc_hms_LegOwn_Ind = 'Y') THEN sfc_prd_hld_Id
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND ((sfc_cnTbl_Ind = 'N'
AND sfc_LegOwn_Issue_Ind = 'Y')
AND sfc_hms_LegOwn_Ind = 'Y') THEN sfc_prd_hld_Id
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND (sfc_cnTbl_Ind = 'Y'
AND sfc_LegOwn_Issue_Ind = 'N'
AND sfc_hms_LegOwn_Ind = 'N') THEN ce_prd_hld_Id
WHEN ((ccnTbl_Ind = 'N'
OR ce_LegOwn_Issue_Ind = 'Y')
AND ce_hms_LegOwn_Ind = 'Y')
AND ((sfc_cnTbl_Ind = 'N'
AND sfc_LegOwn_Issue_Ind = 'Y')
AND sfc_hms_LegOwn_Ind = 'N') THEN ce_prd_hld_Id
END)
FROM (SELECT cnTeng.prd_hld_Id ce_prd_hld_Id,
cnTeng.cnTbl_Ind ccnTbl_Ind,
cnTeng.LegOwn_Issue_Ind ce_LegOwn_Issue_Ind,
cnTeng.hms_LegOwn_Ind ce_hms_LegOwn_Ind,
cnTeng.Create_Source ce_Create_Source,
slsfrc.prd_hld_Id sfc_prd_hld_Id,
slsfrc.cnTbl_Ind sfc_cnTbl_Ind,
slsfrc.LegOwn_Issue_Ind sfc_LegOwn_Issue_Ind,
slsfrc.hms_LegOwn_Ind sfc_hms_LegOwn_Ind,
slsfrc.Create_Source sfc_Create_Source
FROM (SELECT ph_Loi.prd_hld_Id,
ph_Loi.cnTbl_Ind,
ph_Loi.LegOwn_Issue_Ind,
prdhldrl.hms_LegOwn_Ind,
ph_Loi.Create_Source
FROM tl_w_phlc_cnt_sTat ph_Loi,
tl_w_prd_hld_Role prdhldrl
WHERE ph_Loi.prd_hld_Id = prdhldrl.prd_hld_Id
AND ph_Loi.Create_Source != 'slsfrc') cnTeng,
(SELECT ph_Loi.prd_hld_Id,
ph_Loi.cnTbl_Ind,
ph_Loi.LegOwn_Issue_Ind,
prdhldrl.hms_LegOwn_Ind,
ph_Loi.Create_Source
FROM tl_w_phlc_cnt_sTat ph_Loi,
tl_w_prd_hld_Role prdhldrl
WHERE ph_Loi.prd_hld_Id = prdhldrl.prd_hld_Id
AND ph_Loi.Create_Source = 'slsfrc') slsfrc
WHERE cnTeng.prd_hld_Id = slsfrc.prd_hld_Id))
GROUP BY phr2.prd_hld_Id
HAVING COUNT(* ) > 1
How long does this take:
SELECT COUNT(DISTINCT rEat_elg.prd_hld_Id)
FROM tl_w_prd_hld prdhld,
tl_w_prd_hld_Role prdhldrl,
tl_w_phlc_cnt_sTat prdhldco,
tl_w_rEat_elg rEat_elg
WHERE rEat_elg.prd_hld_Id = prdhld.prd_hld_Id
AND prdhld.prd_hld_Id = prdhldrl.prd_hld_Id
AND prdhld.prd_hld_Id = prdhldco.prd_hld_Id
AND rEat_elg.prd_hld_Id != '-1'
AND rEat_elg.Eligible_Ind = 'Y'
AND prdhldrl.eff_End_Date IS NULL
AND prdhldrl.hms_LegOwn_Ind = 'Y'
AND prdhldco.LegOwn_Issue_Ind = 'N'
AND prdhld.jnTown_Type_Ind = 'N'
You should be getting the idea eh? Take pieces of your code and figure out where the slowdown kicks in. Once you see where it is, you can then start to think about how to change the code.
Good luck, Kevin
|
|
|
|
| Re: Long running SQL needs tuning [message #327172 is a reply to message #327069] |
Sat, 14 June 2008 08:22 |
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
|
Senior Member |
|
|
The problem is the FILTER step on line 3 of the Explain Plan.
This step corresponds to the top-level IN sub-query in your SQL.
Take a look at this article on Explain Plan - it describes the dangers of the two-child FILTER.
And this one describes the perils of nested high-volume SQL.
I'm not too sure how to go about fixing your problem. The Plan Oracle has chosen is so stupid, it's difficult to know where to start. I am not aware of any case where an IN sub-query MUST be executed as a filter. As far as I am aware, it is always possible for the CBO to re-write it as a join.
What you want is for the sub-query to be HASH JOINED to the top four tables. My only suggestion is to do the re-write yourself rather than leaving it to the optimizer - no sub-query means no filter (it can still go wrong - but we'll have more options).
Rewrite it as:
SELECT ...
FROM t1, t2, t2, t4, (sub-query) t5
...
Ross Leishman
|
|
|
|
| Re: Long running SQL needs tuning [message #330278 is a reply to message #327021] |
Sat, 28 June 2008 15:03 |
Kriptas
Messages: 118
Registered: June 2008
|
Senior Member |
|
|
So...
i tries torewrite that query to ANSI SQL,
because query is very simple, but is writen in very complex way.
all queries SELECT x from table 1 where z in (select z from table2) is very slow, so try to not use that.
when i tried to rewrite query to ANSI SQL i found that in first subselect WHERE clause is writen statement "ph_loi.prd_hld_id=ph_loi.prd_hld_id" i think it is a problem!!!!! maybe you need cartesian join there?
but I think there must be "phr2.prd_hld_id=ph_loi.prd_hld_id"
try this select (there may be some mistakes, because I cant check all of them, without data sources)
SELECT COUNT(DISTINCT reat_elg.prd_hld_id)
FROM tl_w_prd_hld prdhld
INNER JOIN tl_w_reat_elg reat_elg ON reat_elg.prd_hld_id = prdhld.prd_hld_id
AND reat_elg.prd_hld_id != '-1'
AND reat_elg.eligible_ind = 'Y'
INNER JOIN tl_w_prd_hld_role prdhldrl ON prdhld.prd_hld_id = prdhldrl.prd_hld_id
AND prdhldrl.eff_end_date IS NULL
AND prdhldrl.hms_legown_ind = 'Y'
INNER JOIN tl_w_phlc_cnt_stat prdhldco ON prdhld.prd_hld_id = prdhldco.prd_hld_id
AND prdhldco.legown_issue_ind = 'N' AND
INNER JOIN (SELECT phr2.prd_hld_id, count(1)
FROM tl_w_prd_hld_role phr2
INNER JOIN tl_w_phlc_cnt_stat ph_loi ON ph_loi.prd_hld_id = phr2.prd_hld_id
INNER JOIN (SELECT DISTINCT
CASE
WHEN (cnteng.cntbl_ind='Y' OR cnteng.legown_issue_ind='N' OR slsfrc.cntbl_ind='' OR slsfrc.legown_issue_ind='') AND cnteng.hms_legown_ind='Y' THEN cnteng.create_source
WHEN cnteng.cntbl_ind='Y' AND cnteng.legown_issue_ind='N' AND cnteng.hms_legown_ind='Y' THEN cnteng.create_source
WHEN ((cnteng.cntbl_ind='N' OR cnteng.legown_issue_ind='Y') AND cnteng.hms_legown_ind='Y' ) AND (slsfrc.cntbl_ind='Y' AND slsfrc.legown_issue_ind='N' AND slsfrc.hms_legown_ind='Y') THEN slsfrc.create_source
WHEN ((cnteng.cntbl_ind='N' OR cnteng.legown_issue_ind='Y') AND cnteng.hms_legown_ind='Y' ) AND ((slsfrc.cntbl_ind='N' AND slsfrc.legown_issue_ind='Y') AND slsfrc.hms_legown_ind='Y') THEN slsfrc.create_source
WHEN ((cnteng.cntbl_ind='N' OR cnteng.legown_issue_ind='Y') AND cnteng.hms_legown_ind='Y' ) AND (slsfrc.cntbl_ind='Y' AND slsfrc.legown_issue_ind='N' AND slsfrc.hms_legown_ind='N') THEN cnteng.create_source
WHEN ((cnteng.cntbl_ind='N' OR cnteng.legown_issue_ind='Y') AND cnteng.hms_legown_ind='Y' ) AND ((slsfrc.cntbl_ind='N' AND slsfrc.legown_issue_ind='Y') AND slsfrc.hms_legown_ind='N') THEN cnteng.create_source
END src,
CASE
WHEN (cnteng.cntbl_ind='Y' OR cnteng.legown_issue_ind='N' OR slsfrc.cntbl_ind='' OR slsfrc.legown_issue_ind='') AND cnteng.hms_legown_ind='Y' THEN cnteng.prd_hld_id
WHEN cnteng.cntbl_ind='Y' AND cnteng.legown_issue_ind='N' AND cnteng.hms_legown_ind='Y' THEN cnteng.create_source
WHEN ((cnteng.cntbl_ind='N' OR cnteng.legown_issue_ind='Y') AND cnteng.hms_legown_ind='Y' ) AND (slsfrc.cntbl_ind='Y' AND slsfrc.legown_issue_ind='N' AND slsfrc.hms_legown_ind='Y') THEN slsfrc.prd_hld_id
WHEN ((cnteng.cntbl_ind='N' OR cnteng.legown_issue_ind='Y') AND cnteng.hms_legown_ind='Y' ) AND ((slsfrc.cntbl_ind='N' AND slsfrc.legown_issue_ind='Y') AND slsfrc.hms_legown_ind='Y') THEN slsfrc.prd_hld_id
WHEN ((cnteng.cntbl_ind='N' OR cnteng.legown_issue_ind='Y') AND cnteng.hms_legown_ind='Y' ) AND (slsfrc.cntbl_ind='Y' AND slsfrc.legown_issue_ind='N' AND slsfrc.hms_legown_ind='N') THEN ce_prd_hld_id
WHEN ((cnteng.cntbl_ind='N' OR cnteng.legown_issue_ind='Y') AND cnteng.hms_legown_ind='Y' ) AND ((slsfrc.cntbl_ind='N' AND slsfrc.legown_issue_ind='Y') AND slsfrc.hms_legown_ind='N') THEN ce_prd_hld_id
END prd_hld_id
FROM tl_w_prd_hld_role prdhldrl_1
INNER JOIN tl_w_phlc_cnt_stat cnteng on cnteng.prd_hld_id=prdhldrl_1.prd_hld_id AND cnteng.create_source!='slsfrc'
INNER JOIN tl_w_phlc_cnt_stat slsfrc on slsfrc.prd_hld_id=prdhldrl_1.prd_hld_id AND slsfrc.create_source='slsfrc'
WHERE prdhld.jntown_type_ind='N') t1 ON ph_loi.create_source = t1.src
AND phr2.prd_hld_id=t1.prd_hld_id
WHERE phr2.eff_end_date IS NULL
AND phr2.hms_legown_ind = 'Y'
GROUP BY phr2.prd_hld_id
HAVING count(1)>1) t2 ON reat_elg.prd_hld_id=t2.prd_hld_id
I think query can be tuned more, but without knowing logic of database and query purpose it can be done.
|
|
|
|
Goto Forum:
Current Time: Thu Jan 09 20:34:46 CST 2025
|
] 
Blog
Search
Help
Members
Register
Login
Home
