Members   Search   Help    Register    Login   Home
Home » SQL & PL/SQL » SQL & PL/SQL » Help - Slow running Quey - (Sort Merge Join)
Help - Slow running Quey - (Sort Merge Join) [message #224050] Mon, 12 March 2007 14:30 Go to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi,
can anybody help me tuning the following query
it is taking lot of time

I have attached the plan with this message

select   hdr.strvoutype strvoutype, hdr.strvouseries strvouseries,
         hdr.nfiscalyear nfiscalyear, hdr.nfiscalmonth nfiscalmonth,
         hdr.lvounbr lvounbr, hdr.dtvou dtvou, hdr.dtauth dtauth,
         hdr.nbasedoctype nbasedoctype, hdr.strbasedocnbr strbasedocnbr,
         hdr.nisposted nisposted, hdr.strcurrcd strcurrcd,
         hdr.dexchangerate dexchangerate, hdr.ntransind ntransind,
         hdr.strrefnbr strrefnbr, hdr.nrefind nrefind,
         hdr.strbrandcd strbrandcd, hdr.strclientcd strclientcd,
         hdr.strcreatedby strcreatedby, hdr.strrefvoutype strrefvoutype,
         hdr.strrefvouseries strrefvouseries,
         hdr.nreffiscalyear nreffiscalyear, hdr.lrefvounbr lrefvounbr,
         hdr.strrefbrandcd strrefbrandcd
FROM     fin_vou_hdr hdr,
         ( select start_range, end_range
          FROM   com_rec_parallel_load
          WHERE  process_id = :b3
          AND    process_date = TRUNC ( sysdate )
          AND    sub_process_id = :b1
          UNION
          select :b4 start_range, :b4 end_range
          FROM   DUAL
          WHERE  :b4 IS NOT NULL
          AND    :b1 IS NULL ) crpl
WHERE    (   hdr.nisposted = 0
          OR hdr.nisposted IS NULL )
AND      (
             (    hdr.strvoutype IN ( 'JV', 'RV' )
              AND hdr.nvoustat = 3 )
          OR (    hdr.strvoutype = 'PV'
              AND hdr.nvoustat IN ( 7, 9, 14 ) )
          OR (
                  hdr.strvoutype = 'PV'
              AND hdr.nvoustat IN ( 3 )
              AND hdr.strrefvoutype > ' '
              AND hdr.strrefvouseries > ' '
              AND hdr.nreffiscalyear > 0
              AND hdr.lrefvounbr > 0
              AND hdr.strrefbrandcd > ' '
             )
         )
AND      hdr.strbasedocnbr = NVL ( :b4, hdr.strbasedocnbr )
AND      hdr.strbasedocnbr >= crpl.start_range
AND      hdr.strbasedocnbr <= crpl.end_range
ORDER BY hdr.dtvou,
         strrefvoutype DESC,
         strrefvouseries DESC,
         nreffiscalyear DESC,
         lrefvounbr DESC,
         strrefbrandcd DESC


Thanks and Regards,
OraSaket

  • Attachment: plan.sql
    (Size: 1.90KB, Downloaded 108 times)
Report message to a moderator
Re: Help - Slow running Quey - (Sort Merge Join) [message #224058 is a reply to message #224050] Mon, 12 March 2007 15:35 Go to previous messageGo to next message
gintsp
Messages: 115
Registered: February 2007
Senior Member
It is too late for me to think of you query whether it can be rewritten in a more efficient way as such, but you can speed up your sorts and merge joins increasing your sort_area_size if you use workarea_size_policy = manual, or increasing the pga_aggregate_target if you use workarea_size_policy = auto. In the latter you can switch also to manual and increase the sort_area_size. All that you can do in your necessary session. not on the all DB level.
The other question of course is - why in case of full scans and joining larg data sets you have merge joins but not hash joins?
Have you disabled them explicitly or your db version is so old?
Generally hash joins are faster than merge joins of course assuming there is enough memory Smile

Gints Plivna
http://www.gplivna.eu
Report message to a moderator
Re: Help - Slow running Quey - (Sort Merge Join) [message #224089 is a reply to message #224058] Tue, 13 March 2007 00:49 Go to previous messageGo to next message
rleishman
Messages: 3364
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Here's the plan for those who dont want to open the attachment:

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Hint=CHOOSE		357 K	 	390233  	 	      	             	 
  SORT ORDER BY		357 K	176 M	390233  	 	      	             	 
    CONCATENATION		  	 	 	 	      	             	 
      FILTER		  	 	 	 	      	             	 
        MERGE JOIN		1  	112  	 	 	      	             	 
          SORT JOIN		1  	516  	 	 	      	             	 
            VIEW		1  	404  	6  	 	      	             	 
              SORT UNIQUE		83  	33  	7  	 	      	             	 
                UNION-ALL		  	 	 	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	COM_REC_PARALLEL_LOAD	1  	33  	2  	 	      	             	 
                    INDEX RANGE SCAN	PK_COM_REC_PAR_LOAD	1  	 	3  	 	      	             	 
                  FILTER		  	 	 	 	      	             	 
                    TABLE ACCESS FULL	DUAL	82  	 	3  	 	      	             	 
          FILTER		  	 	 	 	      	             	 
            SORT JOIN		  	 	 	 	      	             	 
              TABLE ACCESS FULL	FIN_VOU_HDR	1  	112  	5  	 	      	             	 
      FILTER		  	 	 	 	      	             	 
        NESTED LOOPS		1  	516  	10  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	FIN_VOU_HDR	1  	112  	5  	 	      	             	 
            INDEX RANGE SCAN	INDX_FVHDR_NBASEDOCTY_NBR	48  	 	5  	 	      	             	 
          VIEW		1  	404  	6  	 	      	             	 
            SORT UNIQUE		83  	33  	7  	 	      	             	 
              UNION-ALL		  	 	 	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	COM_REC_PARALLEL_LOAD	1  	33  	2  	 	      	             	 
                  INDEX RANGE SCAN	PK_COM_REC_PAR_LOAD	1  	 	3  	 	      	             	 
                FILTER		  	 	 	 	      	             	 
                  TABLE ACCESS FULL	DUAL	82  	 	3
Report message to a moderator
Re: Help - Slow running Quey - (Sort Merge Join) [message #224091 is a reply to message #224050] Tue, 13 March 2007 01:00 Go to previous messageGo to next message
rleishman
Messages: 3364
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Instead of joining to a UNION inline view, why don't you LEFT JOIN to com_rec_parallel_load.

Also, you probably want to stop it from performing the CONCATENATION step. You are alread full-scanning fin_vou_hdr, so there's no point in re-reading it via the index. All OR conditions should be handled in the full scan.


Something like this:
select   /*+ no_use_concat */
         hdr.strvoutype strvoutype, hdr.strvouseries strvouseries,
         hdr.nfiscalyear nfiscalyear, hdr.nfiscalmonth nfiscalmonth,
         hdr.lvounbr lvounbr, hdr.dtvou dtvou, hdr.dtauth dtauth,
         hdr.nbasedoctype nbasedoctype, hdr.strbasedocnbr strbasedocnbr,
         hdr.nisposted nisposted, hdr.strcurrcd strcurrcd,
         hdr.dexchangerate dexchangerate, hdr.ntransind ntransind,
         hdr.strrefnbr strrefnbr, hdr.nrefind nrefind,
         hdr.strbrandcd strbrandcd, hdr.strclientcd strclientcd,
         hdr.strcreatedby strcreatedby, hdr.strrefvoutype strrefvoutype,
         hdr.strrefvouseries strrefvouseries,
         hdr.nreffiscalyear nreffiscalyear, hdr.lrefvounbr lrefvounbr,
         hdr.strrefbrandcd strrefbrandcd
FROM     fin_vou_hdr hdr,
LEFT JOIN com_rec_parallel_load crpl
ON       hdr.strbasedocnbr >= crpl.start_range
AND      hdr.strbasedocnbr <= crpl.end_range
WHERE    (   hdr.nisposted = 0
          OR hdr.nisposted IS NULL )
AND      (
             (    hdr.strvoutype IN ( 'JV', 'RV' )
              AND hdr.nvoustat = 3 )
          OR (    hdr.strvoutype = 'PV'
              AND hdr.nvoustat IN ( 7, 9, 14 ) )
          OR (
                  hdr.strvoutype = 'PV'
              AND hdr.nvoustat IN ( 3 )
              AND hdr.strrefvoutype > ' '
              AND hdr.strrefvouseries > ' '
              AND hdr.nreffiscalyear > 0
              AND hdr.lrefvounbr > 0
              AND hdr.strrefbrandcd > ' '
             )
         )
AND      hdr.strbasedocnbr = NVL ( :b4, hdr.strbasedocnbr )
ORDER BY hdr.dtvou,
         strrefvoutype DESC,
         strrefvouseries DESC,
         nreffiscalyear DESC,
         lrefvounbr DESC,
         strrefbrandcd DESC


Also see here for some hints on joining to range-keyed tables
Ross Leishman
Report message to a moderator
Re: Help - Slow running Quey - (Sort Merge Join) [message #224310 is a reply to message #224091] Tue, 13 March 2007 17:47 Go to previous messageGo to next message
orasaket
Messages: 70
Registered: November 2006
Member
Hi Ross and Gints,

In my database workarea_size_policy is auto

Also Seeing PGA_Aggregate_Target advice, it dosen't look like i will gain something by increasing pga memory

Also has join hash_join_enabled is set to True.

Ross, at present the change in the query is giving more cost also the plan still shows FTS on fin_vou_hdr.

Can we avoid CONCATENTION in the query by any means?

Thanks and Regards,
OraSaket
Report message to a moderator
Re: Help - Slow running Quey - (Sort Merge Join) [message #224323 is a reply to message #224310] Tue, 13 March 2007 20:55 Go to previous message
rleishman
Messages: 3364
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Hmmm. Looking at it again, I don't think the concatenation is a problem.

I thought it was one of the OR clauses causing the concatenation, but it is hdr.strbasedocnbr = NVL ( :b4, hdr.strbasedocnbr )

The bottom half of the plan is performing and index scan on strbasedocnbr, the top half is doing a FTS. If you supply a non-null :b4, the top half would not be run.

I can understand it being slow when :b4 is NULL, but when it is non null it should be a lot better, unless strbasedocnbr = :b4 returns a lot of rows or com_rec_parallel_load is large.

Are there overlapping ranges in com_rec_parallel_load? If not, try the sub-query technique from the link I supplied above. You will need to use the LEFT JOIN per my previous post as well.
Ross Leishman
Report message to a moderator
Previous Topic:Copy long raw to long raw from pl sql
Next Topic:index not getting dropped
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Sun Sep 05 21:03:40 CDT 2010

Total time taken to generate the page: 0.16801 seconds
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.