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.