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 126.96.36.199
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
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
Let me give you a complementary path to address the issue meaning, you have to follow Dave's sugestion.
Originally Posted by philthee
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?
Pablo (Paul) Berzukov
Author of Understanding Database Administration
available at amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
Thanks for reply (you too Dave)
Originally Posted by PAVB
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
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.
exec dbms_stats.gather_schema_stats(ownname =>'TASKDBA',estimate_percent => NULL
,method_opt => 'FOR ALL COLUMNS SIZE SKEWONLY',cascade => TRUE);
Click Here to Expand Forum to Full Width