We use oracle as the back-end database for our product. I have been running series of stress tests on our system and I have started noticing that oracle is much faster right after the database was restarted. Over time (a couple hours or so) the database seems to get slower and slower and I will see the database machine under more stress.
Running the test right after an oracle restart, i will see a 1 min load average of 5 or so and average CPU around 10-15%. After a few hours, I see the load average at 13 and CPU at 40-70%. (This is red hat linux 2x Quad core xeon, Raid 10 10k rpm sas drives).
My first thought was wouldn't database transactions get faster because those queries are getting cached?
I can't seem to figure out the problem.
EDIT: Turns out this was a problem on the connecting software side due to bad design. Every action on the system created a new insert, delete, and select. With all these unique queries being generated, what was cached was constantly changing. The spike I am talking about is when the query cache filled up.</div