DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Performance Tuning Question..

  1. #1
    Join Date
    May 2000
    Posts
    2
    Hey :

    What are all the basic procedures to follow if
    a user complaints about a performance slow down
    in the oracle database. Can somebody give me steps
    to follow with priority ? :-D

    Thanks
    Gshan

  2. #2
    Join Date
    Jul 2000
    Location
    Oxford, OH
    Posts
    117
    Kill the user's process or better yet, just kill the user.

    If that's too drastic then you should look to see:

    1. Look at cache hit/miss ratio's to see if they are down.

    2. Are there any queries that are out there running that are
    sucking up resources?

    3. Look at the O/S level to see if any processes have started
    which might taking up resources such as nightly batch
    processes, etc...

    Other than that...

    You can look to make sure you don't have any invalid objects, check to make sure that your statistics are fresh if using the cost-based optimizer...etc..

    I actually had an interesting problem today. We had some users who were very unhappy because some of their monthly reports were taking forever to run. They said it should run in two hours and it had been six and still not completed. It turned out that on the partitioned tables, the september partitions had been created and analyzed once before when they actually had no data loaded into them yet so the row_count was 0. Well after the data load there were some other issues and the tables had not be reanalyzed. Therefore the cost based analyzer was choosing a totally inappropriate path for execution where it should have been using the partition in question. After analyzing the partitions again, the query executed in a little over an hour. Better yet, because of looking at the trace and tkprof output I was able to find some inefficencies in the query and I brought the execution time down from a little over an hour to about 8 minutes. Needless to say, the users were very happy.

    The moral of the story:

    Make ABSOLUTELY SURE your statistics are up to date. When the statistics are screwed up, the CBO can really screw you *laugh*



    [Edited by wjramsey on 11-15-2000 at 07:35 PM]
    _________________________
    Joe Ramsey
    Senior Database Administrator
    dbaDirect, Inc.
    (877)687-3227

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