I am trying to be clear about how Oracle allocated and used the CPU. I have a problem with when the users ran any stored procedures and it peaks up the CPU, and every body experience the bad performance.
I understand that bad SQL could cause this but I don't think it's bad query b/c it's just a simple insert or update to the database. Here is my questions.
I am running Oracle SPatial and I undersize the SGA from what Oracle require, could it be one of the reason since Oracle have to use a lot of swap and paging file ???
Can someone give me some advises of what need to be done to resolve this problem ???
I am running Oracle 8.1.7 and windows 2000 server with 2 processors.
Check your library cache hits, buffercache hits and the sort hit ratios. Then take a step at a time. I would suggest you to run the statspack at the time of bad performance to undestand what is causing the bogging.