Recent Posts by Nigel Bayliss

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. Why it is important for you to be aware of it. How to configure. Summary. What is it? Automatic SQL plan management identifies SQL statements that consume significant system resources and, in addition, they have been observed by the database to be using multiple SQL execution plans, some apparently more optimal than others. Auto...

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

A popular enhancement request I see is to provide an easier way to diagnose issues with SQL plan baselines; in particular the situation where a SQL plan baseline is ACCEPTED but is not being used for whatever reason. This is rare, but can happen if changes are made to the database such as dropping indexes or changing partitioning schemes. If a SQL plan baseline can't be used, you will see something like this in Oracle Database 19c: So why did it fail? In this example I captured...

Since LiveSQL is running on Oracle Database 19c, now is the time to be one of the first to take a look at the new Hint Usage Reporting feature. Trying to figure out why a hint is not being used is not on my top-100-fun-things-to-do list. Luckily, there's now a way to do it much more easily than before. Here is a very simple example - but it's just the tip of the iceberg... create table mytab (a number(10) primary key); insert into mytab values (1); commit; exec...

Oracle Database 18c Standard Edition includes a new way to control SQL execution plans. Stored outlines were deprecated in Oracle Database 11g Release 1 and SQL plan management (SPM) was only available in Oracle Database Enterprise Edition. This meant that many DBAs continued to use stored outlines in Standard Edition and they continue to work to this day. At some point in the future we will begin to remove support for stored outlines so, to avoid leaving a hole in SE, we...

It's nearly time for Oracle OpenWorld so I thought I'd share the details of a couple of Optimizer-related sessions. You will find full details in the session catalog, but here's a summary of what we have for you. The following session is a rundown of the features available in the latest Oracle Optimizer. There's a lot to cover, so it's going to be a flash-card introduction, but don't worry, the intention is to give you an overview and you can always find out more from speaking...

Many applications need to paginate rows fetched from the database, or at least retrieve the first N rows. In most cases the data needs to be returned in some kind of order too. If you are an old-school developer, then you are likely to use a variation on this theme: select * from ( select * from the_table order by object_id ) where rownum <= 10; It's not pretty, but it is effective. In fact, it is very effective if the column (or columns) in the ORDER BY have a...