Saw some warning messages in the alert log. It seems that Auto SQL Tuning task exceeded the time limit.
2024-08-20T23:10:04.345748-04:00
Process 0x0x7000103404a1680 appears to be hung in Auto SQL Tuning task
Current time = 1723648203, process death time = 1723648200
Attempting to kill process 0x0x7000103404a1680 with OS pid = 41180384
OSD kill succeeded for process 0x7000103404a1680
This is because the job process for Auto SQL Tuning appeared to be hung and exceeded the time limit in the maintenance window and the database decided to kill it.
Consequently, we have no the newest auto SQL tuning report can be obtained today.
Let's see the timeout settings we have in SYS_AUTO_SQL_TUNING_TASK.
SQL> column parameter_name format a20;
SQL> column parameter_value format a20;
SQL> select parameter_name, parameter_value from dba_advisor_parameters where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name in ('TIME_LIMIT', 'LOCAL_TIME_LIMIT');
PARAMETER_NAME PARAMETER_VALUE
-------------------- --------------------
TIME_LIMIT 3600
LOCAL_TIME_LIMIT 1200
The first parameter TIME_LIMIT is an overall time limit for daily auto SQL tuning job, whereas LOCAL_TIME_LIMIT is the execution time limit for single SQL ID tuning.
For more parameters of SYS_AUTO_SQL_TUNING_TASK, you may check SYS.DBMS_AUTO_SQLTUNE package.
Solution
To avoid single statement timeout in the task, we may raise the two parameters to make it have more chances to finish its tuning before timeout.
SQL> exec dbms_sqltune.set_tuning_task_parameter ('SYS_AUTO_SQL_TUNING_TASK', 'TIME_LIMIT', 7200);
PL/SQL procedure successfully completed.
SQL> exec dbms_sqltune.set_tuning_task_parameter ('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 3600);
PL/SQL procedure successfully completed.
Then we check the current time limits.
SQL> select parameter_name, parameter_value from dba_advisor_parameters where task_name = 'SYS_AUTO_SQL_TUNING_TASK' and parameter_name in ('TIME_LIMIT', 'LOCAL_TIME_LIMIT');
PARAMETER_NAME PARAMETER_VALUE
-------------------- --------------------
TIME_LIMIT 7200
LOCAL_TIME_LIMIT 3600
As you can see, we raised 3 times of LOCAL_TIME_LIMIT for single tuning, while 2 times of TIME_LIMIT for overall job. This can reduce the possibility of timeout.