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
packageDBMS_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.sp_query_store_force_plan
procedure.Database Engine in SQL Server 2017 (14.x) provides information about regressed plans and recommended corrective actions.Additionally, Database Engine enables you to fully automate this process and let Database Engine fix any problem found relatedto the plan changes.sp_query_store_force_plan
procedure. The best practice would be to force the last known good plan because older plans might be invalid due to statistic or index changes. The user who forces the last known good plan should monitor performance of the query that is executed using the forced plan and verify that forced plan works as expected. Depending on the results of monitoring and analysis, plan should be forced or user should find some other way to optimize the query.Manually forced plans should not be forced forever, because the Database Engine should be able to apply optimal plans. The user or DBA should eventuallyunforce the plan using sp_query_store_unforce_plan
procedure, and let the Database Engine find the optimal plan.type | description | datetime | score | details | ... |
---|---|---|---|---|---|
FORCE_LAST_GOOD_PLAN | CPU time changed from 4 ms to 14 ms | 3/17/2017 | 83 | queryId recommendedPlanId regressedPlanId T-SQL | |
FORCE_LAST_GOOD_PLAN | CPU time changed from 37 ms to 84 ms | 3/16/2017 | 26 | queryId recommendedPlanId regressedPlanId T-SQL |
FORCE_LAST_GOOD_PLAN
reason | score | script | query_id | current plan_id | recommended plan_id | estimated_gain | error_prone |
---|---|---|---|---|---|---|---|
CPU time changed from 3 ms to 46 ms | 36 | EXEC sp_query_store_force_plan 12, 17; | 12 | 28 | 17 | 11.59 | 0 |
estimated_gain
represents the estimated number of seconds that would be saved if the recommended plan would be executed instead of the current plan. The recommended plan should be forced instead of the current plan if the gain is greater than 10 seconds. If there are more errors (for example, time-outs or aborted executions) in the current plan than in the recommended plan, the column error_prone
would be set to the value YES
. Error prone plan is another reason why the recommended plan should be forced instead of the current one.