Queries suddenly slow down after a few hours
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Queries suddenly slow down after a few hours

  1. #1
    Join Date
    Dec 2002
    Location
    Reading, Berkshire, UK
    Posts
    2

    Question Queries suddenly slow down after a few hours

    Oracle 8.1.7.3 running on Compaq NT 4.0 with 1GB memory

    Hi

    I'm hoping that one of you Oracle guru's might like to shed some light on a paculiar problem I'm encountering.

    I am running an application 24 * 7 which stores and retrieves data from Oracle tables several times a seconds (table sizes > 2 million records) and for the most part behaves well. Typically the queries I'm using run in under 0.1 seconds. The database tables have an extensive set of indexes which have all been recently rebuilt and analysed. What I see is that the system can run quite happily for several hours (> 5) then suddenly the query performance drops off drastically (the same query now takes > 2 seconds). The cache hit ratio before this event is >99% but after is ~77% and the amount of disk access increases significantly. A resart of the system solves the problem for a few more hours.

    Looking through the way the system is set-up, there are lots of things I can do to improve performance in general (such as Init.ora settings, changing the location of archive & redo logs etc) which I might get onto in the medium term, but nothing I've read so far can explain why the same software can suddenly behave so diffrerently. Has anyone come across this problem and can you suggest any changes I can make? One thing that has recently changed is the tablespace size which hold the main tables. This become full a few days ago and was increased. Since then it has sufferred from this odd behaviour. Copuld this have affected the way the optimisation works? Any help would be most apprediated.

    I've attched my Init.ora FYI.
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Does the query use the same plan before and after you experience the slowdown? If not, maybe your data skews your stats to the point where it changes your plan. (Although I don't think a re-start would fix the problem if that was the case)

    If so, it almost sounds like your db_block_buffers is too small...

    What does statspack say?

    (sounds like your server is more like 5x7 than 24x7)
    Last edited by marist89; 12-07-2002 at 12:34 PM.
    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."

  3. #3
    Join Date
    Dec 2002
    Location
    Reading, Berkshire, UK
    Posts
    2
    How do I go about finding the plan that Oracle is using before and after the slowdown? I am aware that there are tools available but have never run them. Could you provide some guidance?

    I have updated the DB_BLOCK_BUFFERS as you suggested. I'll see if this makes a difference.

    Thanks again.

  4. #4
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    connect to your database

    alter session set timed_statistics = true
    alter session set sql_trace=true

    run your query

    then find the trace file (show parameter udump)

    then run tkprof

    tkprof report.txt (this will be the output this will show the execution plan for the statement)

  5. #5
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,333
    hmm ok, my post got messed up

    should been

    tkprof "file_name" report.txt (this will be the output. It will show the execution plan for the statement)

    tkprof can be found in $ORACLE_HOME/bin

  6. #6
    Join Date
    Jul 2000
    Posts
    119
    Matt,
    It looks like there is something else running in your database that's eating up resources, like hourly running dbms_job which never ends?
    Is your database in archivelog mode and still on NT ( sorry haven't bothered to have a look at init file, it's easy to ask!)
    Run statspack and pick out resource eating sql's which can ease your life.

    By the way how far you have been successful in tkprofing ?


    Cheers
    OCP 8.0, 8i, 9i

  7. #7
    Join Date
    Apr 2002
    Location
    Shenzhen, China
    Posts
    327
    Also check the top ten waitevents by StatsPack.

    Is it possible a non-database job consuming the OS resources?
    Oracle Certified Master - September, 2003, the Second OCM in China
    *** LOOKING for PART TIME JOB***
    Data Warehouse & Business Intelligence Expert
    MCSE, CCNA, SCJP, SCSA from 1998

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