| General Information |
| Purpose |
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. |
| Source |
{$ORACLE_HOME}/rdbms/admin/dbmssqlt.sql |
| Constants |
| Name |
Data Type |
Value |
| Report Level |
| LEVEL_ALL |
VARCHAR2(3) |
dbms_sqltune.LEVEL_ALL |
| LEVEL_BASIC |
VARCHAR2(5) |
dbms_sqltune.LEVEL_BASIC |
| LEVEL_TYPICAL |
VARCHAR2(7) |
dbms_sqltune.LEVEL_TYPICAL |
| Report Section |
| SECTION_ALL |
VARCHAR2(3) |
dbms_sqltune.SECTION_ALL |
| SECTION_ERRORS |
VARCHAR2(6) |
dbms_sqltune.SECTION_ERRORS |
| SECTION_FINDINGS |
VARCHAR2(8) |
dbms_sqltune.SECTION_FINDINGS |
| SECTION_INFORMATION |
VARCHAR2(11) |
dbms_sqltune.SECTION_INFORMATION |
| SECTION_PLANS |
VARCHAR2(5) |
dbms_sqltune.SECTION_PLANS |
| SECTION_SUMMARY |
VARCHAR2(7) |
dbms_sqltune.SECTION_SUMMARY |
| Report Type |
| TYPE_HTML |
VARCHAR2(4) |
dbms_sqltune.TYPE_HTML |
| TYPE_TEXT |
VARCHAR2(4) |
dbms_sqltune.TYPE_TEXT |
| TYPE_XML |
VARCHAR2(3) |
dbms_sqltune.TYPE_XML |
|
| Dependencies |
| DBMS_ADVISOR |
DBMS_SQLTUNE_INTERNAL |
PRVT_SQLADV_INFRA |
| DBMS_SQLTUNE |
PRVT_ADVISOR |
|
|
| Security Model |
Execute is granted to the DBA role only |
| Subprograms |
|
| |
| EXECUTE_AUTO_TUNING_TASK |
Called to execute SYS_AUTO_SQL_TUNING_TASK
manually. The behavior will be the same as in automatic executions.Only SYS can call this API.
Overload 1 |
dbms_auto_sqltune(
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.arglist := NULL,
execution_desc IN VARCHAR2 := NULL)
RETURN VARCHAR2; |
set serveroutput on
DECLARE
retVal VARCHAR2(64);
BEGIN
retVal := dbms_auto_sqltune;
dbms_output.put_line(retVal);
END;
/ |
| Overload 2 |
dbms_auto_sqltune(
execution_name IN VARCHAR2 := NULL,
execution_params IN dbms_advisor.arglist := NULL,
execution_desc IN VARCHAR2 := NULL); |
| exec dbms_auto_sqltune; |
| |
| REPORT_AUTO_TUNING_TASK |
| Retrieves a report from the automatic tuning task.
This differs from the report_tuning_task API in that it takes a range of subtasks to report on. This API also exists in the DBMS_SQLTUNE package.
All users with access to the views can see a report. |
dbms_auto_sqltune(
begin_exec IN VARCHAR2 := NULL,
end_exec IN VARCHAR2 := NULL,
type IN VARCHAR2 := TYPE_TEXT,
level IN VARCHAR2 := TYPE_TYPICAL,
section IN VARCHAR2 := TYPE_ALL,
object_id IN NUMBER := NULL,
result_limit IN NUMBER := NULL)
RETURN CLOB; |
set serveroutput on
DECLARE
retVal CLOB;
BEGIN
retVal := dbms_auto_sqltune.report_auto_tuning_task(type=>TYPE_HTML, result_limit=>20);
dbms_output.put_line(retVal);
END;
/ |
| |
| SET_AUTO_TUNING_TASK_PARAMETER |
Similar to set_tuning_task_parameter, but used for the reserved auto tuning task. Only SYS can set them.
Overload 1 |
dbms_auto_sqltune.set_auto_tuning_task_parameter(parameter IN VARCHAR2, value IN VARCHAR2); |
| exec dbms_auto_sqltune.set_auto_tuning_task_parameter('TEST_EXECUTE', 'FULL); |
| Overload 2 |
dbms_auto_sqltune.set_auto_tuning_task_parameter(parameter IN VARCHAR2, value IN NUMBER); |
| exec dbms_auto_sqltune.set_auto_tuning_task_parameter('DAYS_TO_EXPIRE', 90); |
