Queries suddenly slow down after a few hours

[Matthew Durkin]

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.