System Load is the key feature to observe Oracle Database Performance:
System load levels are determined by all 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 environments. 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 or 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 the system load constantly during peak hours and off-peak hours. The constant monitoring process can provide you with the exact observation of the 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 of the problem.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals, Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert.
This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.