Run Sys_auto_sql_tuning_task

• 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 ACCEPTSQLPROFILES parameter is set to TRUE, or reported if it is set to FALSE.
• Enabling Automated SQL Tuning in Oracle 11g To identify if Automatic SQL Tuning job is enabled and regularly running. Use the following query to determine if any Automatic SQL Tuning jobs are enabled: SELECT clientname, status, consumergroup, windowgroup FROM dbaautotaskclient ORDER BY clientname.

1. Oracle Run Sys_auto_sql_tuning_task
2. Run Sys_auto_sql_tuning_task Lyrics

Last updated on JANUARY 22, 2020

Applies to:

Hello, Setting Resource Manager plan SCHEDULER0x2COD: DEFAULTMAINTENANCEPLAN via scheduler window Begin automatic SQL Tuning Advisor run for special tuning task 'SYSAUTOSQLTUNINGTASK' This SYS ´ session was blocking another one by this problem.

Oracle Database - Enterprise Edition - Version 11.1.0.6 to 11.2.0.3 [Release 11.1 to 11.2]
Oracle Database Cloud Schema Service - Version N/A and later
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Information in this document applies to any platform.

Symptoms

The automatic SQL Tune job fails with an ORA-13639 in the alert log:

...
Setting Resource Manager plan SCHEDULER[0x2FFA]:DEFAULT_MAINTENANCE_PLAN via scheduler window
Setting Resource Manager plan DEFAULT_MAINTENANCE_PLAN via parameter
Sun Sep 25 06:00:00 2011
...
...
Sun Sep 25 07:00:36 2011
Errors in file /u02/diag/rdbms/trace/j003_453837.trc:
ORA-13639: The current operation was interrupted because it timed out.
End automatic SQL Tuning Advisor run for special tuning task 'SYS_AUTO_SQL_TUNING_TASK'
...

Changes

The database was in this case moved to a different system, i.e. from a solaris global zone to another global zone.

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

Oracle Run Sys_auto_sql_tuning_task

Symptoms

Changes

Cause

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.

Run Sys_auto_sql_tuning_task Lyrics

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