Performance issue
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Performance issue

  1. #1
    Join Date
    Oct 2008
    Posts
    69

    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?

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,331
    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

  3. #3
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by philthee View Post
    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?
    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.

  4. #4
    Join Date
    Oct 2008
    Posts
    69
    Quote Originally Posted by PAVB View Post
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width