Queries suddenly slow down after a few hours
Oracle 184.108.40.206 running on Compaq NT 4.0 with 1GB memory
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.
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.
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.
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)
hmm ok, my post got messed up
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
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 ?
OCP 8.0, 8i, 9i
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
Click Here to Expand Forum to Full Width