Recent Posts by Nigel Bayliss

I was asked to compare SQL plan management auto capture with automatic SQL plan management, so here goes... SQL plan management (SPM) auto capture is enabled by setting the database parameter optimizer_capture_sql_plan_baselines to TRUE (covered here). Then, by default, all plans for all SQL statements executed in the database more than once are captured. From Oracle Database 12c Release 2 you can specify filters to limit which SQL statements are chosen. This is done using the...

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...

I mentioned in an earlier blog post that in Oracle Database 19c we introduced a hint usage report for DBMS_XPLAN. If you have been using this feature then you might have noticed that some hints are not reported. We can loosely categorize them as execution hints, and this is a known limitation of the hint usage report. The ones you are most likely to notice are perhaps APPEND, BIND_AWARE and GATHER_OPTIMIZER_STATISTICS. There are some inconsistencies in the reporting of...

As you might imagine, I meet a lot of customers that need to load large volumes of data continuously into database tables. When high volumes are involved, use of table partitioning is common and, in response to that, there are operational procedures designed to manage partition creation, exchange, movement and so on. In addition, the timing of statistics maintenance operations is likely to be tightly coupled to the batch workflow so that execution plans remain consistent and...

Always check your license guide for up-to-date information on your specific database version, but you will see that SPM is available as part of Oracle Database Enterprise Edition and, in addition, a subset of SPM functionality is available in Oracle Database Standard Edition from Oracle Database 18c onwards. If you have a licence to use AWR, then SQL plan management can be used in conjunction with this repository without additional licenses. SQL plan management can interact...

You were hoping for an uneventful day at work when you notice that you have a critical workload query that is taking much longer to execute than usual, and you can see that it is beginning to affect your service levels. You suspect that it has a sub-optimal execution plan and you need to get it sorted out immediately. Is there a quick and easy way to do that? Automatic SQL plan management can deal with this type of issue without DBA intervention, but what can you do if this...

It's nearly time for Oracle OpenWorld 2019. As ever, there are a number of interesting optimizer-related sessions in the catalogue (and I see there's one on MySQL too).  Here's ours: If you're coming to OOW but can't make the session, then come and speak to the Oracle developers themselves at the demo booths in Moscone South. We're in booth "ODB-013". I'll be around there as much as possible too. What makes OOW special for me is being able to listen to your feedback first-hand;...

Occasionally I want to get historical information on histogram changes for a particular table. I have had a script in my back pocket for a long while so I thought I'd share it in the Oracle Optimizer GitHub repository. My main motivation for doing this is that I still consider the script experimental and I would like feedback from the field. Feel free to comment below, particularly if you see room for improvement or flaws. Perhaps the output could be clearer or maybe there...

Introduction I am often asked, “How does Oracle choose which columns need a histogram?” Also, “Why have I got a histogram on my primary key column?” Jonathan Lewis posted an explanation in response to the latter question some while ago, but I thought I’d cover a little bit more on this topic and focus on how Oracle chooses which columns should have histograms. In particular, I want to clarify how the database determines what histograms to create if you use the...