Oracle DBA Tuning Tips to Know and Use Regularly!

The biggest responsibility every DBA has is to make sure that the Oracle database has been tuned properly. Oracle’s RDBMS is can be tuned to a great extent allowing for easy monitoring of the database and easy adjustments to help boost the performance of the database. Improper tuning of the database can create plenty of problems such as low response time, slow systems poor hardware usage and also loss of money. As a result, it is very important to keep your Oracle DB in top shape and tuned to handle all the work load using minimal resources. Here are 5 such tips to help you tune your DB for optimum performance.

Performance tuning in Oracle is done with a top-down approach with the initial steps with a broader scope and the consecutive steps start getting more focused towards a particular function. The process of performance tuning itself is quite complex and it is not exactly possible to come up with one solution that suits all situations. You can encounter several different problems and you may need to use several different approaches to solve the issue and performance to tune the DB. However, a top-down approach should always be your go to approach to begin tuning the database and then you can make suitable alterations as per your needs.

The right tuning order

Oracle DBA Tuning Tips to Know and Use Regularly

License: Image author owned

It is very important to make sure that you are using the right tuning order for performance tuning. It is very important that you stick to the steps as listed by Oracle to make sure that no tuning side effects take place. This will also save you time by avoiding unnecessary actions say for example if you can reduce the I/O by simply modifying an SQL statement, it would be foolhardy to meddle with the buffer cache instead.

The right order recommended by Oracle for tuning includes:

1.      Database tuning
2.      Application tuning
3.      Memory tuning
4.      I/O tuning
5.      Eliminating database contention
6.      Tuning the OS

Server tuning

While most DBAs begin performance tuning with instance tuning, it is a good practice to start with server tuning. Many problems in the database may arise from a poor server environment and ignoring this may not fully solve the problem. You may even realize that the server is running in a poor environment and may be forced to come back to this step and begin the tuning process once again. So starting with server and network tuning will not only help you make sure everything is working according to your wishes but can also save you crucial time. Make sure that your server is using all the settings that help the server function better. These guidelines are generally provided by the manufacturer and can help you make an informed judgment about the underlying problem with the database.

Instance tuning

The next step would be to naturally tune the SGA and review other initialization parameters. This will help you to ensure that the database has been configured properly to the workload. If the database is operating in bi-modal environment, it is important to make sure that the instance parameters have been adjusted as per the requirements during performance tuning. It is always a good idea to get some form of feedback from the users about the issue in performance and then examine the host statistics to find out what has been going wrong. Once you have done this, it would be fairly easier to rectify the performance issue easily.

Reference: http://docs.oracle.com/cd/B19306_01/server.102/b14211/instance_tune.htm

Object tuning

Once you have completed instance tuning, you can turn towards object tuning. This step would require you to take a good look at the tables and indexes such as FREELISTS, PCTUSED and PCTFREE. These can have a significant impact on the performance of the database. You will need to make sure that all of these table settings are tuned according to your needs.

SQL tuning

The final and last step involved in tuning the Oracle database would be to tune the SQL and this can often be the most time consuming activity when it comes to performance tuning as the volume of SQL statements accessing the database could be enormous. This is also the reason why you need to follow a system for performance tuning. If you have done instance tuning properly, when it comes to SQL tuning, all you will need to do is make sure that the outlier statements are tuned. This may include removing any unnecessary large table or full table scans, verify the usage of optimal index, materializing the summaries for static tables and cache small and full table scans.

By Seth Williams

Seth Williams has been writing with Firebox Training since July, 2011. He writes on many topics across IT programming, training and developments in the industry. Through spreading wisdom across forums and tech blogs, Seth has realized an open source approach to training professionals across the globe is the way to go.