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 …!
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
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 22.214.171.124.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:
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.
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 126.96.36.199.
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. Then the SQL Tuning Set will be still created but won’t get populated.
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
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 …