Home » RDBMS Server » Performance Tuning » Inconsistent query behaviour (9.2.0.6)
Inconsistent query behaviour [message #385957] Thu, 12 February 2009 00:43 Go to next message
jkmurthy
Messages: 22
Registered: May 2005
Location: India
Junior Member

Hi,

We have a query that is as below:

SELECT DISTINCT C.USER_ID, C.USER_NM FROM RETAILER B, GSS_USER C,FIMS_DOR D WHERE D.RETAILER_ID IN (SELECT RETAILER_ID FROM gss_USER_RETAILER_MAP WHERE USER_ID = 'COLIN.USER@TEST.COM' AND MANAGED_BY = 'Y') AND B.RETAILER_ID=D.RETAILER_ID AND (C.USER_ID = D.SALES_PERSON_ID OR C.USER_ID=D.FI_MANAGER_ID) AND C.ROLE_NM NOT IN ('CFO/DEALER ACCOUNTANT','DEALER ADMIN') AND DELIVERY_DT >= '01-JAN-2008' AND DELIVERY_DT <= '31-DEC-2008' AND NVL(CLOSE_MATCH_FLG,'N') = 'N'

The above query takes different amounts of time at different instances. This is used in a stored procedure and when the procedure was run on day1, it took 0.8 secs. On day3 and there on its taking 32 seconds. Could you please let me know what could be the issue ?
How do i find out whats causing this delay ? I would like to know if i can find out whether the CPU is overloaded at this time and any other factors that could be causing this.

Regards
Keshav
Re: Inconsistent query behaviour [message #386037 is a reply to message #385957] Thu, 12 February 2009 05:15 Go to previous message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
All sorts of things could be causing the difference.
I would suggest tracing the query and running statspack or awr reports.
Previous Topic: procedure tunning (merged)
Next Topic: Environment problem...
Goto Forum:
  


Current Time: Fri Dec 09 00:08:06 CST 2016

Total time taken to generate the page: 0.13481 seconds