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


Which is faster SSD or Memory

DRAM is faster than NAND flash. A RAM access is on the order of 100 ns, while a SSD random read around 16,000 ns. Latency Numbers Every Programmer Should Know

You need to do a systematic analysis of what is happening and why. Do not change things merely because you heard something secondhand about another system. Find what the problem is on this system.

Fortunately DBMS systems tend to have performance tools. Oracle and other RDBMS have EXPLAIN PLAN to show full scans and other inefficiencies. Queries can perform terribly even with indices. There are also system performance reports like Oracle AWR to find bottlenecks. If you wish to inquire further about DB performance you can also ask our sister site, https://dba.stackexchange.com/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.