Recent Posts by Nigel Bayliss

Execution Plan Changes I am often asked: Why do my SQL execution plans keep changing, and why are they sometimes so bad? Is there a bug? I have upgraded a database, but some SQL statements perform poorly compared to the previous release. There are many possible reasons for plan changes. Still, an essential factor to consider is the possibility that one or more cardinality estimates (used during optimization) are inaccurate, even though optimizer statistics are fresh. Estimating...

The Oracle Database 21c database initialisation parameter optimizer_real_time_statistics has been backported to Oracle Database 19c RU 10. The real-time statistics feature is available of some platforms (as specified in the licensing manual). It is now disabled by default in Oracle Database 19c RU10 on-premises Exadata systems, but it can be enabled using the new parameter.  This means that if you are... Upgrading an on-premises Exadata platform to Oracle Database RU 19.10 (or...

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