DBMS_AUTO_SQLTUNE
TheDBMS_AUTO_SQLTUNE
package is the interface for managing the Automatic SQL Tuning task. DBMS_AUTO_SQLTUNE Overview TheDBMS_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 theDBA_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 typeVARCHAR2
orNUMBER
forSYS_AUTO_SQL_TUNING_TASK
. But let we first check current parameter setting forSYS_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
orFALSE
) 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>
© 2021 Ace2Oracle. All Rights Reserved | Developed By IBOX444