Insights into Statistics, Query Optimization and the Oracle Optimizer

  • July 22, 2020

What is the Automatic SQL Tuning Set?

Nigel Bayliss
Product Manager

Oracle Database 19c Release Update 19.7 exposed a new database infrastructure component called the automatic SQL Tuning Set (ASTS). It was on by default in this Release Update (RU) but Oracle has decided to disable it by default in future RUs for this database release, giving customers the responsibility to enable it explicitly. It is therefore disabled by default in Oracle Database 19c, Release Update 19.8.

If you want to know more about what it is, its purpose, how to control it and also see how much system resources it uses, then take a look at the MOS note on the topic:

Automatic SQL Tuning Sets (ASTS) 19c RU 19.7 Onwards (Doc ID 2686869.1)

In short, it is a system-maintained SQL tuning set that is an incredibly useful source of historic SQL performance metrics and execution plans. You can use it to repair SQL performance regressions very quickly using SQL plan management. I know that some customers are worried that it sometimes captures a very large number of SQL statements, but pause for a moment. It is designed to work like this, and the amount of resource it uses is very easy to check if you are concerned about it (the MOS note has details). Compare ASTS usage of SYSAUX with AWR, for example. So give it a chance - and you might find that it's one of the most useful tools the Oracle Database has in its toolkit.


Join the discussion

Comments ( 4 )
  • Sergii Shcheteniuk Tuesday, July 28, 2020
    Hi Nigel,

    Thank you for sharing this. How does ASTS work with global temporary tables? When working with STS in 18c I had to replace GTT by permanent tables.
  • Nigel Bayliss Tuesday, July 28, 2020
    Hi Sergii - we will store queries in ASTS even if they use GTTs. ASTS is a repository, so what you do with it is largely up to you still. It is true that SPA and SPM evolve can me misled in cases where GTTs are empty when SQL performance is measured.
  • Dieter Henig Friday, January 8, 2021
    Hi Nigel,

    this appears to be a nice feature. Unfortunately the retention period is not set by default - this is why it spams the SYSAUX-tbs and everybody switches it off.
    Can you tell us how to set the retention to a reasonable time/disk-space?

    best regards
  • Nigel Bayliss Monday, January 11, 2021
    Hi Dieter,

    In common with autonomous features, we are aiming to minimize the number of levers required to operate functions like this. It is possible to change the retention period, but it remains undocumented because we rely on a particular retention period being set for existing and future features. If it is adjusted down it can then have a knock-on effect, so we must be conservative about granting the ability to change things. I absolutely take your point about space consumption in SYSAUX. We have sought customer feedback, so I know that some consider the ASTS to be too large (for some workloads). We have investigated this and identified areas that can be improved. We are working on this now. The aim is to reduce space consumption significantly and remove any concern that the retention period too long. So - in summary - the approach is to remove issues without any need to add more configuration levers. I hope that you can see that there is some advantage to this approach - as long as we manage to pull it off! I will report back in this blog when these improvements have been implemented.
    Thanks. Nigel.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.