In short, to diagnose whether your resources are adequate, you are going to have to consider both the operating system and the database server, their configuration and their environment. At the server level, you will want to check the database parameters, the SGA size, and the PGA to start. You will check system statistics using a number of tools, or queries.
SearchOracle.com has some great articles on tuning at the database level. Check Don Burleson's latest articles on "Hidden Oracle."
In terms of operating system commands, you will want to use a series of commands, such as vmstat, iostat, pstat, size, ipcs, ps, sar.
Other commands you will want to use:
Check memory with: /bin/vmstat, which gives Virtual Memory statistics
Check CPU utilization with: ps av (or) iostat 3 20
Check the number of CPUs with: /usr/sbin/bindprocessor -q
Check SWAP space with: lsps –a (How much swap space is there, and what is its usage?)
Check shared memory
$ /usr/sbin/lsattr -E -l sys0 -a realmem $ /usr/sbin/lsps -s $ vmstat 5 100 $ iostat 2 100 $ /usr/local/bin/top # May not be installed by default in the server For example: $ /usr/sbin/lsattr -E -l sys0 -a realmem realmem 33554432 Amount of usable physical memory in KB false
NOTE: This is the total physical and swap memory in the system. Use top or monitor command to get better breakup of the memory.
$ /usr/sbin/lsps -s
Total paging space percent used
30528 MB 1%
You may also use pstat, as is pstat –s to find what is allocated and what is reserved. Check man pages on pstat to see how to interpret the results to get RAM and SWAP space.
You could also use topas which is the equivalent of top.
This was first published in October 2003