The automatic SQL tuning set (ASTS or auto STS) collects performance and plan data for SQL statements used by your applications. Information is collected from the cursor cache using a periodic task:

select task_Name,
       interval Task_Interval_in_Seconds,
       enabled
from  dba_autotask_schedule_control
where task_Name = 'Auto STS Capture Task'
and   dbid = sys_context('userenv','con_dbid');

You can see what SQL statements have been captured using the view DBA_SQLSET_STATEMENTS:

select sql_text
from   dba_sqlset_statements
where  sqlset_name = 'SYS_AUTO_STS';

Is the auto STS enabled by default?

The automatic SQL tuning set is ENABLED by default in:

  • Oracle Autonomous AI Database 26ai.
  • Oracle Autonomous Database 19c
  • Oracle AI Database 26ai Base DB and on-premises (in addition to Autonomous AI Database).

If you are upgrading from non-autonomous Oracle Database 19c to Oracle AI Database 26ai, you should be aware of this change in default setting. This is because the auto STS is not enabled by default in Oracle Database 19c, non-autonomous databases.

What is the auto STS for?

The auto STS is a fundamental component in database automation. For some ‘auto’ features, Oracle test-executes application SQL statements to measure the effect of schema changes (such as adding new indexes). For SQL plan management automation, the database uses the auto STS to keep a historical record of execution plans and their performance.

If you intend to use any of the following features, the automatic SQL tuning set must not be disabled:

If you use the following PL/SQL function, it will be less effective if the auto STS is not enabled:

Can you disable the auto STS?

Yes, if you don’t intend to use any of the features listed above, you can disable the automatic SQL tuning set (in Autonomous AI Database and other types of deployment). If you later decide to enable an automatic feature, such as automatic indexing, the automatic SQL tuning set will be enabled for you.

Some DBAs disable auto STS because it consumes space in the SYSAUX tablespace. If your application has large volumes of highly dynamic SQL, the amount of space consumed in SYSAUX can become quite large. There’s more advice on this topic here. However, note that unused SQL is purged after 53 weeks, and the implementation of auto STS is tried and tested and scales well. You should pause to consider whether space usage in SYSAUX is really an issue when weighed against the benefits of auto features or the usefulness of having a full SQL plan and performance history.

How do you enable and disable the auto STS

You can enable and disable the automatic SQL tuning set as follows:

begin
   dbms_auto_task_admin.enable(
      Client_Name => 'Auto STS Capture Task',
      Operation => NULL,
      Window_name => NULL);
end;
/

begin
   dbms_auto_task_admin.disable(
      Client_Name => 'Auto STS Capture Task',
      Operation => NULL,
      Window_name => NULL);
end;
/