Tuning and optimizer

Operating system level tuning. 

 

System-level tuning involves the following steps:

• Monitoring the operating system counters using a tool such as top, gtop, and GKrellM or the VTune analyzer’s counter monitor data collector for applications running on Windows.
• Interpreting the counter data to locate system-level performance bottlenecks and opportunities for improving the way your application interacts with the system.

SQL-level tuning.

• Tuning disk and network I/O subsystem to optimize the I/O time, network packet size and dispatching frequency is called the server kernel optimization.
• Distribution of data can be studied by the optimizer by collecting and storing optimizer statistics. This enables intelligent execution plans.
• Choice of db_block_size, db_cache_size, and OS parameters (db_file_multiblock_read_count, cpu_count, &c), can influence SQL performance.
• Tuning SQL Access workload with physical indexes and materialized views.

Database design level tuning

The steps involved in database design level tuning are:

• Determination of the data needed by an application (what relations are important, their attributes and structuring the data to best meet the performance goals)
• Analysis of data followed by normalization to eliminate data redundancy.
• Avoiding data contention.
• Localizing access to the data to the partition, process and instance levels.
• Using synchronization points in Oracle Parallel Server.
• Implementation of 8i enhancements that can help avoid contention are:
  Consideration on partitioning the data
  Consideration over using local or global indexes.

Explain rule-based optimizer and cost-based optimizer.

 

Oracle decides how to retrieve the necessary data whenever a valid SQL statement is processed.

This decision can be made using one of two methods:

Rule Based Optimizer

If the server has no internal statistics relating to the objects referenced by the statement then the RBO method is used.
This method will be deprecated in the future releases of oracle.

Cost Based Optimizer

The CBO method is used if internal statistics are present.
The CBO checks several possible execution plans and selects the one with the lowest cost based on the system resources.