Oracle database server becomes slow after sone time
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Oracle database server becomes slow after sone time

  1. #1
    Join Date
    Dec 2005
    Posts
    19

    Oracle database server becomes slow after sone time

    Hi,

    I am using Oracle 8i(8.1.17) on sun solaris.
    After running the database for say 2-3 hours, it becomes slow. My queries takes long time to fetch the results.

    If I shutdown the datbase and re-start it, then performance becomes OK.

    SGA STATUS AT 30-Dec-2005 04:57:20 PM
    =====================================
    Object memory : 35721340 bytes (34.07MB)
    Shared sql memory : 23490369 bytes (22.4MB)
    Cursors memory : 368500 bytes (.35MB)
    Shared pool utilization (total) : 71496251 bytes (68.18MB)
    Free memory : 95885068 bytes (91.44MB)
    Shared pool allocation (actual) : 131072000 bytes (125MB)
    Percentage Utilized : 55%
    ----------------------------------------------------
    redo buffer allocation retries : 20
    redo log space wait time : 0
    ----------------------------------------------------
    Event Total_wait Total Timeout
    buffer busy waits : 7839 1582
    db file sequential read : 5138 0


    I am able to see that free memory is there.

    What could be the reason for slowing down.


    It would be appreciable to get a resonse.

    Arun

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    trace the queries, see where the time is being taken and stop guessing

  3. #3
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Is teh whole database slow or some queries are slow ?

    regards
    Hrishy

  4. #4
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by tayalarun
    Hi,

    I am using Oracle 8i(8.1.17) on sun solaris.
    After running the database for say 2-3 hours, it becomes slow. My queries takes long time to fetch the results.

    If I shutdown the datbase and re-start it, then performance becomes OK.

    SGA STATUS AT 30-Dec-2005 04:57:20 PM
    =====================================
    Object memory : 35721340 bytes (34.07MB)
    Shared sql memory : 23490369 bytes (22.4MB)
    Cursors memory : 368500 bytes (.35MB)
    Shared pool utilization (total) : 71496251 bytes (68.18MB)
    Free memory : 95885068 bytes (91.44MB)
    Shared pool allocation (actual) : 131072000 bytes (125MB)
    Percentage Utilized : 55%
    ----------------------------------------------------
    redo buffer allocation retries : 20
    redo log space wait time : 0
    ----------------------------------------------------
    Event Total_wait Total Timeout
    buffer busy waits : 7839 1582
    db file sequential read : 5138 0


    I am able to see that free memory is there.

    What could be the reason for slowing down.


    It would be appreciable to get a resonse.

    Arun
    > Increase the db_cache_size to reduce your buffer waits and you can see some improvement.
    > Run the statspack to get statistics abt the performance again.
    "What is past is PROLOGUE"

  5. #5
    Join Date
    Oct 2002
    Posts
    182
    are you using CBO?
    If so make sure you are refreshing stale statistics
    - Cookies

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    this seems like a clear case for using statspack to determine the major waits on the system after startup and then after three hours.

    I don't see how stale statistics could be relevant
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Quote Originally Posted by dbasan
    Increase the db_cache_size to reduce your buffer waits and you can see some improvement.
    Next thing you know you'll be charging $300 and hour and have your own remote-dba service.

    Quote Originally Posted by slimdave
    this seems like a clear case for using statspack to determine the major waits on the system after startup and then after three hours.
    Well, sort of. I'd take one while things are "fast", one when things started going "a little slow" and then every 10 minutes until things were "slow".

    I don't see how stale statistics could be relevant
    I agree, unlikely.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  8. #8
    Join Date
    Mar 2004
    Location
    DC,USA
    Posts
    650
    Quote Originally Posted by marist89
    Next thing you know you'll be charging $300 and hour and have your own remote-dba service.


    Well, sort of. I'd take one while things are "fast", one when things started going "a little slow" and then every 10 minutes until things were "slow".


    I agree, unlikely.
    Obviously, Thanks marist89.
    "What is past is PROLOGUE"

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