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 …
Hi Mike,
Yesterday i upgraded my nonCDB 18c to PDB 19.7 and i saw both these features FALSE
SQL> 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 ENABL
———————— —————————————————————- ———- —–
27-MAY-20 10:22 Auto STS Capture Task SUCCEEDED FALSE
27-MAY-20 10:22 Auto SPM Task SUCCEEDED FALSE
Hi Manoj,
that’s interesting – because your output says: LAST_SCHEDULE_TIME => 27-MAY-20 at 10:22h – and it SUCCEEDED.
So it had been run. Did somebody disable it? Or did you do some other things after you upgraded within the past 24 hours?
Cheers,
Mike
Hi Mike,
I don’t have any tuning sets before upgrade, that might be the reason?
This is OMS REPO db which i upgraded to PDB.
Thanks
Manoj
Hi Manoj,
no, I don’t think that this is the reason. Maybe an OMS Repo DB disables all Auto Tasks by default. But I don’t know …
Cheers,
Mike
Hi Mike, Manoj,
Apparently, it is disabled in upgraded databases.
I upgraded my 12.2.0.1.190416 DB to 19.7 on 18th May.
The autotask itself is disabled and there are no statements in the STS.
I have other tuning sets as well and no, it is not an EM repo DB.
SQL> select * from dba_autotask_schedule_control;
DBID TASK_ID TASK_NAME STATUS INTERVAL MAX_RUN_TIME ENABL ELAPSED_TIME LAST_SCHEDULE_TIME
———- ———- —————————— ———- ———- ———— —– ———— ——————————–
976619675 5 Auto STS Capture Task SUCCEEDED 900 900 FALSE 0 18-MAY-20 07.15.29.059 AM -04:00
976619675 6 Auto SPM Task SUCCEEDED 3600 1800 FALSE 0 18-MAY-20 07.15.29.415 AM -04:00
SQL> select decode(name, ‘SYS_AUTO_STS’, name, ”) name, created, last_modified, statement_count from dba_sqlset order by created desc;
NAME CREATED LAST_MODIFIED STATEMENT_COUNT
—————————— ——————- ——————- —————
SYS_AUTO_STS 18.05.2020 07:14:35 18.05.2020 07:14:35 0
21.02.2020 04:54:53 21.02.2020 04:54:53 0
21.02.2019 09:06:39 21.02.2019 09:06:58 1
..skipping the rest of the results..
Kind regards,
Mikhail.
Hi Mikhail,
I had upgraded my database as well but from 11.2.0.4 – this is actually how I came across it.
Not I will try to upgrade my 12.2.0.1 database as well, and see what happens.
I’m curious now.
Cheers,
Mike
Hi Mike, Just a note i upgraded from 18.3 to 19.7 so that might be the reason. Looks like issue is only when you upgrade from 11.2.0.4
Thanks
Manoj
Hi Manoj,
I did further tests – and it looks as if the job is not enabled when you upgrade from 12.2.0.1 and 18c, but only when you upgrade from 11.2.0.4 and 12.1.0.2.
Even more obscure – we are still discussing internally …
Cheers,
Mike
I had over 50k tuning sets created for that. I disabled this feature.
How can I delete these tuning sets, sysaux grew quite big due to that.
Raul
Wow!
BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => ‘SYS_AUTO_STS’ );
END;
/
should do the job.
Cheers,
Mike
Thank you for sharing the information, I’ve had an SR open with support for 16 days and this is more information about the root cause than I have received from the SR.
After applying the 19.7 RU it took a few days to notice the problem and some databases captured many more tuning set statements than others. The largest captured 1939401 statements in 4 days and consumed ~4G in SYSAUX before we figured out how to disable it.
I’m wondering if this will need to be something that will need to disabled after each RU going forward. It’s definitely on my list to check after applying 19.8 in a couple months.
Thanks again,
Stan
Hi Stan,
I will file a bug by myself for the next RU – but I fear that it will take at least until Oct 2020 until this gets reverted again.
Thanks for the input – this is highly welcome and helpful.
Cheers,
Mike
I noticed that this also generates lot of archive logs (Redo) after this starts running.
Might be platform specific ? For Windows, I get an execution but enabled is false.
SQL> select to_char(max(last_schedule_time),’DD-MON-YY hh24:mi’) LATEST,
2 task_name, status, enabled
3 from dba_autotask_schedule_control
4 group by task_name, status, enabled ;
LATEST TASK_NAME STATUS ENABL
———————— ————————- ———- —–
05-JUN-20 19:28 Auto STS Capture Task SUCCEEDED FALSE
05-JUN-20 19:28 Auto SPM Task SUCCEEDED FALSE
Nope – it’s the version from which you upgraded. Or do you have a fresh brand new 19c which you patched to 19.7.0?
Actually I haven’t checked this case yet – and it gives me headache when such things don’t even work consistently.
Cheers,
Mike
Hi Mike,
Thanks for this blog post. We also started seeing SYSAUX alerts on out 19.7 DBs and initially we started adding space, but then we became apparent very quickly that something is wrong. I actually have two SRs open on this and for one support said truncate WRH$_SQLTEXT and still working on the other for WRI$_SQLSET_PLAN_LINES. We have close to 230+ 19.7 DBs and this is a real problem for us.
I think this issue needs to be escalated and fixed in the next RU or give customers a one-off patch.
Regards
Naveen
This command has been running for over 30+ mins on one of my production DBs.
BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => ‘SYS_AUTO_STS’);
END;
/
Thanks.
I did a search in MOS on “SYS_AUTO_STS”, not a single knowledge base article !
I know – it’s a secret right now.
But be assured that it gets discussed right now …
Cheers,
Mike
SQL> BEGIN
DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => ‘SYS_AUTO_STS’);
END;
/ 2 3 4
BEGIN
*
ERROR at line 1:
ORA-30036: unable to extend segment by 8 in undo tablespace ‘UNDOTBS1’
ORA-06512: at “SYS.DBMS_SQLTUNE_INTERNAL”, line 14910
ORA-06512: at “SYS.DBMS_SQLTUNE_INTERNAL”, line 14679
ORA-06512: at “SYS.DBMS_SQLTUNE_INTERNAL”, line 14642
ORA-06512: at “SYS.DBMS_SQLTUNE_INTERNAL”, line 14888
ORA-06512: at “SYS.DBMS_SQLTUNE”, line 7315
ORA-06512: at line 2
Hi Mike,
I started with brand new 19c patched to 19.5.0 and to 19.7.0 last weekend.
Noticed problem only now.
SQL> select trunc(PLAN_TIMESTAMP, ‘MONTH’), count(*)
from dba_sqlset_statements
group by trunc(PLAN_TIMESTAMP, ‘MONTH’)
order by 1;
2 3 4
TRUNC(PLA COUNT(*)
——— ———-
01-JUN-20 1605468
47060
Guessing by this statement that problem started with 19.7.0.
Just for your info.
Yes, and the autotask will be disabled again on 19.8.0.
You can disable it as I explain in the blog post.
Thanks
Mike
Hi Mike,
Thnak you for your imformation about important upgrade issue.
I belong to Oracle Japan as database engineer.
One of my customers meet this issue.
You wrote about this issue following, in your comments.
“I will file a bug by myself for the next RU – but I fear that it will take at lease until Oct 2020 until this gets reverted again.” (May 28,2020)
You also wrote following.
“Important Update – June 26, 2020
It looks like that the decision has been taken to disable the Auto Task from Oracle 19.8.0 on by default.”
I think that you and other persons file bugs about this issue, and it will be fixed in 19.8.
Is my understanding right ?
Could you known the bug number about this issue ? I want to read the detail of this issue.
Hi Tsuyoshi-san,
I sent you an email.
Thanks,
Mike
Hi Mike,
Thank you for your mail.
Weclome 🙂
Additional info from Oracle support regarding SYS_AUTO_STS
The ASTS is maintained using an automatic background task, which is enabled by default in Oracle Database 19c RU7.
Oracle has decided to disable ASTS in future RUs for this database release by default, giving customers the responsibility to enable ASTS explicitly. It is disabled by default in 19c RU 8.
Automatic SQL Tuning Sets (ASTS) 19c RU 7 Onwards ( Doc ID 2686869.1 )
Maybe this information is useful for other customers too.
I added this already to the blog post … 🙂
Scroll down to “Important Update for Oracle 19.8.0” where you will find the links to the MOS note, too.
Thanks,
Mike
Thanks for the update. Looking forward to the test this new behaviour when RU 19.8.0 is available for Windows eventually.
Hi Mike,
thanks for your information, we had more hundreds error entry in the log , and after I turned it off the error entries have been terminated.
It was very useful.
Thanks – and glad it helped!
Cheers,
Mike
Hi Mike,
thanks for your information, it’s help me a lot 🙂
I’ve make a DBMS_SQLTUNE.DROP_SQLSET( sqlset_name => ‘SYS_AUTO_STS’);
it’s ok, I have no more dba_sqlset_statements,
but the WRI$_SQLSET_PLAN_LINES is still big.
How can reduce / truncate this table ?
Thanks Franck
Hi Franck,
WRI tables should be purged depending on your AWR retention automatically.
Cheers,
Mike
After the drop_sqlset, select count(*) from WRI$_SQLSET_PLAN_LINES; => 0,
but the HWM of this table is 67839.
Do you think, I can do a truncate safely of this table to reset the HWM and so have more space in sysaux ?
Thanks for your help
Franck
Hi Franck,
I would guess so, yes.
Cheers,
Mike
thanks for your help
welcome 🙂
Hi Mike,
What if its Exadata and we see the space of SYSAUX getting full. It there a way we can set retention for this STS? which will help us reuse the space from SYSAUX. Else, space addition will be a never ending process.
Regards,
Prakash Thakur
Hi Prakash,
you please need to open an SR and let Support know. The developer who created this claimed that this could never happen (your case). But in fact we saw this now several times. As I wrote, the best is to turn it off as there is no benefit of having this STS right now to my knowledge.
Please check with Oracle Support for solutions of a filled up SYSAUX.
Cheers,
Mike
Latest surprise, this STS spawnd sql id runs realy long and sql advisor chokes on it:
SQL ID c61ajdcqbqn42
Support says it is a bug:
Bug 31780567 : KAP STATS AND INDEX RELATED CALLS TAKING HIGH CPU IN 19C ADB
Patch only available for 19.13, they are working on 19.14
Workaround is disable auto STS capture
I will leave it disabled until they incorporate in quarterly.
Hi Avram,
thanks a lot. Please let me know:
Are you on an Exadata or ExaCC system? Or did this happen on a normal database environment?
Do you have an SR number as well (feel free to mail me directly: mike.dietrich —— oracle.com).
Thanks in advance,
Mike
Hi Mike,
Sorry for the slow reply.
We are on a regular system.
I do not have an SR handy at the moment.
BUT!
I did some further testing just recently and it seems that sql id runs in about a dozen seconds in our data warehouse where the optimizer is set to “all rows” , but it takes over 700 seconds in the transaction database where the optimizer is set to “first_rows_1”.
So I did just forward a request to have oracle edit that sql id and put in an “all rows” hint.
If I get a SR number I will share.
Avraam Jack Dectis