Experts tricks for Oracle SQL Tuning

The Oracle cost-based SQL optimizer (the 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 don't 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 RBO generates a fast plan, then you know that it's 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 SQ: 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:

[public_html/include_tuning_book3.htm]

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://www.rampant-books.com/book_0501_silver_bullet.htm

To learn more secrets for SQL tuning, see my book Oracle Tuning: The Definitive Reference:

http://www.rampant-books.com/book_0501_awr_proactive_tuning.htm

*****************************************
Need a
Health Check?

Oracle is the world's most complex and robust database and there are hundreds of sub-optimal setting that can cripple your database performance.

Burleson Consulting has a great Oracle health check where we identify all database bottlenecks to ensure that your mission-critical system is running at optimal speeds.

Just call 800-766-1884 to schedule your health check.

*****************************************

Need Oracle Training?

The very best Oracle training comes from Burleson Consulting, where you get an on-site visit by an experienced Oracle expert and author.  Whether it's one-on-one mentoring or getting a customized on-site Oracle training class, there is no substitute for BC Oracle training.  Just call 800-766-1884 for details, and check-out our on-site Oracle training catalog at the following link:

http://www.dba-oracle.com/bc-catalog.pdf

*****************************************

How do I do that in Oracle?

Explore this series for a collection of tips that explain in detail how to perform specific tasks in Oracle related to database administration. Consisting of expert advice from top Oracle gurus, these how-tos provide step-by-step instructions to guide you through database administration processes related to installation and upgrades, backup and recovery and other Oracle basics.

http://searchoracle.techtarget.com/generic/0,295582,sid41_gci1342129,00.html?mo=1&Offer=ORburltxt0505export