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.
What is a dynamic performance view in Oracle?
What is a dynamic performance view in Oracle?...
How to reference remote tables in procedures
Using SQL statements, remote tables can be accessed in procedures. Database links are specifically used for this purpose.......
Significance of Return clause in stored procedure
Return statement in a stored procedure is not to return values.....
Post your comment