Effective Tuning of Oracle 11g Fully Automated SQL!

 Author: Burleson

Page: 1 2

SQL Plan Manager:

You can use the SQL Plan Manager to test system characteristics against real workloads and ensure that all changed execution plans result in at least 3x faster performance.

Although this is not a truly fully automated approach as some SQL statements must be tuned manually but still it gives dramatic improvement over the hit-and-miss SQL tuning technique.

Disk and network I/O subsystem:

The disk and network I/O subsystem like RAID, DASD bandwidth and network should be tuned to optimize the I/O time, network packet size and dispatching frequency.

Kernel settings:

Kernel settings effects SQL performance indirectly. A kernel setting may speed up I/O which is noted by the CBO workload statistics and hence it influences the optimizer's access decisions.

Parameters:

Always use the best settings for optimizer_mode, optimizer_index_caching, optimizer_index_cost_adj . Similarly db_block_size, db_cache_size , and OS parameters like db_file_multiblock_read_count, cpu_count , etc. can influence SQL performance.

Indexes:

Always optimize your SQL workload with indexes. The function-based indexes have huge impact on SQL tuning.

Intelligent SQL Access Plans:

Optimizer statistics should always be stored. This allows optimizer to learn more about the distribution of your data and hence makes more intelligent SQL access plans.

Histograms:

The histograms can drastically improve SQL by determining optimal table join order and making access decisions on skewed WHERE clause predicates.

Multi-column statistics:

Oracle 11g multi-column statistics can be gathered for use by the optimizer to determine optimal plans. Read Again!!

Page: 1 2

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle Security Guide: Beware of all DBAs having full OS access!!
   Great Tips on solving Temporary Tablespace Problems!
   Adding Custom Messages to Oracle Alert Log!!
   Database Tuning Guide for Third Party Applications