-
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.
Sam
-
Sam,
Thanks for your reply,
1. library cache hit ratio is 1
2. buffer cache hit ratio is .8
3. sort hit ratio
NAME VALUE
sorts (memory) 918173
sorts (disk) 1058
I don't know how to run statpack , could you please give me some instructions of how to run it ???
in the mean time, what do you think about the ratio above ???
Thanks
-
-
Sam,
What is your advise by looking at those statistics I listed above ???
Thanks
-
Your buffer cache hits are low. Run the statspack and it will give you a detail report on what you have to tune.
Sam
