Oracle Database - How to tune for better performance?

Oracle Database - How to tune for better performance?

Why to tune?

Simply to improve performace of the overall system by enabling the best possible resource utilization. The resource here includes all the resources involved including Software, Hardware, and Human Resources. Thankfully the Oracle Databse can be tuned to a great extent and it's probably one of the biggest responsibilities of a DBA to ensure whether the DB is well tuned or not.

When to tune?

Whenever the DBA discovers that the DB is not well tuned :-) There can be a veriety of
reasons why the DB may not look like to be well tuned. Few of them might be:-

• The system is not running at a pace matching with what your business requires
• The system is too slow to respond to the users and thereby wasting the precious human time
• The hardware are probably not being utilized properly and hence hardware cost is rising

How to tune it?

There are so many areas where the DBA can concentrate on for tuning the Oracle DB. Few of these areas are quire dependent on each other, so it's DBA's responsibility to ensure that focusing on one area doesn't produce some side effects to any other tunes area. So, it's important to focus on these areas in a proper order. For example, you can't jump on to SQL Tuning before having a fixed Database Design for the obvious reason that it's pretty difficult (if at all possible) to have a refined SQL statement without having a refined database schema. Some of the areas to focus are:-

• Design of the Database - the design of the database if of utmost importance and the DBA should be very sure that the current design is what he can think of as the best possible considering so many factors including normalization (or selective denormalization to improve performance), access paths, replication, etc.
• SQL Tuning - probably the second most important area to focus on. There are a variety of ways how the performance of a SQL query can be improved and the DBA should not leave even a single stone unturned :-) Poor SQL statements can cause lot of performance problems during peak hours.
• Memory Tuning - it's important to allocate sufficient memory to the various memory structures of the Oracle Instance. These memory structures are Shared Pool, Buffer Cache, Redo Log Buffer, Large Pool, etc.
• Input/Output Tuning - Disk I/O is probably the most time consuming stuff involved in the entire operation of the DB, so the DBA must consider all the possible scenarios involving Disk I/O and try reduce them to minimum possible.
• Connection Tuning - DB locks and latches must be reduced to the minimum and for the least time possible.
• OS Tuning - finally the DBA might focus on the underlying OS and see if it's in the right shape to ensure the proper execution of the Oracle DB installed on the system.