System Load is the key feature to observe Oracle Database Performance:
System load levels are determined by all of the processes sharing the kernel run queue. If there are not enough CPU cycles to handle the workload of all such processes efficiently, chances are that the processes have to get in line, waiting for the CPU to become available. The uptime command gives you a good picture of the system load averages in Oracle Unix and Oracle Linux environment. You can get good insight into typical usage patterns by periodically observing the output of uptime. Such insight can point out potential problems, thus helping you confront them before they cause severe performance degradation of downtime.
How to monitor System Load for Oracle Tuning:
Your system’s tolerance for heavy loads relies upon a number of configurable areas, such as CPUs, memory, and the I/O subsystem. If a system is prone to high load averages without any adverse impact on performance, then perhaps a high load average is normal for that system. In other words, know what is normal for your system by monitoring it when performance is good. Unless you have an understanding of what the load averages look like during good performance, you won’t be able to judge whether the current load averages are bad. If performance is largely bad during high averages and reasonably good during low averages, that is an indication of consistent system load problems, and you should probably take steps to distribute CPU, memory, and disk I/O loads more evenly. During database support, it is necessary to monitor system load constantly in peak hours and off-peak hours. The constant monitoring process can provide you exact observation of system load bottleneck. Expert Database DBA always monitors all activities of the database and system. If you are working on Oracle Unix or Oracle Linux environment then monitoring of system load is very easy. This suggestion is being provided by good Oracle DBA forums and you can get it from Oracle DBA Interview Questions Book and Online Oracle Tutorials.
How to perform Oracle Tuning after observation:
The first thing to do is to look at the big picture. Analyze what the system is doing; during both high and low load levels. Using brute force and implementing stopgap solutions such as adding more hardware or simply reducing the number of jobs might help in the short run. However, a thorough analysis of the entire situation is absolutely required to fully understand the problem and implement the right solution. For example, you may notice that the system performance of your OLTP application is good most of the time, except for those three hours when that late evening batch job populating the data warehouse runs. Once you have made this observation, you can then start thinking about solutions. Maybe that batch job can be moved to the wee hours of the morning, when not too many users are around and it won’t coincide with the hot backups. Or maybe tuning the SQL statements in the job would allow the job to run without causing system loads to shoot up. Or maybe the design of that data warehouse ought to be revamped, so that in certain cases it uses a pull method to get populated, rather than a batch-oriented push method; that might even eliminate the need for the batch job. Or if the batch job is a non-Oracle process, maybe its priority level can be changed to make it less of a nuisance. Various options present themselves once you have a good understanding the problem.