Friday, October 30, 2020
    Home Oracle Tuning Oracle Tuning using System Load Performance

    Oracle Tuning using System Load Performance

    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.

    - Advertisement -

    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.

    - Advertisement -

    More Oracle DBA tips you can find out Oracle DBA blog and Oracle DBA Tips section.

    - Advertisement -


    Comments are closed.

    - Advertisment -

    Most Popular

    Identifying important information in Big Data

    The way in which Big Data technologies have evolved in the real word enterprise goes on to show that even technologists and scientists who might have disparaged the word (Big Data) previously

    Five Reasons Why Database Outsourcing

    The blog post explains why database outsourcing and remote dba support becomes most popular during present time of Covid-19 Pandemic.

    Hardware of Database Server

    The blog post explains how to choose best hardware of your database server for getting high performance and security.

    oracle 19c new features – Automatic Indexing

    A major Oracle 19c autonomous database new Feature automatic indexing using DBMS_AUTO_INDEX some details and how to article.

    Recent Comments