Performance issue

Hi There, users for one of our systems have been reporting major performance issues for a few weeks.
I am fairly new to Oracle yet end up being the most experienced person here to deal with it.
The database is on Sun Solaris 5.10
oracle version 9.2.0.8

All tablespaces appear to have plenty space. There is plenty physical space on the server.
One thing i have noticed when comparing with other databases is that the large pool size is set to 0 whereas our other databases are set at either 16MB or 64MB for the large pool. Is this something that could cause the performance issue? I don't really want to meddle with stuff i don't understand but equally i don't have the time to sit and read about it.
Shared Pool 288MB
Buffer Cachew 304MB
Large Pool 0MB
Java Pool 16MB
Total SGA 610.258MB

Any ideas?

only need a large pool if you have processes which need it (so probbaly no difference)

you need to run statspack, see what your wait events are and deal with them

Quote:

---

Originally Posted by philthee

Any ideas?

---

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?

Quote:

---

Originally Posted by PAVB

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

Code:

---

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.