SYS_AUTO_SQL_TUNING_TASK - "Process 0x%p appears to be hung in Auto SQL Tuning task"
Vale dalla versione 11 in poi
Occasionalmente, durante l'esecuzione di "SYS_AUTO_SQL_TUNING_TASK", nel alert log potrebbero comparire messaggi di avviso come il seguente:
"Process 0x%p appears to be hung in Auto SQL Tuning task"
"Current time = %u, process death time = %u"
"Attempting to kill process 0x%p with OS pid = %s"
"OSD kill skipped for process %p"
"OSD kill succeeded for process %p"
"OSD kill failed for process %p"
Cosa è SYS_AUTO_SQL_TUNING_TASK?
è un task automatico eseguito dal database che seglie un set di SQL ad alto carico ( high-load SQL ) da AWR ed esegue SQL Tuning Advisor su questo SQL.
Il pacchetto DBMS_AUTO_SQLTUNE è l'interfaccia per SQL Tuning Advisor (DBMS_SQLTUNE) quando eseguito all'interno del framework Autotask.
Questo task controlla i profili SQL che trova eseguendo sia i vecchi che i nuovi query plan.
Automatic SQL Tuning differisce da manual SQL tuning in questo modo:
Se l'implementazione automatica dei profili SQL (SQL PROLFIES) è abilitata (di default è disabilitata), il database implementa tutti i profili SQL che promettono un grande vantaggio in termini di prestazioni.
L'implementazione avviene al momento dell tuning in modo che il database possa trarre immediatamente vantaggio dal nuovo piano.
È possibile abilitare o disabilitare l'implementazione automatica utilizzando l'API SET_AUTO_TUNING_TASK_PARAMETER per impostare il parametro ACCEPT_SQL_PROFILES.
To query sql tuning advisor autotask parameter settings:
select * from dba_ADVISOR_parameters where task_name='SYS_AUTO_SQL_TUNING_TASK' and parameter_name='ACCEPT_SQL_PROFILES';
OWNER TASK_ID TASK_NAME PARAMETER_NAME PARAMETER_VALUE EXECUTION_TYPE
--------- ---------- ------------------------------ ------------------------------ ----------------
SYS 1 SYS_AUTO_SQL_TUNING_TASK ACCEPT_SQL_PROFILES FALSE
<==========Default "FALSE"
To turn on automatically accepting sql profile:
SQL> exec dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','ACCEPT_SQL_PROFILES','TRUE');
Note: Please note that turning on this setting may change SQL plans automatically in database.
Turn off:
SQL> exec dbms_sqltune.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK','ACCEPT_SQL_PROFILES','FALSE');
Once SQL profiles are accepted automatically, they should be seen as 'AUTO' type in DBA_SQL_PROFILES:
SQL> select * from DBA_SQL_PROFILES where type='AUTO';
TORNIAMO AI MESSAGGI PRESENTI NELL'alter log.
Questi messaggi indicano che è stata eseguita un'auto kill di un "hung/long running tuning task"
cioè un'attività di ottimizzazione bloccata o in esecuzione prolungata.
Questa è una misura protettiva deil sistema che evita che l'attività di tuning superi un limite di tempo a causa di una singola attività e protegge il sistema dai danni causati da tale superamento.
Non è un anomalia pertanto se si vuole eliminare questo messaggio dall'allert log si può fare un workaround.
Incremenatare il parametro LOCAL_TIME_LIMIT che deve essere minore o uguale a TIME_LIMIT.
TIME_LIMIT è il tempo di esecuzione del SQL tuning task dopo il quale il processo viene chiuso.
I valori attuali sono ricavabili da questa query:
SELECT parameter_name,parameter_value
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='SYS_AUTO_SQL_TUNING_TASK' and parameter_name like '%TIME_LIMIT%';
Se si vuole cambiare il valore ma il job è in esecuzione, riceverai il messaggio:
ORA-13647: Setting of parameter LOCAL_TIME_LIMIT is disallowed during task execution
Per evitarlo, disabilita temporaneamente il job, quindi imposta il parametro e riabilitalo.
Da questa qeury vedi se in esecuzione:
SELECT status FROM DBA_ADVISOR_TASKS WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK';
Se si allora lo disabiliti con questa procedura:
BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
La disattivazione di Automated SQL Tuning Advisor non annulla un TASK attualmente in esecuzione.
Controlla se c'è un TASK attualmente in esecuzione
SELECT status FROM DBA_ADVISOR_TASKS WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK';
STATUS
-----------
EXECUTING
Se cosi cancella il task in esecuzione:
exec DBMS_SQLTUNE.CANCEL_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK');
Ricontrolla con la query di prima
SQL> SELECT status FROM DBA_ADVISOR_TASKS WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK';
STATUS
-----------
CANCELLED
Ora modifica il parametro ad esempio a 2400 sec cioè 40 min essendo il TIME_LIMIT nel mio caso pari a 3600 cioè 1 ora.
BEGIN
DBMS_SQLTUNE.set_tuning_task_parameter('SYS_AUTO_SQL_TUNING_TASK', 'LOCAL_TIME_LIMIT', 2400);
END;
/
Riabilita il tuning task.
BEGIN
DBMS_AUTO_TASK_ADMIN.ENABLE(
client_name => 'sql tuning advisor',
operation => NULL,
window_name => NULL);
END;
/
Controlla se il paramtero è stato modificato.
SELECT parameter_name,parameter_value
FROM DBA_ADVISOR_PARAMETERS
WHERE task_name='SYS_AUTO_SQL_TUNING_TASK' and parameter_name like '%TIME_LIMIT%';
SELECT * FROM DBA_ADVISOR_TASKS WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK';
