Automatic SQL Tuning in Oracle 10g

Publisher Summary

This chapter presents the new Automatic SQL Tuning feature of Oracle 10g. SQL tuning is a very critical aspect of database performance tuning. It is an inherently complex activity requiring a high level of expertise in several domains: query optimization, to improve the execution plan selected by the query optimizer; access design, to identify missing access structures; and SQL design, to restructure and simplify the text of a badly written SQL statement. Furthermore, the SQL tuning is a time consuming task due to the large volume and evolving nature of the SQL workload and its underlying data. The new Automatic SQL Tuning is implemented as a core enhancement of the Oracle query optimizer and offers a comprehensive solution to the SQL tuning challenges. Automatic SQL Tuning introduces the concept of SQL profiling to transparently improve execution plans. It also generates SQL tuning recommendations by performing cost-based access path and SQL structure “what-if” analyses. The Automatic SQL Tuning is an integral part of the Oracle's framework for self-managing databases.