Recently we have been having problems with our oracle database locking up on 1 query that we know so far.
I will give a break down of what is happen however really doesn't have too much to do with my question but I am open to suggestions.
Intermittently no telling when it will happen, has happen 4 times over the past month, a user, will click on something in in the application. What the user is clicking is still unknown. When whatever is click it will run a query on the database which will generate about 700k Rows.
I have checked the table that the query is being run against and the indexes look good.
The database is 60GB there is 32GB on the server.
From the logs on the database server I see high I/O but CPU and RAM stay the same.
One the application server, the CPU goes up to about 75%. I can find the worker process, find the PID, however when I kill the PID associated with the worker process the CPU will go down briefly and then go right back up.
Also recycling the application pool, and restarting IIS does the same thing, the CPU will go down briefly and then go right back up.
The only thing that can be done to get the server back in line is a restart.
So my suggestion IF this query is what is causing the lockups can the memory be increased on the box to allow for the database to get cached and run out of memory. I heard this once but im not sure if it is true.
The storage is an HP PAR 3 with 3 tiers and the database pretty much lives in the SSD tier.
Which is faster SSD or Memory
