Database running very slow
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Database running very slow

  1. #1
    Join Date
    Nov 2000
    Posts
    224
    Oracle 817 on Sun Solaris

    Today morning my users told about Database performing very slow, I querried few tables/views but not much accomplished.
    Can you explain me

    What steps should I take? and in what sequence to deal with recurring issue.

    Thanks!


  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,003
    I had a similar situation. I had Oracle 817 on sun Solaris 8. The database started running slow when I analyzed the schemas. I returned to normal when I deleted the stats. I find that rule optimization works better on Solaris.

    If that isn't it it would be helpful to know how long it has been running slow and what has changed recently.

  3. #3
    Join Date
    Sep 2000
    Posts
    384
    Is it all of a sudden or We had the same performance but now we want to improve the performance ??

    what optimizer_mode u r using ??
    when was the last time you had analyzed all tables and indexes
    did you see the sql statements which has high disk read and buffer_gets ?? are they using the indexes??
    any of the setting has been changed lately
    sort_area_size/db blocks /log buffer
    How long(days) has the database running so far


    Radhakrishnan.M

  4. #4
    Join Date
    Nov 2000
    Posts
    224
    The database start behaving slow from time to time, this has happened quite a few times now,
    I am more interested in finding out is their sequence of steps to be taken to findout what is currently going on in the Database.
    Something like, get all the SQL stmt. currently executing
    How much disk I/O etc.
    Is their any article/white papers on this topic.

  5. #5
    Join Date
    Sep 2000
    Posts
    96
    The sequence of steps that Oracle recommends for performance tuning are as follows:

    1. Database Design (probably a little too late for this since the app is up and running)
    2. Check Application SQL Stmts
    3. Memory Tuning
    4. Disk I/O
    5. Internal Memory Structure Contention

    Inside each of these is a lot of area to cover. I would suggest you start first with app SQL stmts, if possible to make chgs there. You'll get the most "bang for your buck" at that level. After that, proceed through the list. Anymore detailed questions about each, just ask.

  6. #6
    Join Date
    Nov 2000
    Posts
    224
    Lets be specific, since application is already in production so first 2 options are more or less ruledout. Infact second option is workable.

    How to go ahead with memory tuning? What steps / querries can be peroformed for memory tuning.
    Similary for disk IO and contention.

    Thanks!

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    1 Run top in unix to check which process consumes more memory.

    2 Run vmstat to check paging is taking place at the OS level.

    3 Kill unwanted sessions at unix and oracle level.

    4 ALTER SYSTEM FLUSH SHARED_POOL;

    5 Run utlbstat and utlestat and verify all the statistics.

    6 If you can't figure it out the reason, shutdown oracle and unix and restart the box. If you experience slowness in the system, run sql trace at session level and take tkprof report.





  8. #8
    Join Date
    Sep 2000
    Posts
    96
    I would check all that tamilsevan suggests, and possibly these others too:

    * readhitratios
    * check total system waits with v$system_event, v$session_event, and v$session_wait
    * check v$latch for latch contention

  9. #9
    Join Date
    Mar 2002
    Posts
    6

    re:

    FIRST OF ALL CHECK WHETHER YOU DO TABLE INDEXING

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