May 07, 2015  Steps to run DBMSSQLTUNE,DBMSADVISOR (Tunning Advisor, SQL Access Advisor) for a bad SQL - Step 1: Run the below block with the SQL to be analyzed This creates a new tunning task in the database with the given name. Feb 27, 2017  These messages indicate that an auto kill of a “hung”/long running tuning task has taken place. This is a protective measure purely to avoid the task from over-running its time limit because of a single task and protects a the system from harm caused by such over-running.

  1. Dbms_sqltune.create_tuning_task Example
  2. Dbms_sqltune.create_tuning_task Parameters
  3. Dbms_sqltune.set_auto_tuning_task_parameter
  4. Exec Dbms_auto_sqltune.set_auto Tuning Task Parameter
  5. Dbms_auto_sqltune.set_auto Tuning Task Parameter 12c
  6. Dbms_sqltune.create_tuning_task 12c

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.

The chapter contains the following topics:

Dbms_sqltune Set_auto_tuning_task_parameter

29.1 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.

29.2 DBMS_AUTO_SQLTUNE 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.

29.3 Summary of DBMS_AUTO_SQLTUNE Subprograms

The DBMS_AUTO_SQLTUNE package contains EXECUTE, REPORT, and SET subprograms.

Table 29-1 DBMS_AUTO_SQLTUNE Package Subprograms

SubprogramDescription

Executes the Automatic SQL Tuning task immediately (SYS only)

Displays a text report of the automatic tuning task's history

Changes a task parameter value for the daily automatic runs

29.3.1 EXECUTE_AUTO_TUNING_TASK Function and 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 the name of the new execution.

Parameters

Table 29-2 EXECUTE_AUTO_TUNING_TASK Function and Procedure Parameters

ParameterDescription

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. 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

Only SYS can invoke this subprogram. A tuning task can be executed multiple times without having to reset it.

29.3.2 REPORT_AUTO_TUNING_TASK Function

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

Parameters

Table 29-3 REPORT_AUTO_TUNING_TASK Function Parameters

ParameterDescription

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.

29.3.3 SET_AUTO_TUNING_TASK_PARAMETER Procedures

This procedure updates the value of a SQL tuning parameter of type VARCHAR2 or NUMBER for SYS_AUTO_SQL_TUNING_TASK.

Parameters

Table 29-4 SET_AUTO_TUNING_TASK_PARAMETER Procedure Parameters

ParameterDescription

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 and 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 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.

Note:

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

The chapter contains the following topics:

    • Overview

    • Security Model

Using DBMS_AUTO_SQLTUNE

Dbms_sqltune.create_tuning_task Example

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

SubprogramDescription

Executes the Automatic SQL Tuning task immediately (SYS only)

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.

Parameters

Table 28-2 EXECUTE_TUNING_TASK Function & Procedure Parameters

ParameterDescription

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. 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

Dbms_sqltune.create_tuning_task Parameters


Usage Notes

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

REPORT_AUTO_TUNING_TASK Function

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

Parameters

Table 28-3 REPORT_TUNING_TASK Function Parameters

ParameterDescription

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.

Aug 07, 2012  There really isn't much in the Auto-tune manual about installing it to use with different hosts. I tried a couple of things: 1. Moving the 3 dll files into the plug-ins folder. The full path is: Program Files(x86)Common FilesDigidesignDAEPlug-ins. Apr 13, 2020  How to Download and Install Auto-Tune Properly in Fl-Studio Free Bangla Fl-Studio Tutorial Most Popular Vst Plugin. Contact Me For Any Help Or bussiness Related Info. Feb 28, 2019  Getting the Auto-Tune Effect with Auto-Tune Pro - Duration: 2:10. Antares Audio Technologies - Auto-Tune 18,432 views. Auto-Tune Evo VST v6.0.9 Free Download This article shows you how to download and install the full version of Auto-Tune Evo VST v6.0.9 for free on PC. Follow the direct download link and instructions below for guidance on installing Auto-Tune Evo VST v6.0.9 on your computer. How to install auto tune evo in pro tools. In this video tutorial, we learn how to use the Antares Auto-Tune Evo pitch-correction plug-in in Graphical Mode when running Pro Tools 8. Whether you're new to Digidesign's popular DAW (digital audio workstation) or simply new to Auto-Tune, you're sure to be well served by this video guide.

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

Dbms_sqltune.set_auto_tuning_task_parameter


Return Values

A CLOB containing the desired report.

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.

Parameters

Exec Dbms_auto_sqltune.set_auto Tuning Task Parameter

Table 28-4 SET_AUTO_TUNING_TASK_PARAMETER Procedure Parameters

Dbms_auto_sqltune.set_auto Tuning Task Parameter 12c

ParameterDescription

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

Dbms_sqltune.create_tuning_task 12c