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

In Autonomous, Database tuning, DBA, Oracle database on August 27, 2020 at 09:26

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:

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 
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:

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

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

The view DBA_SQLSET_STATEMENTS from the query above displays information about the SQL statements, along with their statistics that form all SQL tuning sets in the database. Worth keeping it in mind. Franck Pachot showed interesting screenshots on the topic.

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
WHERE Task_Name = 'Auto STS Capture Task';

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

SELECT Task_Name, Status
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”.

Check also Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0 by Mike Dietrich.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: