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 ( 2 )
  • 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.
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.