Automated SQL Tuning

Prior to Oracle Database 11g, accurately identifying poorly performing SQL queries and recommending solutions was mainly the purview of veteran SQL tuners. Typically one had to know how to identify high-resource SQL statements and bottlenecks, generate and interpret execution plans, extract data from the dynamic performance views, understand wait events and statistics, and then collate this knowledge to produce good SQL queries. As you’ll see in this chapter, the Oracle SQL tuning paradigm
has shifted a bit.

With the advent of automated SQL tuning features, anybody from novice to expert can generate and recommend solutions for SQL performance problems. This opens the door for new ways to address problematic SQL. For example, imagine your boss coming to you each morning with tuning recommendations and asking what the plan is to implement enhancements. This is different.

The automated SQL tuning feature is not a panacea for SQL performance angst. If you are an expert SQL tuner, there’s no need to fear your skills are obsolete or your job is lost. There will always be a need to verify recommendations and successfully implement solutions. A human is still required to review the automated SQL tuning output and confirm the worthiness of fixes.

Still, there’s been a change in the way SQL performance problems can be identified and solutions can be recommended. Some old-school folks may disagree and argue that you can’t allow just anybody to generate SQL tuning advice. Regardless, Oracle has made these automated tools accessible and usable by the general population (for a fee). Therefore you need to understand the underpinnings of these features and how to use them.

This chapter focuses on the following automated SQL tuning tools:

• Automatic SQL Tuning
• SQL tuning sets (STS)
• SQL Tuning Advisor
• Automatic Database Diagnostic Monitor (ADDM)

Starting with Oracle Database 11g, Automatic SQL Tuning is a preset background database job that by default runs every day. This task examines high resource-consuming statements in the Automatic Workload Repository (AWR). It then invokes the SQL Tuning Advisor and generates tuning advice (if any) for each statement analyzed. As part of automated SQL tuning, you can configure characteristics such as the automatic acceptance of some recommendations such as SQL profiles

A SQL tuning set (STS) is a database object that contains one or more SQL statements and the associated execution statistics. You can populate a SQL tuning set from multiple sources, such as SQL recorded in the AWR and SQL in memory, or you can provide specific SQL statements. It’s critical that you be familiar with SQL tuning sets. This feature is used as an input to several of Oracle’s performance tuning and management tools, such as the SQL Tuning Advisor, SQL Plan Management, SQL Access Advisor, and SQL Performance Advisor.

The SQL Tuning Advisor is central to Oracle’s Automatic SQL Tuning feature. This tool runs automatically on a periodic basis and generates tuning advice for high resource-consuming SQL statements found in the AWR. You can also run the SQL Tuning Advisor manually and provide as input specific snapshot periods in the AWR, high resource-consuming SQL in memory, or user-provided SQL statements. This tool can be invoked via the DBMS_SQLTUNE package, SQL Developer, or Enterprise
Manager.

The Automatic Database Diagnostic Monitor (ADDM) analyzes information in the AWR and provides recommendations on database performance issues including high resource-consuming SQL statements. The main goal of ADDM is to help you reduce the overall time (the DB time metric) spent by the database processing user requests. This tool can be invoked from an Oracle-provided SQL script(@?/rdbms/admin/addmrpt.sql), the DBMS_ADDM package, or Enterprise Manager.

Please take a long look at Figure 11-1. This diagram demonstrates how the various automated tools interact and in what scenarios you would use a particular feature. Refer back to this diagram as you work through the recipes in this chapter.
Particularly notice that you can easily use SQL statements found in the AWR or SQL currently in memory as input for various Oracle tuning tools. This allows you to systematically identify and use high-resource SQL statements as the target for various performance tuning activities.