Oracle 11g introduces Automatic SQL Tuning. Oracle 11g runs the SQL Tuning Advisor against SQL statements determined to be of high impact based on statistics from the Automatic Workload Repository (AWR). The AWR statistics are used to make a list of SQL statements and orders them based on their greatest performance impact on the system during the past week. The SQL list will automatically exclude all SQL statements determined to be less tunable, such as parallel queries, DML, DDL, and any SQL statements where performance problems are determined to be caused by concurrency issues.
The SQL Tuning Advisor generates recommendations to tune SQL that may include SQL profiles (using statistical information). When SQL profiles are recommended, the SQL profiles are performance tested, and if the result is at least a threefold improvement, they are accepted when the SQL Tuning Task parameter ACCEPT_SQL_PROFILES is set to TRUE and reported when ACCEPT_SQL_PROFILES is set to FALSE. You can also perform any of these steps individually for any query.
The ENABLE and DISABLE procedures of the DBMS_AUTO_TASK_ADMIN package control the execution of the Automatic SQL Tuning Tasks. When enabled, the SQL Tuning Advisor is run during the defined maintenance windows; the default maintenance windows are shown in Table 1.
| Window Name | Description |
| MONDAY_WINDOW | Starts at 10 p.m. on Monday to 2 a.m. |
| TUESDAY_WINDOW | Starts at 10 p.m. on Tuesday to 2 a.m. |
| WEDNESDAY_WINDOW | Starts at 10 p.m. on Wednesday to 2 a.m. |
| THURSDAY_WINDOW | Starts at 10 p.m. on Thursday to 2 a.m. |
| FRIDAY_WINDOW | Starts at 10 p.m. on Friday to 2 a.m. |
| SATURDAY_WINDOW | Starts at 6 a.m. on Saturday and is 20 hours long. |
| SUNDAY_WINDOW | Starts at 6 a.m. on Sunday and is 20 hours long. |
TABLE 1. Oracle 11g Default Maintenance Windows
To enable and disable automatic SQL Tuning:
To configuring automatic SQL Tuning:
The following are the DBMS_SQLTUNE options for controlling Automatic SQL Tuning tasks:
- INTERRUPT_TUNING_TASK To interrupt a task while executing, causing a normal exit with intermediate results
- RESUME_TUNING_TASK To resume a previously interrupted task
- CANCEL_TUNING_TASK To cancel a task while executing, removing all results from the task
- RESET_TUNING_TASK To reset a task while executing, removing all results from the task and returning the task to its initial state
- DROP_TUNING_TASK To drop a task, removing all results associated with the task
Leave a Reply