Avoiding Advisor Pitfalls

You may have noticed that the approach taken by the advisors is very similar to that used by a performance analyst. For example, when the SQL Tuning Advisor is invoked, it checks for the presence of valid and up-to-date object statistics. It then performs access path analysis such as checking for the presence of appropriate indexes. The SQL Access Advisor performs a similar task while looking for opportunities to create materialized views and to utilize the query-rewrite facility. In other words, the advisors automate what a human would do. Hence, when used incorrectly or without a complete understanding of the application and environment, the advisors suffer from the same shortfalls and mistakes that a human would be expected to commit. Some of these possible stumbling blocks and how to guard against them include the following:

• Out-of-the-box thinking. Sometimes, the solution to a perplexing performance issue is not the standard one, such as addition of an index or creation of a materialized view. The solution may instead be to redesign a part or even the whole application, or even something as simple as parallelizing the effort via the Parallel Query (PQ) or Parallel DML (PDML) facilities. Obviously, the advisors currently do not have the capability to think along these terms, while a human could. What you will need to do is to use the advice generated by the advisors as just that, and look beyond the obvious.

• False positives. In certain situations, a piece of advice that is currently valid may become invalid during another period. For example, the advice to drop certain unused indexes may be valid during normal days, but implementation (that is, the removal of these indexes) may result in performance issues during period-end reporting. In this case, the out-of-the-box solution may be to drop the indexes during the normal days and create them prior to such reporting requirements.

• Changing workload or environment. It is a given that an application's code, usage, data size, and execution environment change constantly. Thus, the performance analyst must continually look for tuning opportunities and keep track of what was done prior. For example, you may need to regenerate an SQL tuning set because of the increased and changed code base. You might also have to keep regenerating SQL profiles for a set of Top SQL in a scheduled manner so that the profiles can accommodate changes in data and execution pattern.

In short, the performance analyst should consider the SQL advisors as just one more, albeit important, set of tools in the quest to keep the application and database performing well.