Automatic SQL Tuning Sets (ASTS) in Oracle 19c and 20c

A well performing SQL statement starts all of sudden to misbehave? Fixing such queries is very much embedded into Oracle Autonomous’ strategy and here is how Automatic SQL Tuning Sets help.

What is the Automatic SQL Tuning Set? – Nigel Bayliss explains it all in a short informative blog post where he describes it as a system-maintained SQL tuning set that is an incredibly useful source of historic SQL performance metrics and execution plans. You can use ASTS to repair SQL performance regressions very quickly using SQL plan management.

An example of how to fix a SQL statement using SQL Plan management can be found in a previous post of Nigel entitled Repairing SQL Performance Regression with SQL Plan Management. Check this github example or who to “fix a misbehaving SQL query”.

Automatic SQL Tuning Sets were introduced in Oracle 19.7 where they were enabled by default. In 19.8 and 20c, they are disabled by default and MOS Doc ID 2686869.1 gives examples on how to enable and disable them:

exec DBMS_AUTO_TASK_ADMIN.ENABLE(

Client_Name => 'Auto STS Capture Task',

Operation => NULL,

Window_name => NULL);

You can check if the task is disabled or enabled by running:

SELECT Task_Name, Enabled 

FROM DBA_AUTOTASK_SCHEDULE_CONTROL

WHERE Task_Name = 'Auto STS Capture Task';

The MOS note above shows also how to check the amount of space consumed by all SQL tuning sets and also the statement counts per SQL tuning set:

SELECT 

Decode(SQLSet_Name,'SYS_AUTO_STS','ASTS','NON-ASTS'), count(*)

FROM DBA_SQLSet_Statements

GROUP BY Decode(SQLSet_Name,'SYS_AUTO_STS','ASTS','NON-ASTS');

Oracle recommends that you enable ASTS and one reason for this is that Automatic Indexing relies on the ASTS.

In 20c, the data dictionary views related to ASTS changed a bit, so the queries form 19c will not work. Oracle moved it to DBA_AUTOTASK_SETTINGS.

The 20c run the following:

SELECT Task_Name, Enabled, Interval Task_Interval_in_Seconds

FROM DBA_AUTOTASK_SETTINGS

WHERE Task_Name = 'Auto STS Capture Task';

You can check details about the status of the task by running:

SELECT Task_Name, Status

FROM DBA_AUTOTASK_SCHEDULE_CONTROL

WHERE Task_Name = 'Auto STS Capture Task';

Do not worry that Oracle captures a lot of SQL statements – that is the idea after all as you improve the likelihood that you can repair queries that previously ran very quickly and did not show up in AWR. SQL statement performance regressions are repaired automatically and the automatic mode is enabled by setting the DBMS_SPM parameter ALTERNATE_PLAN_BASELINE to AUTO on supported platforms and it is currently equivalent to “AUTOMATIC_WORKLOAD_REPOSITORY+CURSOR_CACHE+SQL_TUNING_SET”.