Error "ORA-16957"

Error "ORA-16957"

happens when some SQL took more time to analyze during the Automatic Maintenance window. This will not have any impact to database. You can ignore it if it is one time occurrence, but if this is happening daily then you can Manually tune the query to fix the issue.

1) You can use the below steps to identify the sql causing the issue. 


########### 
Please follow the below steps to identify if any particular SQL is causing the issue. 

----- get the execution_name for the long run day 

set lines 200 pages 100 
col error_message for a60 
col execution_name for a15 
select execution_name, advisor_name,to_char(execution_start,'dd-mon-yy hh:mi:ss'), to_char(execution_end,'dd-mon-yy hh:mi:ss'), status,error_message 
from dba_advisor_executions 
where task_name = 'SYS_AUTO_SQL_TUNING_TASK' 
order by execution_start; 

----- supply the execution name in the below query 
----- below query will identify if any particular SQL had overran and caused hung 

SELECT sql_id, sql_text FROM dba_hist_sqltext 
WHERE sql_id IN (SELECT attr1 FROM dba_advisor_objects 
WHERE execution_name = '&execution_name' 
AND task_name = 'SYS_AUTO_SQL_TUNING_TASK' 
AND type = 'SQL' AND bitand(attr7,64) <> 0 ); 

----> from the output above find the sql with the attr value 64 and that is the sql causing the issue. 
---> Then use the step below to tune them manually. 

Steps 
========= 

-- Replace 72xruzkm3mfv0 with your SQL ID 

DECLARE 
l_sql_tune_task_id VARCHAR2(100); 
BEGIN 
l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task ( 
sql_id => '72xruzkm3mfv0', 
scope => DBMS_SQLTUNE.scope_comprehensive, 
time_limit => 600, 
task_name => '72xruzkm3mfv0_tuning_task', 
description => 'Tuning task for statement 72xruzkm3mfv0.'); 
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id); 
END; 
/ 

SQL> 
BEGIN 
DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => '72xruzkm3mfv0_tuning_task'); 
end; 
/ 

--- Get the report 
SQL> 
SET LONG 5000 
SET LONGCHUNKSIZE 5000 
SET LINESIZE 200 
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('72xruzkm3mfv0_tuning_task') from DUAL; 

(OR) 

2) 

Increase the timing of the Auto sql tuning . 

--- check the current timing 

COLUMN parameter_value FORMAT A30 
SELECT parameter_name, parameter_value 
FROM dba_advisor_parameters 
WHERE task_name = 'SYS_AUTO_SQL_TUNING_TASK' 
AND parameter_name IN ('TIME_LIMIT', 
'DEFAULT_EXECUTION_TYPE', 
'LOCAL_TIME_LIMIT'); 



---- increase the timing 

BEGIN 
DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'TIME_LIMIT', value => 7200); 
END; 
/