"The Performance Hacker"

I've just read the Oracle Real World Performance Group's Open World 2008 presentations entitled "Current Trends in Real World Database Performance". The bit about the performance hacker was interesting. Tuning via initialisation parameters is a bad practice with little or no mileage to gained, unless parameters have been grossly mis-configured in the first place. At the extreme end of the performance hacker spectrum are those that dabble with initialisation parameters that most DBAs will never have any cause to change in their entire careers, such as spin_count (_spin_count from 8i), worse still are those that dabble with the '_' parameters without direction from Oracle support. In most sessions Oracle's Graham Wood gives at the UKOUG conferences, he always mentions that the scope of a fix should match the scope of the problem. You need to get to the root cause of the problem, although the use of hints is no where near as bad as hacking initialisation parameters, it is fixing the symptoms of the problem rather than directly addressing the problem itself.

Of the other characteristics that define the "Performance hacker", they like to indulge in tuning via the internet. There are, however, some excellent sources of information on the internet and in blog sphere regarding tuning:

• The Real World Performance Group
• The Optimizer Group Blog
• Jonathan Lewis's blog
• Wolfgang Breitlings web site etc

The trick is to prefer sources of information from organisations and individuals that provide clearly worked through test cases in order to demonstrate their points. Sites that make grandiose claims without backing them up with worked examples should be avoided like the plague.

If you are completely new to Oracle you may have a tuning advisor view of the performance world. Read the 11g documentation and you would be forgiven for thinking that 11g is a self tuning database and that the addm and bundled in advisors are the only tools you need. Also note that 11g comes with an SQL test case builder, for creating test cases to pass onto to Oracle support for poorly performing statements. The SQL API of which is available through the DBMS_SQLDIAG package, read into this what you will.

The approach recommended by the Real World Performance Group is based around what I first found in a paper entitled "Performance Tuning By Cardinality Feedback" by Wolfgang Breitling, a reviewer of Jonathan Lewis's cost based fundamentals book. The basic premise of this is that you look at where actual cardinality versus the predicted cardinality in a plan is out, usually by an order of magnitude and usually somewhere near the start of the plan. To the best of my knowledge, this approach is not mentioned in the standard Oracle performance tuning material.

Knowing about this technique and how to apply it is made easier in Oracle 10g with the gather_plan_statistics hint (or statistics_level=all) and the DBMS_STATS.XPLAN package. If you use the "Tuning by cardinality" feedback approach and find that you plan does not improve through addressing issues including:-

1. Inaccurate stats, particularly the number of distinct values against highly skewed data.
2. Data correlation or predicate independence assumption issues.
3. Predicate values being out of bounds when compared to min max values for a column.
4. Missing histograms or histograms with inappropriate numbers of buckets.
5. Pseudo columns relating to function based indexes with out stats or histograms.
6. Selectivity assumptions made when functions are used.
7. Skewed data, for which there is more than 254 disitinct values and the values you are interested in are not 'popular' values, i.e. do not fall on a bucket boundary end point.

By providing full evidence that this approach has been applied and followed, you will get a much more expediant resolution to your issue out of Oracle support than someone reporting a poorly performing query or a query with a large full table scan in it's plan.