Automated SQL Tuning in Oracle 11g
Oracle Database 11g made it easier for the less experienced to have a shot at SQL tuning, which was earlier considered to be a bastion for veteran SQL tuners. A typical Oracle SQL tuning process had the following steps:
- Identification of high-resource SQL statements and bottlenecks
- Generating and interpreting execution plans
- Extracting data from the dynamic performance views
- Understanding wait events and statistics
- Collating all the information to recommend changes for a good SQL.
SQL tuning paradigm has shifted a bit and with the advent of automated SQL tuning features, anybody from novice to expert can generate and recommend solutions for SQL performance problems. Oracle has made these automated tools accessible and usable
by the general population (for a fee). We will focus 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.
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 Automatic Workload Repository (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, the DBMS_ADDM package, or Enterprise Manager.
All of the prior listed tools require an extra license from Oracle. You may not have a license to run these tools. We will show you how to determine if and when the automated job is running and how to modify its characteristics; creating and managing SQL tuning sets. SQL tuning sets are used widely as input to various Oracle performance tuning tools; Finally we will show you how to
manually run the SQL Tuning Advisor and ADDM to generate performance recommendations for SQL statements.
