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.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.SET_AUTO_TUNING_TASK_PARAMETER
API to set the ACCEPT_SQL_PROFILES
parameter.DBA_ADVISOR_EXECUTIONS
view for information about task executions. Use the REPORT_AUTO_TUNING_TASK Function to view reports that span multiple executions.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.ADVISOR
privilege to have access to this package.Subprogram | Description |
---|---|
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 |
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.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. 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 |
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:
|
section | Section of the report to include:
|
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 |
CLOB
containing the desired report.VARCHAR2
or NUMBER
as used for the reserved auto tuning task, SYS_AUTO_SQL_TUNING_TASK
.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 :
The following parameters are supported for the automatic tuning task only:
|
value | New value of the specified parameter |
OPTIMIZER_MODE
initialization parameter setting from ALL_ROWS
to FIRST_ROWS
for that SQL statement.CATEGORY
profile attribute. This attribute determines which user sessions can apply the profile. You can view the CATEGORY
attribute for a SQL Profile in CATEGORY
column of the DBA_SQL_PROFILES
view. By default, all profiles are created in the DEFAULT
category. This means that all user sessions where the SQLTUNE_CATEGORY
initialization parameter is set to DEFAULT
can use the profile.DEV
, only those users sessions where the SQLTUNE_CATEGORY
initialization parameter is set to DEV
can use the profile. All other sessions do not have access to the SQL Profile and execution plans for SQL statements are not impacted by the SQL profile. This technique enables you to test a SQL Profile in a restricted environment before making it available to other user sessions.SQLTUNE_CATEGORY
initialization parameterSELECT
statementsUPDATE
statementsINSERT
statements (only with a SELECT
clause)DELETE
statementsCREATE
TABLE
statements (only with the AS
SELECT
clause)MERGE
statements (the update or insert operations)UNION
operator with UNION
ALL
or to replace NOT
IN
with NOT
EXISTS
. An application developer can then determine if the advice is applicable to their situation or not. For instance, if the schema design is such that there is no possibility of producing duplicates, then the UNION
ALL
operator is much more efficient than the UNION
operator. These changes require a good understanding of the data properties and should be implemented only after careful consideration.ACCEPT_SQL_PROFILES
task parameter is set to TRUE
). Otherwise, only the recommendation to create a SQL profile will be reported in the automatic SQL tuning reports.AUTO
in the DBA_SQL_PROFILES
view.ENABLE
procedure in the DBMS_AUTO_TASK_ADMIN
package:window_name
parameter to enable or disable the task in certain maintenance windows only.STATISTICS_LEVEL
parameter to BASIC
will disable automatic statistics gathering by the AWR and, as a result, also disable automatic SQL tuning.DBMS_AUTO_TASK_ADMIN
packageDBMS_SQLTUNE
package. To use the APIs, the user needs at least the ADVISOR
privilege.DBMS_SQLTUNE
package enables you to configure automatic SQL tuning by specifying the task parameters using the SET_TUNING_TASK_PARAMETER
procedure. Because the automatic tuning task is owned by SYS, only the SYS user can set the task parameters.Parameter | Description |
---|---|
ACCEPT_SQL_PROFILE | Specifies whether to accept SQL profiles automatically. |
MAX_SQL_PROFILES_PER_EXEC | Specifies the limit of SQL profiles that are accepted for each automatic SQL tuning task. Consider setting the limit of SQL profiles that are accepted for each automatic SQL tuning task based on the acceptable level of changes that can be made to the system on a daily basis. |
MAX_AUTO_SQL_PROFILES | Specifies the limit of SQL profiles that are accepted in total. |
EXECUTION_DAYS_TO_EXPIRE | Specifies the number of days for which to save the task history in the advisor framework schema. By default, the task history is saved for 30 days before it expires. |
SET_TUNING_TASK_PARAMETER
procedure in the DBMS_SQLTUNE
package:DBMS_SQLTUNE
packageDBMS_SQLTUNE
.REPORT_AUTO_TUNING_TASK
function and contains information about all executions of the automatic SQL tuning task. To run this report, you need the ADVISOR
privilege and SELECT privileges on the DBA_ADVISOR
views. Unlike the standard SQL tuning report generated using the DBMS_SQLTUNE
.REPORT_TUNING_TASK
function, which only contains information about a single task execution of the SQL Tuning Advisor, the automatic SQL tuning report contains information about multiple executions of the automatic SQL tuning task.REPORT_AUTO_TUNING_TASK
function in the DBMS_SQLTUNE
package:DBMS_SQLTUNE
DBMS_SQLTUNE
package. To use the APIs, the user must be granted specific privileges.DBMS_SQLTUNE
packageDBMS_SQLTUNE
package is a multi-step process:DBMS_SQLTUNE
package.DBMS_SQLTUNE
packageADVISOR
privilege and the function is run as user HR on the employees
table in the HR schema.:bnd
passed as function argument of type SQL_BINDS
, HR
is the user under which the CREATE_TUNING_TASK
function analyzes the SQL statement, the scope is set to COMPREHENSIVE
which means that the advisor also performs SQL Profiling analysis, and 60 is the maximum time in seconds that the function can run. In addition, values for task name and description are provided.CREATE_TUNING_TASK
function returns the task name that you have provided or generates a unique task name. You can use the task name to specify this task when using other APIs. To view the task names associated with a specific owner, you can run the following:SET_TUNING_TASK_PARAMETER
procedure in the DBMS_SQLTUNE
package:SET_TUNING_TASK_PARAMETER
procedure.Parameter | Description |
---|---|
MODE | Specifies the scope of the tuning task:
|
USERNAME | Username under which the SQL statement will be parsed |
DAYS_TO_EXPIRE | Number of days before the task is deleted |
DEFAULT_EXECUTION_TYPE | Default execution type if not specified by the EXECUTE_TUNING_TASK function when the task is executed |
TIME_LIMIT | Time limit (in number of seconds) before the task times out |
LOCAL_TIME_LIMIT | Time limit (in number of seconds) for each SQL statement |
TEST_EXECUTE | Determines if the SQL Tuning Advisor will test execute the SQL statements to verify the recommendation benefit:
|
BASIC_FILTER | Basic filter used for SQL tuning set |
OBJECT_FILTER | Object filter used for SQL tuning set |
PLAN_FILTER | Plan filter used for SQL tuning set |
RANK_MEASURE1 | First ranking measure used for SQL tuning set |
RANK_MEASURE2 | Second ranking measure used for SQL tuning set |
RANK_MEASURE3 | Third ranking measure used for SQL tuning set |
RESUME_FILTER | Extra filter used for SQL tuning set (besides BASIC_FILTER ) |
SQL_LIMIT | Maximum number of SQL statements to tune |
SQL_PERCENTAGE | Percentage filter of statements from SQL tuning set |
SYS_AUTO_SQL_TUNING_TASK
using the EXECUTE_TUNING_TASK
API. The SQL Tuning Advisor will perform the same analysis and actions as it would when run automatically. You can also pass an execution name to the API to name the new execution.V$SESSION_LONGOPS
view. For example:V$ADVISOR_PROGRESS
view. For example:REPORT_TUNING_TASK
function. For example:INTERRUPT_TUNING_TASK
to interrupt a task while executing, causing a normal exit with intermediate resultsRESUME_TUNING_TASK
to resume a previously interrupted taskCANCEL_TUNING_TASK
to cancel a task while executing, removing all results from the taskRESET_TUNING_TASK
to reset a task while executing, removing all results from the task and returning the task to its initial stateDROP_TUNING_TASK
to drop a task, removing all results associated with the taskDBMS_SQLTUNE
package procedures.DBMS_SQLTUNE
package procedures. Typically you would use the STS operations in the following sequence:ADMINISTER SQL TUNING SET
system privilege to manage SQL Tuning Sets that you own, or the ADMINISTER
ANY
SQL
TUNING
SET
system privilege to manage any SQL Tuning Sets.DBMS_SQLTUNE
packageCREATE_SQLSET
procedure is used to create an empty STS object in the database. For example, the following procedure creates an STS object that could be used to tune I/O intensive SQL statements during a specific period of time:my_sql_tuning_set
is the name of the STS in the database and 'I/O intensive workload'
is the description assigned to the STS.LOAD_SQLSET
procedure populates the STS with selected SQL statements. The standard sources for populating an STS are the workload repository, another STS, or the cursor cache. For both the workload repository and STS, predefined table functions can be used to select columns from the source to populate a new STS.my_sql_tuning_set
from an AWR baseline called peak
baseline
. The data has been filtered to select only the top 30 SQL statements ordered by elapsed time. First a ref cursor is opened to select from the specified baseline. Next the statements and their statistics are loaded from the baseline into the STS.SELECT_SQLSET
table function reads the contents of the STS. After an STS has been created and populated, you can browse the SQL in the STS using different filtering criteria. The SELECT_SQLSET
procedure is provided for this purpose.DBA_SQLSET
, DBA_SQLSET_STATEMENTS
, and DBA_SQLSET_BINDS
.DELETE_SQLSET
procedure deletes SQL statements from my_sql_tuning_set
that have been executed less than fifty times.CREATE_STGTAB_SQLSET
procedure to create a staging table where the SQL Tuning Sets will be exported.staging_table
. Table names are case-sensitive.PACK_STGTAB_SQLSET
procedure to export SQL Tuning Sets into the staging table.my_sts
to the staging table.UNPACK_STGTAB_SQLSET
procedure to import SQL Tuning Sets from the staging table.DROP_SQLSET
procedure is used to drop an STS that is no longer needed. For example:UPDATE_SQLSET
procedure updates the attributes of SQL statements (such as PRIORITY
or OTHER
) in an existing STS identified by STS name and SQL identifier.CAPTURE_CURSOR_CACHE_SQLSET
function enables the capture of the full system workload by repeatedly polling the cursor cache over a specified interval. This function is a lot more efficient than repeatedly using the SELECT_CURSOR_CACHE
and LOAD_SQLSET
procedures to capture the cursor cache over an extended period of time. This function effectively captures the entire workload, as opposed to the AWR—which only captures the workload of high-load SQL statements—or the LOAD_SQLSET
procedure, which accesses the data source only once.ADD_SQLSET_REFERENCE
function adds a new reference to an existing STS to indicate its use by a client. The function returns the identifier of the added reference. The REMOVE_SQLSET_REFERENCE
procedure is used to deactivate an STS to indicate it is no longer used by the client.DBMS_SQLTUNE
package. To use the SQL Profiles APIs, you need the ADMINISTER SQL MANAGEMENT OBJECT
privilege.DBMS_SQLTUNE
packageDBMS_SQLTUNE.ACCEPT_SQL_PROFILE
procedure to accept a SQL Profile recommended by the SQL Tuning Advisor. This creates and stores a SQL Profile in the database. For example:sql_tuning_task
is the name of the SQL tuning task and my_sql_profile
is the name of the SQL Profile that you want to accept.force_match
to true, the SQL Profile will additionally target all SQL statements that have the same text after normalizing literal values to bind variables. This may be useful for applications that use literal values rather than bind variables, since this will allow SQL with text differing only in its literal values to share a SQL Profile. If both literal values and bind variables are used in the SQL text, or if this parameter is set to false (the default value), literal values will not be normalized.DBA_SQL_PROFILES
view.STATUS
, NAME
, DESCRIPTION
, and CATEGORY
attributes of an existing SQL Profile with the ALTER_SQL_PROFILE
procedure. For example:my_sql_profile
is the name of the SQL Profile that you want to alter. The status attribute is changed to disabled, which means the SQL Profile is not used during SQL compilation.DROP_SQL_PROFILE
procedure. For example:my_sql_profile
is the name of the SQL Profile you want to drop. You can also specify whether to ignore errors raised if the name does not exist. For this example, the default value of FALSE
is accepted.DBA_ADVISOR_TASKS
, DBA_ADVISOR_EXECUTIONS
, DBA_ADVISOR_FINDINGS
, DBA_ADVISOR_RECOMMENDATIONS
, and DBA_ADVISOR_RATIONALE
views.DBA_SQLTUNE_STATISTICS
, DBA_SQLTUNE_BINDS
, and DBA_SQLTUNE_PLANS
views.DBA_SQLSET
, DBA_SQLSET_BINDS
, DBA_SQLSET_STATEMENTS
, and DBA_SQLSET_REFERENCES
views.DBA_SQLSET_PLANS
and USER_SQLSET_PLANS
views.DBA_SQL_PROFILES
view.TYPE
parameter shows if the SQL profile was created manually by the SQL Tuning Advisor (if TYPE
= MANUAL
) or automatically by automatic SQL tuning (if TYPE
= AUTO
).V$ADVISOR_PROGRESS
view.V$SQL
, V$SQLAREA
, V$SQLSTATS
, and V$SQL_BINDS
views.