ORACLE_TUNING/Automatic_SQL_Tuning at master · arasmuc/ORACLE_TUNING · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
-- 11.2.0.2 Onward
BEGIN
  DBMS_AUTO_SQLTUNE.set_auto_tuning_task_parameter(
    parameter => 'ACCEPT_SQL_PROFILES',
    value     => 'FALSE');
END;
/

BEGIN
DBMS_AUTO_SQLTUNE.SET_AUTO_TUNING_TASK_PARAMETER(
parameter => 'ACCEPT_SQL_PROFILES', value => 'TRUE');
END;
/
------------------------------------------------------------------------------------------------------------------------------
How to Enable or DISABLE Automatic SQL Tuning

BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
------------------------------------------------------------------------------------------------------------------------------
determine if any Automatic SQL Tuning jobs are enabled:

SELECT client_name, status, consumer_group, window_group
FROM dba_autotask_client
ORDER BY client_name;
------------------------------------------------------------------------------------------------------------------------------
view the maintenance window details with this query:

SELECT window_name,TO_CHAR(window_next_time,'DD-MON-YY HH24:MI:SS')
,sql_tune_advisor, optimizer_stats, segment_advisor
FROM dba_autotask_window_clients;
------------------------------------------------------------------------------------------------------------------------------
How to View the Results of SQL Tuning Advisor:

SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;
SELECT DBMS_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;
------------------------------------------------------------------------------------------------------------------------------


------------------------------------------------------------------------------------------------------------------------------
1. Execute the Automatic SQL Tuning task immediately: Note: must be run as SYS.
EXEC DBMS_AUTO_SQLTUNE.EXECUTE_AUTO_TUNING_TASK;

2. Display a text report of the automatic tuning task’s history:
SELECT DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK FROM DUAL;

3. Change a task parameter value for the daily automatic runs:
see in the beginning how to enable automatic acceptance of SQL profiles.
You can view all parameters as follows:

SELECT PARAMETER_NAME, PARAMETER_VALUE, IS_DEFAULT, DESCRIPTION
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK';
------------------------------------------------------------------------------------------------------------------------------