0

Enabling Automated SQL Tuning in Oracle 11g

You need to identify if Automatic SQL Tuning job is enabled and regularly running. Use the following query to determine if any Automatic SQL Tuning jobs are enabled:

SELECT client_name, status, consumer_group, window_group
FROM dba_autotask_client
ORDER BY client_name;

If the Automatic SQL Tuning job is enabled, you will see something like this:

 

 

 

Run the following query to view the last several times the Automatic SQL Tuning Advisor job has run:

SELECT task_name, status, TO_CHAR(execution_end,'DD-MON-YY HH24:MI')
FROM dba_advisor_executions
WHERE task_name='SYS_AUTO_SQL_TUNING_TASK'
ORDER BY execution_end;

You will get the last execution date-time and status of Automatic SQL Tuning Task (SYS_AUTO_TUNING_TASK) or you will get the message “No rows selected,” which obviously means that the Automatic SQL Tuning is enabled but the job has not been running.

How Does Automatic SQL Tuning Work?

When you create a database in Oracle Database 11g or higher, Oracle automatically implements three automatic maintenance jobs:

• Automatic SQL Tuning Advisor
• Automatic Segment Advisor
• Automatic Optimizer Statistics Collection

These tasks are automatically configured to run in maintenance windows. A maintenance window is a specified time and duration for the task to run. You can view the maintenance window details with this query:

SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')
,sql_tune_advisor, optimizer_stats, segment_advisor
FROM dba_autotask_window_clients;

 

 

 

There are several data dictionary views related to the automatically scheduled jobs. They are:

• DBA_AUTOTASK_CLIENT – Statistical information about automatic jobs
• DBA_AUTOTASK_CLIENT_HISTORY – Window history of job execution
• DBA_AUTOTASK_CLIENT_JOB – Currently running automatic scheduled jobs
• DBA_AUTOTASK_JOB_HISTORY – History of automatic scheduled job runs
• DBA_AUTOTASK_SCHEDULE – Schedule of automated tasks for next 32 days
• DBA_AUTOTASK_TASK – Information regarding current and past tasks
• DBA_AUTOTASK_OPERATION – Operations for automated tasks
• DBA_AUTOTASK_WINDOW_CLIENTS – Displays windows that belong to the MAINTENANCE_WINDOW_GROUP

How to View the Results of SQL Tuning Advisor:

Here’s the quickest way to display automatically generated SQL tuning advice. You will have to run either of the following commands via SQL *Plus (Oracle Database 11g Release 2 uses the DBMS_AUTO_SQLTUNE package instead of DBMS_SQLTUNE):

SQL> SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;
SQL> SELECT DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

Depending on the activity and load on your database, the report may contain no suggestions or may provide a great deal of advice. The Automatic SQL Tuning job uses the high workload SQL statements identified in the AWR as the target SQL statements to report on.

The general information section contains high-level information regarding the start and end time, number of SQL statements considered, cumulative elapsed time of the SQL statements, and so on. The summary section contains summarized information regarding the SQL statements analyzed and the detail section contains information describing specific SQL statements, such as the owner and SQL text.

A typical recommendation would be something similar to below:

FINDINGS SECTION
-------------------------------------------------------------------------------
1- SQL Profile Finding (see explain plans section below)
-------------------------------------------------------
A potentially better execution plan was found for this statement.
Recommendation (estimated benefit: 98.27%)
-----------------------------------------
- Consider accepting the recommended SQL profile to use parallel execution for this statement.
execute dbms_sqltune.accept_sql_profile(task_name =>
'SYS_AUTO_SQL_TUNING_TASK', object_id => 1160, task_owner =>
'SYS', replace => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE);
.................
2- Index Finding (see explain plans section below)
-------------------------------------------------
The execution plan of this statement can be improved by creating one or more indices.
Recommendation (estimated benefit: 99.9%)
----------------------------------------
- Consider running the Access Advisor to improve the physical schema design or 
creating the recommended index.
create index CHAINSAW.IDX$$_90890002 on
CHAINSAW.COMPUTER_SYSTEM("UPDATE_TIME_DTT");

Where appropriate, the original execution plan for a query is displayed along with a suggested fix and new execution plan. This allows you to see the before and after plan differences. This is very useful when determining if the findings (such as adding an index) would improve performance. Lastly, there is an error section of the report, which will usually be blank in most of the cases.

The parameters for the REPORT_AUTO_TUNING_TASK function are mentioned below:

• BEGIN_EXEC – Name of beginning task execution; NULL means the most recent task is used.
• END_EXEC – Name of ending task; NULL means the most recent task is used.
• TYPE – Type of report to produce; TEXT specifies a text report.
• LEVEL – Level of detail; valid values are BASIC, TYPICAL, and ALL.
• SECTION – Section of the report to include; valid values are ALL, SUMMARY, FINDINGS, PLAN,
  INFORMATION, and ERROR.
• OBJECT ID – Used to report on a specific statement; NULL means all statements.
• RESULT_LIMIT – Maximum number of SQL statements to include in report

You will need the DBMS_SQLTUNE.SCRIPT_TUNING_TASK function to generate the SQL statements to implement the
advice of a tuning task. You need to provide as input the name of the automatic tuning task. If the tuning task doesn’t have any advice to give, then there won’t be any SQL statements generated in the output. SYS_AUTO_SQL_TUNING_TASK is the default name of the Automatic SQL Tuning task.

You can use this query DBA_ADVISOR_LOG to check the execution time of the same.

You can also use Enterprise Manager to manage the features regarding Automatic SQL Tuning. From the main database page, navigate to the Advisor Central page. Next, click the SQL Advisors link. Now click the Automatic SQL Tuning Results page. From this screen, you can configure, view results, disable, and enable various aspects of Automatic SQL Tuning.

How to Enable or DISABLE Automatic SQL Tuning

To ENABLE Automatic SQL Tuning, use the ENABLE procedure as shown below

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

To DISABLE Automatic SQL Tuning, use the DISABLE procedure as shown below

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;

Go to the top of this article post to get the SQL to validate whether the Auto SQL Tuning job is enabled or not. Usually the systems which are online 24×7 and highly active do not prefer to have Auto SQL Tuning enabled to ensure that this job doesn’t impact the overall performance of the database.