Recent Posts by Nigel Bayliss

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

Oracle Database 19c adds a new feature called automatic SQL plan management. This post covers: What is automatic SQL plan management and how it works. How to configure. Summary. Where is it available? This is an autonomous feature so it is only available on some platforms. See Automatic SQL Plan Management in table 1-6 of the license guide for full details. If you are not using a supported platform, there are still some great SPM enhancements in Oracle Database 19c for you to make...

For those of you that want to keep up-to-speed with the latest changes, this post includes a quick summary of the headline new features in Oracle Database 19c with links to the documentation. In addition, most of the new optimizer white papers have been published. You will find links to them in the new Oracle Query Optimization page (and there will be further updates to this page soon). Automatic Indexing This is a complex and sophisticated piece of functionality...