The amount of resources required for good performance is dependent upon many factors. In some cases, performance may be degraded not because there are insufficient resources, but because the database configuration is inappropriate for what you are trying to do. In other cases, something about an application or user process may have caused abnormal resource contention. (A user sets off a process to run during the day which normally runs at night, or a user writes a particularly inefficient query, or the applications concurrent managers have been poorly configured, etc.)
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.
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.