SQL-level tuning

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.