OraFAQ Forum: Performance Tuning » How to make this query fast?

Home » RDBMS Server » Performance Tuning » How to make this query fast?

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

How to make this query fast? [message #132102]

Wed, 10 August 2005 18:19

oracle_user_2005
Messages: 6
Registered: August 2005

Junior Member

This is my first post to your forum. Presently this query takes 3 minutes 14 secs to come back with 100 rows if I give it a bind variable of 100 :endrow. Can someone please suggest me an alternate way to write this to make it faster or some hint.

select *
FROM
(
SELECT
*
FROM
(
SELECT
INNER.*,
ROWNUM
AS QUERY_ROWNUM
FROM
(
SELECT --+
COUNT(*) OVER() AS TOTAL_ROWS,
NVL(MT.TRNSF_FL, 'N') AS TRNSF_FL,
MT.MASS_TRNSF_INDVL_PK,
IND.INDVL_PK,
IND.LAST_NM,
IND.FIRST_NM,
IND.MID_NM,
IFA.ORG_PK,
IFA.RR_FL,
IFA.IAR_FL,
IFA.BACK_OFC_FL
FROM
INDVL IND,
INDVL_FIRM_ASCTN IFA,
(
SELECT
MTI.MASS_TRNSF_INDVL_PK,
MTI.INDVL_PK,
MTI.TRNSF_FL
FROM
MASS_TRNSF_PRDSR MTP,
MASS_TRNSF_INDVL MTI
WHERE
MTP.MASS_TRNSF_PK = :i_MASS_TRNSF_PK AND
MTP.ORG_PK = :i_ORG_PK AND
MTP.MASS_TRNSF_PRDSR_PK = MTI.MASS_TRNSF_PRDSR_PK
) MT
WHERE
IND.LAST_NM LIKE :l_STARTS_WITH AND
IFA.ORG_PK = :i_ORG_PK AND
IFA.INDVL_FIRM_ST_CD = 'ACTIVE' AND
IFA.PAGE_2_FL = 'N' AND
(IFA.RR_FL = 'Y' OR
IFA.BACK_OFC_FL = 'Y' ) AND
IFA.INDVL_PK = IND.INDVL_PK AND
MT.INDVL_PK (+) = IND.INDVL_PK
ORDER BY
IND.LAST_NM,
IND.FIRST_NM,
IND.MID_NM
) INNER
WHERE
ROWNUM <= :EndRow
) OUTER
WHERE
OUTER.QUERY_ROWNUM >= :StartRow
) B

This is the explain plan:

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

SELECT STATEMENT Optimizer Mode=CHOOSE 24 21
VIEW 24 2 K 21
COUNT STOPKEY
VIEW 24 2 K 21
WINDOW SORT 24 1 K 21
HASH JOIN OUTER 24 1 K 21
NESTED LOOPS 24 1 K 18
TABLE ACCESS BY INDEX ROWID CRDMAIN.INDVL_FIRM_ASCTN 24 576 9
INDEX RANGE SCAN CRDMAIN.XIE2INDVL_FIRM_ASCTN 63 3
TABLE ACCESS BY INDEX ROWID CRDMAIN.INDVL 1 27 1
INDEX UNIQUE SCAN CRDMAIN.XPKINDVL 1 1
VIEW 1 13 2
TABLE ACCESS BY INDEX ROWID CRDMAIN.MASS_TRNSF_INDVL 81 1 K 1
NESTED LOOPS 1 31 2
TABLE ACCESS BY INDEX ROWID CRDMAIN.MASS_TRNSF_PRDSR 1 14 1
INDEX RANGE SCAN CRDMAIN.XIF1MASS_TRNSF_PRDSR 1 1
INDEX RANGE SCAN CRDMAIN.XIF1MASS_TRNSF_INDVL 81

Report message to a moderator

Re: How to make this query fast? [message #132122 is a reply to message #132102]

Wed, 10 August 2005 21:00

Art Metzer
Messages: 2480
Registered: December 2002

Senior Member

How does your query perform without the line

 COUNT(*) OVER() AS TOTAL_ROWS,

Report message to a moderator

Re: How to make this query fast? [message #132256 is a reply to message #132122]

Thu, 11 August 2005 10:22

oracle_user_2005
Messages: 6
Registered: August 2005

Junior Member

I commented out the line as you suggested but still the query is very slow. Can you please suggest me any hints or any other way to rewrite this query to make it faster after looking at the explain plan.

Report message to a moderator

Re: How to make this query fast? [message #132263 is a reply to message #132256]

Thu, 11 August 2005 10:42

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

What is oracle version/ OS version?
Hash Outer joins has problems.
Can you convert the query to use an inner join and try again?
please Post your code/statspack/trace/plan etc within CODE
So we get see them formatted.

Report message to a moderator

Re: How to make this query fast? [message #132270 is a reply to message #132263]

Thu, 11 August 2005 11:35

oracle_user_2005
Messages: 6
Registered: August 2005

Junior Member

Mahesh,
Thank You for your reply. For my requirement I do need an outer join with MT.INDVL_PK (+)=IND.INDVL_PK. Can you suggest me a workaround to avoid the outer hash join? I am submitting the query and explain plan within [code] now.

Thank You


select  
*
FROM
	(
	SELECT
		*
	FROM
		(
		SELECT
			INNER.*,
			ROWNUM
				AS QUERY_ROWNUM
		FROM
			( 
			SELECT	
         	COUNT(*) OVER() 			AS	TOTAL_ROWS,
	        	NVL(MT.TRNSF_FL, 'N') AS TRNSF_FL,
            MT.MASS_TRNSF_INDVL_PK,
            IND.INDVL_PK,
	         IND.LAST_NM,
	         IND.FIRST_NM,
	         IND.MID_NM,
            IFA.ORG_PK,
	         IFA.RR_FL,
	         IFA.IAR_FL,
	         IFA.BACK_OFC_FL 
			FROM
	         	INDVL					IND,
	            INDVL_FIRM_ASCTN	IFA,
	            (
	            SELECT
	            	MTI.MASS_TRNSF_INDVL_PK,
	               MTI.INDVL_PK,
	               MTI.TRNSF_FL
	            FROM
	              	MASS_TRNSF_PRDSR			MTP,
	            	MASS_TRNSF_INDVL			MTI
	            WHERE
	            	MTP.MASS_TRNSF_PK			= 	:i_MASS_TRNSF_PK			AND
	               MTP.ORG_PK					=	:i_ORG_PK					AND
	               MTP.MASS_TRNSF_PRDSR_PK	= 	MTI.MASS_TRNSF_PRDSR_PK
	            ) MT
			WHERE
         	IND.LAST_NM					LIKE	:l_STARTS_WITH				AND 
            IFA.ORG_PK					=		:i_ORG_PK				AND
         	IFA.INDVL_FIRM_ST_CD		= 		'ACTIVE'	AND
            IFA.PAGE_2_FL				=		'N' AND 
	      	(IFA.RR_FL					= 	'Y'			OR
            IFA.BACK_OFC_FL			= 	'Y'			) AND 
	            IFA.INDVL_PK				= 		IND.INDVL_PK			AND
	            MT.INDVL_PK (+)			=		IND.INDVL_PK
			ORDER BY
         	IND.LAST_NM,
            IND.FIRST_NM,
            IND.MID_NM
			) INNER
		WHERE
			ROWNUM <= :EndRow
		) OUTER
	WHERE
		OUTER.QUERY_ROWNUM >= :StartRow	
	)								B	
	
	
	
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		24  	 	21  	 	      	             	 
  VIEW		24  	2 K	21  	 	      	             	 
    COUNT STOPKEY		  	 	 	 	      	             	 
      VIEW		24  	2 K	21  	 	      	             	 
        WINDOW SORT		24  	1 K	21  	 	      	             	 
          HASH JOIN OUTER		24  	1 K	21  	 	      	             	 
            NESTED LOOPS		24  	1 K	18  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	CRDMAIN.INDVL_FIRM_ASCTN	24  	576  	9  	 	      	             	 
                INDEX RANGE SCAN	CRDMAIN.XIE2INDVL_FIRM_ASCTN	63  	 	3  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	CRDMAIN.INDVL	1  	27  	1  	 	      	             	 
                INDEX UNIQUE SCAN	CRDMAIN.XPKINDVL	1  	 	1  	 	      	             	 
            VIEW		1  	13  	2  	 	      	             	 
              TABLE ACCESS BY INDEX ROWID	CRDMAIN.MASS_TRNSF_INDVL	81  	1 K	1  	 	      	             	 
                NESTED LOOPS		1  	31  	2  	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	CRDMAIN.MASS_TRNSF_PRDSR	1  	14  	1  	 	      	             	 
                    INDEX RANGE SCAN	CRDMAIN.XIF1MASS_TRNSF_PRDSR	1  	 	1  	 	      	             	 
                  INDEX RANGE SCAN	CRDMAIN.XIF1MASS_TRNSF_INDVL	81

Report message to a moderator

Re: How to make this query fast? [message #132276 is a reply to message #132270]

Thu, 11 August 2005 12:55

oracle_user_2005
Messages: 6
Registered: August 2005

Junior Member

Mahesh,

You were absolutely right. When I remove the outer join , the query becomes extremely fast. But I do need the outer join as per the requirements. Here is the explain plan when I remove the outer join. Can you suggest me any hint or a new way to write this query so I can mimic this plan:

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

SELECT STATEMENT Optimizer Mode=CHOOSE		1  	 	4  	 	      	             	 
  VIEW		1  	116  	4  	 	      	             	 
    COUNT STOPKEY		  	 	 	 	      	             	 
      VIEW		1  	103  	4  	 	      	             	 
        WINDOW SORT		1  	82  	4  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CRDMAIN.INDVL_FIRM_ASCTN	1  	24  	1  	 	      	             	 
            NESTED LOOPS		1  	82  	4  	 	      	             	 
              NESTED LOOPS		1  	58  	3  	 	      	             	 
                NESTED LOOPS		1  	31  	2  	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	CRDMAIN.MASS_TRNSF_PRDSR	1  	14  	1  	 	      	             	 
                    INDEX RANGE SCAN	CRDMAIN.XIF1MASS_TRNSF_PRDSR	1  	 	1  	 	      	             	 
                  TABLE ACCESS BY INDEX ROWID	CRDMAIN.MASS_TRNSF_INDVL	81  	1 K	1  	 	      	             	 
                    INDEX RANGE SCAN	CRDMAIN.XIF1MASS_TRNSF_INDVL	81  	 	 	 	      	             	 
                TABLE ACCESS BY INDEX ROWID	CRDMAIN.INDVL	1  	27  	1  	 	      	             	 
                  INDEX UNIQUE SCAN	CRDMAIN.XPKINDVL	1  	 	1  	 	      	             	 
              INDEX RANGE SCAN	CRDMAIN.XPKINDVL_FIRM_ASCTN	1  	 	2

Report message to a moderator

Re: How to make this query fast? [message #132280 is a reply to message #132276]

Thu, 11 August 2005 13:33

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

But, generally hash joins are better than the nested loops.
Now you are considering only 100 rows. If the records are more, a hash join should be better.
We need to understand about data distribution here.
You still havent given the oracle version/os.
Enable tracing with a level 8 (level 12 if using bind variables).
Generate the tkprof output.
Post the tkprof output.

and
THe only method to understand what acctually happens inside a hashjoin is toenable event 10104.

alter session set events '10046 trace name context forever, level 12';
alter session set events '10104 trace name context forever, level 1';

Report message to a moderator

Re: How to make this query fast? [message #132298 is a reply to message #132280]

Thu, 11 August 2005 16:19

oracle_user_2005
Messages: 6
Registered: August 2005

Junior Member

Mahesh,

The Db version is 9.2.0.6 and the os is SUN. I am uploading the output of tkprof. Please suggest me if you find anything from this info.
Thx

Attachment: TEST.prf
(Size: 44.82KB, Downloaded 1502 times)

Report message to a moderator

Re: How to make this query fast? [message #132316 is a reply to message #132298]

Thu, 11 August 2005 19:50

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

1. do a little change in code.
Use more bind variables, wherever possible.
2. what is the value of pga_aggregate_target
Increase it significantly (default would be around 24 m.
start with a minimum 200m . Got upt 1024m).
and WORKAREA_SIZE_POLICY should be AUTO.
3. try again (with trace enabled).

Before doing any of this, apply caution. Never do this to production database.
First create a pfile from spfile.
secure this pfile.
create another_pfile from sfpile.
do all changes only this another_spfile. startup and mount the database with this another_pfile. thus if we want to revert it will be easy.
And Tkprof will not show the hashjoin information.
you will be seeing that inside the raw tracefile and it will start something like

*** HASH JOIN STATISTICS (INITIALIZATION) ***
..
...
....
manyentries
...
*** END OF HASH JOIN BUILD (PHASE 1) ***
followed by some bucket information.
Please post it. or email the whole trc file.

Report message to a moderator

Re: How to make this query fast? [message #132371 is a reply to message #132298]

Fri, 12 August 2005 04:44

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Please gothrough this discussion and see whether it helps
http://asktom.oracle.com/pls/ask/f?p=4950:8:12437389681828441939::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:76812348057

Report message to a moderator

Re: How to make this query fast? [message #133131 is a reply to message #132371]

Wed, 17 August 2005 15:36

oracle_user_2005
Messages: 6
Registered: August 2005

Junior Member

Mahesh,

When I run the query by adding a hint ( --+ FULL(MTP) )to the inline view MT and I comment out count over () and order by the query comes back in 1 sec. I am still working on sending you the trace file output.
If this gives your some new insight please let me know.

Here is the query:


SELECT * 
FROM
	(
	SELECT
		*
	FROM
		(
		SELECT
			INNER.*,
			ROWNUM
				AS QUERY_ROWNUM
		FROM
			( 
			SELECT	--+  	
         	--COUNT(*) OVER() 			AS	TOTAL_ROWS,
	        	NVL(MT.TRNSF_FL, 'N') AS TRNSF_FL,
            MT.MASS_TRNSF_INDVL_PK,
            IND.INDVL_PK,
	         IND.LAST_NM,
	         IND.FIRST_NM,
	         IND.MID_NM,
            IFA.ORG_PK,
	         IFA.RR_FL,
	         IFA.IAR_FL,
	         IFA.BACK_OFC_FL 
			FROM  
	         	INDVL					IND,
	            INDVL_FIRM_ASCTN	IFA,
	            (  
	            SELECT --+ FULL( MTP ) 
	            	MTI.MASS_TRNSF_INDVL_PK,
	               MTI.INDVL_PK,
	               MTI.TRNSF_FL
	            FROM
	              	MASS_TRNSF_PRDSR			MTP,
	            	MASS_TRNSF_INDVL			MTI
	            WHERE
	            	MTP.MASS_TRNSF_PK			= 	:i_MASS_TRNSF_PK			AND
	               MTP.ORG_PK					=	:i_ORG_PK					AND
	               MTP.MASS_TRNSF_PRDSR_PK	= 	MTI.MASS_TRNSF_PRDSR_PK	
	            ) MT
			WHERE  
            IFA.ORG_PK					=		:i_ORG_PK				AND	
         	IFA.INDVL_FIRM_ST_CD		= 		'ACTIVE'	AND
            IFA.PAGE_2_FL				=		'N' AND          
	      	( IFA.RR_FL					= 	'Y'	OR 
              IFA.BACK_OFC_FL			= 	'Y'		)	AND 	         
            IFA.INDVL_PK				= 	IND.INDVL_PK			AND
            MT.INDVL_PK (+)			=	IND.INDVL_PK
-- 			ORDER BY 	
--          	IND.LAST_NM,
--             IND.FIRST_NM,
--             IND.MID_NM
			) INNER
		WHERE
			ROWNUM <= :EndRow
		) OUTER
	WHERE
		OUTER.QUERY_ROWNUM >= :StartRow	
	)								B	

Here is the explain plan:
Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Optimizer Mode=CHOOSE		24  	 	23  	 	      	             	 
  VIEW		24  	2 K	23  	 	      	             	 
    COUNT STOPKEY		  	 	 	 	      	             	 
      NESTED LOOPS OUTER		24  	1 K	23  	 	      	             	 
        NESTED LOOPS		24  	1 K	18  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CRDMAIN.INDVL_FIRM_ASCTN	24  	576  	9  	 	      	             	 
            INDEX RANGE SCAN	CRDMAIN.XIE2INDVL_FIRM_ASCTN	63  	 	3  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CRDMAIN.INDVL	1  	27  	1  	 	      	             	 
            INDEX UNIQUE SCAN	CRDMAIN.XPKINDVL	1  	 	1  	 	      	             	 
        VIEW PUSHED PREDICATE		1  	13  	1  	 	      	             	 
          TABLE ACCESS BY INDEX ROWID	CRDMAIN.MASS_TRNSF_INDVL	1  	17  	1  	 	      	             	 
            NESTED LOOPS		1  	31  	5  	 	      	             	 
              TABLE ACCESS FULL	CRDMAIN.MASS_TRNSF_PRDSR	1  	14  	4  	 	      	             	 
              INDEX RANGE SCAN	CRDMAIN.XIF1MASS_TRNSF_INDVL	81

Report message to a moderator

Previous Topic:	slow server
Next Topic:	Selecivity on like in a query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Nov 23 16:40:33 CST 2024