Oracle10g SQL Time-series Tuning

Oracle10g SQL Time-series Tuning: "Oracle10g SQL Time-series Tuning

by Donald K. Burleson

This article is a brief overview of SQL tuning in Oracle10g, with a focus on the new 10g SQL tuning features. The main new features of SQL tuning in Oracle10g include the following:

* Oracle10g now automatically collects and refreshes schema statistics using the dbms_stats package, and histogram collection is now easily automated with dbms_stats
* One common cause of sub-optimal SQL is missing materialized view and indexes, especially function-based indexes. The SQLTuning Advisor and SQLAccess Advisor provide an easy method for identifying and tuning SQL with sub-optimal execution plans.
* SQL Profiles are a great improvement of Optimizer Plan Stability (stored outlines).
* The new dba_hist tables contain a wealth of historical information about historical SQL execution statistics.
* Time-series analysis of object usage within SQL can give us important insights into holistic tuning for SQL statements."