Home » RDBMS Server » Performance Tuning » Query performance (Oracle 10g)
Query performance [message #349565] Mon, 22 September 2008 04:27 Go to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Hi,
Following query takes 3 hours to complete.
Also it allocates 17g Temporary tablespace during execution.

MASTERL tablesize 25g
MARKETING tablesize 30g.

Please tell me what changes needed in the following query to become faster.

SELECT COUNT (A.ID) SEARCHED_MASTERLS
  FROM CV_MASTERL A
 WHERE 1 = 1
   AND (    1 = 1
        AND (ID IN (
                SELECT /*+ INDEX(B) */
                       A.ID
                  FROM MASTERL A, MARKETING B
                 WHERE (B.FF_ID = '1' OR B.FF_ID IS NULL)
                   AND A.ID = B.MASTERL_ID(+)
                   AND NVL (UPPER (B.VALUE), '') LIKE '%PARTNER%')

            )
       )

Table structure
CREATE TABLE MASTERL
(
  ID                 NUMBER(12)                 NOT NULL primary key,
  DATABASE_ID        NUMBER(12)                 NOT NULL,
  MASTERLNO             VARCHAR2(15 BYTE),
  FIRSTNAME          VARCHAR2(255 BYTE)
)

CREATE INDEX MASTERL_DATABASE_FK_I ON MASTERL (DATABASE_ID)

CREATE TABLE MARKETING
(
  DB_ID         NUMBER(12)                      NOT NULL,
  FF_ID         NUMBER(12)                      NOT NULL,
  MASTERL_ID       NUMBER(12)                      NOT NULL,
  VALUE         VARCHAR2(255 BYTE)
)
CREATE INDEX MARKETING_DB_ID_IDX ON MARKETING (DB_ID)
CREATE INDEX MARKETING_IDX ON MARKETING (MASTERL_ID, DB_ID)



Operation	 	   	   	   		  			 	  	   		 	OBJECT NAME		  				ROWS 	Bytes	   COST		   OBJECT Node		IN/OUT		PStart		PStop

-SELECT STATEMENT Optimizer MODE=ALL_ROWS		  		 				   			  			  			1  		   14 M	 	      	             	 
	  -SORT 	  																			1  	58  	 	 	      	             	 
	   	-HASH JOIN RIGHT OUTER																		124 K	6 M	   14 M	 	      	             	 
		  	  INDEX FAST FULL SCAN									CALC.CAMPAIGN_PK						20 K	99 K	   15  	 	      	             	 
			-HASH JOIN SEMI																		124 K	6 M	   14 M	 	      	             	 
			 	 -TABLE ACCESS BY INDEX ROWID							CALC.MASTERL							7 K	90 K	   2167  	 	      	             	 
				 	-NESTED OOPS																527 K	20 M	   31967  	 	      	             	 
						-NESTED LOOPS															69  	1 K	   22  	 	      	             	 
							-TABLE ACCESS BY INDEX ROWID				CALC.USERS							1  	15  	   2  	 	      	             	 
								   INDEX RANGE SCAN				CALC.USERS_DBUSERNAME_I						1  	  	   1  	 	      	             	 
							-TABLE ACCESS BY INDEX ROWID				CALC.DATABASE							69  	897  	   20  	 	      	             	 
								   INDEX RANGE SCAN				CALC.DATABASE_CLIENT_FK_I					43  	 	   1  	 	      	             	 
						INDEX RANGE SCAN	   					CALC.MASTERL_DATABASE_FK_I					60 K	 	   213  	 	      	             	 
				  -VIEW	SYS.VW_NSO_1															   	616 M	7G	   14 M	 	      	             	 
				  	-FILTER		  	 	 	 	      	             	 
						-HASH JOIN OUTER								 						616 M	12G	   14 M	 	      	             	 
							INDEX FAST FULL SCAN					CALC.MASTERLS_PK						222 M	1G	   167570  	 	      	             	 
						  -TABLE ACCESS BY INDEX ROWID					CALC.MARKETING							600 M	7G	   12 M	 	      	             	 
						    INDEX FULL SCAN		 				CALC.FORMFETLVALUS_DB_ID_IDX					600 M	 	   2067451  	 	      	             	 
	   	   


Thanks,

[Updated on: Mon, 22 September 2008 04:34]

Report message to a moderator

Re: Query performance [message #349590 is a reply to message #349565] Mon, 22 September 2008 05:23 Go to previous messageGo to next message
joshirish
Messages: 3
Registered: September 2008
Junior Member
You may want to consider
1. Replacing the ID IN (A join B) with
CV_MASTERL INTERSECT MASTERL INTERSECT MARKETING
2. Replace the (B.FF_ID = '1' OR B.FF_ID IS NULL) with COALESCE/CASE
3. Try to discourage %SEARCHSTRING% queries (or use full text search indexes)

--Sjo

[Updated on: Mon, 22 September 2008 05:24]

Report message to a moderator

Re: Query performance [message #349599 is a reply to message #349565] Mon, 22 September 2008 05:58 Go to previous messageGo to next message
ora_2007
Messages: 430
Registered: July 2007
Location: Mumbai
Senior Member
Thanks joshirish for your comments.
But it doesnt help in the current scenario.

Please tell me other solutions.
I think the problem is with the inner query.
But

SELECT A.ID
FROM MASTERL A, MARKETING B
WHERE (B.FF_ID = '1' OR B.FF_ID IS NULL)
AND A.ID = B.MASTERL_ID(+)
AND NVL (UPPER (B.VALUE), '') LIKE '%PARTNER%'  ---This condition causes slowdown. How can this be improved?


Thanks,
Re: Query performance [message #349602 is a reply to message #349565] Mon, 22 September 2008 06:08 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
try this select
SELECT COUNT (A.ID) SEARCHED_MASTERLS
FROM CV_MASTERL A
INNER JOIN MASTERL A1 on A.ID=A1.ID
LEFT JOIN MARKETING B ON A1.ID = B.MASTERL_ID
                         AND NVL (UPPER (B.VALUE), '') LIKE '%PARTNER%')
WHERE  NVL(B.FF_ID,'1') = '1'


and show execution plan and time
Re: Query performance [message #349606 is a reply to message #349565] Mon, 22 September 2008 06:13 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
and one more select regarding your last post
SELECT A.ID
FROM MASTERL A
LEFT JOIN (SELECT B.MASTERL_ID, B.FF_ID 
           FROM  MARKETING B
           WHERE NVL(B.FF_ID,'1') = '1'
                AND  UPPER (B.VALUE) LIKE '%PARTNER%') C ON C.MASTERL_ID=A.ID
WHERE NVL(C.FF_ID,'1') = '1'


there must be only HASH join, no nested loops
Re: Query performance [message #349661 is a reply to message #349599] Mon, 22 September 2008 09:56 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
In your query:
SELECT A.ID
FROM MASTERL A, MARKETING B
WHERE (B.FF_ID = '1' OR B.FF_ID IS NULL)
AND A.ID = B.MASTERL_ID(+)
AND NVL (UPPER (B.VALUE), '') LIKE '%PARTNER%'

The Outer join serves only to restirct the optimizers set of options - you'll get all the Ids in table A - that's what an outer join does.

Your query is funtionally the same as
SELECT COUNT (A.ID) SEARCHED_MASTERLS
  FROM CV_MASTERL A
 WHERE ID IN (SELECT B.ID
              FROM MASTERL B)
Re: Query performance [message #349665 is a reply to message #349661] Mon, 22 September 2008 10:27 Go to previous messageGo to next message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
JRowbottom wrote on Mon, 22 September 2008 09:56
In your query:
SELECT A.ID
FROM MASTERL A, MARKETING B
WHERE (B.FF_ID = '1' OR B.FF_ID IS NULL)
AND A.ID = B.MASTERL_ID(+)
AND NVL (UPPER (B.VALUE), '') LIKE '%PARTNER%'

The Outer join serves only to restirct the optimizers set of options - you'll get all the Ids in table A - that's what an outer join does.

Your query is funtionally the same as
SELECT COUNT (A.ID) SEARCHED_MASTERLS
  FROM CV_MASTERL A
 WHERE ID IN (SELECT B.ID
              FROM MASTERL B)



I do not agree

see predicate in where clause " (B.FF_ID = '1' OR B.FF_ID IS NULL)"

it shows that query gets from outer join rows where table A was joined with table B and B.FF_ID = '1' OR B.FF_ID IS NULL, AND table A was not joined with table B then B.FF_ID will be NULL and it fit to where clause predicate.

Re: Query performance [message #349799 is a reply to message #349665] Tue, 23 September 2008 03:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The lines
(B.FF_ID = '1' OR B.FF_ID IS NULL)
is a poor mans version of the oracle outer join notation.
The Outer Join record returned if there is no match consists entirely of null values, so these clauses are just taking account of that possibility.

It is syntactically the same, but less clear than
B.FF_ID(+) = '1'
Re: Query performance [message #349801 is a reply to message #349799] Tue, 23 September 2008 03:10 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Looking at it again, there is a problem with the outer join in the original query. This line
AND NVL (UPPER (B.VALUE), '') LIKE '%PARTNER%'
will completely undo the effects of the outer join, as null rows from table B will not be matched.
That would make the original query the same as this:
SELECT COUNT (A.ID) SEARCHED_MASTERLS
  FROM CV_MASTERL A
 WHERE ID IN (SELECT A.ID
              FROM   MASTERL A, MARKETING B
              WHERE  B.FF_ID = '1'
              AND    A.ID = B.MASTERL_ID)
              AND    UPPER (B.VALUE) LIKE '%PARTNER%')


Rewrit that like this:
SELECT COUNT (A.ID) SEARCHED_MASTERLS
  FROM CV_MASTERL A
 WHERE ID IN (SELECT A.ID
              FROM   MASTERL A, MARKETING B
              WHERE  B.FF_ID = '1'
              AND    A.ID = B.MASTERL_ID)
              AND    INSTR(B.VALUE,'PARTNER')>0

and create an index on Marketing(MASTERL_ID,FF_ID,INSTR(B.VALUE,'PARTNER') and you hould see some improvements.
Re: Query performance [message #349996 is a reply to message #349565] Tue, 23 September 2008 13:13 Go to previous message
Kriptas
Messages: 118
Registered: June 2008
Senior Member
maybe i do not understand you clearly, but I dissagree with you on:
The lines (B.FF_ID = '1' OR B.FF_ID IS NULL)is a poor mans version of the oracle outer join notation.
The Outer Join record returned if there is no match consists entirely of null values, so these clauses are just taking account of that possibility.

It is syntactically the same, but less clear thanB.FF_ID(+) = '1'

lest say, table B and table A was joined,
and B.FF_ID='2' (and other value then '1' and null), so in orginal query that rows will be filtered out, in yours query will be not.
i think that oracle convert character type '1' to 1 and join tables if there is match... but that will cost performance.
Previous Topic: First_rows Versus All_rows
Next Topic: procedure tuning
Goto Forum:
  


Current Time: Wed Dec 07 05:04:55 CST 2016

Total time taken to generate the page: 0.09466 seconds