Oracle Server Tuning - Part 1 OVERVIEW OF TUNING OF AN ORACLE PRODUCTION INSTANCE The Oracle server is a sophisticated and highly tunable software product. Whether you are designing or maintaining a system, you should set specific performance goals so that you know when to tune. The most effective method to tune the system is as follows: Consider performance when designing the system Tune the operating system hardware and software Identify performance bottlenecks Determine the cause of the problems Take corrective actions. Tuning a production system Each performance problem has a unique cause, and you may need to query several dynamic performance views to find the cause of some problems (like v$session_wait view lists the events that cause all users and system sessions to wait.). Performance problems tend to fall into one of these categories: CPU Memory I/O Contention for latches or other structures CPU tuning Checking system CPU utilization: You have a CPU problem if idle time and time waiting for I/O are both close to zero at a normal or low workload. In UNIX system you can check whether your system is suffering from CPU problem through sar –u command In NT system, this can be checked through the system performance monitor. Whether the system is suffering from paging/swapping, can be checked by vmstat command in UNIX system. Checking Oracle CPU utilization: 1. Reparsing SQL statement Execute the following query Code (Text): Select * From v$sysstat Where name in ( 'parse time CPU' , 'parse time elapsed' , 'parse count'); Where, Response time = service time + wait time Response time = elapsed time Service time = CPU time Elapsed time – CPU time = wait time Wait time / parse count = average wait time per parse The average wait time should be extremely low. Parsing can be reduced by utilizing the initialization parameter SESSION_CACHED_CURSORS 2. Inefficient SQL statement Execute the following query Code (Text): Select buffer_gets, sql_text, parse_calls, executions From v$sqlarea Order by parse_calls The sql statements with high number of buffer gets may be inefficient. I will talk about Tuning Memory Allocation in Part II of this article.