Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0

Last week I recorded seminars – and I wanted to garnish the Performance part with demo recordings from our Hands-On Lab. I used the Hands-On Lab instructions we published. And while I edited a recording, I realized that there was something new I haven’t seen before. Do you love unexpected surprises? SYS_AUTO_STS in Oracle 19.7.0? Of course you do …!

Photo by Pete Wright on Unsplash

What happened?

In my environment I run load against my database, I collect statements from AWR and from Cursor Cache into two separate SQL Tuning Sets (STS). And then I upgrade my database to Oracle 19.7.0 and do all sorts of fun stuff. AWR Diff Reports, SQL Plan Management, SQL Tuning Advisor, SQL Performance Analyzer. I use a script which compares the number of entries in the SQL Tuning Set. And I created 2 STSs. But apparently there were now 3.

And magically the 3rd one I didn’t create had a massive number of statements in it – compared to what I collected during my application load from cursor cache. 533 statements in a SQL Tuning Set named SYS_AUTO_STS.

What is it?

Where does it come from? And what is its purpose? At first, I did check the Oracle 19c documentation. Result?

Then I googled a bit. But the results I found all pointed me to Auto Indexing in Oracle 19c. But if I remember correctly, this isn’t available on non-Exadata systems. At this point I wanted to find out more. It seems to be the case that this SQL Tuning Set may be used for Automatic SQL Plan Management as well. But that is an Exadata-only feature since Oracle 19.4, too.

Where does it come from?

As far as I can see, the starting point for this is Bug 30001331 - CAPTURE SQL STATEMENTS INTO STS FOR PLAN STABILITY. It directed me to Bug 30260530 - CONTENT INCLUSION OF 30001331 IN DATABASE RU 19.7.0.0.0. So this seem to be present since 19.7.0.  And the capture into it happens by default.

You can check this by yourself when you upgrade to 19.7.0 (or higher I guess):

select to_char(max(last_schedule_time),'DD-MON-YY hh24:mi') LATEST, task_name, status, enabled from dba_autotask_schedule_control group by task_name, status, enabled ;

LATEST			 TASK_NAME			STATUS	   ENABLED
------------------------ ------------------------------ ---------- --------
27-MAY-20 20:00 	 Auto STS Capture Task		SUCCEEDED  TRUE
15-APR-20 00:16 	 Auto SPM Task			SUCCEEDED  FALSE

As I’m not on an Exadata here, the “Auto SPM Task” is disabled. But the “Auto STS Capture Task” task is on by default. The question is Why?

Checking the settings reveals that “Auto STS Capture Task” runs every 15 minutes. And it runs for 15 minutes. Hm …

select interval, max_run_time, task_name from dba_autotask_schedule_control;

  INTERVAL MAX_RUN_TIME TASK_NAME
---------- ------------ ------------------------------
       900	    900 Auto STS Capture Task
      3600	   1800 Auto SPM Task

Digging a bit deeper led me to Franck Pachot’s blog post about Auto Indexing. There were not many references anywhere to this SQL Tuning Set “SYS_AUTO_STS“. But this blog post at least had a bit information. And some useful views in addition.

I checked it my HOL environment for the CDB2 database:

select count(*) from dba_sqlset_statements where sqlset_owner = 'SYS' and sqlset_name = 'SYS_AUTO_STS'; 

  COUNT(*)
----------
       799

Interesting. I queried the content now:

select sql_text from dba_sqlset_statements where sqlset_name='SYS_AUTO_STS';

And while I did that, the amount of statements in the SQL Tuning Set increased already by 156 to now 955. Please note: This is only a “toy” system with no active load on it.

How do I turn it off?

This was the first question I’ve got asked by my former team mates when I had changed from Oracle Support to Presales a long while ago. I delivered a workshop about 10g automatic features. And somebody asked: “How do we turn this off?“. Well, in this particular case here, I can’t see what it does. And it isn’t documented as a change in Oracle 19.7.0 anywhere as far as I can see at the moment.

Just on the side, Auto Indexing is obviously OFF in my 19c environments:

select parameter_name, parameter_value from dba_auto_index_config order by 1;   

PARAMETER_NAME				 PARAMETER_VALUE
---------------------------------------- ---------------
AUTO_INDEX_COMPRESSION			 OFF
AUTO_INDEX_DEFAULT_TABLESPACE
AUTO_INDEX_MODE 			 OFF
AUTO_INDEX_REPORT_RETENTION		 31
AUTO_INDEX_RETENTION_FOR_AUTO		 373
AUTO_INDEX_RETENTION_FOR_MANUAL
AUTO_INDEX_SCHEMA
AUTO_INDEX_SPACE_BUDGET 		 50

8 rows selected.

Hence I don’t see a connection to it.

Going on a sidetrack

Out of curiosity, when I try to turn Auto Indexing on, I received this (expected) error message:

exec dbms_auto_index.configure('auto_index_mode','implement');
BEGIN dbms_auto_index.configure('auto_index_mode','implement'); END;

*
ERROR at line 1:
ORA-40216: feature not supported
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.DBMS_AUTO_INDEX_INTERNAL", line 9509
ORA-06512: at "SYS.DBMS_AUTO_INDEX", line 295
ORA-06512: at line 1

I’m not on an Exadata – and Auto Indexing is still an Exadata-only feature. See the Oracle Database 19c License Guide:

Oracle Database 19c License Guide

I then used the “secret” underscore from Tim Hall’s blog post to try out the feature. Again, just out of curiosity. And within half an hour and two additional load runs, over 50 findings were documented – and 1 automatic index got created. So the feature works – pretty well indeed.

Back to SYS_AUTO_STS

Earlier today, somebody from Oracle Support asked me whether it would harm to disable the collection. So I’d like to document it here:

BEGIN
  dbms_auto_task_admin.disable(
    client_name => 'Auto STS Capture Task',
    operation   => NULL,
    window_name => NULL);
END;
/

Let me check whether the Auto Task is off:

select task_name, enabled from dba_autotask_schedule_control;

TASK_NAME		       ENABL
------------------------------ -----
Auto STS Capture Task	       FALSE
Auto SPM Task		       FALSE

This seems to work.

Summary

As this change hasn’t been documented yet, I can just guess what the collection of statements every 15 minutes into the SYS_AUTO_STS is meant for. But as there is no clear indication but feedback shows that kind of space gets potentially consumed by this STS, I don’t see a deeper reason to have this mechanism enabled on a non-Exadata systems.

I checked with Oracle 19.6.0 as well. And there this mechanism isn’t present. I didn’t verify any other releases such as Oracle 18c and Oracle 12.1.0.2.

Important Update for Oracle 19.8.0

With Oracle 19.8.0, the SYS_AUTO_STS still exists and gets created during upgrade, but the AUTO TASK is disabled by default. So the only release where this was enabled was Oracle 19.7.0.

This is what you should see from Oracle 19.8.0 on:

SQL> column task_name format a30
SQL> column status format a10
SQL> column enabled format a10
SQL> r
  1* select to_char(max(last_schedule_time),'DD-MON-YY hh24:mi') LATEST, task_name, status, enabled from dba_autotask_schedule_control group by task_name, status, enabled

LATEST			 TASK_NAME			STATUS	   ENABLED
------------------------ ------------------------------ ---------- ----------
15-JUL-20 09:56 	 Auto STS Capture Task		SUCCEEDED  FALSE
15-APR-20 00:16 	 Auto SPM Task			SUCCEEDED  FALSE

And I didn’t verify the RUR 19.7.1 as I don’t apply RURs.

In addition, there a blog post by the Optimizer PM Nigel Bayliss is available now as well as a MOS note:

• Oracle Optimizer Blog: What is the Automatic SQL Tuning Set?
• MOS Note:2686869.1 –  Automatic SQL Tuning Sets (ASTS) 19c RU 7 Onwards

The MOS Note:2686869.1 contains also queries to monitor the task, check space and resource consumption.

Further Information and Links

• Automatic SQL Plan Management – Slight Change with Oracle 19.4.0
• Oracle 19c Auto Indexing – the dictonary views (by Franck Pachot)
• Auto Indexing (by Richard Foote)
• Auto Indexing (by Tim Hall)
• Oracle Database 19c License Guide – Performance Features
• MOS Note:2686869.1 –  Automatic SQL Tuning Sets (ASTS) 19c RU 7 Onwards
• Oracle Optimizer Blog: What is the Automatic SQL Tuning Set?

–Mike

P.S. While I wrote this, my CDB2 database had now over 1200 statements in the STS – with absolutely no load on the system:

select count(*) from dba_sqlset_statements where sqlset_owner = 'SYS' and sqlset_name = 'SYS_AUTO_STS';

  COUNT(*)
----------
      1202

P.P.S. Received a twitter message today – almost 350k statements in 4 days collected …