Oracle System Optimization

The process of identifying and eliminating bottlenecks of the system performance is commonly referred as system optimization or tuning. System-level Oracle tuning is the first step of the overall database performance optimization. This whitepaper explores main steps of this process.

Optimize the server hardware. It is extremely important to constantly optimize the disk and network subsystems to increase the performance in terms of the input/output time, network packet size and dispatching frequency. Unix/Linux OS provide the following tools for monitoring performance of hardware components:

• vmstat - is used to collect information about CPU, memory, paging, etc
• iostat - is used to monitor input/output devices, i.e. hard disk

Adjust optimizer statistics. Oracle model of the execution plan's cost relies on statistics collected by the optimizer on the host, the database and objects involved in a query. Optimizer statistics include the following entries:

• Table statistics (number of rows and blocks, average size of row)
• Column statistics (number of unique values and nulls in a column, data distribution)
• Index statistics (number of leafs and levels, clustering factor)
• System statistics (performance and utilization of the CPU and I/O systems)

Those statistics must be always collected and stored carefully to allow the optimizer to learn more about the data and to make better execution plans.

Also, it is reasonable to adjust the following optimizer parameters:

• optimizer_mode - specifies optimization approach for the instance (e.g. how many rows mush be analyzed for best response time on cost-based estimation)
• optimizer_index_caching - estimates the probable portion of index (0..100) that resides in the RAM data buffer cache. It helps cost based optimizer decide whether to use an index for a table join or to favor a full-table scan.
• optimizer_index_cost_adj - modify he relative costs of full-scan in comparison to index operations. Reduce this value to make the cost of index access lower.
• sort_area_size - specifies the maximum memory amount (in bytes) used by Oracle for a sort. This setting makes cost based optimizer to choose between index access and sorting the result set. As the value of sort_area_size increases, CBO will more likely prioritize sorting over retrieving data from pre-sorted indexes.

Optimize the instance. Key to succeed this step is to know the performance baseline, it helps to identify performance issues and analyze the related statistics. The baseline information should contain the following: application statistics (transaction volumes, response time), database statistics, operating system statistics, disk I/O and network statistics.

These settings can affect the performance of Oracle instance: db_block_size, db_cache_size, db_file_multiblock_read_count, and cpu_count.

Use diagnostic software and follow its recommendations. For example, SQL Access Advisor identifies performance problems caused by missing indexes and materialized views. In this case just follow the recommendations - optimize your SQL workload by adding required indexes, especially function-based indexes.

Distinguish problems from symptoms

The most common mistake in performance tuning is to mix the symptoms of a problem and the actual problem itself. Most statistics gathered by performance analyzers identify the symptoms, not the problem. For example:

• Slow physical I/O may be caused by either poorly-configured disks or significant amount of unnecessary physical I/O on those disks issued by poorly-tuned SQL.
• Latch contention is rarely tunable by reconfiguring the instance. Rather, it is usually resolved through application changes.
• Excessive CPU usage usually means that there is little idle CPU on the system. This could be caused by an inadequately-sized system, by inefficient SQL statements or application programs.

Have questions? Contact us