Do you know what changed?

"The database has been running for over two years without performance problems. Now some users have started to complain that some queries have started to take too long."

Do you know what changed? Maybe in the two years you've grown your data size, added new custom reports/form, increased your use bases.

It is important to generate and review execution plans for all of the key SQL statements in your application. Doing so let you compare the optimizer execution plans of a SQL statement when the statement performed well to the plan when that the statement is not performing well. Having the comparison, along with information such as changes in data volumes, can assist in identifying the cause of performance degradation.

For now I would suggest that you sort 'some queries that users complained' in certain order, then conquer them one by one. Performing 10046 trace, level 12 will tell you where the time was spent on. Then try whatever you can to bring down the waits.

By the way if you use statspack and found out most of expensive sqls are from spotlight then you probably consider to stop using it.

HTH,
http://www.webyourcompany.com Received on Tue Oct 21 2003 - 17:21:56 CDT