Oracle 220.127.116.11 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.
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)
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 ?