Oracle has codified the "silver bullet" approach to Oracle tuning with the Oracle Real Application Testing (RAT) facility. RAT is used along with the SQL Performance Analyzer to provide a complete framework for system-wide testing of Oracle silver bullet changes.
RAT allows the DBA to perform holistic tests when introducing silver bullets, a way to ensure that the global change will have the maximum effect and not clobber the database.
A silver bullet is any single Oracle change that has a profound impact in system-wide performance, both good and bad!
Should you fail to optimize the system for its workload, SQL tuning can be undone with subsequent changes to any of the Silver Bullets, changes that could alter the execution plans of thousands of SQL statements! A silver bullet can be any of these Oracle changes:
- SGA pool sizes (shared_pool_size, db_32k_cache_size, etc.): Pool sizes have a dramatic effect on Oracle performance.
- Materialized Views: MV can pre-join popular tables together and prefetch commonly referenced summary data.
- Indexing : Intelligent indexing ensures SQL query completion with a minimum amount of buffer gets.
- Optimizer knobs (optimizer_mode, Optimizer_index_cost_adj): Changing these global parameters can have a profound impact on Oracle performance, both good and bad.
- I/O subsystem changes (RAID level, multiple blocksizes, SSD): I/O remains the most significant performance bottleneck in most production databases.
- Optimizer statistics : Applying CBO histograms can have a huge benefit to an entire system.
These "broad brush" settings change the way that Oracle processes data in a profound way, and a single change can either make you a hero or get you fired.
Click below to learn more about global tuning with Oracle Silver Bullets:
more
Be careful with Oracle silver bullets
