Optimizing Oracle SQL Execution

The key to success with the Oracle Cost-based Optimizer (CBO) is stability, and ensuring success with the CBO involves the consideration of several important infrastructure issues.

§       Ensure static execution plans: Whenever an object is re-analyzed, the execution plan for thousands of SQL statements may be changed.  Most successful Oracle sites will choose to lock down their SQL execution plans by carefully controlling CBO statistics, using stored outlines (optimizer plan stability), adding detailed hints to their SQL, or by using Oracle10g SQL Profiles . Again, there are exceptions to this rule such as LIMS databases, and for these databases, the DBA will choose to use dynamic sampling and allow the SQL execution plans to change as the data changes.

§       Reanalyze statistics only when necessary: One of the most common mistakes made by Oracle DBAs is to frequently re-analyze the schema. The sole purpose of doing that is to change the execution plans for its SQL, and if it isn’t broken, don't fix it. If the DBA is satisfied with current SQL performance, re-analyzing a schema could cause significant performance problems and undo the tuning efforts of the development staff. In practice, very few shops are sufficiently dynamic to require periodic schema re-analysis.

§       Pre-tune the SQL before deploying: Many Oracle systems developers assume that their sole goal is to write SQL statements that deliver the correct data from Oracle. In reality, writing the SQL is only half their job and successful Oracle sites require all developers to ensure that their SQL accesses Oracle in an optimal fashion.  Many DBAs will export their production CBO statistics into their test databases so that their developers can see how their SQL will execute when it is placed into the production system.  DBAs and staff should be trained to use the AUTOTRACE  and TKPROF utilities and to interpret SQL execution results.

§       Manage schema statistics: All Oracle DBAs should carefully manage the CBO statistics to ensure that the CBO works the same in their test and production environments. A savvy DBA knows how to collect high quality statistics and migrate their production statistics into their test environments. This approach ensures that all SQL migrating into production has the same execution plan as it did in the test database.

§       Tune the overall system first: The CBO parameters are very powerful because a single parameter change could improve the performance of thousands of SQL statements. Changes to critical CBO parameters such as optimizer_mode , optimizer_index_cost_adj , and optimizer_index_caching should be done before tuning individual SQL statements.  This reduces the number of suboptimal statements that require manual tuning.

Prior to Oracle10g, it was an important job of the Oracle DBA to properly gather and distribute statistics for the CBO. The goal of the DBA was to keep the most accurate production statistics for the current processing. In some cases, there may be more than one set of optimal statistics.

For example, the best statistics for OLTP processing may not be the best statistics for the data warehouse processing that occurs each evening. In this case, the DBA will keep two sets of statistics and import them into the schema when processing modes change.

The following section provides a quick, simple review of the goals of SQL tuning.

SEE CODE DEPOT FOR FULL SCRIPTS

---

This is an excerpt from my latest book "Oracle Tuning: The Definitive Reference".  You can buy it direct from the publisher for 50%-off and get instant access to the code depot of Oracle tuning scripts: