Tuner's Manifesto

Note: This note contains some ideas for getting customers and tuners on the same page. Oracle is a complex piece of software and doesn't always do the 'right thing'.

• SQL Selects can have multiple logically equivalent forms.5 people could write a SQL Query to solve the same problem but they may arrive at the same correct result set using different combinations of SQL constructs and SQL features.
• Logically equivalent forms can be analyzed differently by the CBO... odds are they will not all come up with the same plan except in the case of very simple queries
• Different analysis can result in different Execution Plans and different query performance
• Major factors in determining Execution Plans (in no particular order):
  Object Statistics - Information about tables and indexes including Data Distribution and Data Volume.
  System Statistics - Information about CPU and disk read performance. These can change Costs a lot as they are used as a multiplier in cost calculations. The more accurate these are the better.
  Instance Parameter Settings - There are lots of Big and Small Knobs available for tuning.
  Optimizer Version and Binary Patches - The Optimizer is continually being changed and improved. If bugs crop up there may be a workaround or a patch of Oracle binaries may be required to get around the problem.
• Aside from DB Instance Parameters that are obviously set wrong, the biggest performance gains can usually be made by tuning SQL and PL/SQL code.
• Thousands of programmer hours went into the Cost Based Optimizer logic.
  Very few people in the world know it inside out. Most experience by non Oracle personnel is gained by controlled tests, observations, and research.
• "Optimizer tries every..." is usually a false statement. There are limits to how many join and plan permutations the optimizer will try during its analysis. The optimizer can definitely miss the optimal plan at this stage. At this point a SQL Re-write into something logically equivalent or hint may get it back in line.
• The fastest way to do something is to not do it (Cary Millsap?) ... in other words, eliminate unnecessary and redundant work from your queries whenever possible. Control your execution steps with WITH clause, filter unneeded records out early so further steps don't have to do unnecessary work, and indexes instead of FTS where appropriate.