April 2020

One of the most important skills needed when investigating badly performing SQL is the ability to read Execution Plans. It’s a topic I’ve written and spoken about frequently – even to the extent of doing full-day seminars – but there’s always scope for finding another way of presenting the method.

This is a note based on a few introductory Powerpoint slides I created for the (sadly cancelled) Polish OUG Workshop and Tanel Poder’s Virtual Conference taking a slightly different approach from one I normally use to get people stated on interpreting (serial) execution plans.

I want to begin with a query, rather than a plan:

By Franck Pachot

.
On Twitter, Ludovico Caldara mentioned the #licensing #pitfall when using the Online Partition Move with Basic Compression. Those two features are available in Enterprise Edition without additional option, but when used together (moving online a compressed partition) they enable the usage of Advance Compression Option:

Will it be reflected in dba_feature_usage_statistics?

— Niels Jespersen (@njesp) April 19, 2020

By Franck Pachot

.
If you decide to capture SQL Plan Baselines, you achieve plan stability by being conservative: if the optimizer comes with a new execution plan, it is loaded into the SQL Plan Management base, but not accepted. One day, you may add an index to improve some queries. Then you should check if there is any SQL Plan Baseline for queries with the same access predicate. Because the optimizer will probably find this index attractive, and add the new plan in the SPM base, but it will not be used unless you evolve it to accept it. Or you may remove the SQL Plan Baseline for these queries now that you know you provided a very efficient access path.

But how do you find all SQL Plan Baselines that are concerned? Here is an example.

I start with the SCOTT schema where I capture the SQL Plan Baselines for the following queries:

Along with all the other Oracle quarterly patches comes VirtualBox 6.1.6.

The downloads and changelog are in the usual places.

I’ve done the installation on Windows 10, macOS Catalina, and Oracle Linux 7 hosts. All worked fine.

I’ve done some Vagrant builds with it and they worked too.