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 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
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 Go to previous messageGo to next message
rleishman
Messages: 3724
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 Go to previous message
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.
Previous Topic: performance of database , please help
Next Topic: Optimize this query..
Goto Forum:
  


Current Time: Thu Dec 08 16:06:08 CST 2016

Total time taken to generate the page: 0.13183 seconds