Home » RDBMS Server » Performance Tuning » Optimizing the query (10.2.0.3.0)
Optimizing the query [message #393577] Mon, 23 March 2009 07:43 Go to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi All,

I have written a query. But It's taking around 15 minutes. Can you please check the query and Explain plan of the query and give me the solution for optimize the query.
Query :
SELECT *
  FROM temp.r_view
 WHERE cur IN (SELECT p_id_d
                      FROM temp.pm
                     WHERE p_id_h = 'CURRENCY' AND p_txt 
                               = 'EUR')
   AND rec_amt >= 10
   AND r_view.date_exec >
          TO_DATE ((SELECT p_txt
                      FROM temp.pm
                     WHERE p_id_h = 'MONETARY'
                       AND p_id_d = 'LAST_DATE'
                       AND p_id_t = 'S'),
                   'DD-MON-YYYY'
                  )
   AND SID NOT IN (
          SELECT gl_sid
            FROM s_wr.p_smst
           WHERE p_gst.p_idc =
                       (SELECT p_txt
                          FROM temp.pm
                         WHERE p_id_h  = 'MONETARY' 
                         AND   pm_id_d = 'MONETARY_ID'));

OPERATION	OPTIONS	    OBJECT_NAME 	POSITION
--------------------------  ------------ -------------
SELECT STATEMENT			         199600
FILTER		                	          1
HASH JOIN	RIGHT SEMI		          1
TABLE ACCESS	FULL	        PM	          1
VIEW		R_VIEW	                          2
UNION-ALL			                  1
TABLE ACCESS	FULL	        IN_CUST_TEMP	  1
TABLE ACCESS	FULL	        RC_REG_WORK	  2
HASH            JOIN		                  3
VIEW		                V_SQ_1	          1
HASH	        GROUP BY		                  1
TABLE ACCESS	FULL	        TP_MAIN_WORK	  1
TABLE ACCESS	FULL	        TP_MAIN_WORK	  2
TABLE ACCESS	FULL	        IN_SS_RELOAD	  4
TABLE ACCESS	FULL	        CTF_AORD_WORK	  5
TABLE ACCESS	FULL	        MANXA_RELOAD	  6
TABLE ACCESS	FULL	        STX_FR_PURSE	  7
TABLE ACCESS	BY INDEX ROWID	PM	          2
INDEX	        RANGE SCAN	K_PRM             1
TABLE ACCESS	BY INDEX ROWID	P_GST	          2
INDEX	        RANGE SCAN	ID1_P_GST         1
TABLE ACCESS	BY INDEX ROWID	PM	          1
INDEX	        RANGE SCAN	K_PM	          1

Find the explain plan in the attached doc.

thank you,

[Updated on: Mon, 23 March 2009 07:45]

Report message to a moderator

Re: Optimizing the query [message #393578 is a reply to message #393577] Mon, 23 March 2009 07:46 Go to previous messageGo to next message
babuknb
Messages: 1734
Registered: December 2005
Location: NJ
Senior Member


Post your explain plan statistics report.

Babu
Re: Optimizing the query [message #393580 is a reply to message #393577] Mon, 23 March 2009 07:46 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Since we don't know ANYTHING about how big your tables are and what indexes you have, we can't suggest anything meaningful.

So you have to go and buy faster hardware.

Re: Optimizing the query [message #393582 is a reply to message #393580] Mon, 23 March 2009 07:51 Go to previous messageGo to next message
user71408
Messages: 585
Registered: November 2007
Location: NE
Senior Member

Hi ,

The tables is having 60089 records and it is having 17 columns.

Thank you.
Re: Optimizing the query [message #393584 is a reply to message #393580] Mon, 23 March 2009 07:55 Go to previous messageGo to next message
ora_baby
Messages: 89
Registered: May 2008
Member
IN -> EXISTS
what is r_view.date_exec?

Re: Optimizing the query [message #393585 is a reply to message #393582] Mon, 23 March 2009 07:55 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Then you should buy REALLY fast hardware.
Re: Optimizing the query [message #393799 is a reply to message #393577] Tue, 24 March 2009 07:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The first thing I'd do would be to use DBMS_STATS to gather statistics on all the tables used in the query.

That way the CBO might be able to make a better execution plan.
Re: Optimizing the query [message #393802 is a reply to message #393577] Tue, 24 March 2009 07:31 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Come on OP - you've got nearly 500 posts - you know what we need if we're going to have a chance of helping you.
Previous Topic: optimisation of queries
Next Topic: Performance tuning
Goto Forum:
  


Current Time: Thu Dec 08 08:13:49 CST 2016

Total time taken to generate the page: 0.08320 seconds