The Oracle cost-based SQL optimizer (the Cost Based Optimizer - CBO) is one of the world's most complex software packages ever created. Imagine, writing a program that will always generate the "best" execution plan for any SQL statement. Oracle's implementation of SQL optimization is the best in the world, and along with this great power and flexibility comes great complexity.
Oracle SQL tuning is phenomenally complex, but there are some secrets that can help you succeed. Here are a few tricks that they do not teach you in Oracle University:
- Do the system-level tuning first - Setting the optimizer parms properly (especially optimizer_mode, optimizer_index_cost_adj, and optimizer_index_caching) can tune thousands of SQL statements in a single action.
- Try a /*+ RULE */ hint - The first thing I do when tuning a query is to invoke the rule-based optimizer. If the Rule Based Optimizer (RBO) generates a fast plan, then you know that it is not a problem with missing indexes, and you can focus on sub-optimal schema statistics (especially histogram).
- Choose the optimizer_mode wisely - The default optimizer mode of all_rows is designed to minimize computing resources. If you want to optimizer your SQL for fast response time, you need first_rows (or first_rows_10, first_rows_100).
- Avoid hints whenever possible - In Oracle, hints are a last resort, and you can adjust your CBO statistics (with dbms_stats) and use histograms to reproduce the execution plan from a hint. This has the side benefit of tuning other SQL statements as well!
- Use Histograms wisely - Oracle sometimes makes incorrect "guesses" (cardinality estimates), resulting in incorrect index usage and improper table join order. Make sure to deploy histograms to resolve these issues. Here are my notes on tuning with histograms: link
Remember, adjusting initialization parms, adding missing indexes and re-computing schema statistics can impact thousands of SQL statements, reducing the amount of manual SQL tuning. These system-wide approaches are codified in the 11g SQL Performance Analyzer, and many of these "silver bullets" are described in the book "Oracle Silver Bullets"
http://oracle-tips.c.topica.com/maam3F4abP15obLGJrib/
To learn more secrets for SQL tuning, see my book Oracle Tuning: The Definitive Reference:
more
Experts tricks for Oracle SQL Tuning
