We are using oracle database on solaris 10 sparc 64 bit on M5000 machine. we facing performance related issues.
We diagnose using prstat -a command that oracle user not utilizing the ram more than 30 gb total we have 64 gb ram available as in project max-shm-memory set to 42 gb . We are running just a single oracle job and CPU usage goes to 91 % and used memory only stuck to 30 %. Kindly update us why memory not utilizing more than 30gb and why CPU usage shoot to 90%.
prstat -a
NPROC USERNAME SWAP RSS MEMORY TIME CPU
260 oracle 19G 19G 29% 85:48:51 91%
more /etc/project
oraclenew:104:Resource new for oracle:oracle:dba:project.max-shm-memory=(priv,45097156608,deny)
A process uses as much memory as it needs, no more and no less. You cannot force it to "use" memory it doesn't need.
Of course the above is also limited by the amount of RAM the server physically has, kernel parameters, projects, etc.
If you're seeing that you have a high CPU usage, then perhaps the problem is related to CPU and not to memory.
Unfortunately we don't have any details about your environment so we cannot really tell exactly what the issue is.
Hi Zeeshan,
The amount of memory for oracle process is also limited by oracle SGA set by the DBA. It will not cross that limit, so get in touch with DBA and ask him to increase SGA as having free memory on your server is of no use to anybody. More memory for oracle processes might just solve your cpu utilisation problem as well, though i am not sure of this.
Thanks
abhishek