Performance issue

[philthee]

Let me give you a complementary path to address the issue meaning, you have to follow Dave's sugestion.

Your posting suggests performance was good until a few weeks ago when it deteriorated, is that a fact?

In the affirmative case you may want to find out what happened around the time performance went bad.

- Any major data load?
- A new application went live?
- Did you lost some indexes?
- Are your performance stats up-to-date?
- Any kind of maintenance or reorganization happened around that time?

A follow up question would be... did performance got deteriorated and then got in a bad but stable state or does performance continues to deteriorate over time?

Thanks for reply (you too Dave)
I have taken Daves advice and installed Statspack and given a few runs. It comes up with numerous recommendations. I am going to try out one at a time and see what (if any) performance gains it gives. This is a bought in application with an SLA so i don't think we can mess with the SQL it runs, that would be up to the vendor. First off i have doubled the db_cache_size as the report seemed to suggest would reduce disk reads by about 70%.

As for your questions, it was brought to my attention that there were some serious performance problems just before christmas. On further enquiries it turns out this had been going on for about a month or so but no one had asaid anything, i think perhaps had been getting steadily worse.

I don't think there has been any major dataload or loss of indexes.
Performance stats are run every weekend through this

exec dbms_stats.gather_schema_stats(ownname =>'TASKDBA',estimate_percent => NULL
,method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE);

There has been a lot of server virtualization going on over the last few months here so there could be an issue there. This application has about 7 related servers some of which could have been virtualised.