Tuesday, March 29, 2016

Fix: ORA-13639: The current operation was interrupted because it timed out.

Sometimes SQL Tuning Advisor interrupts cause time limit took more than defined. You have to analyze it or increase value. Here you can see increasing of value.

Example result of sql

select
                 execution_name, advisor_name,
                 to_char(execution_start,'dd-mon-yy hh:mi:ss') execution_start,
                 to_char(execution_end,'dd-mon-yy hh:mi:ss') execution_end, status,error_message
from dba_advisor_executions
where task_name = 'SYS_AUTO_SQL_TUNING_TASK'
order by execution_start;



Check value of TIME_LIMIT`s parameter :

SQL> column parameter_value for A35   
 SQL> 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'); 
 
 PARAMETER_NAME                 PARAMETER_VALUE  
 ------------------------------ ------------------------------  
 LOCAL_TIME_LIMIT                1200  
 TIME_LIMIT                      3600  
 DEFAULT_EXECUTION_TYPE          TUNE SQL  

In our case it is default value. Let`s increase it

 SQL> begin   
 DBMS_SQLTUNE.SET_TUNING_TASK_PARAMETER(task_name => 'SYS_AUTO_SQL_TUNING_TASK', parameter => 'TIME_LIMIT', value => 7200);   
 end;   
 / 2  3  4   
 PL/SQL procedure successfully completed. 

Check again:
 PARAMETER_NAME,             PARAMETER_VALUE  
 LOCAL_TIME_LIMIT,           1200  
 TIME_LIMIT,                 7200  
 DEFAULT_EXECUTION_TYPE,     TUNE SQL 

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...