1. Sys_auto_sql_tuning_task Not Running
2. Sys_auto_sql_tuning_task Exiting With Error

The DBMS_AUTO_SQLTUNE package is the interface for managing the Automatic SQL Tuning task. Unlike DBMS_SQLTUNE, the DBMS_AUTO_SQLTUNE package requires the DBA role.

Sys_auto_sql_tuning_task Not Running

Note:

Nov 13, 2015  This is the second in our series of 'Questions You Asked' and this time the question has to do with interval partitioning and whether newly created partitions will be populated into the IM column store.We'll begin our experiment by creating an interval partitioned table based on the SUPPLIER table from our SSB schema. I've added a keyno column to make the interval partitioning easy. Delivering Oracle Success Automatic SQL Tuning in Oracle Database 10g and 11g Lucy Feng RMOUG Training Days February 15-17, 2011.

This functionality is available starting with Oracle Database 11g Release 2 (11.2.0.2).

The chapter contains the following topics:

• • Overview

  • Security Model

Using DBMS_AUTO_SQLTUNE

Overview

The DBMS_AUTO_SQLTUNE package is the interface to SQL Tuning Advisor (DBMS_SQLTUNE) when run within the Autotask framework. The database creates the automated system task SYS_AUTO_SQL_TUNING_TASK as part of the catalog scripts. This task automatically chooses a set of high-load SQL from AWR and runs the SQL Tuning Advisor on this SQL. The automated task performs the same comprehensive analysis as any other SQL Tuning task.

The automated task tests any SQL profiles it finds by executing both the old and new query plans. Automatic SQL Tuning differs from manual SQL tuning in one important way. If automatic implementation of SQL profiles is enabled (the default is disabled), then the database implements any SQL profiles that promise a great performance benefit. The implementation occurs at tuning time so that the database can immediately benefit from the new plan. You can enable or disable automatic implementation by using the SET_AUTO_TUNING_TASK_PARAMETER API to set the ACCEPT_SQL_PROFILES parameter.

In each maintenance window, the automated tuning task stores its results as a new execution. Each execution result has the same task name but a different execution name. Query the DBA_ADVISOR_EXECUTIONS view for information about task executions. Use the REPORT_AUTO_TUNING_TASK Function to view reports that span multiple executions.

Security Model

This package is available to users with the DBA role. For other users, you must grant the EXECUTE privilege on the package explicitly. Note that the EXECUTE_AUTO_TUNING_TASK procedure is an exception: only SYS can invoke it.

Users can call APIs in this package to control how the automatic tuning task behaves when it runs, such as enabling automatic SQL profile creation and configuring the total and per-SQL time limits under which the task runs. Because these settings affect the overall performance of the database, it may not be appropriate for all users with the ADVISOR privilege to have access to this package.

Summary of DBMS_AUTO_SQLTUNE Subprograms

Table 28-1 DBMS_AUTO_SQLTUNE Package Subprograms

Subprogram	Description
Executes the Automatic SQL Tuning task immediately (SYS only) Spitfire audio vst download free. LABS Amplified Cello Quartet was recorded by our founder Christian Henson and engineer Harry Wilson in 2017, here at Spitfire HQ. Christian played with the sound during the live recordings using effects. Spitfire Audio is a British music technology company, founded in 2007 by composers Christian Henson and Paul Thomson, producing high quality virtual instruments and sample libraries.
Displays a text report of the automatic tuning task's history
Changes a task parameter value for the daily automatic runs

EXECUTE_AUTO_TUNING_TASK Function & Procedure

This function and procedure executes the Automatic SQL Tuning task (SYS_AUTO_SQL_TUNING_TASK). Both the function and the procedure run in the context of a new task execution. The difference is that the function returns that new execution name. Note that only SYS can invoke this subprogram.

Syntax

Parameters

Table 28-2 EXECUTE_TUNING_TASK Function & Procedure Parameters

Parameter	Description
execution_name	A name to qualify and identify an execution. If not specified, it is generated by the advisor and returned by function.
execution_params	List of parameters (name, value) for the specified execution. The execution parameters have effect only on the execution for which they are specified. Precision auto tune alignment price. They override the values for the parameters stored in the task (set through the SET_AUTO_TUNING_TASK_PARAMETER Procedures).
execution_desc	A 256-length string describing the execution

Usage Notes

A tuning task can be executed multiple times without having to reset it.

Examples

REPORT_AUTO_TUNING_TASK Function

This procedure displays the results of an Automatic SQL Tuning task.

Syntax

Parameters

Table 28-3 REPORT_TUNING_TASK Function Parameters

Parameter	Description
begin_exec	Name of the beginning task execution to use. If NULL, the report is generated for the most recent task execution.
end_exec	Name of the ending task execution to use. If NULL, the report is generated for the most recent task execution.
type	Type of the report to produce. Possible values are TEXT which produces a text report.
level	Level of detail in the report: BASIC: simple version of the report. Just show info about the actions taken by the advisor. TYPICAL: show information about every statement analyzed, including requests not implemented. ALL: highly detailed report level, also provides annotations about statements skipped over.
section	Section of the report to include: SUMMARY: summary information FINDINGS: tuning findings PLAN: explain plans INFORMATION: general information ERROR: statements with errors ALL: all sections
object_id	Advisor framework object id that represents a single statement to restrict reporting to. NULL for all statements. Only valid for reports that target a single execution.
result_limit	Maximum number of SQL statements to show in the report

Return Values

A CLOB containing the desired report.

Examples

SET_AUTO_TUNING_TASK_PARAMETER Procedures

This procedure updates the value of a SQL tuning parameter of type VARCHAR2 or NUMBER as used for the reserved auto tuning task, SYS_AUTO_SQL_TUNING_TASK.

Syntax

Parameters

Table 28-4 SET_AUTO_TUNING_TASK_PARAMETER Procedure Parameters

Parameter	Description
parameter	Name of the parameter to set. The possible tuning parameters that can be set by this procedure using the parameter in the form VARCHAR2: MODE: tuning scope (comprehensive, limited) USERNAME: username under which the statement is parsed DAYS_TO_EXPIRE: number of days until the task is deleted EXECUTION_DAYS_TO_EXPIRE: number of days until the tasks's executions is deleted (without deleting the task) DEFAULT_EXECUTION_TYPE: the task defaults to this type of execution when none is specified by the EXECUTE_AUTO_TUNING_TASK Function & Procedure. TIME_LIMIT: global time out (seconds) LOCAL_TIME_LIMIT: per-statement time out (seconds) TEST_EXECUTE: FULL/AUTO/OFF. * FULL - test-execute for as much time as necessary, up to the local time limit for the SQL (or the global task time limit if no SQL time limit is set) * AUTO - test-execute for an automatically-chosen time proportional to the tuning time * OFF - do not test-execute BASIC_FILTER: basic filter for SQL tuning set OBJECT_FILTER: object filter for SQL tuning set PLAN_FILTER: plan filter for SQL tuning set (see SELECT_SQLSET for possible values) RANK_MEASURE1: first ranking measure for SQL tuning set RANK_MEASURE2: second possible ranking measure for SQL tuning set RANK_MEASURE3: third possible ranking measure for SQL tuning set RESUME_FILTER: a extra filter for SQL tuning sets besides BASIC_FILTER SQL_LIMIT: maximum number of SQL statements to tune SQL_PERCENTAGE: percentage filter of SQL tuning set statements The following parameters are supported for the automatic tuning task only: ACCEPT_SQL_PROFILES: TRUE/FALSE: whether the task should accept SQL profiles automatically MAX_AUTO_SQL_PROFILES: maximum number of automatic SQL profiles allowed on the system, in sum MAX_SQL_PROFILES_PER_EXEC: maximum number of SQL profiles that can be automatically implemented per execution of the task.
value	New value of the specified parameter

The Compare Explain Plans section at the bottom of the page includes tabs that that enable you to view one or more execution plans for the selected statement. The four tabs that can appear are the Original Plan, Original Plan with Adjusted Cost, Plan Using SQL Profile, and Alternative Plan tabs. The Graphical and Tabular buttons enable you to display an execution plan in graphical or tabular format. In this example, the execution plan is displayed in tabular format.

For recommendations that do not include a potentially better execution plan, only the Original Plan tab appears, and the operations for the original plan are shown on the tab.

When you click the Original Plan with Adjusted Cost tab, the execution plan steps are the same as the Original Plan steps, but the Original Plan with Adjusted Cost steps have different costs for the steps (as shown in the Operation Cost column).

If you click the Plan Using SQL Profile tab, the steps are different than the Original Plan steps, and the steps have different costs (as shown in the Operation Cost column).

The Alternative Plan button appears when the execution history for the original plan cannot be found. In this case, if you know that the alternative plan suggested by SQL Tuning Advisor is better than the original plan, you can create a SQL plan baseline for the alternative plan so that the Oracle optimizer will pick the alternative plan for the statement in the future.

Sys_auto_sql_tuning_task Exiting With Error

Click the Implement button at the top of the Recommendation Details page to implement a recommendation.