ORA-07445 issues for SYS_AUTO_SQL_TUNING_TASK job

ORA-07445 for SYS_AUTO_SQL_TUNING_TASK

Automatic SQL Tuning in Oracle Database 11g

• As part of Automatic SQL Tuning, Oracle 11g automatically runs the SQL Tuning Advisor against high impact SQL statements during maintenance windows. This process involves the following steps:
• AWR statistics are used to compile an ordered list of the SQL statements with the greatest performance impact on the system, where the impact is the sum of the CPU and I/O times for the statement during the past week. The list excludes statements that are inherently less tunable, such as recently (within a month) tuned recursive statements, parallel queries, DML, DDL and SQL statements whose performance problems are caused by concurrency issues.
• The SQL tuning advisor is run against each statement in turn. The outcome may include both SQL profiles and other recommendations.
• Suggested SQL profiles are performance tested, and those that result in at least a threefold improvement are accepted if the ACCEPT_SQL_PROFILES parameter is set to TRUE, or reported if it is set to FALSE.The accepted SQL profiles are optionally implemented . Several factors many prevent SQL profiles from being implemented automatically, including stale optimizer statistics of dependent objects. The TYPE column of the DBA_SQL_PROFILES view indicates if SQL profiles are created manually (MANUAL) or automatically (AUTO-TUNE).

Oracle Database 11g Release 2 (11.2.0.2 onward) has some minor changes to the Automatic SQL Tuning feature introduced on Oracle 11g Release 1. In the previous release, reports and amendments to the automatic tuning task parameters was performed using the DBMS_SQLTUNE package. From 11.2.0.2 onward, this should be done using the DBMS_AUTO_SQLTUNE package, which requires the DBA role.

Note. The top-level enabling and disabling of the admin task is still done using the DBMS_AUTO_TASK_ADMIN package,


Issue:

If you are facing issues with "auto tuning task" issue, then it will lead more core dump generation issues.


Below sample errors may be found from alert log.
-----------------------------------------------

< Wed Mar 11 22:00:02 2015
< Begin automatic SQL Tuning Advisor run for special tuning task  "SYS_AUTO_SQL_TUNING_TASK"
< Wed Mar 11 22:00:35 2015
< Exception [type: SIGSEGV, Address not mapped to object] [ADDR:0x20] [PC:0x47EDC6F, __intel_new_memcpy()+3343] [flags: 0x0, count: 1]
< Errors in file /u02/app/oracle/diag/rdbms/PROD/PROD/trace/PROD_j000_53026.trc  (incident=36860):
< ORA-07445: exception encountered: core dump [__intel_new_memcpy()+3343] [SIGSEGV] [ADDR:0x20] [PC:0x47EDC6F] [Address not mapped to object] []
< Incident details in: /u02/app/oracle/diag/rdbms/PROD/PROD/incident/incdir_36860/PROD_j000_53026_i36860.trc
< Use ADRCI or Support Workbench to package the incident.
< See Note 411.1 at My Oracle Support for error and packaging details.
< Wed Mar 11 22:00:43 2015
< Dumping diagnostic data in directory=[cdmp_20150311220043], requested by (instance=1, osid=53026 (J000)), summary=[incident=36860].
< Wed Mar 11 22:00:44 2015
< Sweep [inc][36860]: completed
< Sweep [inc2][36860]: completed
< Wed Mar 11 22:01:11 2015
< Thread 1 cannot allocate new log, sequence 2199
< Private strand flush not complete
<   Current log# 3 seq# 2198 mem# 0: /u02/PROD/oradata/redo03.log
< Thread 1 advanced to log sequence 2199 (LGWR switch)
<   Current log# 4 seq# 2199 mem# 0: /u02/PROD/oradata/redo04.log
< Wed Mar 11 22:01:16 2015
< Archived Log entry 3026 added for thread 1 sequence 2198 ID 0x81a6dea4 dest 1:


Action:

Disable auto tuning task advisor.

To disable:

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


If required to enable:

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

To verify:

SELECT parameter_name, parameter_value
FROM   dba_advisor_parameters
WHERE  task_name = 'SYS_AUTO_SQL_TUNING_TASK'
AND    parameter_name = 'ACCEPT_SQL_PROFILES';

output:

PARAMETER_NAME                 PARAMETER_VALUE
------------------------------       --------------------------------
ACCEPT_SQL_PROFILES            FALSE