- Summary You can create an SQL TUNING TASK manually ad hoc with the following simple steps. ALTER SESSION SET NLSLANGUAGE='AMERICAN'; 0. Find the sqlid of the oracle.
- Oracle Database automatically runs SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis. By default, automatic SQL tuning runs for at most one hour.
- Enterprise Manager for Oracle Database. The task SYSAUTOSQLTUNINGTASK contains no results for ex. ORA-13663: The task SYSAUTOSQLTUNINGTASK contains no results for execution (Doc ID 1364523.1) Last updated on FEBRUARY 14, 2019. Applies to: Enterprise Manager for Oracle Database - Version 10.2.0.1 to 11.2.0.3 Release 10.2 to 11.2.
- Oracle Sql Tuning
- Sys_auto_sql_tuning_task Oracle 11g
- Sql Tuning Advisor In Oracle
- Automatic Sql Tuning Task Oracle
![Kill Kill](https://www.linuxidc.com/upload/2017_08/170822151528464.png)
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. You will get the last execution date-time and status of Automatic SQL Tuning Task (SYSAUTOTUNINGTASK) or you will get the message No rows. The SQL Tuning Advisor page appears, with the Automatic tab showing. The SQL tuning task that appears on the Automatic tab is the SYSAUTOSQLTUNINGTASK. This tuning task is created daily by the Automatic SQL Tuning Advisor. The task includes any high-load SQL queries for which the Automatic SQL Tuning Advisor has generated tuning recommendations. What is Automatic SQL Tuning?. Oracle automatically runs the SQL Tuning Advisor on selected high-load SQL statements from the Automatic Workload Repository (AWR) that qualify as tuning candidates. This task, called Automatic SQL Tuning, runs in the default maintenance windows on a nightly basis.
When you upgrade to a new database release, there will be changes. Some are obvious, others are not. This one is somewhere in between because it is documented quite well but not very well known yet as far as I see. I’m talking about the Automatic SQL Plan Management in Oracle Database 19c.
What is changing in Oracle 19c?
PLEASE READ THIS UPDATED BLOG POST:
https://mikedietrichde.com/2019/10/02/automatic-sql-plan-management-slight-change-with-19-4-0/
AS THERE IS A CHANGE WITH ORACLE 19.4.0.
https://mikedietrichde.com/2019/10/02/automatic-sql-plan-management-slight-change-with-19-4-0/
AS THERE IS A CHANGE WITH ORACLE 19.4.0.
First of all, I don’t want to rewrite everything Nigel Bayliss wrote in his detailed blog post about Automatic SQL Plan Management in Oracle 19c in April 2019. But the essence is:
- Automatic SQL Plan Management is enabled by default in Oracle 19c
- New SQL plan baselines will be created automatically
Don’t get me wrong. I fully agree with Nigel’s conclusion that this is a good feature. And we are in agreement too, that you need to be aware of such things happening in the background. I’m a very big fan of SQL Plan Baselines as it is a very useful tool to prevent unwanted plan changes as part of an upgrade or a migration. Actually I use it quite often with customers. Personally, I’m still scared how little people use it despite the fact that it s a free Enterprise Edition feature since Oracle 11.1, and became partially even an SE2 feature later.
A closer look at different settings
I compare the different settings between Oracle 12.1.0.2, Oracle 18c (settings are identical with 12.2.0.1) and Oracle 19c. The key view you need to access is
DBA_SQL_PLAN_BASELINES
.Hereafter I compare only default values which are not
UNUSED
.The new default
AUTO
for ALTERNATE_PLAN_BASELINE
and ALTERNATE_PLAN_SOURCE
enables the Automatic SQL Plan Management in Oracle Database 19c. The attribute ALTERNATE_PLAN_SOURCE
lets the advisor identify the sources for plans by itself. The ALTERNATE_PLAN_BASELINE
parameter determines which SQL statements are eligible for plan capture. AUTO
in this case means that any SQL statement in AWR will be a potential candidate.How to switch between different behavior?
Nigel documented this already in his blog post.
If you’d like to restore the Oracle 12.1 behavior:
If you’d like to revert to the Oracle 12.2.0.1 and Oracle 18c behavior:
Of course, switching to the Oracle 19c defaults works this way:
Please note, that
“You must include
ALTERNATE_PLAN_LIMIT
is defined in the documentation as NUMBER
with an extra explanation:“You must include
ALTERNATE_PLAN_LIMIT
for VARCHAR2
to accept the UNLIMITED
parameter value.”And finally, let me say this again: I don’t recommend to revert to the old behavior. This is a recommendation to check and keep and eye on it. My intention is to explain a behavior change in Oracle 19c.
Links and more information
- Optimizer Blog – Nigel Bayliss – Apr 8, 2019:
What is automatic SQL plan management and why should you care?
–Mike
Related
Oracle Sql Tuning
Last updated on AUGUST 04, 2018
Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.2 and laterEnterprise Manager for Oracle Database - Version 11.2.0.4 to 11.2.0.4 [Release 11.2]
Information in this document applies to any platform.
Symptoms
- One of the following errors occur in the alert.log and relevant trace file for the job running SQL Tuning Advisor
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! |
Sys_auto_sql_tuning_task Oracle 11g
In this DocumentSymptoms |
Cause |
Sql Tuning Advisor In Oracle
Solution |