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.

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)

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.

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

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

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 ?
:D

Cheers

Also check the top ten waitevents by StatsPack.

Is it possible a non-database job consuming the OS resources?