Home » RDBMS Server » Performance Tuning » Tuning suggestions on query (Oracle 11g;window2007)
Tuning suggestions on query [message #605196] Tue, 07 January 2014 23:41 Go to next message
mohan1760
Messages: 59
Registered: June 2008
Member
Hi all,
 
Any suggestions on tuning below query.

SELECT A.AMT_MATURITY
  FROM AMT_HISTORY A
 WHERE A.ACCT_NO = 100252652    
   AND A.DEP_NO = 5
   AND A.DAT_CALC = (SELECT MAX(DAT_CALC)
                       FROM AMT_HISTORY
                      WHERE COD_ACCT_NO = A.ACCT_NO
                        AND COD_DEP_NO = A.DEP_NO);

Explain plan:

SELECT STATEMENT, GOAL = ALL_ROWS			10	1	100
 FILTER					
  SORT GROUP BY			10	1	100
   HASH JOIN			5	1	100
    TABLE ACCESS FULL	HOST1	AMT_HISTORY	2	1	60
    TABLE ACCESS FULL	HOST1	AMT_HISTORY	2	2	80

Thanks in advance..

*BlackSwan added {code} tags. Please do so yourself in the future.

[Updated on: Tue, 07 January 2014 23:51] by Moderator

Report message to a moderator

Re: Tuning suggestions on query [message #605218 is a reply to message #605196] Wed, 08 January 2014 02:34 Go to previous messageGo to next message
cookiemonster
Messages: 13917
Registered: September 2008
Location: Rainy Manchester
Senior Member
According to the plan that query is pretty quick, how long does it actually take?
Re: Tuning suggestions on query [message #605284 is a reply to message #605196] Wed, 08 January 2014 07:11 Go to previous messageGo to next message
michael_bialik
Messages: 621
Registered: July 2006
Senior Member
1. Are stats up to date?
2. How many rows in AMT_HISTORY table?
3. Can you try index
CREATE INDEX ... ON AMT_HISTORY ( ACCT_NO, DEP_NO, DAT_CALC ) COMPUTE STATISTICS ...
?
Re: Tuning suggestions on query [message #605557 is a reply to message #605284] Fri, 10 January 2014 08:35 Go to previous message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
1. how many rows are in table AMT_HISTORY
2. how many rows satisfy the query predicates (A.ACCT_NO = 100252652 AND A.DEP_NO = 5)
3. is there an index on columns (ACCT_NO,DEP_NO,DAT_CALC)
4. are the above three columns UNIQUE for this table

A query plan is next to useless without the associated PREDICATE INFO. Please provide both.

Kevin

[Updated on: Fri, 10 January 2014 08:36]

Report message to a moderator

Previous Topic: Performance issue
Next Topic: Checking Lock history
Goto Forum:
  


Current Time: Fri Mar 29 09:06:38 CDT 2024