Category - TUNING

Use of DBMS_AUTO_SQLTUNE for Automatic SQL Tuning Advisor

DBMS_AUTO_SQLTUNE

The DBMS_AUTO_SQLTUNE package is the interface for managing the Automatic SQL Tuning task.

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 for SQL Tuning task. This task automatically chooses a set of high-load SQL from AWR and runs SQL Tuning Advisor on this SQL. The automated task performs the same comprehensive analysis as any other SQL Tuning task.

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.

Query the DBA_ADVISOR_EXECUTIONS view for information about task executions

Summary of DBMS_AUTO_SQLTUNE Subprograms

A=> EXECUTE_AUTO_TUNING_TASK Function and Procedure

This function and procedure executes the Automatic SQL Tuning task (SYS_AUTO_SQL_TUNING_TASK) Manually.

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.

Why we need to execute this Manually –“ Sometimes we need to execute autotask manually due to different factor like time,urgency”

So for that we can use EXECUTE_AUTO_TUNING_TASK.

Let we check last execution Task title for Task_name=’ SYS_AUTO_SQL_TUNING_TASK’  as this is related to AUTOTASK Framework for STA.

SQL> select task_id,task_name,advisor_name,LAST_EXECUTION,STATUS from DBA_ADVISOR_TASKS  where task_name='SYS_AUTO_SQL_TUNING_TASK';

TASK_ID TASK_NAME				 ADVISOR_NAME				  LAST_EXECUTION       STATUS
------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
      1 SYS_AUTO_SQL_TUNING_TASK		 SQL Tuning Advisor			  EXEC_612	       COMPLETED
SQL> 

As we get details from Above query , let we execute mamually Auto task as below,

SQL>

Now let we execute Auto Task for STA as below.

SQL> SET SERVEROUTPUT ON

DECLARE
  l_return VARCHAR2(50);
BEGIN
  l_return := DBMS_AUTO_SQLTUNE.execute_auto_tuning_task;
  DBMS_OUTPUT.put_line(l_return);
END;
/
EXEC_613

PL/SQL procedure successfully completed.

AS we can see we get name for task execution as EXEC_613 ,Let we check from above query for the same,

SQL> select task_id,task_name,advisor_name,LAST_EXECUTION,STATUS from DBA_ADVISOR_TASKS  where task_name='SYS_AUTO_SQL_TUNING_TASK';

TASK_ID TASK_NAME				 ADVISOR_NAME				  LAST_EXECUTION       STATUS
------- ---------------------------------------- ---------------------------------------- -------------------- ------------------------------
      1 SYS_AUTO_SQL_TUNING_TASK		 SQL Tuning Advisor			  EXEC_613	       COMPLETED

SQL> 

B=> REPORT_AUTO_TUNING_TASK Function

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

First we need to get Start and End of execution names , We will fetch from below.

col error_message for a70
set lines 200 pages 1000
Select execution_name,to_char(execution_start,'dd-mon-yyyy hh24:mi:ss') as execution_start,
status,error_message from DBA_ADVISOR_EXECUTIONS
where task_name='SYS_AUTO_SQL_TUNING_TASK'
order by execution_id desc;

EXECUTION_NAME    EXECUTION_START                       STATUS                                               ERROR_MESSAGE
-------------------- ----------------------------- ------------------------------ ----------------------------------------------------------------------
EXEC_613                 30-jan-2022 21:08:13                 COMPLETED
EXEC_612                 30-jan-2022 21:04:18                 COMPLETED
EXEC_603                 30-jan-2022 16:47:53                 COMPLETED
EXEC_593                 29-jan-2022 21:43:55                 COMPLETED
EXEC_543                 16-jan-2022 20:14:01                 COMPLETED
EXEC_523                 09-jan-2022 15:20:05                 COMPLETED
EXEC_513                 08-jan-2022 22:18:07                 COMPLETED
7 rows selected.

Now we will generate report as what SQL AUTO TUNING ADVISOR did during that window as below.

VARIABLE report CLOB;
BEGIN
  :report:=DBMS_SQLTUNE.report_auto_tuning_task(begin_exec=>'EXEC_612', end_exec=>'EXEC_613', LEVEL=>'ALL', section=>'ALL');
END;
/
SET LONG 999999999
PRINT :report

SQL> SQL>
REPORT
--------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name                                           : SYS_AUTO_SQL_TUNING_TASK
Tuning Task Owner                                          : SYS
Tuning Task ID                                                   : 1
Workload Type                                                  : Automatic High-Load SQL Workload
Time Span                                                           : 01/30/2022 21:04 - 01/30/2022 21:08
Number of Executions                                   : 2
First Execution Name                                      : EXEC_612
Last Execution Name                                      : EXEC_613
Number of Candidate SQLs (w/duplicates) : 2
Cumulative Elapsed Time of SQL (s)          : 0
…
…

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

But let we first check current parameter setting for SYS_AUTO_SQL_TUNING_TASK.

SET lines 200 pages 100
col DESCRIPTION FOR a100
col parameter_value FOR a30
SELECT parameter_name,parameter_value,description FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='SYS_AUTO_SQL_TUNING_TASK' ORDER BY parameter_name;

PARAMETER_NAME				 PARAMETER_VALUE DESCRIPTION
---------------------------------------- --------------- ----------------------------------------------------------------------------------------------------
ACCEPT_SQL_PROFILES			 FALSE		 TRUE if SQL Profiles should be created by the task, FALSE otherwise
APPLY_CAPTURED_COMPILENV		 UNUSED 	 Indicated whether the advisor could use the compilation environment captured with the SQL statements
BASIC_FILTER				 UNUSED 	 SQL predicate to filter the SQL from the SQL Tuning Set
CON_DBID_MAPPING			 UNUSED
DATABASE_LINK_TO			 UNUSED
DAYS_TO_EXPIRE				 UNLIMITED	 The expiration time in days for the current SQL Access Advisor task
DEFAULT_EXECUTION_TYPE			 TUNE SQL	 Tune the performance of SQL statements
END_SNAPSHOT				 UNUSED 	 The last snapshot id in the analysis period
END_TIME				 UNUSED 	 The end time filter for selecting data
EXECUTION_DAYS_TO_EXPIRE		 30		 Specifies the expiration time in days for individual executions of the current task
INSTANCE				 UNUSED 	 The instance number for analysis
JOURNALING				 INFORMATION	 Specifies logging of messages to the advisor journal
LOCAL_TIME_LIMIT			 1200		 Time limit per statement in a SQL Tuning Set
MAX_AUTO_SQL_PROFILES			 10000		 Maximum number of system SQL Profiles that are allowable at any one time
MAX_SQL_PROFILES_PER_EXEC		 20		 Maximum number of SQL Profiles that can be created by the system in one run
MODE					 COMPREHENSIVE	 Specifies either a limited or comprehensive analysis operation, where limited runs in less time but
							 may produce slightly lower quality results

ORA_EM_PARAM1				 UNUSED
ORA_EM_PARAM10				 UNUSED
ORA_EM_PARAM2				 UNUSED
ORA_EM_PARAM3				 UNUSED
ORA_EM_PARAM4				 UNUSED
ORA_EM_PARAM5				 UNUSED
ORA_EM_PARAM6				 UNUSED
ORA_EM_PARAM7				 UNUSED
ORA_EM_PARAM8				 UNUSED
ORA_EM_PARAM9				 UNUSED
PLAN_FILTER				 UNUSED 	 Plan filter applicable in case there are multiple plans (plan_hash_value) associated with the same s
							 tatement

RANK_MEASURE1				 UNUSED 	 A first ORDER BY clause on the selected SQL
RANK_MEASURE2				 UNUSED 	 A second ORDER BY clause on the selected SQL
RANK_MEASURE3				 UNUSED 	 A third ORDER BY clause on the selected SQL
REMOTE_SOURCE				 UNUSED
RESUME_FILTER				 UNUSED 	 Specify the default action of a task execution.
SQL_LIMIT				 -1		 Limit the number of SQL from the filtered and ranked SQL tuning set
SQL_PERCENTAGE				 1		 A percentage on the sum of a ranking measure
START_SNAPSHOT				 UNUSED 	 The first snapshot id in the analysis period
START_TIME				 UNUSED 	 The start time filter for selecting data
TARGET_OBJECTS				 1		 Deprecated Parameter
TEST_EXECUTE				 FULL		 FULL if SQL statements should be test-executed for the full local time limit to measure benefit, AUT
							 O for some automatically-chosen smaller time, and NO for no test execution whatsoever

TIME_LIMIT				 3600		 The maximum time that an analysis can execute
USERNAME				 UNUSED 	 The username for whom the SQL statement will be tuned

40 rows selected.


Below parameters can be altered in case of AUTO SQL TUNING ADVISOR.

The following parameters are supported for the automatic tuning task only:

whether the task should accept SQL profiles automatically (TRUE or FALSE)
maximum number of automatic SQL profiles allowed on the system, in sum
maximum number of SQL profiles that can be automatically implemented per execution of the task.
SQL> SET lines 200 pages 100
col DESCRIPTION FOR a100
col parameter_value FOR a30
SELECT parameter_name,parameter_value,description FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='SYS_AUTO_SQL_TUNING_TASK' and  parameter_name='ACCEPT_SQL_PROFILES' ORDER BY parameter_name;SQL> SQL> SQL>   2    3    4 

PARAMETER_NAME		       PARAMETER_VALUE		      DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------
ACCEPT_SQL_PROFILES	       FALSE			      TRUE if SQL Profiles should be created by the task, FALSE otherwise

SQL> exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(parameter => 'ACCEPT_SQL_PROFILES' , value => 'TRUE');
PL/SQL procedure successfully completed.

SQL> SELECT parameter_name,parameter_value,description
     FROM DBA_ADVISOR_PARAMETERS
     WHERE task_name='SYS_AUTO_SQL_TUNING_TASK' and  parameter_name='ACCEPT_SQL_PROFILES'
     ORDER BY parameter_name;  2    3    4  
PARAMETER_NAME		       PARAMETER_VALUE		      DESCRIPTION
------------------------------ ------------------------------ ----------------------------------------------------------------------------------------------------
ACCEPT_SQL_PROFILES	       TRUE			      TRUE if SQL Profiles should be created by the task, FALSE otherwise
SQL> 
SQL> exec DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(parameter => 'ACCEPT_SQL_PROFILES' , value => 'FALSE' );

PL/SQL procedure successfully completed.

SQL>