SQL tuning is one of the challenging tasks faced by DBAs and developers. It is an interesting and creative, but at the same time, daunting task. Manual tuning of SQL statements requires a high level of expertise and experience to understand and design suitable access paths to yield better response times. It is also a time consuming process. Other challenges include periodic collection of statistics and an ever-changing workload. And in a typical application, there are just too many SQL statements to tune.

Oracle Database 10g introduces many useful and easy-to-use tuning tools and methodologies. In this section we will examine these new and improved features.

The new and enhanced features include the following:

• SQL Tuning Advisor
• SQLAccess Advisor
• Automatic Statistics Gathering
• Automatic Workload Repository - AWR
• Automatic Database Diagnostic Monitor - ADDM

SQL Tuning Advisor, newly introduced in 10g, is primarily designed to replace the manual tuning of SQL statements and speed up the overall SQL tuning process. SQL Tuning Advisor studies poorly executing SQL statements and evaluates resource consumption in terms of CPU, I/O, and temporary space. The advisor receives one or more SQL statements as input and provides advice on how to optimize their execution plans, gives the rationale for the advice, the estimated performance benefit, and the actual command to implement the advice. We will cover more details in the following sections.

In addition to SQL Tuning Advisor, Oracle provides the SQLAccess Advisor, which provides expert advice on materialized views, indexes, and materialized view logs. A full detailed account of its utility and usage is covered in Chapter 16, Business Intelligence.

Automatic Workload Repository (AWR) collects, processes, and monitors performance-related statistics. The information aids problem detection and self-tuning. Statistics collected by the AWR are persistent and are stored in the database. Automatic Database Diagnostic Monitor (ADDM) constantly analyzes them. The topics of AWR and ADDM are fully covered in Chapter 9, New Manageability Features.

Automatic Statistics Gathering is another new feature that significantly helps the SQL Tuning process. A more detailed account is available in Chapter 9, New Manageability Features.