OraFAQ Forum: Performance Tuning » Help- Tuning Query

Home » RDBMS Server » Performance Tuning » Help- Tuning Query (Oracle 9i R2)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Help- Tuning Query [message #292082]

Mon, 07 January 2008 13:54

orasaket
Messages: 70
Registered: November 2006

Member

Hi,

I am using Oracle 9iR2 on Linux

Following query is taking 6-7 hours on live system (doing Db sequential reads)


SELECT     cpm.strpolnbr ,
           cpm.dtnextprmdue,
           cpm.dtpolexpiry ,
	   cpm.dtpolcomc	  ,
              cpm.stracctid  ,
              cpm.strclientcd strpolholder,
              cpm.npmtmode,
              cpm.npayday,
              cpm.strrecoprefname,
              cpm.nsalfrq,
              cpm.npoltype,
              cpm.strschemeid,
              cpm.strgroupid,
              cpm.strsourcecd,
              cpm.strbrandcd,
              1 nclienttype ,
	      cppd.nsaccd ,
	     cpm.nadvarrears
    FROM    com_pol_prod_dtl cppd ,
            com_policy_m        cpm
    WHERE   cpm.strpolnbr   = cppd.strpolnbr
    AND cppd.strprodcd      = cp_strprodcd
    AND cppd.nprodver       = cp_nprodver
    AND cppd.nprodstatcd    = 1
    AND cpm.npolstatcd      = 1
    AND cpm.strpolnbr       = NVL(pi_strpolnbr,cpm.strpolnbr)
    AND cpm.strclientcd     = cppd.strclientcd
    AND NOT EXISTS (SELECT 1 FROM ps_alt_hdr pah, ps_alt_map pam
            WHERE  pah.stralttranshdrnbr = pam.stralttranshdrnbr
                        AND    pah.strpolnbr = cpm.strpolnbr
                        AND    pam.nalttype  = 56
                        AND    pah.dtalteff  >= cq_prod_rev_eff
                        AND    pah.nalttranstatcd IN (6, 7)
                       )
    ORDER BY lpolproddtlseq ASC;

Operation	                                             Object Name	                  Rows	Bytes	Cost	
                                                                                                         
SELECT STATEMENT Hint=CHOOSE		                                                      2  	 	   1551  
  SORT ORDER BY		                                                                  2  	246  	1551  
    CONCATENATION		  	 	 	 	      	             	                                                
      FILTER		  	 	 	 	      	             	                                                   
        TABLE ACCESS BY INDEX ROWID	                        COM_POLICY_M	            1  	85  	3  	
          NESTED LOOPS		                                                            1  	85  	 	 	
            TABLE ACCESS BY INDEX ROWID	                  COM_POL_PROD_DTL	         1  	38  	3  	
              INDEX RANGE SCAN	                           COM_POLPROD_PRODSTAT	      1  	 	   3  	
            INDEX RANGE SCAN	                              XPKCOM_POLICY_M	         1  	 	   3  	
              TABLE ACCESS BY INDEX ROWID	                  PS_ALT_MAP	               1  	17  	3  	
                NESTED LOOPS		                                                      1  	50  	13  	
                  INLIST ITERATOR		  	 	 	 	      	             	                              
                    TABLE ACCESS BY INDEX ROWID	            PS_ALT_HDR	               1  	33  	10  	
                      INDEX RANGE SCAN	                     IDX_POLNBR_STAT	         11  	 	   4  	
                  INDEX RANGE SCAN	                        IDX_PAM_STRALTTRANSHDRNBR	1  	 	   3  	
      FILTER		  	 	 	 	      	             	                                                   
        NESTED LOOPS		                                                               1  	123  	5  	
          TABLE ACCESS BY INDEX ROWID	                     COM_POLICY_M	            1  	85  	3  	
            INDEX UNIQUE SCAN	                              XPKCOM_POLICY_M	         1  	 	   3  	
              TABLE ACCESS BY INDEX ROWID	                  PS_ALT_MAP	               1  	17  	3  	
                NESTED LOOPS		                                                      1  	50  	13  	
                  INLIST ITERATOR		  	 	 	 	      	             	                              
                    TABLE ACCESS BY INDEX ROWID	            PS_ALT_HDR	               1  	33  	10  	
                      INDEX RANGE SCAN	                     IDX_POLNBR_STAT	         11  	 	   4  	
                  INDEX RANGE SCAN	                        IDX_PAM_STRALTTRANSHDRNBR	1  	 	   3  	
          TABLE ACCESS BY INDEX ROWID	                     COM_POL_PROD_DTL	         1  	38  	3  	
            INDEX RANGE SCAN	                              INDX_CPPD_POLNBR	         1  	 	   3

I wanted to avoid the co-related part in it, so i tried to tweak it little as following , however there is prob in the following query since it is not giving correct results.

Can anybody help me on this?


SELECT   /*+ full(cpm) */ cpm.strpolnbr , 
              cpm.dtnextprmdue, 
              cpm.dtpolexpiry , 
	      cpm.dtpolcomc	  , 
              cpm.stracctid  , 
              cpm.strclientcd strpolholder, 
              cpm.npmtmode, 
              cpm.npayday, 
              cpm.strrecoprefname, 
              cpm.nsalfrq, 
              cpm.npoltype, 
              cpm.strschemeid, 
              cpm.strgroupid, 
              cpm.strsourcecd, 
              cpm.strbrandcd, 
              1 nclienttype , 
	      cppd.nsaccd , 
	      cpm.nadvarrears 
    FROM    com_pol_prod_dtl cppd , 
            com_policy_m        cpm ,
	    ps_alt_hdr pah, 
	    ps_alt_map pam 
    WHERE   cpm.strpolnbr   = cppd.strpolnbr 
    AND     cppd.strprodcd      = cp_strprodcd 
    AND     cppd.nprodver       = cp_nprodver 
    AND     cppd.nprodstatcd    = 1 
    AND     cpm.npolstatcd      = 1 
    AND     cpm.strpolnbr       = NV(pi_strpolnbr,cpm.strpolnbr) 
    AND     cpm.strclientcd     = cppd.strclientcd 
    [B]AND     pah.stralttranshdrnbr = pam.stralttranshdrnbr 
    AND     cpm.strpolnbr = pah.strpolnbr(+) 
    AND     pam.nalttype  = 56 
    AND     pah.dtalteff  >= cq_prod_rev_eff 
    AND     pah.nalttranstatcd IN (6, 7) 
    AND     pah.rowid is null[/B]    ORDER BY lpolproddtlseq ASC;

Thanks and Regards,
OraSaket

P.S. Sorry for the unformatted plan, ttached file for this

Attachment: PLAN.TXT
(Size: 2.84KB, Downloaded 1254 times)

Report message to a moderator

Re: Help- Tuning Query [message #292110 is a reply to message #292082]

Mon, 07 January 2008 21:12

navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA

Senior Member

Try this ....

     SELECT   /*+ full(cpm) */ cpm.strpolnbr , 
              cpm.dtnextprmdue, 
              cpm.dtpolexpiry , 
	      cpm.dtpolcomc	  , 
              cpm.stracctid  , 
              cpm.strclientcd strpolholder, 
              cpm.npmtmode, 
              cpm.npayday, 
              cpm.strrecoprefname, 
              cpm.nsalfrq, 
              cpm.npoltype, 
              cpm.strschemeid, 
              cpm.strgroupid, 
              cpm.strsourcecd, 
              cpm.strbrandcd, 
              1 nclienttype , 
	      cppd.nsaccd , 
	      cpm.nadvarrears 
      FROM    com_pol_prod_dtl cppd , 
              com_policy_m        cpm ,
	      ps_alt_hdr pah, 
	      ps_alt_map pam 
     WHERE   cpm.strpolnbr       = cppd.strpolnbr 
       AND   cppd.strprodcd      = cp_strprodcd 
       AND   cppd.nprodver       = cp_nprodver 
       AND   cppd.nprodstatcd    = 1 
       AND   cpm.npolstatcd      = 1 
       AND   cpm.strpolnbr       = NVL  
                                   (pi_strpolnbr,cpm.strpolnbr) 
       AND   cpm.strclientcd     = cppd.strclientcd 
    [B]AND   cpm.strpolnbr = pah.strpolnbr(+) 
       AND   pah.stralttranshdrnbr = pam.stralttranshdrnbr(+) 
       AND   pam.nalttype(+)  = 56 
       AND   pah.dtalteff(+)  >= cq_prod_rev_eff 
       AND   pah.nalttranstatcd(+) IN (6, 7) 
       AND   pah.rowid is null
    [/B]    
  ORDER BY lpolproddtlseq ASC;

Regards,
Naveen

Report message to a moderator

Re: Help- Tuning Query [message #292164 is a reply to message #292082]

Tue, 08 January 2008 02:02

orafan23
Messages: 13
Registered: December 2005

Junior Member

How many rows do these tables com_pol_prod_dtl ,com_policy_m have?

Report message to a moderator

Re: Help- Tuning Query [message #292367 is a reply to message #292082]

Tue, 08 January 2008 15:28

orasaket
Messages: 70
Registered: November 2006

Member

Hi,

Following are the counts for tables in the query

Com_policy_m - 3138182
com_pol_prod_dtl - 5552495
ps_alt_hdr - 5552495
ps_alt_map - 16306844

Naveen,
While using the query suggested by you, I am getting following error
ORA-01719: outer join operator (+) not allowed in operand of OR or IN

And it seems it is difficult to use the ANSI syntax of Outer join in this particular query to avoid this error.

Thanks and Regards,
OraSaket

Report message to a moderator

Re: Help- Tuning Query [message #292707 is a reply to message #292082]

Wed, 09 January 2008 12:59

orasaket
Messages: 70
Registered: November 2006

Member

Naveen / orafan23,

The number of records returned by the query are not matching with the original. I am getting less number of records.

Something is missing in the query

Thanks and Regards,
OraSaket

Report message to a moderator

Re: Help- Tuning Query [message #292767 is a reply to message #292082]

Wed, 09 January 2008 16:45

Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA

Senior Member

a couple of thoughts:

1) you have done a pretty good job of prefixing all column references with alias. But unfortunately you have missed two. Please correct the code and resubmit it so we don't have to guess about the joins when we make recommendations.

2) your query plan says you are getting back two rows (2)? Is this correct. You have millions in the tables so how is Oracle figuring two? Is this a ballpark good guess, or are you getting lots more than two rows back and thus therefore maybe you should compute some statistics and try again.

3) you should post indexes (in a readable layout) for all four tables involved.

Thanks, Kevin

Report message to a moderator

Re: Help- Tuning Query [message #292845 is a reply to message #292367]

Thu, 10 January 2008 00:53

orafan23
Messages: 13
Registered: December 2005

Junior Member

With so many rows,going in Nested loop with Index scan, I wouldn't be surprised to see a lot of DB File sequential read.
Apart from this try using the Minus Operator instead of the not Exists and outer Join,which will force a full table scan.Try this on an test environment.

Also ,Please answer Kevin`s questions.If possible a SQL trace of the query would be useful to dig further.

Good Luck!

Report message to a moderator

Re: Help- Tuning Query [message #293429 is a reply to message #292082]

Sat, 12 January 2008 10:45

michael_bialik
Messages: 621
Registered: July 2006

Senior Member

Are you passing a value to pi_strpolnbr in your query or is it NULL?

If you are using a value rewrite

AND   cpm.strpolnbr = NVL (pi_strpolnbr,cpm.strpolnbr)

AND   cpm.strpolnbr = pi_strpolnbr

.

It may help as well.

Report message to a moderator

Previous Topic:	works in test env but very slow in production
Next Topic:	Tuning with Oracle Collections

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 20:48:31 CST 2024