Recent Posts by Nigel Bayliss

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

There are a number of ways to speed up the process of gathering optimizer statistics, but I'm not sure that it's common knowledge just how much of an effect some simple changes can make. If you have been asking yourself, "why is stats gathering taking so long and what can I do about it?", then this post is for you. If you are already familiar with the different methods of gathering optimizer statistics, you might want to jump to the end of this post where I compare them and...

This feature made its debut in Oracle Database 12c Release 2 and enhancements in Oracle Database 18c allow it to be used in a broader range of scenarios. SQLMaria mentioned it in a blog post so I realized that I am overdue in producing a blog post for this cool feature. What is Fine-Grained Cursor Invalidation? You might be familiar with the concept of rolling or deferred cursor invalidation because DBMS_STATS uses it if you gather statistics with...