When Oracle introduced automatic SQL tuning in 10g I was a bit reluctant to try it out because I personally felt that SQL tuning is one of the most daunting task as part of DBA job. There are thousands of queries that run on a production database everyday and to find out which are causing problems on overall system performance. Identifying is one thing and then finding out appropriate execution plans or SQL profiles for each of those queries is hectic as well as time consuming. But upon exploring this option I found out that this was exactly the purpose of automatic SQL tuning. Till 10g the task only recommended the SQL profiles and did not applied them by default. In 11g the automatic SQL tuning applies the SQL profiles which meet a certain criteria thus releasing a lot work from DBA’s shoulders.
Oracle automatic SQL tuning is one of three Oracle automated tasks feature and runs in the maintenance window of Oracle scheduler. The automatic SQL tuning is dependent on ADDM which gets its data from AWR snapshots. Automatic SQL tuning will not work if the snapshot period of AWR is set lower to the default value of eight days.
Automatic SQL tuning as mentioned above is a three step process. Here the three steps.
[title type=”h2″ class=”tfuse”]Identifying Inefficient Queries[/title] The first step is to identify the queries which are under performing. Again here for data the task depends on AWR to identify which queries are eligible for tuning. There are four different time periods for which the under performing queries are selected from. These time periods are called buckets and they are weekly, daily, hourly and top SQL in a single execution based on response time. Oracle then assigns these queries weights based on their impact on the overall system.
These weights are then used to define the order in which the queries will be tuned. The most important queries will be tuned first and then in order of their importance are processed further. All under performing queries are candidates for selection except for the below mentioned ones.
[title type=”h2″ class=”tfuse”]Implementing the Recommendations[/title] The last step is to take those recommendations from SQL Advisor and implement them. This step was not automatic in 10g and DBA had to check the recommendations and apply them by himself. However in 11g the job also tries to implement the SQL profile recommendations on the under performing queries. However before Oracle implements any SQL profile the specific SQL profile must meet the following two requirements.
Also it is important to note that job only implements the SQL profile recommendations and not any other recommendations that SQL Advisor provides. For example the job will not create the indexes if it is necessary to improve the performance of the query.
Oracle also provides the PL/SQL package to manage the different options for automatic SQL tuning. You can enable and disable the task as well as configuring the different tuning options. But the easiest method to configure all these options and also enable/disable the task is to use the Enterprise Manager. You can find the option under Server → Oracle Scheduler → Automated Maintenance Tasks → Automatic SQL Tuning.