Search

Top 60 Oracle Blogs

Recent comments

Oakies Blog Aggregator

Hacking Session: Oracle SQL Monitoring - Understanding the Execution Timeline column and CPU usage

Update: I have uploaded the videos of this hacking session to YouTube.
I will run another hacking session that has been in my mind for a while. It will be a pretty narrow deep-dive into one of the SQL Monitoring report’s columns that is not well explained:
Oracle SQL Monitoring - Understanding the Execution Timeline column In this hacking session, I will explain how to read the “Execution Timeline” column in the SQL Monitoring reports.

The myth of NoSQL (vs. RDBMS) agility: adding attributes

By Franck Pachot

.
There are good reasons for NoSQL and semi-structured databases. And there are also many mistakes and myths. If people move from RDBMS to NoSQL because of wrong reasons, they will have a bad experience and this finally deserves NoSQL reputation. Those myths were settled by some database newbies who didn’t learn SQL and relational databases. And, rather than learning the basics of data modeling, and capabilities of SQL for data sets processing, they thought they had invented the next generation of persistence… when they actually came back to what was there before the invention of RDBMS: a hierarchical semi-structured data model. And now encountering the same problem that the relational database solved 40 years ago. This blog post is about one of those myths.

Myth: adding a column has to scan and update the whole table

I have read and heard that too many times. Ideas like: RDBMS and SQL are not agile to follow with the evolution of the data domain. Or: NoSQL data stores, because they are loose on the data structure, makes it easier to add new attributes. The wrong, but unfortunately common, idea is that adding a new column to a SQL table is an expensive operation because all rows must be updated. Here are some examples (just taking random examples to show how this idea is widely spread even with smart experts and good reputation forums):

A comment on twitter: “add KVs to JSON is so dramatically easier than altering an RDBMS table, especially a large one, to add a new column”

A question on StackOverflow: “Is ‘column-adding’ (schema modification) a key advantage of a NoSQL (mongodb) database over a RDBMS like MySQL” https://stackoverflow.com/questions/17117294/is-column-adding-schema-modification-a-key-advantage-of-a-nosql-mongodb-da/17118853. They are talking about months for this operation!

An article on Medium: “A migration which would add a new column in RDBMS doesn’t require this Scan and Update style migration for DynamoDB” https://medium.com/serverless-transformation/how-to-remain-agile-with-dynamodb-eca44ff9817.

Those are just examples. People hear it. People repeat it. People believe it. And they don’t test. And they don’t learn. They do not crosscheck with documentation. They do not test with their current database. When it is so easy to do.

Adding a column in SQL

Actually, adding a column is a fast operation in the major modern relational databases. I’ll create a table. Check the size. Then add a nullable column without default. Check the size. Then add a column with a default value. Check the size again. Size staying the same means no rows updated. Of course, you can test further: look at the elapsed time on a large table, and the amount of reads, and the redo/WAL generated,… You will see nothing in the major current RDBMS. Then you actually update all rows and compare. There you will see the size, the time, the reads, and the writes and understand that, with an explicit update the rows are actually updated. But not with the DDL to add a column.

PostgreSQL

Here is the example in PostgreSQL 12 in dbfiddle:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=9acf5fcc62f0ff1edd0c41aafae91b05

Another example where I show the WAL size:

Oracle Database

Here is the example in Oracle Database 18c in dbfiddle:
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=b3a2d41636daeca5f8e9ea1d771bbd23

Another example:

Yes, I even tested in Oracle7 where, at that time, adding a not null column with a default value actually scanned the table. The workaround is easy with a view. Adding a nullable column (which is what you do in NoSQL) was already a fast operation, and that’s 40 years ago!

MySQL

Here is the example in MySQL 8 in dbfiddle:
https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=8c14e107e1f335b505565a0bde85f6ec

https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1996w" sizes="(max-width: 1024px) 100vw, 1024px" />

Microsoft SQL Server

It seems that the table I use is too large for dbfiddle but I’ve run the same on my laptop:


1> set statistics time on;
2> go

1> create table demo (x numeric);
2> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 2 ms,  elapsed time = 2 ms.

1> with q as (select 42 x union all select 42)
2> insert into demo
3> s join q j cross join q k cross join q l cross join q m cross join q n cross join q o  cross join q p  cross join q r cross join q s cross join q t  cross join q u;
4> go
SQL Server parse and compile time:
   CPU time = 11 ms, elapsed time = 12 ms.

 SQL Server Execution Times:
   CPU time = 2374 ms,  elapsed time = 2148 ms.

(1048576 rows affected)

1> alter table demo add b numeric ;
2> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 3 ms.

1> alter table demo add c numeric default 42 not null;
2> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 1 ms,  elapsed time = 2 ms.

2> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.
x                    b                    c
-------------------- -------------------- --------------------
                  42                 NULL                   42
                  42                 NULL                   42
                  42                 NULL                   42

(3 rows affected)

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

3> go
SQL Server parse and compile time:
   CPU time = 0 ms, elapsed time = 0 ms.

 SQL Server Execution Times:
   CPU time = 3768 ms,  elapsed time = 3826 ms.

(1048576 rows affected)

2 milliseconds for adding a column with a value, visible on all those million rows (and it can be more).

YugaByte DB

In a distributed database, metadata must be updated in all nodes, but this is still in milliseconds whatever the table size is:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1142w" sizes="(max-width: 1024px) 100vw, 1024px" />

I didn’t show the test with not null and default value as I encountered an issue (adding column is fast but default value not selected). I don’t have the latest version (YugaByte DB is open source and in very active development) and this is probably an issue going to be fixed.

Tibero

Tibero is a database with very high compatibility with Oracle. I’ve run the same SQL. But this version 6 seems to be compatible with Oracle 11 where adding a non null column with default had to update all rows:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/Annotat... 1382w" sizes="(max-width: 1024px) 100vw, 1024px" />

You can test on any other databases with a code similar to this one:


-- CTAS CTE Cross Join is the most cross-RDBMS I've found to create one million rows
create table demo as with q as (select 42 x union all select 42) select 42 x from q a cross join q b cross join q c cross join q d cross join q e cross join q f cross join q g cross join q h cross join q i cross join q j cross join q k cross join q l cross join q m cross join q n cross join q o  cross join q p  cross join q r cross join q s cross join q t  cross join q u;
-- check the time to add a column
alter table demo add b numeric ;
-- check the time for a column with a value set for all existing rows
alter table demo add c numeric default 42 not null;
-- check that all rows show this value
select * from demo order by x fetch first 3 rows only;
-- compare with the time to really update all rows
update demo set c=42;

and please don’t hesitate to comment this blog post or the following tweet with you results:

NoSQL semi-structured

The myth comes from old versions of some databases that did no implement the ALTER TABLE .. ADD in an optimal way. And the NoSQL inventors probably knew only MySQL which was late in this area. Who said that MySQL evolution suffered from its acquisition by Oracle? They reduce the gap with other databases, like with this column adding optimisation.

If you stay with this outdated knowledge, you may think that NoSQL with semi-structured collections is more Agile, right? Yes, of course, you can add a new attribute when inserting a new item. It has zero cost and you don’t have to declare it to anyone. But what about the second case I tested in all those SQL databases, where you want to define a value for the existing rows as well? As we have seen, SQL allows that with a DEFAULT clause. In NoSQL you have to scan and update all items. Or you need to implement some logic in the application, like “if null then value”. That is not agile at all: as a side effect of a new feature, you need to change all data or all code.

Relational databases encapsulate the physical storage with a logical view. And in addition to that this logical view protects the existing application code when it evolves. This E.F Codd rule number 9: Logical Data Independence. You can deliver declarative changes to your structures without modifying any procedural code or stored data. Now, who is agile?

Structured data have metadata: performance and agility

How does it work? The RDBMS dictionary holds information about the structure of the rows, and this goes beyond a simple column name and datatype. The default value is defined here, which is why the ADD column was immediate. This is just an update of metadata. It doesn’t touch any existing data: performance. It exposes a virtual view to the application: agility. With Oracle, you can even version those views and deliver them to the application without interruption. This is called Edition Based Redefinition.

There are other smart things in the RDBMS dictionary. For example, when I add a column with the NOT NULL attribute, this assertion is guaranteed. I don’t need any code to check whether the value is set or not. Same with constraints: one declaration in a central dictionary makes all code safe and simpler because the assertion is guaranteed without additional testing. No need to check for data quality as it is enforced by design. Without it, how many sanity assumptions do you need to add in your code to ensure that erroneous data will not corrupt everything around? We have seen adding a column, but think about something even simple. Naming things is the most important in IT. Allow yourself to realize you made a mistake, or some business concepts change, and modify the name of a column for a more meaningful one. That can be done easily, even with a view to keep compatibility with previous code. Changing an attribute name in a large collection of JSON items is not so easy.

Relational databases have been invented for agility

Let me quote the reason why CERN decided to use Oracle in 1982 for the LEP – the ancestor of the LHC: Oracle The Database Management System For LEP: “Relational systems transform complex data structures into simple two-dimensional tables which are easy to visualize. These systems are intended for applications where preplanning is difficult…”

Preplanning not needed… isn’t that the definition of Agility with 20th century words?

Another good read to clear some myth: Relational Database: A Practical Foundation for Productivity by E.F. Codd Some problems that are solved by relational databases are the lack of distinction between “the programmer’s (logical) view of the data and the (physical) representation of data in storage”, the “subsequent changes in data description” forcing code changes, and “programmers were forced to think and code in terms of iterative loops” because of the lack of set processing. Who says that SQL and joins are slow? Are your iterative loops smarter than hash joins, nested loops and sort merge joins?

Never say No

I’m not saying that NoSQL is good or bad or better or worse. It is bad only when the decision is based on myths rather than facts. If you want agility on your data domain structures, stay relational. If you want to allow any future query pattern, stay relational. However, there are also some use cases that can fit in a relational database but may also benefit from another engine with optimal performance in key-value lookups. I have seen tables full of session state with a PRIMARY KEY (user or session ID) and a RAW column containing some data meaningful only for one application module (login service) and without durable purpose. They are acceptable in a SQL table if you take care of the physical model (you don’t want to cluster those rows in a context with many concurrent logins). But a Key-Value may be more suitable. We still see Oracle tables with LONG datatypes. If you like that you probably need a key-value NoSQL. Databases can store documents, but that’s luxury. They benefit from consistent backups and HA but at the prize of operating a very large and growing database. Timeseries, or graphs, are not easy to store in relational tables. NoSQL databases like AWS DynamoDB are very efficient for those specific use cases. But this is when all access patterns are known from design. If you know your data structure and cannot anticipate all queries, then relational databases systems (which means more than a simple data store) and SQL (the 4th generation declarative language to manipulate data by sets) are still the best choice for agility.

Cet article The myth of NoSQL (vs. RDBMS) agility: adding attributes est apparu en premier sur Blog dbi services.

Versioning for your local Vagrant boxes: handling updates

In my last post I summarised how to enable versioning for Vagrant box outside Vagrant’s cloud. In this part I’d like to share how to update a box.

My environment

The environment hasn’t changed compared to the first post. In summary I’m using

  • Ubuntu 20.04 LTS
  • Virtualbox 6.1.6
  • Vagrant 2.2.7

Updating a box

Let’s assume it’s time to update the base box for whatever reason. I most commonly update my boxes every so often after having run an “yum upgrade -y” to bring it up to the most current software. A new drop of the Guest Additions also triggers a rebuild, and so on.

Packaging

Once the changes are made, you need to package the box again. Continuing the previous example I save all my boxes and their JSON metadata in ~/vagrant/boxes. The box comes first:

[martin@host ~]$ vagrant package --base oraclelinux7base --output ~/vagrant/boxes/ol7_7.8.1.box

This creates a second box right next to the existing one. Note I bumped the version number to 7.8.1 to avoid file naming problems:

[martin@host boxes]$ ls -1
ol7_7.8.0.box
ol7_7.8.1.box
ol7.json 

Updating metadata

The next step is to update the JSON document. At this point in time, it references version 7.8.0 of my box:

[martin@host boxes]$ cat ol7.json 
{
    "name": "ol7",
    "description": "Martins Oracle Linux 7",
    "versions": [
      {
        "version": "7.8.0",
        "providers": [
          {
            "name": "virtualbox",
            "url": "file:///home/martin/vagrant/boxes/ol7_7.8.0.box",
            "checksum": "db048c3d61c0b5a8ddf6b59ab189248a42bf9a5b51ded12b2153e0f9729dfaa4",
            "checksum_type": "sha256"
          }
        ]
      }
    ]
  } 

You probably suspected what’s next :) A new version is created by adding a new element into the versions array, like so:

{
  "name": "ol7",
  "description": "Martins Oracle Linux 7",
  "versions": [
    {
      "version": "7.8.0",
      "providers": [
        {
          "name": "virtualbox",
          "url": "file:///home/martin/vagrant/boxes/ol7_7.8.0.box",
          "checksum": "db048c3d61c0b5a8ddf6b59ab189248a42bf9a5b51ded12b2153e0f9729dfaa4",
          "checksum_type": "sha256"
        }
      ]
    },
    {
      "version": "7.8.1",
      "providers": [
        {
          "name": "virtualbox",
          "url": "file:///home/martin/vagrant/boxes/ol7_7.8.1.box",
          "checksum": "f9d74dbbe88eab2f6a76e96b2268086439d49cb776b407c91e4bd3b3dc4f3f49",
          "checksum_type": "sha256"
        }
      ]
    }
  ]
} 

Don’t forget to update the SHA256 checksum!

Check for box updates

Back in my VM directory I can now check if there is a new version of my box:

[martin@host versioning]$ vagrant box outdated
Checking if box 'ol7' version '7.8.0' is up to date...
A newer version of the box 'ol7' for provider 'virtualbox' is
available! You currently have version '7.8.0'. The latest is version
'7.8.1'. Run `vagrant box update` to update.
[martin@host versioning]$ 

And there is! Not entirely surprising though, so let’s update the box:

[martin@host versioning]$ vagrant box update
==> default: Checking for updates to 'ol7'
    default: Latest installed version: 7.8.0
    default: Version constraints: 
    default: Provider: virtualbox
==> default: Updating 'ol7' with provider 'virtualbox' from version
==> default: '7.8.0' to '7.8.1'...
==> default: Loading metadata for box 'file:///home/martin/vagrant/boxes/ol7.json'
==> default: Adding box 'ol7' (v7.8.1) for provider: virtualbox
    default: Unpacking necessary files from: file:///home/martin/vagrant/boxes/ol7_7.8.1.box
    default: Calculating and comparing box checksum...
==> default: Successfully added box 'ol7' (v7.8.1) for 'virtualbox'! 

At the end of this exercise both versions are available:

[martin@host versioning]$ vagrant box list | grep ^ol7
ol7               (virtualbox, 7.8.0)
ol7               (virtualbox, 7.8.1)
[martin@host versioning]$  

This is so much better than my previous approach!

What are the effects of box versioning?

You could read earlier when I created a Vagrant VM based on version 7.8.0 of my box. This VM hasn’t been removed. What happens if I start it up now that there’s a newer version of the ol7 box available?

Bringing machine 'default' up with 'virtualbox' provider...
==> default: Checking if box 'ol7' version '7.8.0' is up to date...
==> default: A newer version of the box 'ol7' is available and already
==> default: installed, but your Vagrant machine is running against
==> default: version '7.8.0'. To update to version '7.8.1',
==> default: destroy and recreate your machine.
==> default: Clearing any previously set forwarded ports...
==> default: Fixed port collision for 22 => 2222. Now on port 2200.
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2200 (host) (adapter 1)
==> default: Running 'pre-boot' VM customizations...
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2200
    default: SSH username: vagrant
    default: SSH auth method: private key
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
==> default: Setting hostname...
==> default: Mounting shared folders...
    default: /vagrant => /home/martin/vagrant/versioning
==> default: Machine already provisioned. Run `vagrant provision` or use the `--provision`
==> default: flag to force provisioning. Provisioners marked to run always will still run. 

Vagrant tells me that I’m using an old version of the box, and how to switch to the new one. I think I’ll do this eventually, but I can still work with the old version.

And what if I create a new VM? By default, Vagrant creates the new VM based on the latest version of my box, 7.8.1. You can see this here:

Bringing machine 'default' up with 'virtualbox' provider...
==> default: Importing base box 'ol7'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'ol7' version '7.8.1' is up to date...
==> default: Setting the name of the VM: versioning2_default_1588259041745_89693
==> default: Fixed port collision for 22 => 2222. Now on port 2201.
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2201 (host) (adapter 1)
==> default: Running 'pre-boot' VM customizations...
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2201
    default: SSH username: vagrant
    default: SSH auth method: private key
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
==> default: Setting hostname...
==> default: Mounting shared folders...
    default: /vagrant => /home/martin/vagrant/versioning2 

Cleaning up

As with every technology, housekeeping is essential to keep disk usage in check. Refer back to the official documentation for more details on housekeeping and local copies of Vagrant boxes.

Summary

In the past I really struggled maintaining my local Vagrant boxes. Updating a box proved quite tricky and came with undesired side effects. Using versioning as demonstrated in this post is a great way out of this dilemma. And contrary to what I thought for a long time uploading my boxes to Vagrant cloud is not needed.

There is of course a lot more to say about versioning as this feature can do so much more. Maybe I’ll write another post about that subject some other time, until then I kindly refer you to the documentation.

Video : SQLcl : Format Query Results

In today’s video we demonstrate how to format query results in SQLcl using query comments and the SQLFORMAT setting.

For those of you that prefer to read, the video is based on this article.

The star of today’s video is Charles Wilson, who I got chatting with at the OTN lounge at a previous OpenWorld. </p />
</p></div>

    	  	<div class=

Oracle 19c Automatic Indexing: Mixing Manual and Automatic Indexes Part II (Stay)

  In my previous post, I discussed how Automatic Indexing did not recognise there was already an existing logically equivalent manually created index and so created effectively a redundant Automatic Index. I also discussed previously how Automatic Indexing was clever enough to logically add new columns to existing Automatic Indexes if it determined such a […]

Versioning for your local Vagrant boxes: adding a new box

I have been using Vagrant for quite some time now can’t tell you how much of a productivity boost it has been. All the VMs I have on my laptop are either powered by Vagrant, or feed into the Vagrant workflow.

One thing I haven’t worked out though is how to use versioning outside of Vagrant’s cloud. I don’t think I have what it takes to publish a good OS image publicly, and rather keep my boxes to myself to prevent others from injury.

My environment

While putting this post together I used the following software:

  • Ubuntu 20.04 LTS acts as my host operating system
  • Virtualbox 6.1.6
  • Vagrant 2.2.7

This is probably as current as it gets at the time of writing.

The need for box versioning

Vagrant saves you time by providing “gold images” you can spin up quickly. I prefer to always have the latest and greatest software available without having to spend ages on updating kernels and/or other components. As a result, I update my “gold image” VM from time to time, before packaging it up for Vagrant. Until quite recently I haven’t figured out how to update a VM other than delete/recreated it. This isn’t the best idea though, as indicated by this error message:

$ vagrant box remove debianbase-slim
Box 'debianbase-slim' (v0) with provider 'virtualbox' appears
to still be in use by at least one Vagrant environment. Removing
the box could corrupt the environment. We recommend destroying
these environments first:

default (ID: ....)

Are you sure you want to remove this box? [y/N] n 

This week I finally sat down trying to work out a better way of refreshing my Vagrant boxes.

As I understand it, box versioning allows me to update my base box without having to trash any environments. So instead of removing the box and replacing it with another, I can add a new version to the box. Environments using the old version can do so until they are torn down. New environments can use the new version. This works remarkably easy, once you know how to set it up! I found a few good sources on the Internet and combined them into this article.

Box versioning for Oracle Linux 7

As an Oracle person I obviously run Oracle Linux a lot. Earlier I came up with a procedure to create my own base boxes. This article features “oraclelinux7base” as the source for my Vagrant boxes. It adheres to all the requirements for Vagrant base boxes to be used with the Virtualbox provider.

Packaging the base box

Once you are happy to release your Virtualbox VM to your host, you have to package it for use with Vagrant. All my Vagrant boxes go to ~/vagrant/boxes, so this command creates the package:

$ vagrant package --base oraclelinux7base --output ~/vagrant/boxes/ol7_7.8.0.box
==> oraclelinux7base: Attempting graceful shutdown of VM...
==> oraclelinux7base: Clearing any previously set forwarded ports...
==> oraclelinux7base: Exporting VM...
==> oraclelinux7base: Compressing package to: /home/martin/vagrant/boxes/ol7_1.0.0.box 

In plain English this command instructs Vagrant to take Virtualbox’s oraclelinux7base VM and package it into ~/vagrant/boxes/ol7_7.8.0.box. I am creating this VM as the first OL 7.8 system, the naming convention seems optional yet I think it’s best to indicate the purpose and version in the package name.

At this stage, DO NOT “vagrant add” the box!

Creating box metadata

The next step is to create a little metadata describing the box. This time it’s not to be written in YAML, but JSON for a change. I found a few conflicting sources and I couldn’t get them to work until I had a look at how Oracle solved the problem. If you navigate to yum.oracle.com/boxes, you can find the links to their metadata files. I really appreciate Oracle changing to using versioning of their boxes, too!

After a little trial-and-error I came up with this file. It’s probably just the bare minimum, but it works for me in my lab so I’m happy to keep it the way it is. The file lives in ~/vagrant/boxes alongside the box file itself.

$ cat ol7.json
{
    "name": "ol7",
    "description": "Martins Oracle Linux 7",
    "versions": [
      {
        "version": "7.8.0",
        "providers": [
          {
            "name": "virtualbox",
            "url": "file:///home/martin/vagrant/boxes/ol7_7.8.0.box",
            "checksum": "db048c3d61c0b5a8ddf6b59ab189248a42bf9a5b51ded12b2153e0f9729dfaa4",
            "checksum_type": "sha256"
          }
        ]
      }
    ]
  } 

The file should be self-explanatory. The only noteworthy issue to run into is an insufficient number of forward slashes in the URL the URI is composed of “file://” followed by the fully qualified path to the box file, 3 forward slashes in total.

I used “sha256sum /home/martin/vagrant/boxes/ol7_7.8.0.box” to calculate the checksum.

Creating a VM

Finally it’s time to create the VM. I tend to create a directory per Vagrant environment, in this example I called it “versioning”. Within ~/vagrant/versioning I can create a Vagrantfile with the VM’s definition. At this stage, the base box is unknown to Vagrant.

$ nl Vagrantfile 
     1    # -*- mode: ruby -*-
     2    # vi: set ft=ruby :

     3    Vagrant.configure("2") do |config|
     4      config.vm.box = "ol7"
     5      config.vm.box_url = "file:///home/martin/vagrant/boxes/ol7.json"
     6      
     7      config.ssh.private_key_path = '/home/martin/.ssh/vagrantkey'

     8      config.vm.hostname = "server1"

     9      config.vm.provider "virtualbox" do |vb|
    10        vb.cpus = 2
    11        vb.memory = "4096"
    12      end

    13    end
 

The difference to my earlier post is the reference to the JSON file in line 5. The JSON file tells vagrant where to find the Vagrant box. The remaining configuration isn’t different from using non-versioned Vagrant boxes.

Based on this configuration file I can finally spin up my VM:

$ vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'ol7' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: >= 0
==> default: Loading metadata for box 'file:///home/martin/vagrant/boxes/ol7.json'
    default: URL: file:///home/martin/vagrant/boxes/ol7.json
==> default: Adding box 'ol7' (v7.8.0) for provider: virtualbox
    default: Unpacking necessary files from: file:///home/martin/vagrant/boxes/ol7_7.8.0.box
    default: Calculating and comparing box checksum...
==> default: Successfully added box 'ol7' (v7.8.0) for 'virtualbox'!
==> default: Importing base box 'ol7'...
==> default: Matching MAC address for NAT networking...
==> default: Checking if box 'ol7' version '7.8.0' is up to date...
==> default: Setting the name of the VM: versioning_default_1588251635800_49095
==> default: Fixed port collision for 22 => 2222. Now on port 2200.
==> default: Clearing any previously set network interfaces...
==> default: Preparing network interfaces based on configuration...
    default: Adapter 1: nat
==> default: Forwarding ports...
    default: 22 (guest) => 2200 (host) (adapter 1)
==> default: Running 'pre-boot' VM customizations...
==> default: Booting VM...
==> default: Waiting for machine to boot. This may take a few minutes...
    default: SSH address: 127.0.0.1:2200
    default: SSH username: vagrant
    default: SSH auth method: private key
==> default: Machine booted and ready!
==> default: Checking for guest additions in VM...
==> default: Setting hostname...
==> default: Mounting shared folders...
    default: /vagrant => /home/martin/vagrant/versioning 

Right at the beginning you can see that Vagrant loads “metadata for box ‘file:///home/martin/vagrant/boxes/ol7.json'” and then loads the box from the location specified in the JSON file.

Once the machine is started, I can also see it available for future use:

$ vagrant box list | grep ^ol7
ol7               (virtualbox, 7.8.0) 

The box is registered as ol7, using the Virtualbox provider in version 7.8.0.

Summary

In this post I summarised (mainly for my own later use ;) how to use box versioning on my development laptop. It really isn’t that much of a difference compared to the previous way I worked and the benefit will become apparent once you update the box. I’m going to cover upgrading my “ol7” box in another post.

Execution Plans

Table Of Contents

1.0 Introduction
2.0 Overview
3.0 The Main Course
4.0 Simplify
5.0 Filling the Gaps
6.0 Looking at the numbers
7.0 Predicate Information
8.0 Resolution
9.0 Summary
Footnote


 

1.0 Introduction

1.1 In a comment to a recent post on reading a non-trivial execution someone asked me to repeat the exercise using a plan I had published a few days previously in a post about tweaking the hints in an outline. The query in question involved a number of subqueries and transformations of different types, which means it’s going to take a little work explaining the details, and it’s probably going to be a fairly long read.

1.2 Here’s the query that produced the plan we’re going to examine. I’ve done some cosmetic alteration  to make it a little easier to read (though it’s still not perfect according to my standards). I’ve also made one very important addition to the query to make it easier to follow my walkthrough of the execution plan; the original text didn’t specify any query block names (/*+ qb_name() */ hints) even though it starts off with 9 separate query blocks, so I’ve walked through the text very carefully adding in the query block names that Oracle would have used (sel$NN) for each query block. In this case I got lucky because there were no views of other recursive problems involved so all I had to do was find each occurence of the word “select” in literal text order and increment the NN in sel$NN for each one.


SELECT  /*+ QB_NAME(SEL$1) */
        COUNT(applicant_id)
FROM    (
        SELECT  /*+ QB_NAME(SEL$2) */
                applicant_id,
                academic_year,
                applicant_gender,
                medium_of_study,
                education_type,
                college_id,
                course_id,
                medium_id,
                hostel_required,
                preference_order,
                status_flag,
                attribute7,  -- Added on 7-mar-20
                college_status_flag,
                percentage,
                caste_category,
                alloted_category,
                NULL allotment_type
        FROM    (
                SELECT   /*+ QB_NAME(SEL$3) */
                        adt.applicant_id,
                        lmt_gender.lov_code applicant_gender,
                        adt.medium_of_study,
                        act.college_id,
                        lmt_education_type.lov_code education_type,
                        act.course_id,
                        act.medium_id,
                        act.hostel_required,
                        act.preference_order,
                        act.status_flag,
                        act.attribute7, -- Added on 7-mar-20
                        adt.college_status_flag,
                        adt.academic_year,
                        adt.percentage,
                        adt.applicant_dob,
                        adt.legacy_appln_date,
                        adt.caste_category,
                        act.attribute1 alloted_category,
                        DECODE (lmt_pass.lov_code,  'ATTFIRST', 1,  'COMPARTL', 2,  3) order_of_pass,
                        DECODE (late_entry_flag,  'N', 1,  'Y', 2,  3)      order_of_entry,
                        DECODE (lmt_appearance.lov_code,  'REGULAR', 1,  'PRIVATE', 2,  3) order_of_appearance,
                        DECODE (adt.is_ttd_employ_ward,  'Y', 1,  'N', 2,  3) order_of_ttd_emp,
                        DECODE (adt.is_balbhavan_studnt,  'Y', 1,  'N', 2,  3) order_of_schooling,
                        act.attribute3 course_qe_priority,
                        adt.is_local_canditature_valid,
                        adt.is_ttd_emp_ward_info_valid,
                        adt.is_sv_bm_student_info_valid,
                        adt.is_social_ctgry_info_valid,
                        DECODE(adt.college_status_flag,'B',1,'O',2,'N',3) order_of_status
                FROM 
                        xxadm.xxadm_applicant_details_tbl    adt,
                        xxadm.xxadm_applicant_coursprefs_tbl act,
                        xxadm.xxadm_college_master_tbl       cmt,
                        xxadm.xxadm_course_master_tbl        crmt,
                        xxadm.xxadm_medium_master_tbl        mmt,
                        xxadm.xxadm_lov_master_tbl           lmt_gender,
                        xxadm.xxadm_lov_master_tbl           lmt_pass,
                        xxadm.xxadm_lov_master_tbl           lmt_appearance,
                        xxadm.xxadm_lov_master_tbl           lmt_religion,
                        xxadm.xxadm_lov_master_tbl           lmt_education_type
                WHERE
                        adt.applicant_id = act.applicant_id
                AND     act.college_id = cmt.college_id
                AND     act.course_id = crmt.course_id
                AND     act.medium_id = mmt.medium_id
                AND     adt.applicant_gender = lmt_gender.lov_id
                AND     adt.pass_type = lmt_pass.lov_id
                AND     adt.appearance_type = lmt_appearance.lov_id
                AND     adt.religion = lmt_religion.lov_id
                AND     cmt.education_type = lmt_education_type.lov_id
                AND     adt.status = 'Active'
                AND     1 = (CASE 
                                WHEN act.hostel_required = 'Y'
                                        THEN (CASE
                                                     WHEN    adt.distance_in_kms >20
                                                     AND     lmt_religion.lov_code = 'HINDU'
                                                     AND     adt.caste_category NOT IN (
                                                                     SELECT  /*+ QB_NAME(SEL$4) */
                                                                             category_id
                                                                     FROM    xxadm.xxadm_category_master_tbl
                                                                     WHERE   category_code IN ('BACKWRDC', 'BACKWRDE')
                                                             )
                                                             THEN 1
                                                             ELSE 2 
                                              END
                                             )
                                        ELSE 1 
                               END
                              )
                AND     1 =  (CASE 
                                WHEN act.hostel_required  = 'Y'
                                        THEN    (CASE 
                                                        WHEN    (    lmt_education_type.lov_code = 'COEDUCOL' 
                                                                 AND mt_gender.lov_code = 'FEMALE'
                                                                )
                                                                THEN 2
                                                                ELSE 1 
                                                 END
                                                )
                                        ELSE 1 
                               END
                              )
                AND     adt.course_applied_for = 'DEG' 
                AND     (adt.college_status_flag IS NULL OR adt.college_status_flag IN ('N','T','C','B','O')) 
                AND     act.preference_order <= NVL( -- > comment to avoid WordPress format issue
                                (SELECT  /*+ QB_NAME(SEL$5) */ 
                                         preference_order 
                                 FROM    xxadm.xxadm_applicant_coursprefs_tbl act1 
                                 WHERE   act1.applicant_id = adt.applicant_id 
                                 AND     status_flag IN('B','T','C','O') 
                                 ), act.preference_order 
                        )
                AND     act.preference_order >=  NVL(
                                (SELECT /*+ QB_NAME(SEL$6) */
                                        preference_order
                                FROM    xxadm.xxadm_applicant_coursprefs_tbl act2 
                                WHERE   act2.applicant_id = adt.applicant_id
                                AND     status_flag  = 'C'
                                ), act.preference_order
                        )
                AND     act.preference_order NOT IN (
                                SELECT  /*+ QB_NAME(SEL$7) */
                                        act3.preference_order 
                                FROM    xxadm.xxadm_applicant_coursprefs_tbl act3
                                WHERE   act3.applicant_id = adt.applicant_id 
                                AND     act3.status_flag  = 'O'
                        ) 
                AND     act.preference_order NOT IN (
                                SELECT  /*+ QB_NAME(SEL$8) */
                                        act1.preference_order 
                                FROM    xxadm.xxadm_applicant_coursprefs_tbl act1 
                                WHERE   act1.applicant_id = adt.applicant_id 
                                AND     act1.status_flag IN ('C','B')
                                AND     act1.attribute1 IN (
                                                SELECT  /*+ QB_NAME(SEL9) */
                                                        category_id 
                                                FROM    xxadm.xxadm_category_master_tbl 
                                                WHERE   category_code IN ('OPENMERT')
                                        ) 
                                AND     NVL(act1.attribute7,'N') = 'N'
                        ) 
                AND     cmt.college_id = :p_college_id
                AND     crmt.course_id = :p_course_id
                AND     mmt.medium_id  = :p_medium_id
                AND     act.hostel_required = :p_hostel_required
                ORDER BY
                        order_of_pass,
                        course_qe_priority,
                        percentage DESC,
                        applicant_dob,
                        legacy_appln_date
                ) 
        WHERE
                 ROWNUM <=  :p_seats
        ) 
WHERE 
        applicant_id = :p_applicant_id
;

Figure 1-1

1.3 This query first came to light in a thread on the Oracle Developer forums with an extract from a tkprof file showing that it had executed 842,615  times. That number should be ringing alarms and flashing warning lights, but if we assume that there really is no way of doing some sort of batch processing to get through the data we need to do a little bit of arithmetic to see how much of a threat this query is and how much is matters.

1.4 For every extra 0.01 seconds it takes to execute this query the total run-time goes up by8,426 seconds, which is 2 hours and 20 minutes. If the average execution time is a mere 0.06 seconds you’ll be at it all night long – and it will be a long, long night.


 

2.0 Overview

2.1 Before we look at the execution plan let’s take a moment to pick out a few points from the query. You may want to re-open this post in a separate window so that you can switch easily between the SQL and my comments.

2.2 We start off with a simple select from an inline view – and if we replace the inline view the simple “object name” V_THING we get the following query:


select  count(applicant_id)
from    V_THING
where   applicant_id = :p_applicant_id
;

Figure 2-1

2.3 This should prompt two questions

  • First, how far into the view V_THING will the optimizer be able to push that predicate, possibly the entire content of the view will have to be constructed before the predicate can apply, possibly the nature of the view is such that the optimizer could do a simple filter pushdown to apply the predicate very early. That still leaves (or leads on to) the question of whether the optmizer might then be able to generate further uses of the predicate through transitive closure.
  • Secondly, if the view V_THING is a multiable view will we be able to work out which table applicant_id comes from by the time it becomes visible in the view.  It’s possible that changing the table from which applicant_id comes will change the execution plan.

2.4 Digging down one layer we see that our V_THING is also a simple select from an inline view – let’s call it V_ANOTHER – so if we again forget about the complexity of the inner view we’re looking at a query that goes:


select  /*+ QB_NAME(SEL$1) */
        count(applicant_id)
from    (
        select  /*+ QB_NAME(SEL$2) */
                applicant_id, 
                {15 more columns}
                null    allotment_type
        from
                V_OTHER
        where
                rownum <=  :p_seats
        )       V_THING
where 
        applicant_id = :p_applicant_id
;

Figure 2-2

2.5 A couple of details hit the eye when you look at this: Why are we selecting 17 columns from a complex view, and then counting only one of them and discarding the rest. Let’s hope the optimizer is smart enough to discard the excess columns at the earliest possible moment (which might allow it to do some index-only accesses instead of visiting tables for columns we don’t really need).

2.6 Stranger still, one of those columns is a delberately generated NULL! This hints at the possibility that the client code is doing something like “count how many query X will give me, then run query X”– giving us the pattern “select count(*) from (inlne query X); execute query X” Maybe this whole query is a waste of time, but if it can’t be avoided maybe it should be edited down to the smallest  query that will get the correct count.

2.7 Another thought about this layer of the query, the predicate “rownum <= :bind_variable” may be pushing the optimizer into first_rows(n) optimization and this might be enough to make it choose a bad execution plan. I’d have to check, and check for specific versions, but off the top of my head I think that when comparing rownum with a bind variable the optimizer will optimizer for first_rows(10) unless there’s some other reason for choosing anything else.)

2.8 I’m also a little curious about a requirement that seems to say – “pick at most N rows, then tell me how many you’ve picked”. What’s it actually trying to do and why?

2.9 Let’s dig one layer deeper before we get into the complex stuff. Here’s a version of the code that expands V_OTHER in an extremely stripped down form:


SELECT  /*+ QB_NAME(SEL$1) */
        COUNT(applicant_id)
FROM    (
        SELECT  /*+ QB_NAME(SEL$2) */
                applicant_id, 
                {15 more columns}
                NULL allotment_type
        FROM    (
                SELECT   /*+ QB_NAME(SEL$3) */
                        {lots of columns}
                FROM 
                        {lots of tables}
                WHERE
                        {lots of predicates}
                ORDER BY
                        order_of_pass,
                        course_qe_priority,
                        percentage DESC,
                        applicant_dob,
                        legacy_appln_date
                )  
        WHERE
                ROWNUM <=  :p_seats
        ) 
WHERE 
        applicant_id = :p_applicant_id
;

Figure 2-3

2.10 At this point we can start to see reasons for the layering of inline views – we need to select data in the right order before we apply the rownum predicate; as for the excess columns in the select list – even if we selected only the applicant_id in the outer layers the optimizer would still have to acquire the five columns in the order by clause.

2.11 But this emphasises the oddity of the query. If we’re only counting applicant_id to see whether we got :p_seats or fewer rows for a specific applicant_id why does the order matter – surely the order will only matter when we “repeat” the query to get the actual rows (if that’s what we do). As it is, to count a small number of rows this query might have to fetch and sort a large number, then discard most of them. (Some statistics from other posts by the OP indicated that the underlying query might fetch anything between a few hundred and a couple of thousand rows. This particular run showed the query finding 171 rows to sort and then restricting the rowsource to the first two sorted rows)


 

3.0 The Main Course

3.1 To make it a little easier to discuss the detail of the execution plan I’ve laid it out in a small number of sections corresponding to the final (outline_leaf) query blocks the optimizer generated. To do this I applied two sets of information – the Query Block / Object Alias information (which follows the body of the plan) and any appearances of the VIEW operation in the plan.


--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |                                |      1 |        |   574 (100)|      1 |00:00:00.02 |    3822 |       |       |         |
|   1 |  SORT AGGREGATE                             |                                |      1 |      1 |            |      1 |00:00:00.02 |    3822 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
select count(applicant_id) - above
select where rownum less than - below
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  2 |   VIEW                                      |                                |      1 |      1 |   574   (2)|      0 |00:00:00.02 |    3822 |       |       |         |
|*  3 |    COUNT STOPKEY                            |                                |      1 |        |            |      2 |00:00:00.02 |    3822 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Start of "real" main query, query block SEL$7E0D484F
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   4 |     VIEW                                    |                                |      1 |      1 |   574   (2)|      2 |00:00:00.02 |    3822 |       |       |         |
|*  5 |      SORT ORDER BY STOPKEY                  |                                |      1 |      1 |   574   (2)|      2 |00:00:00.02 |    3822 |  2048 |  2048 | 2048  (0)|
|*  6 |       FILTER                                |                                |      1 |        |            |    171 |00:00:00.02 |    3822 |       |       |         |
|   7 |        NESTED LOOPS                         |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    3128 |       |       |         |
|   8 |         NESTED LOOPS                        |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    2946 |       |       |         |
|   9 |          NESTED LOOPS                       |                                |      1 |      1 |   567   (2)|    182 |00:00:00.02 |    2942 |       |       |         |
|  10 |           NESTED LOOPS                      |                                |      1 |      1 |   566   (2)|    182 |00:00:00.02 |    2938 |       |       |         |
|  11 |            NESTED LOOPS ANTI                |                                |      1 |      1 |   565   (2)|    182 |00:00:00.02 |    2752 |       |       |         |
|  12 |             NESTED LOOPS ANTI               |                                |      1 |      1 |   562   (2)|    182 |00:00:00.02 |    2388 |       |       |         |
|* 13 |              HASH JOIN                      |                                |      1 |      5 |   557   (2)|    182 |00:00:00.02 |    2022 |  1599K|  1599K| 1503K (0)|
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
join index transformation query block SEL$082F290F
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  14 |               VIEW                          | index$_join$_008               |      1 |    127 |     2   (0)|    127 |00:00:00.01 |       8 |       |       |         |
|* 15 |                HASH JOIN                    |                                |      1 |        |            |    127 |00:00:00.01 |       8 |  1368K|  1368K| 1522K (0)|
|  16 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_CODE_UK             |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |
|  17 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_PK                  |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Continuation of "real" main query, query block SEL$7E0D484F
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 18 |               HASH JOIN                     |                                |      1 |    478 |   555   (2)|    182 |00:00:00.01 |    2014 |  1245K|  1245K| 1277K (0)|
|  19 |                NESTED LOOPS                 |                                |      1 |    478 |   243   (2)|    209 |00:00:00.01 |     883 |       |       |         |
|  20 |                 NESTED LOOPS                |                                |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |       |       |         |
|  21 |                  TABLE ACCESS BY INDEX ROWID| XXADM_COLLEGE_MASTER_TBL       |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |       |       |         |
|* 22 |                   INDEX UNIQUE SCAN         | XXADM_COLLEGES_PK              |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |       |       |         |
|  23 |                  TABLE ACCESS BY INDEX ROWID| XXADM_LOV_MASTER_TBL           |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |       |       |         |
|* 24 |                   INDEX UNIQUE SCAN         | XXADM_LOVS_PK                  |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |       |       |         |
|* 25 |                 TABLE ACCESS FULL           | XXADM_APPLICANT_COURSPREFS_TBL |      1 |    478 |   241   (2)|    209 |00:00:00.01 |     879 |       |       |         |
|* 26 |                TABLE ACCESS FULL            | XXADM_APPLICANT_DETAILS_TBL    |      1 |   6685 |   311   (2)|  10488 |00:00:00.01 |    1131 |       |       |         |
|* 27 |              TABLE ACCESS BY INDEX ROWID    | XXADM_APPLICANT_COURSPREFS_TBL |    182 |   8881 |     1   (0)|      0 |00:00:00.01 |     366 |       |       |         |
|* 28 |               INDEX UNIQUE SCAN             | XXADM_APPLCNT_PREF_ORDER_UK    |    182 |      1 |     0   (0)|    182 |00:00:00.01 |     184 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unnested subquery SEL$A75BE177 (from sel$8, sel$9)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  29 |             VIEW PUSHED PREDICATE           | VW_SQ_1                        |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |       |       |         |
|  30 |              NESTED LOOPS                   |                                |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |       |       |         |
|* 31 |               TABLE ACCESS BY INDEX ROWID   | XXADM_APPLICANT_COURSPREFS_TBL |    182 |      1 |     2   (0)|      0 |00:00:00.01 |     364 |       |       |         |
|* 32 |                INDEX UNIQUE SCAN            | XXADM_APPLCNT_PREF_ORDER_UK    |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     184 |       |       |         |
|* 33 |               TABLE ACCESS BY INDEX ROWID   | XXADM_CATEGORY_MASTER_TBL      |      0 |      1 |     1   (0)|      0 |00:00:00.01 |       0 |       |       |         |
|* 34 |                INDEX UNIQUE SCAN            | XXADM_CATEGORY_PK              |      0 |      1 |     0   (0)|      0 |00:00:00.01 |       0 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Start of "real" main query, query block SEL$7E0D484F
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  35 |            TABLE ACCESS BY INDEX ROWID      | XXADM_LOV_MASTER_TBL           |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     186 |       |       |         |
|* 36 |             INDEX UNIQUE SCAN               | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |       |       |         |
|* 37 |           INDEX UNIQUE SCAN                 | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |       |       |         |
|* 38 |          INDEX UNIQUE SCAN                  | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |       |       |         |
|  39 |         TABLE ACCESS BY INDEX ROWID         | XXADM_LOV_MASTER_TBL           |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     182 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Filter subquery, query block SEL$5
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 40 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    182 |      1 |     3   (0)|     29 |00:00:00.01 |     507 |       |       |         |
|* 41 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_PREFS_UK         |    182 |      5 |     2   (0)|   1450 |00:00:00.01 |     191 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Filter subquery, query block SEL$6
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  42 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    171 |      1 |     2   (0)|      0 |00:00:00.01 |     173 |       |       |         |
|* 43 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_APPLICANT_STATUS |    171 |      1 |     1   (0)|      0 |00:00:00.01 |     173 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Filter subquery SEL$F665FE1B (from sel$4 with tranform for index join)
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 44 |        VIEW                                 | index$_join$_014               |      6 |      1 |     0   (0)|      0 |00:00:00.01 |      14 |       |       |         |
|* 45 |         HASH JOIN                           |                                |      6 |        |            |      0 |00:00:00.01 |      14 |  1519K|  1519K|  666K (0)|
|* 46 |          INDEX RANGE SCAN                   | XXADM_CATEGORY_PK              |      6 |      1 |     0   (0)|      6 |00:00:00.01 |       6 |       |       |         |
|  47 |          INLIST ITERATOR                    |                                |      6 |        |            |     12 |00:00:00.01 |       8 |       |       |         |
|* 48 |           INDEX UNIQUE SCAN                 | XXADM_CATEGORY_CODE_UK         |     12 |      1 |     0   (0)|     12 |00:00:00.01 |       8 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$2        / from$_subquery$_001@SEL$1
   3 - SEL$2
   4 - SEL$7E0D484F / from$_subquery$_002@SEL$2
   5 - SEL$7E0D484F
  14 - SEL$082F290F / LMT_GENDER@SEL$3
  15 - SEL$082F290F
  16 - SEL$082F290F / indexjoin$_alias$_001@SEL$082F290F
  17 - SEL$082F290F / indexjoin$_alias$_002@SEL$082F290F
  21 - SEL$7E0D484F / CMT@SEL$3
  22 - SEL$7E0D484F / CMT@SEL$3
  23 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3
  24 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3
  25 - SEL$7E0D484F / ACT@SEL$3
  26 - SEL$7E0D484F / ADT@SEL$3
  27 - SEL$7E0D484F / ACT3@SEL$7
  28 - SEL$7E0D484F / ACT3@SEL$7
  29 - SEL$A75BE177 / VW_SQ_1@SEL$67DC521B
  30 - SEL$A75BE177
  31 - SEL$A75BE177 / ACT1@SEL$8
  32 - SEL$A75BE177 / ACT1@SEL$8
  33 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9
  34 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9
  35 - SEL$7E0D484F / LMT_PASS@SEL$3
  36 - SEL$7E0D484F / LMT_PASS@SEL$3
  37 - SEL$7E0D484F / LMT_APPEARANCE@SEL$3
  38 - SEL$7E0D484F / LMT_RELIGION@SEL$3
  39 - SEL$7E0D484F / LMT_RELIGION@SEL$3
  40 - SEL$5        / ACT1@SEL$5
  41 - SEL$5        / ACT1@SEL$5
  42 - SEL$6        / ACT2@SEL$6
  43 - SEL$6        / ACT2@SEL$6
  44 - SEL$F665FE1B / XXADM_CATEGORY_MASTER_TBL@SEL$4
  45 - SEL$F665FE1B
  46 - SEL$F665FE1B / indexjoin$_alias$_001@SEL$F665FE1B
  48 - SEL$F665FE1B / indexjoin$_alias$_002@SEL$F665FE1B

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optimizer_use_feedback' 'false')
      OPT_PARAM('_optimizer_dsdir_usage_control' 0)
      OPT_PARAM('_optimizer_adaptive_plans' 'false')
      OPT_PARAM('_optimizer_gather_feedback' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$F665FE1B")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$A75BE177")
      PUSH_PRED(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B" 16 15)
      OUTLINE_LEAF(@"SEL$082F290F")
      OUTLINE_LEAF(@"SEL$7E0D484F")
      UNNEST(@"SEL$9D10C90A")
      UNNEST(@"SEL$7")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$1")
      OUTLINE(@"SEL$180402DE")
      OUTLINE(@"SEL$7E0D484F")
      UNNEST(@"SEL$9D10C90A")
      UNNEST(@"SEL$7")
      OUTLINE(@"SEL$67DC521B")
      OUTLINE(@"SEL$9D10C90A")
      UNNEST(@"SEL$9")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$C04829E0")
      ELIMINATE_JOIN(@"SEL$3" "CRMT"@"SEL$3")
      ELIMINATE_JOIN(@"SEL$3" "MMT"@"SEL$3")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$9")
      OUTLINE(@"SEL$3")
      NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
      NO_ACCESS(@"SEL$2" "from$_subquery$_002"@"SEL$2")
      INDEX_RS_ASC(@"SEL$7E0D484F" "CMT"@"SEL$3" ("XXADM_COLLEGE_MASTER_TBL"."COLLEGE_ID"))
      INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_EDUCATION_TYPE"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
      FULL(@"SEL$7E0D484F" "ACT"@"SEL$3")
      FULL(@"SEL$7E0D484F" "ADT"@"SEL$3")
      INDEX_JOIN(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_CODE") ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
      INDEX_RS_ASC(@"SEL$7E0D484F" "ACT3"@"SEL$7" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."PREFERENCE_ORDER"))
      NO_ACCESS(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B")
      INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_PASS"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
      INDEX_RS_ASC(@"SEL$7E0D484F" "LMT_APPEARANCE"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
      INDEX(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3" ("XXADM_LOV_MASTER_TBL"."LOV_ID"))
      LEADING(@"SEL$7E0D484F" "CMT"@"SEL$3" "LMT_EDUCATION_TYPE"@"SEL$3" "ACT"@"SEL$3" "ADT"@"SEL$3" "LMT_GENDER"@"SEL$3" "ACT3"@"SEL$7" "VW_SQ_1"@"SEL$67DC521B"
              "LMT_PASS"@"SEL$3" "LMT_APPEARANCE"@"SEL$3" "LMT_RELIGION"@"SEL$3")
      USE_NL(@"SEL$7E0D484F" "LMT_EDUCATION_TYPE"@"SEL$3")
      USE_NL(@"SEL$7E0D484F" "ACT"@"SEL$3")
      USE_HASH(@"SEL$7E0D484F" "ADT"@"SEL$3")
      USE_HASH(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3")
      USE_NL(@"SEL$7E0D484F" "ACT3"@"SEL$7")
      USE_NL(@"SEL$7E0D484F" "VW_SQ_1"@"SEL$67DC521B")
      USE_NL(@"SEL$7E0D484F" "LMT_PASS"@"SEL$3")
      USE_NL(@"SEL$7E0D484F" "LMT_APPEARANCE"@"SEL$3")
      USE_NL(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3")
      NLJ_BATCHING(@"SEL$7E0D484F" "LMT_RELIGION"@"SEL$3")
      SWAP_JOIN_INPUTS(@"SEL$7E0D484F" "LMT_GENDER"@"SEL$3")
      PQ_FILTER(@"SEL$7E0D484F" SERIAL)
      INDEX_RS_ASC(@"SEL$A75BE177" "ACT1"@"SEL$8" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."PREFERENCE_ORDER"))
      INDEX_RS_ASC(@"SEL$A75BE177" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9" ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_ID"))
      LEADING(@"SEL$A75BE177" "ACT1"@"SEL$8" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9")
      USE_NL(@"SEL$A75BE177" "XXADM_CATEGORY_MASTER_TBL"@"SEL$9")
      INDEX_RS_ASC(@"SEL$6" "ACT2"@"SEL$6" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."STATUS_FLAG"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$6" "ACT2"@"SEL$6")
      INDEX_RS_ASC(@"SEL$5" "ACT1"@"SEL$5" ("XXADM_APPLICANT_COURSPREFS_TBL"."APPLICANT_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."COLLEGE_ID"
              "XXADM_APPLICANT_COURSPREFS_TBL"."COURSE_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."MEDIUM_ID" "XXADM_APPLICANT_COURSPREFS_TBL"."HOSTEL_REQUIRED"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$5" "ACT1"@"SEL$5")
      INDEX_JOIN(@"SEL$4" "XXADM_CATEGORY_MASTER_TBL"@"SEL$4" ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_ID") ("XXADM_CATEGORY_MASTER_TBL"."CATEGORY_CODE"))
      END_OUTLINE_DATA
  */

Figure 3-1

3.2 There’s no rigid rule I can give you about an approach for looking for query blocks and transformations, but it’s worth checking to see which of your original query blocks still exist in the final execution plan and which have disappeared thanks to some transformation.

3.3 If we look down the Query Block Name list above we can see that sel$1, sel$2, sel$5 and sel$6 have “survived” the machinations of the optimizer. We’ve already noted that sel$1 and sel$2 are simply “select from {inline view}” as far as the optimizer is concerned; sel$5 and sel$6 are simple subqueries that appeared as filter subqueries in the original query text and have kept that status by the end of the optimizer’s transformation stage.

3.4 Tracking down the other query blocks that we named we can see the following:

  • sel$3 – most of its tables appear in a new query block called SEL$7E0D484F but one of them appears in a query block called SEL$082F290F; a closer look at SEL$082F290F shows us that it ranges from operations 14 to 17 and holds a “single table” transformation where the optimizer has chosen to use an index join of two indexes on the xxadm_lov_master_tbl rather than doing a tablescan. The index join is represented as a VIEW of a hash join, hence the separate query block. Another little detail we note – the xxadm_lov_master_tbl appears five times in the query, so we need to know which occurrence this is: fortunately the Object Alias information tells us at operation it’s the LMT_GENDER alias.
  • sel$4 is the scalar subquery inside a CASE expression, involving table xxadm_category_master_tbl. We can find the table name (which hasn’t been given an alias) and the query block name in the Object Alias information at operation 44 in a query block called SEL$F665FE1B. There are two points of interest about this query block – it has come into existence because it’s another example where the optimizer has used an index join to avoid a full tablescan; and it has been used in a filter subquery (the parent of operation 44 is the FILTER at operation 6).
  • sel$7 appeared in the original text as a NOT IN subquery against xxadm_applicant_coursprefs_tbl with an alias of act3. The Query Block / Object Alias information tells us that ACT3@SEL$7 appears at operations 27 and 28 – and when we track up the plan from operation 27 we see that it is the second child of operation 12 which is a nested loop anti. The optimizer has unnested the subquery and turned it into an anti join as one of the steps that produced query block SEL$7E0D484F
  • sel$8 appeared in the original text as a NOT IN subquery against xxadm_applicant_coursprefs_tbl aliased as act1 (OUCH – that’s the second time the alias act1 has appeared in this query!). But the subquery had it’s own subquery, named sel$9, against xxadm_category_master_tbl which didn’t have an alias (more OUCH!). When we search for ACT1@SEL$8 and XXADM_CATEGORY_MASTER_TBL@SEL$9 in the Query Block / Object Alias information we find that they both appear in a query block called SEL$A75BE177 which ranges from operations 29 to 34, and a check of the plan shows that operation 29 is a view pushed predicate of a view called VW_SQ_1 – a name that identifies the view as an internally generated non-mergeable view that was created as the result of unnestingn a subquery. The view contains a join between xxadm_applicant_coursprefs_tbl and xxadm_category_master_tbl, so we can say that the optimizer has unnested our sel$9 to create a NOT IN subquery that is a join subquery, then it has unnested again to produce an inline non-mergeable view, and it has then allowed “join predicate pushdown (JPPD)” so that the non-mergeable view can be the second table of a nested loop. To confirm the last comment we track up the plan to discover that operation 29 is the second child of operation 11 which is, indeed, a nested loop and (since the subquery was a NOT IN subquery) a nested loop anti.
  • sel$9 – see sel$8.

3.5 As you can see, when you’re using the execution plan output to identify what’s happened to the individual query blocks from your original query you’re likely to jump around from the query to the plan body, to the Query Block / Object Alias information in a fairly arbitrary way.

3.6 I’ll close this chapter of the analysis with a quick look at the Outline Data – in particular two of the hint types that appear: OUTLINE() and OUTLINE_LEAF() – which I’ve extracted and sorted for ease of reading:

      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$7")
      OUTLINE(@"SEL$8")
      OUTLINE(@"SEL$9")

      OUTLINE(@"SEL$180402DE")
      OUTLINE(@"SEL$67DC521B")
      OUTLINE(@"SEL$7E0D484F")
      OUTLINE(@"SEL$9D10C90A")
      OUTLINE(@"SEL$C04829E0")

      OUTLINE_LEAF(@"SEL$1")
      OUTLINE_LEAF(@"SEL$2")
      OUTLINE_LEAF(@"SEL$4")
      OUTLINE_LEAF(@"SEL$5")
      OUTLINE_LEAF(@"SEL$6")
      OUTLINE_LEAF(@"SEL$082F290F")
      OUTLINE_LEAF(@"SEL$7E0D484F")
      OUTLINE_LEAF(@"SEL$A75BE177")
      OUTLINE_LEAF(@"SEL$F665FE1B")

Figure 3-2

3.7 In this context an OUTLINE() is a query block that existed at some point in the optimization sequence that got us to the final execution plan but did not appear as a query block in the final plan. In the previous paragraphs we described how the original query blocks sel$3, sel$4, sel$7, sel$8 and sel$9 disappeared through transformation so (apart from sel$4 which is a bit of an anomaly that I’ll pick up in a moment) they appear in an   OUTLINE() hint. I described how sel$9 would have been unnested into sel$8 to create a join which would still have been a filter subquery until that too was unnested – that join subquery would have been one of the five OUTLINE() query blocks above with the 8 character hexadecimal names.

3.8 An OUTLINE_LEAF() is a “final” query block – one that is present in the final execution plan. If you ignore sel$4, you’ll see that the other 8 query blocks correspond to the 8 query block names that appear in the Query Block Name / Object Alias information. The appearance of sel$4 as an OUTLINE_LEAF() looks like an anomaly to me; I can’t think of a good reason why it should be in the list.


 

4.0 Simplify

4.1 We’re just about ready to do a full read-through of the execution plan. We’ve taken the two outer layers off the query/plan because they represent such simple in-line views, and we’ve discussed the disappearance of some of our initial query blocks and identified and explained all the different query blocks that have appeared in the final plan. So with the extra bits of information in hand let’s take a couple more steps in simplifying the execution plan.

4.2 First I’ll replace each of the three VIEW operations and their descendants with a single line that says “this is a rowsource”. I’ll distinguish between the two variants of the operation (VIEW and VIEW PUSHED PREDICATE) by calling them BULK ROWSOURCE and PRECISION ROWSOURCE respectively: it’s not a perfect description but broadly speaking we expect a VIEW to be called once by its parent to produce a “large” data set and we expect a VIEW PUSHED PREDICATE to be called many times by its parent to produce (each time) a “small” data set using extremely efficient methods.

4.3 Then I’ll remind you that a multichild FILTER operation calls the first child once to supply a rowsource then, for each row returned, calls the other child operations in turn to determine whether or not to keep the row from the first child. This means we can examine just the first child in isolation to see how the optimizer wants to get the driving bulk of the data (and we can examine the other children later, bearing in mind how often they might need to be called and checking how efficient each call is likely to be).

4.4 Finally I’ll note that query block SEL$7E0D484F (the “real main query” as I labelled it in the plan above) starts: “VIEW -> SORT ORDER BY STOPKEY -> FILTER” – after we’ve filtered our data we simply sort it with the intention of keeping only the “top few” rows. That part of the plan is so simple we’ll ignore those lines of the plan and focus on just the first child of the FILTER- leaving the core plan looking like this:


-----------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------------------------------------------
|   7 |        NESTED LOOPS                         |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    3128 |
|   8 |         NESTED LOOPS                        |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    2946 |
|   9 |          NESTED LOOPS                       |                                |      1 |      1 |   567   (2)|    182 |00:00:00.02 |    2942 |
|  10 |           NESTED LOOPS                      |                                |      1 |      1 |   566   (2)|    182 |00:00:00.02 |    2938 |
|  11 |            NESTED LOOPS ANTI                |                                |      1 |      1 |   565   (2)|    182 |00:00:00.02 |    2752 |
|  12 |             NESTED LOOPS ANTI               |                                |      1 |      1 |   562   (2)|    182 |00:00:00.02 |    2388 |
|* 13 |              HASH JOIN                      |                                |      1 |      5 |   557   (2)|    182 |00:00:00.02 |    2022 |
|  14 |               BULK ROWSOURCE                | index$_join$_008               |      1 |    127 |     2   (0)|    127 |00:00:00.01 |       8 |
|* 18 |               HASH JOIN                     |                                |      1 |    478 |   555   (2)|    182 |00:00:00.01 |    2014 |
|  19 |                NESTED LOOPS                 |                                |      1 |    478 |   243   (2)|    209 |00:00:00.01 |     883 |
|  20 |                 NESTED LOOPS                |                                |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |
|  21 |                  TABLE ACCESS BY INDEX ROWID| XXADM_COLLEGE_MASTER_TBL       |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|* 22 |                   INDEX UNIQUE SCAN         | XXADM_COLLEGES_PK              |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|  23 |                  TABLE ACCESS BY INDEX ROWID| XXADM_LOV_MASTER_TBL           |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|* 24 |                   INDEX UNIQUE SCAN         | XXADM_LOVS_PK                  |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|* 25 |                 TABLE ACCESS FULL           | XXADM_APPLICANT_COURSPREFS_TBL |      1 |    478 |   241   (2)|    209 |00:00:00.01 |     879 |
|* 26 |                TABLE ACCESS FULL            | XXADM_APPLICANT_DETAILS_TBL    |      1 |   6685 |   311   (2)|  10488 |00:00:00.01 |    1131 |
|* 27 |              TABLE ACCESS BY INDEX ROWID    | XXADM_APPLICANT_COURSPREFS_TBL |    182 |   8881 |     1   (0)|      0 |00:00:00.01 |     366 |
|* 28 |               INDEX UNIQUE SCAN             | XXADM_APPLCNT_PREF_ORDER_UK    |    182 |      1 |     0   (0)|    182 |00:00:00.01 |     184 |
|  29 |             PRECISION ROWSOURCE             | VW_SQ_1                        |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |
|  35 |            TABLE ACCESS BY INDEX ROWID      | XXADM_LOV_MASTER_TBL           |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     186 |
|* 36 |             INDEX UNIQUE SCAN               | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |
|* 37 |           INDEX UNIQUE SCAN                 | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |
|* 38 |          INDEX UNIQUE SCAN                  | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |
|  39 |         TABLE ACCESS BY INDEX ROWID         | XXADM_LOV_MASTER_TBL           |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     182 |
-----------------------------------------------------------------------------------------------------------------------------------------------------

Figure 4-1

4.5 We need to examine a plan of only 25 lines with no complicated bits (because we’ve hidden any bits that looked complicated and will get back to them later). The thing now looks like a single query block which means we can think “First Child First”, so:

  • operation 7 calls operation 8 which calls operation 9 which calls operation 10 which calls operation 11 which calls operation 12 which calls operation 13 which calls operation 14 which is the first operation to produce a rowsource (though we don’t care how at present).
  • Operation 13 is a hash join, so the rowsource from operation 14 becomes its “build” table, and we call operation 18 to supply the “probe” table.
  • Operaion 18 calls operation 19 which calls operation 20 which calls operation 21 which is a table access by rowid that has to call operation 22 to get rowids. So operation 22 supplies the second rowsource (in our collapsed plan). It’s an INDEX UNIQUE SCAN of the index that appears (judging from its name)to be the primary key index of a table, so operation 22 will produce at most one rowid that is passed up to operation 21 that will use that rowid to get the one row from the table. (Operation 21 supplies the 3rd rowsource).
  • Operation 21 passes a row up to operation 20 which calls operation 23 which calls operation 24 (4th rowsource) to do another unique scan of a unique index to get a rowid to pass up to operation 23 to find (and test) a row from the table (5th rowsource) which it passes up to operation 20 to do the join and pass the result up (6th rowsource) to operation 19.
  • Operation 19 calls its second child (operatiomn 25) for each row it receives – but because of the primary key/unique scans the optimizer knows that the first child will return at most one row and sees no problem with using a full tablescan as the second child of the nested loop. So the tablescan of XXADM_APPLICANT_COURSPREFS_TBL is the 7th rowsource. Any rows survinging the join are passed up to operation 18 (making operation 19 the 8th rowsource).
  • Operation 18 uses the incoming rowsource to build its in-memory hash table, and calls operation 26 to supply its second (probe table) rowsource. Operation 26 is the the 9th rowsource, executing a full tablescan of XXADM_APPLICANT_DETAILS_TBL and passing the results up to operation 18, which performs the join and passes the results up to its parent, making it the 10th rowsource.
  • Operation 18 was the second child of the hash join at operation 13, which now uses the incoming data as the probe table to generate the 11th rowsource and pass the results up to operation 12.
  • Operation 12 is a nested loop anti-join and operation 13 has just supplied it with its first child rowsource, so operation 12 will now call its second child once for each row in the first child. Its second child is operation 27 (table access by rowid) which calls its first child (operation 28 index range scan) which fetches rowids from the index passes them up to operation 27 which fetches table rows and passes them up to operation 12. So operation 28 supplies the 12th rowsource, operation 27 the 13th. Since operation 12 is an ANTI join a row from the first child survives if operation 27 doesn’t find a row to return. Operation 12 passes any survivors (14th rowsource) up to operation 11.
  • Operation 11 is another ANTI-join nested loop so for each row from operation 12 it will call its second child, passing in values from its first child to drive an efficient access path and forwarding any rows from the first child where the second child returns no rows. Its second child is operation 29 – our “precision rowsource” – and we can postpone worrying about the exact details of how that works. Operation 29 produces the 15th rowsource in our reduced plan, which it passes up to operation 11.
  • Operation 11 is the first child of the nested loop at operation 10 – and from this point onwards we have 4 nested loop joins and we can simply list through the order of operation. Operation 11 produces the 16th rowsource, then Operation 10 calls its second child (operation 35) which calls operation 36 which passes rowids (17th rowsource) up to operation 35 which passes rows (18th rowsource) up to operation 10.
  • Operation 10 passes its data (19th rowsource) up to operation 9 which calls operation 37 as its second child. Operation 37 (20th rowsource) passes index entries up to operation 9 which performs the join and passes the result (21st rowsource) up to operation 8.
  • Operation 8 calls operation 38 as its second child. Operation 38 (22nd rowsource) passes index entries up to operation 8 which performs the join and passes the result (23rd rowsource) up to operation 7.
  • Operation 7 calls operation 39 as its second child. Operation 39 (24th rowsource) passes index entries up to operation 7 which performs the join and that’s the final (25th) rowsource as far as our reduced execution plan is concerned.

4.6 Here’s the reduced plan, cut back to minimum width, with the order of rowsource generation included:


-----------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                           |  Order |
-----------------------------------------------------------------------------------------------
|   7 |        NESTED LOOPS                         |                                |     25 |
|   8 |         NESTED LOOPS                        |                                |     23 |
|   9 |          NESTED LOOPS                       |                                |     21 |
|  10 |           NESTED LOOPS                      |                                |     19 |
|  11 |            NESTED LOOPS ANTI                |                                |     16 |
|  12 |             NESTED LOOPS ANTI               |                                |     14 |
|* 13 |              HASH JOIN                      |                                |     11 |
|  14 |               BULK ROWSOURCE                | index$_join$_008               |      1 |
|* 18 |               HASH JOIN                     |                                |     10 |
|  19 |                NESTED LOOPS                 |                                |      8 |
|  20 |                 NESTED LOOPS                |                                |      6 |
|  21 |                  TABLE ACCESS BY INDEX ROWID| XXADM_COLLEGE_MASTER_TBL       |      3 |
|* 22 |                   INDEX UNIQUE SCAN         | XXADM_COLLEGES_PK              |      2 |
|  23 |                  TABLE ACCESS BY INDEX ROWID| XXADM_LOV_MASTER_TBL           |      5 |
|* 24 |                   INDEX UNIQUE SCAN         | XXADM_LOVS_PK                  |      4 |
|* 25 |                 TABLE ACCESS FULL           | XXADM_APPLICANT_COURSPREFS_TBL |      7 |
|* 26 |                TABLE ACCESS FULL            | XXADM_APPLICANT_DETAILS_TBL    |      9 |
|* 27 |              TABLE ACCESS BY INDEX ROWID    | XXADM_APPLICANT_COURSPREFS_TBL |     13 |
|* 28 |               INDEX UNIQUE SCAN             | XXADM_APPLCNT_PREF_ORDER_UK    |     12 |
|  29 |             PRECISION ROWSOURCE             | VW_SQ_1                        |     15 |
|  35 |            TABLE ACCESS BY INDEX ROWID      | XXADM_LOV_MASTER_TBL           |     18 |
|* 36 |             INDEX UNIQUE SCAN               | XXADM_LOVS_PK                  |     17 |
|* 37 |           INDEX UNIQUE SCAN                 | XXADM_LOVS_PK                  |     20 |
|* 38 |          INDEX UNIQUE SCAN                  | XXADM_LOVS_PK                  |     22 |
|  39 |         TABLE ACCESS BY INDEX ROWID         | XXADM_LOV_MASTER_TBL           |     24 |
-----------------------------------------------------------------------------------------------

Figure 4-2

4.7 Once we’ve got this image sorted out we still have a few details to fill in before we’ve gpt the full picture of the execution plan.

  • What does Oracle do to generate the “bulk rowsource” at operation 14
  • What does Oracle do on every call to the “precision rowsource” at operation 29
  • We know that the reduced plan above is the first child of a FILTER operation and if we refer back to previous “real main query” we know that there are three further children to the FILTER that might have to execute once for each row produced by the first child. So that’s another 3 (small) query blocks we need to examine in detail.
  • We need to bring in the predicates to see how the optimizer has used them
  • We need to look at the Starts and A-Rows to compare what happened with the optimizer’s expectation
  • We need to look at disk reads and buffer gets to see how much excess work we did to acquire the data


 

5.0 Filling the Gaps

5.1 After getting the overall shape of the query’s execution we can go back and examine the bits we have so far postponed view. There are three pieces to consider

  • the “bulk rowsource” at operation 14 that was the first child of a hash join.
  • the “precision rowsource” at operation 29 that was the second child of a nested loop anti-join
  • the filter subqueries that were the 2nd, 3rd and 4th children of the explicit FILTER at operation 6

5.2 We start with the “bulk rowsource” that was a VIEW with a highly suggestive name of index$_join$_008. This shows Oracle finding a way of selecting data from a table without visiting the table, using a couple of indexes as if they were skinny tables that could be scanned and joined. In effect Oracle has transformed “select key1, key2 from tableX” into something like:


select  ix1.key1, ix2.key2
from
        (select key1, rowid r1 from tableX) ix1,
        (select key2, rowid r2 from tableX) ix2
where
        ix1.r1 = ix2.r2
;

5.3 This strategy can only be used when Oracle knows that every relevant row will appear in the two indexes – which basically means you have to be careful about NULLs. In the simplest case you might have to have a NOT NULL constraint on at least one column in each of the target indexes; or a predicate in each inline view that ensures that Oracle can use just the index without losing some of the rowids that it needs. After acquiring key values and rowids from each index in turn, Oracle then joins the two sets of data using a hash join. Technically there is no limit to the number of indexes that Oracle can join in this fashion, the choice of strategy depends largely on how big the table is compared to the sum of the sizes of the indexes that could be used instead; practically (as in our main query) it’s rare to see more than two indexes used for this “index join” mechanism.


join index transformation query block SEL$082F290F, with parent
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|* 13 |              HASH JOIN                      |                                |      1 |      5 |   557   (2)|    182 |00:00:00.02 |    2022 |  1599K|  1599K| 1503K (0)|
|  14 |               VIEW                          | index$_join$_008               |      1 |    127 |     2   (0)|    127 |00:00:00.01 |       8 |       |       |         |
|* 15 |                HASH JOIN                    |                                |      1 |        |            |    127 |00:00:00.01 |       8 |  1368K|  1368K| 1522K (0)|
|  16 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_CODE_UK             |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |
|  17 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_PK                  |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

5.4 Moving on to the “precision rowsource” that appears in the original plan as a VIEW PUSHED PREDICATE. This means that Oracle has optimised a non-mergeable view allowing for an input value from its parent. If you take operations 30 to 34 in complete isolation it’s just a simple nested loop join and you might wonder why the view is non-mergable. But when you look back at the parent you discover that it’s an ANTI-join, so Oracle has to say (for each driving row) “join these two tables and see if you get any rows as a result”, it doesn’t have a generic mechanism for doing two separate but consecutive (anti-)join operations at this point.


Unnested subquery SEL$A75BE177 (from sel$8, sel$9) with parent and its first child
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|  11 |            NESTED LOOPS ANTI                |                                |      1 |      1 |   565   (2)|    182 |00:00:00.02 |    2752 |       |       |         |
|  12 |             Driving Rowsource               |                                |      1 |      1 |   562   (2)|    182 |00:00:00.02 |    2388 |       |       |         |
|  29 |             VIEW PUSHED PREDICATE           | VW_SQ_1                        |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |       |       |         |
|  30 |              NESTED LOOPS                   |                                |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |       |       |         |
|* 31 |               TABLE ACCESS BY INDEX ROWID   | XXADM_APPLICANT_COURSPREFS_TBL |    182 |      1 |     2   (0)|      0 |00:00:00.01 |     364 |       |       |         |
|* 32 |                INDEX UNIQUE SCAN            | XXADM_APPLCNT_PREF_ORDER_UK    |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     184 |       |       |         |
|* 33 |               TABLE ACCESS BY INDEX ROWID   | XXADM_CATEGORY_MASTER_TBL      |      0 |      1 |     1   (0)|      0 |00:00:00.01 |       0 |       |       |         |
|* 34 |                INDEX UNIQUE SCAN            | XXADM_CATEGORY_PK              |      0 |      1 |     0   (0)|      0 |00:00:00.01 |       0 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

5.5 Finally we have the three filter subqueries, which I’ve shown with their parent FILTER and a one-liner for the driving rowsource. For each row in operation 7 we call operations 40, 42 and 44 in turn although the parent filter may decide after calling operation 40 that it doesn’t need to call the other two and can simply move on to the next row from operation 7. Similarly the filter might call operations 40 and 42 and not need to call operation 44. It’s also possible that, thanks to scalar subquery caching, Oracle can say “I’ve already called operation 40 for this value, so I know the result and don’t need to call it again”. When we look at the Starts and A-Rows columns for the three operations we will get some idea of how the “notional” execution sequence turned into an actual workload.


Filter subqueries SEL$5, SEL$6 and SEL$F665FE1B, with their parent and its first child
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name                           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|*  6 |       FILTER                                |                                |      1 |        |            |    171 |00:00:00.02 |    3822 |       |       |         |
|   7 |        "Simplify" Plan                      |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    3128 |       |       |         |
|* 40 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    182 |      1 |     3   (0)|     29 |00:00:00.01 |     507 |       |       |         |
|* 41 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_PREFS_UK         |    182 |      5 |     2   (0)|   1450 |00:00:00.01 |     191 |       |       |         |
|  42 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    171 |      1 |     2   (0)|      0 |00:00:00.01 |     173 |       |       |         |
|* 43 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_APPLICANT_STATUS |    171 |      1 |     1   (0)|      0 |00:00:00.01 |     173 |       |       |         |
|* 44 |        VIEW                                 | index$_join$_014               |      6 |      1 |     0   (0)|      0 |00:00:00.01 |      14 |       |       |         |
|* 45 |         HASH JOIN                           |                                |      6 |        |            |      0 |00:00:00.01 |      14 |  1519K|  1519K|  666K (0)|
|* 46 |          INDEX RANGE SCAN                   | XXADM_CATEGORY_PK              |      6 |      1 |     0   (0)|      6 |00:00:00.01 |       6 |       |       |         |
|  47 |          INLIST ITERATOR                    |                                |      6 |        |            |     12 |00:00:00.01 |       8 |       |       |         |
|* 48 |           INDEX UNIQUE SCAN                 | XXADM_CATEGORY_CODE_UK         |     12 |      1 |     0   (0)|     12 |00:00:00.01 |       8 |       |       |         |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

5.6 The operations for the first two subqueries (40,41) and (42,43) shouldn’t need any explanation. The third set of operations (44 to 48) is a little more complex. In fact it’s similar to the mechanism that appeared in our first “bulk rowsource” – we have Oracle turning a table access into an “index (only) join”, collecting key values and rowids from two different indexes and doing a hash join on the rowids. The plan is just a little more subtle, though – instead of getting their data from “index fast full scans”, one index is accessed by a range scan (possibly using a predicate value passed in by the filter operation) and the other is using an INLIST ITERATOR with unique scan. In the second case we must be handling a predicate of the form: “key_column in {list of values}”. (Taking a first look at the Starts column we can see that the INLIST ITERATOR runs 6 times calling the INDEX UNIQUE SCAN a total of 12 times so it’s fairly obvious that there are exactly two elements in the list in this case.)


 

6.0 Looking at the Numbers

6.1 Rather than walking through the entire plan again putting the pieces together I’m going to assume that I can carry on to the next stage of analysis, and assume that the pieces will fall into place as we talk about some of the critical numbers.

6.2 It’s probably best to open a copy of the note in a separate window so that you can examine the plan and read my comments at the same time. Starting at the top we apply “first child first”, noting in passing that the examples of the VIEW operations we have in this plan don’t have a significicant impact on the order of operation as we work down the plan – they simply remind us that there are “non-mergeable” parts of the plan. Eventually we get to operation 13 which is a hash join and operations 14 through 17 give us the build (first) table – a quick check shows 127 rows for estimated (E-rows) and actual (A-rows); then we see the probe (second) table is itself a hash join returning 182 rows (estimate 487, so in the right ballpark) and the hash join at operation 13 produces a result set of 182 rows.

6.3 At this point a quick check back UP the plan tells us that the 182 rows survive all the way up to operation 6, where a FILTER eliminates just a few of them; then the result set drops to just 2 rows at operation 5. Then a quick check of operation 5 (with a cross reference to the query) reminds us that we have an inline view that does an “order by” followed by a “rownum < :bind” predicate – so the sort order by stop key at operation 5 is sorting all the data but only passing on the first two rows: so there’s no way we could have modified the join order to eliminate the redundant rows sooner.

6.4 So we see that we get the right volume of data at about the right moment in the plan, and probably can’t do much to avoid the volume of data access – but let’s check how we got the 182 rows at operation 18. Using “first child first” – we see a nested loop joining two “single row by unique index” rowsources, then a nested loop to a full tablescan of XXADM_APPLICANT_COURSPREFS_TBL. The knee-jerk reaction to a “full tablescan as the second table in a nested loop” is that it must be bad – but in this case we know that it will happen no more than once, so we don’t need to panic immediately. Applying a little more thought (and arithmetic): we note that the tablescan returns 209 rows (estimated 478) using 879 buffer gets; that’s not an extreme number of buffer gets per row (especially if the 478 is a reasonably accurate average for the operation). We’ll postpone worrying about the tablescan for the moment but take note that it might be worth revisiting.

6.5 The second child to the hash join at operation 18 is another full tablescan (of XXADM_APPLICANT_DETAILS_TBL) which requires 1,141 buffer gets. Again, though this might not be a bad move, since the alternative would be an actual 209 index probes (or an estimated average 478 index probes). The workload is, again, in the right ballpark but, again, something we might come back to. In fact in both tablescans it might be more important to worry about the work done at each row by the row predicates rather than worrying about the fact that the operation is a tablescan; a predicate involving a CPU-intensive PL/SQL function might be the thing that makes 478 index probes to 478 rows a better option than a tablescan of (say) 100,000 rows.

6.6 From this point onwards (operation 13) we have 6 nested loop joins (though the first two are anti-joins) so it’s “call the second child for each row in the first child” all the way down, and we’ve seen that we don’t eliminate any data as we go. If we want to make the execution plan any faster by “local” tweaking we’ll just have to make sure each “second child” operation is as efficient as possible, which tends to mean looking for cases where we supply a lots of rows (rowids) from an index range scan but find that we then discard the table rows after visiting the table. So …

6.6.1 Operation 12 – nested loop anti join – calls 28/27 (table access by unique index). We find an index entry on each call, but the table row doesn’t qualify – which is what we want for a “successful” anti-join. We could make this a little more efficient by adding a column to the (already unique) index and avoid visiting the table.

6.6.2 Operation 11 – nested loop anti join – calls operation 29 (view with pushed predicate) which operates a high-precision nested loop join at operation 30. The first child of operation 30 is a table access by index, but the table never returns a row (which is nice) so we never call the second child of operation 30. Again we could make this view access a little more efficient by adding extra columns to (already unique) indexes to avoid any need to visit tables.

6.6.3 Operations 10, 9, 8, 7 – nested loop joins – operating very efficiently, some not even visiting tables to acquire data. The order of operation at this point is: 11, 36, 35, 10, 37, 9. 38, 8, 19, 7. And then we get to the FILTER operation, which has 3 subqueries to operate in turn,

6.7 Operation 6 executes in turn the two subqueries we named sel$5 and sel$6 respectively, the first one 182 times, the second one 171 times. Since operation 6 produces 171 rows it seems likely that the initial 182 rows dropped to 171 rows as a consequence of the sel$5 subquery resulting in the smaller number of calls to the sel$6 subquery. It’s worth noting here that operation 41, the index range scan, returned 1,450 rowids, but the subsequent table accesses returned a total of only 29 rows after an additional 316 buffer gets (507 – 191). There may be an opportunity here (yet again) for adding an extra column to the index so that we visit the table only 29 times rather than visiting it 1,450 times. In fact, though it’s not obvious in this SQL Monitor report, the indications from other examples from the same query suggested that this subquery was the single more resource intensive part of the plan.

6.8 The last subquery executed by Operation 6 is the one identified by query block sel$4. The sub-plan starts with a VIEW operation because the table (identified as originating in sel$4 in the query block / object alias information) is “accessed” by way of an index hash join. This subquery is executed only 6 times. Given that there are (at least) 171 rows for which this subquery could be called this means one of two things.  First we can from the query text that this subquery is part of a complex CASE expression – so maybe the simple conditions in the expression mean we rarely need to call the subquery;. secondly it could mean the run-time engine has managed to take advantage of scalar subquery caching and the query doesn’t have many distinct inputs for this subquery – and when we check the predicate section we can see the relevant predicate for a query against the XXADM_CATEGORY table was “category_id = {correlation variable}” which has the look of a table with a few rows and distinct ids..

6.9 In summary, then, there may be a few “localised” tweaks that we can to do improve performance of this plan – largely by adding columns to existing indexes and using them effectively. There are indications that one of the filter subqueries might be a particularly good target for this type of tweak; after which we might want to look at what could be done with the two tablescans which are in that grey area where it’s not easy to decide whether an indexed access or a tablescan is the better option. We have to remember, though, that this query was originaly reported as executing 842,000 times – so maybe we need to do much better than just a little tweaking.


 

7.0 Predicate Information

7.1 Why are we running a query 842,000 times in a batch? The right way to find an answer to that question is to ask the right person – if you can find them. A slightly more long winded way is to find out what is driving the 842,000 executions – and you might be able to do that if you have the full tkprof output from the trace file. (Hint: statement X runs 842,000 times, and if statement Y executes 13 times and produces 842,000 rows maybe Y is driving X.) Sometimes, though, you don’t have the people, or the full data set, or the access you need, so you might take a look at the query and the predicates and start making some reasonable guesses.

7.2 Here’s the tail end of the query, conveniently capturing all the input bind variables:

                AND     cmt.college_id = :p_college_id
                AND     crmt.course_id = :p_course_id
                AND     mmt.medium_id  = :p_medium_id
                AND     act.hostel_required = :p_hostel_required
                ORDER BY
                        order_of_pass,
                        course_qe_priority,
                        percentage DESC,
                        applicant_dob,
                        legacy_appln_date
                ) 
        WHERE
                 ROWNUM <=  :p_seats
        ) 
WHERE 
        applicant_id = :p_applicant_id

7.3 There are two things we can note about these predicates – first they don’t follow the pattern of “:Bnnn” so they’re not from a statement statically embedded in PL/SQL, secondly the names are intelligible and meaningful, so we might draw some tentative conclusions from them, in particular how many distinct values there might be and how this lead to 842,000 executions of the query.

7.4 The variable name that stands out is :p_applicant_id. We seem to be looking at a query about applicants for courses at colleges – and the latter pair probably gives us a relatively small number of combinations. The variable :p_hostel_required is surely just going to be a “yes/no/maybe/null” option. The :p_medium_id is a bit of a puzzle but scanning through the query it looks like it’s the id for the “medium of study” so probably another variable with a small number of values. So where in the plan do these variables get used? Here’s the full list of predicates from the plan, followed by an extra few lines showing just the predicates that reference the bind variables:

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("APPLICANT_ID"=:P_APPLICANT_ID)
   3 - filter(ROWNUM<=:P_SEATS) -- > comment added to avoid wordpress format issue
   5 - filter(ROWNUM<=:P_SEATS) -- > comment added to avoid wordpress format issue
   6 - filter((    "ACT"."PREFERENCE_ORDER"<=NVL(,"ACT"."PREFERENCE_ORDER") -- > comment added to avoid wordpress format issue
               AND "ACT"."PREFERENCE_ORDER">=NVL(,"ACT"."PREFERENCE_ORDER") AND CASE "ACT"."HOSTEL_REQUIRED"
              WHEN 'Y' THEN CASE  WHEN ("ADT"."DISTANCE_IN_KMS">20 AND "LMT_RELIGION"."LOV_CODE"='HINDU' AND  IS NULL) THEN 1 ELSE 2 END  ELSE 1 END =1))
  13 - access("ADT"."APPLICANT_GENDER"="LMT_GENDER"."LOV_ID")
       filter(CASE "ACT"."HOSTEL_REQUIRED" WHEN 'Y' THEN CASE  WHEN ("LMT_EDUCATION_TYPE"."LOV_CODE"='COEDUCOL' AND "LMT_GENDER"."LOV_CODE"='FEMALE') THEN 2 ELSE 1 END
               ELSE 1 END =1)
  15 - access(ROWID=ROWID)
  18 - access("ADT"."APPLICANT_ID"="ACT"."APPLICANT_ID")
  22 - access("CMT"."COLLEGE_ID"=:P_COLLEGE_ID)
  24 - access("CMT"."EDUCATION_TYPE"="LMT_EDUCATION_TYPE"."LOV_ID")
  25 - filter(("ACT"."COURSE_ID"=:P_COURSE_ID AND "ACT"."COLLEGE_ID"=:P_COLLEGE_ID AND "ACT"."MEDIUM_ID"=:P_MEDIUM_ID AND "ACT"."HOSTEL_REQUIRED"=:P_HOSTEL_REQUIRED))
  26 - filter(("ADT"."STATUS"='Active' AND "ADT"."COURSE_APPLIED_FOR"='DEG' AND (INTERNAL_FUNCTION("ADT"."COLLEGE_STATUS_FLAG") OR "ADT"."COLLEGE_STATUS_FLAG" IS
              NULL)))
  27 - filter("ACT3"."STATUS_FLAG"='O')
  28 - access("ACT3"."APPLICANT_ID"="ADT"."APPLICANT_ID" AND "ACT"."PREFERENCE_ORDER"="ACT3"."PREFERENCE_ORDER")
  31 - filter((INTERNAL_FUNCTION("ACT1"."STATUS_FLAG") AND NVL("ACT1"."ATTRIBUTE7",'N')='N'))
  32 - access("ACT1"."APPLICANT_ID"="ADT"."APPLICANT_ID" AND "ACT1"."PREFERENCE_ORDER"="ACT"."PREFERENCE_ORDER")
  33 - filter("CATEGORY_CODE"='OPENMERT')
  34 - access("CATEGORY_ID"=TO_NUMBER("ACT1"."ATTRIBUTE1"))
  36 - access("ADT"."PASS_TYPE"="LMT_PASS"."LOV_ID")
  37 - access("ADT"."APPEARANCE_TYPE"="LMT_APPEARANCE"."LOV_ID")
  38 - access("ADT"."RELIGION"="LMT_RELIGION"."LOV_ID")
  40 - filter(("STATUS_FLAG"='B' OR "STATUS_FLAG"='C' OR "STATUS_FLAG"='O' OR "STATUS_FLAG"='T'))
  41 - access("ACT1"."APPLICANT_ID"=:B1)
  43 - access("ACT2"."APPLICANT_ID"=:B1 AND "STATUS_FLAG"='C')
  44 - filter(("CATEGORY_ID"=:B1 AND INTERNAL_FUNCTION("CATEGORY_CODE")))
  45 - access(ROWID=ROWID)
  46 - access("CATEGORY_ID"=:B1)
  48 - access(("CATEGORY_CODE"='BACKWRDC' OR "CATEGORY_CODE"='BACKWRDE'))


   2 - filter("APPLICANT_ID"=:P_APPLICANT_ID)
   3 - filter(ROWNUM<=:P_SEATS) -- > comment added to avoid wordpress format issue
   5 - filter(ROWNUM<=:P_SEATS) -- > comment added to avoid wordpress format issue
  22 - access("CMT"."COLLEGE_ID"=:P_COLLEGE_ID)
  25 - filter(("ACT"."COURSE_ID"=:P_COURSE_ID AND "ACT"."COLLEGE_ID"=:P_COLLEGE_ID AND "ACT"."MEDIUM_ID"=:P_MEDIUM_ID AND "ACT"."HOSTEL_REQUIRED"=:P_HOSTEL_REQUIRED))

Figure 7-1

7.5 Apart from the predicates in the final shortlist you probably noticed further bind variables at operation 41, 43, 44, and 46 – but these are all named :B1, which is Oracle flagging up the need to pass correlating values into filter subqueries.

7.6 Operation 25 (where we test almost all the predicates) is one of the first operations to drive the query while operation 2 (where we test the :p_applicant_id) is close to the very last thing we do in the execution plan. So we generate a load of data for a college, course, and couple of other predicates, sort it then – at the last moment – decide that we only want a few (:p_seats) rows and count how many rows we’ve found for the specific applicant – and we do that a very large number of times. This takes me back to section 2 where I asked a couple of critical questions:

7.6.1 (2.3) First, how far into the view V_THING will the optimizer be able to push that predicate, possibly the entire content of the view will have to be constructed before the predicate can apply,

7.6.2 (2.8) I’m also a little curious about a requirement that seems to say – “pick at most N rows, then tell me how many you’ve picked”. What’s it actually trying to do and why?

7.7 We now know the answer to the first query – that predicate isn’t going anywhere, and we recognise why not, of course: it’s a consequence of the “rownum” pseudo-column which has to be evaluated for all the generated data before the rownum restriction can be applied: “select for the applicant then apply the rownum” is very different from “apply the rownum (column and predicate) then restrict to the applicant”. That brings us to the second question – why would you generate all the data, then order it, then restrict it to the first N, and then count how many times a specific applicant appeared? And there’s one “valid” answer to the last bit of the question – what if you’re not really trying to count how many times the applicant appeared, you’re only trying to find out whether the count is zero or non-zero.

7.8 The intent of the query is to answer the question: “does this applicant appear in the first N candidates”. Once you’ve realised this the underlying performance problem with the query becomes clear. In the monitored example show here the query found 171 applicants that matched the initial predicates – and at some point in the batch it’s going to do the same work all over again for each of the other 170 applicants that we’ve discarded. For each combination of the initial predicates (excluding applicant id and seat count) we run the query N times if there are N candidates that match that combination. It’s bad enough that this query took 0.02 seconds to run and would have run 172 times (for a total of 3,4 seconds) but another sample run took 0.05 seconds to run identifying 1,835 applicants (which means another 1,834 executions for a total of 91 seconds run time).


 

8.0 Resolution

8.1 There is a serious flaw in the design of this application. We are seeing a piece of code running once per applicant_id when (with some minor variations) it looks as if it should be running no more than once per set of distinct combinations of (course_id, college_id, medium_id, hostel_required). In fact, if the set of distinct combinations could be generated by a simple query, you could imagine the entire required result set as a join between two non-mergable views, with a little row-by-row post processing – but that might be too ambitious a change to implement in the short term.

8.2 Realistically (as a low-risk strategy) it might be possible to keep a very large percentage of the existing code structure for whatever this task does but precede it with a PL/SQL loop that steps through each of the distinct combinations required, populating a table (perhaps an IOT) with {applicant_id, (combination columns), “rownum”); and then replace our problem query by a simple primary key look up to find the saved “rownum” for each applicant and combination, to check whether the stored “rownum” was fell within the required seat count.


 

9.0 Summaryi

9.1 For a DBA working on-site, or a consultant on a short-term visit, the analysis shown in this post is probably not how things would have progressed. I could imagine the sequence of events being more like:

9.1.1 This “start of year / start of term” batch job takes too long

9.1.2 What is it trying to achieve (business overview) – sketch an outline of the process (technical overview)

9.1.3 Trace the job and discover most of the time went on this query

9.1.4 Investigate the logic of this query and why it is run for every applicant_id

9.1.5 Recognise the fundamental design threat then choose between three possible strategies:

9.1.5.1 make the query much faster

9.1.5.2 re-engineer this part of the batch completely

9.1.5.3 subvert this section of the batch to pre-build a “materialized result” and use a much simpler query to query it

9.2 Effectively, however, we’ve come in at 9.1.5.1 and run the consultation backwards. As we did so we raised an early question about the applicant_id and pushing predicates and the oddity of counting a limited list, and we finally came back to those points towards the end of the post with an educated guess about what the query was trying to achieve and how it should be reduced from “once per applicant” to “once per combination”.

9.3 Despite the post starting at the wrong place it’s quite possible that we would have reached 9.1.5.1 by following a sensible order of problem analysis, and still want to think about how the query might run more quickly – so this investigation wasn’t a total waste of time and it’s allowed us to work through a real-world query and plan in a realistic way which we can sum up in the following stages:

9.3.1 Simplify: cross-referencing between the overall plan shape, the Query Block / Object Alias information, and original query we can take out sections of the plan (sub-plans) and analyse them separately. In particular we can identify and reduce to a minimum the core of the plan that generates the final result set, calling on the various sub-plans as it goes.

9.3.2 Follow the workload: in this case we didn’t get much help from the timing information, but buffer gets, disk reads and A-rows also supply clues to where most work is done. Critically we noted that the volume of data we picked up early on in the query was needed all the way through the query – until the last moment – and we didn’t waste resources carrying and processing unnecessary rows. Along the way, of course, we compare Oracle’s predictions of data volume with the actual data volume (A-Rows = Starts * E-Rows as a guideline). We also noted a couple of opportunities where modifying indexes might eliminate table visits, potentially reducing I/O and buffer gets.

9.3.3 Check the predicates: which goes hand in hand with following the workload – how and where are our predicates used. What predicates have been generated (or eliminated) by transitive closure; which predicates are (or could be) pushed further down the plan tree to eliminate data earlier; will multiple predicates result in bad optimizer estimates followed by bad choices for access paths.

9.4 It would be nice to think that there was a simple progression, a fixed sequence of steps that one could follow to interpret an execution plan quickly and accurately. Unfortunately (like the optimisation process itself) interpretation requires a measure of looping and recursion. It’s probably always best to start with simplifying – but how much you simplify and how you pick which subplan to simplify (or start analysing in detail) depends on being able to spot where the biggest workload appears; and before you get stuck too deeply into a sub-plan you might glance down at the use of predicates because a change in one predicate might make the optimizer completely re-engineer its choice of plan. And maybe, before anything else, you’ll see a single operation which you know should (for exanple) generate about 10 rows when the optimizer is predicting 25,000 rows (or vice versa) and you’ll want to check why there’s such a bad estimate at one point in the plan before you tackle any of the harder questions.

9.5 The bottom line with execution plans is simply this: the more you practice the faster you get at spotting the clues that are worth pursuing; and the faster you spot the clues the less time you waste unpicking every little detail, and the less time you spend on the preripheral pieces of the plan the easier it becomes to keep the big picture in your mind and see how the optimizer got to where it is, and how you might want to redirect it. So pick a couple of random queries each week that produce plans of about 20 lines and use them to exercise your interpretation skills; and increase the complexity of the queries every couple of weeks.

The End

 
 

 

Footnote

I think I’ve spent more than 20 hours writing a detailed description of something that would normally take me a few minutes to do [and some poeple wonder why I’ve not yet written another book on the optimizer]. In part the difference in time is because with practice the “intuitive” skill grows and the pattern of reading is more like –

  • How does it start (ignoring the “trivial” bits around the edges)
  • How does it end ( ditto )
  • Where do we do most work reading and discarding data

What I’ve done in this note is talk about every single query block and every single line whereas in real-life I might have scanned the plan, examined about 10 lines, and done a quick check on the corresponding predicates as the first step to deciding whether or not the plan was reasonably efficient.

Oracle 19c: Automatic Indexing. Part 2. Testing Automatic Indexing with Swingbench

This is the second of a two-part post that looks at the Automatic Indexing feature introduced in Oracle 19c.
I have used Dominic Giles' Swingbench utility to create a realistic and repeatable OLTP load test using the Sales Order Entry (SOE) benchmark.  This post explains how I set up and ran the test, and what results I obtained.

Installation & Setup of Swingbench

I have tested Automatic Indexing on an Exadata X4 running Oracle 19.3.1.0.0, and I have used the results from that system in this blog.  I have also successfully tested it on 19.6 and 20.2 running in Oracle VirtualBox VMs (built with Frits Hoogland's vagrant-builder) and have enabled Exadata features by setting _exadata_feature_on = TRUE.  Of course, I could never recommend setting this on anything other than a play database, but it does show the feature could work on any database platform.

#eeeeee; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; width: 95%;">alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;

Swingbench requires a Java 8 in a Java virtual machine.

#eeeeee; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; width: 95%;">yum install java

Then, it is simply a matter of downloading and unzipping the distribution.

#eeeeee; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; width: 95%;">curl http://www.dominicgiles.com/swingbench/swingbench261082.zip -o swingbench.zip
unzip swingbench.zip

To assist with monitoring the test and capturing SQL and metrics, I set the AWR snapshot frequency to 15 minutes.

#eeeeee; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; width: 95%;">execute dbms_workload_repository.modify_snapshot_settings(interval => 15);

I have created a dedicated tablespace for the SOE schema

#eeeeee; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; width: 95%;">CREATE TABLESPACE SOE DATAFILE SIZE 10M AUTOEXTEND ON NEXT 1M;

The SOE schema is built with the oewizard utility. I am creating all the indexes, and not using any partitioning.

#eeeeee; border: 0px solid rgb(0, 0, 0); font-family: "courier new"; line-height: 95%; overflow: auto; width: 95%;">cd ~/swingbench/bin
./oewizard -cs //enkx4c02-scan/swingbench_dmk -dt thin -dba "sys as sysdba" -dbap welcome1 -ts SOE -u soe -p soe -create -allindexes -nopart -cl -v

Test 1: Baseline Test

The Swingbench SOE benchmark has 9 tables with 27 indexes. 15 of those indexes are on primary key or referential integrity constraints.

#eeeeee; border: 0px solid #000000; font-family: courier new; font-size: 90%; line-height: 95%; overflow: auto; width: 95%;">Table                      Index                                     Cons
Owner TABLE_NAME Owner INDEX_NAME UNIQUENES Type STATUS VISIBILIT AUT INDEX_KEYS
----- -------------------- ----- ------------------------- --------- ---- -------- --------- --- ----------------------------
SOE ADDRESSES SOE ADDRESS_CUST_IX NONUNIQUE R VALID VISIBLE NO CUSTOMER_ID
SOE ADDRESS_PK UNIQUE P VALID VISIBLE NO ADDRESS_ID

SOE CARD_DETAILS SOE CARDDETAILS_CUST_IX NONUNIQUE VALID VISIBLE NO CUSTOMER_ID
SOE CARD_DETAILS_PK UNIQUE P VALID VISIBLE NO CARD_ID

SOE CUSTOMERS SOE CUST_EMAIL_IX NONUNIQUE VALID VISIBLE NO CUST_EMAIL
SOE CUSTOMERS_PK UNIQUE P VALID VISIBLE NO CUSTOMER_ID
SOE CUST_FUNC_LOWER_NAME_IX NONUNIQUE VALID VISIBLE NO SYS_NC00017$,SYS_NC00018$
SOE CUST_DOB_IX NONUNIQUE VALID VISIBLE NO DOB
SOE CUST_ACCOUNT_MANAGER_IX NONUNIQUE VALID VISIBLE NO ACCOUNT_MGR_ID

SOE INVENTORIES SOE INV_WAREHOUSE_IX NONUNIQUE R VALID VISIBLE NO WAREHOUSE_ID
SOE INV_PRODUCT_IX NONUNIQUE R VALID VISIBLE NO PRODUCT_ID
SOE INVENTORY_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID,WAREHOUSE_ID

SOE ORDERS SOE ORD_WAREHOUSE_IX NONUNIQUE VALID VISIBLE NO WAREHOUSE_ID,ORDER_STATUS
SOE ORDER_PK UNIQUE P VALID VISIBLE NO ORDER_ID
SOE ORD_SALES_REP_IX NONUNIQUE VALID VISIBLE NO SALES_REP_ID
SOE ORD_CUSTOMER_IX NONUNIQUE R VALID VISIBLE NO CUSTOMER_ID
SOE ORD_ORDER_DATE_IX NONUNIQUE VALID VISIBLE NO ORDER_DATE

SOE ORDER_ITEMS SOE ITEM_ORDER_IX NONUNIQUE R VALID VISIBLE NO ORDER_ID
SOE ITEM_PRODUCT_IX NONUNIQUE R VALID VISIBLE NO PRODUCT_ID
SOE ORDER_ITEMS_PK UNIQUE P VALID VISIBLE NO ORDER_ID,LINE_ITEM_ID

SOE PRODUCT_DESCRIPTIONS SOE PRD_DESC_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID,LANGUAGE_ID
SOE PROD_NAME_IX NONUNIQUE VALID VISIBLE NO TRANSLATED_NAME

SOE PRODUCT_INFORMATION SOE PROD_SUPPLIER_IX NONUNIQUE VALID VISIBLE NO SUPPLIER_ID
SOE PRODUCT_INFORMATION_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID
SOE PROD_CATEGORY_IX NONUNIQUE VALID VISIBLE NO CATEGORY_ID

SOE WAREHOUSES SOE WAREHOUSES_PK UNIQUE P VALID VISIBLE NO WAREHOUSE_ID
SOE WHS_LOCATION_IX NONUNIQUE VALID VISIBLE NO LOCATION_ID

At this stage, Automatic Indexing is off. If you rebuild the SOE schema having previously run Automatic Indexing, remember to disable the feature, otherwise, it might act on the basis of previous activity. It is administered via the DBMS_AUTO_INDEX package.

#eeeeee; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; width: 95%;">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');

I ran Swingbench using the character mode charbench front end.  Each test runs for an hour.

#eeeeee; border: 0px solid rgb(0, 0, 0); font-family: "courier new"; line-height: 95%; overflow: auto; width: 95%;">./charbench -c ../configs/SOE_Client_Side.xml -cs //enkx4c02-scan/swingbench_dmk -dt thin -u soe -p soe -rt 01:00 -v

Author : Dominic Giles
Version : 2.6.0.1082

Results will be written to results.xml.
Hit Return to Terminate Run...

Time Users TPM TPS

12:54:54 PM 0 58500 869
Completed Run.

The results are written to an XML file, from which a formatted report can be produced using Result2Pdf. I run this on Windows.

#eeeeee; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; width: 95%;">>results2pdf -c results00001.xml

>java -cp ../launcher LauncherBootstrap -executablename results2pdf results2pdf -c results.xml
Application : Results2Pdf
Author : Dominic Giles
Version : 2.6.0.1076
Success : Pdf file null was created from results.xml results file.

The report gives average response times for the 9 different transactions and an overall average number of transactions per second.

Results

This test is my baseline.
#BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1248;">
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Transaction
#9CC2E5 1.5pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: solid #BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-alt: solid #9CC2E5 1.5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 153; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Average Response (ms)
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1: Delivered Indexes
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Update Customer Details
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1.18
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Browse Products
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.03
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Browse Orders
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.38
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Customer Registration
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.50
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Order Products
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.67
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Warehouse Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
6.20
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Process Orders
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
13.42
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Warehouse Activity Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
14.89
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Sales Rep Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
31.76
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TPS
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1060.81

Test 2: Drop Secondary Indexes

In many applications, developers and DBAs add indexes to resolve performance problems. It is easy to add indexes, but harder to know whether and where they are used, and therefore when it is safe to remove or change an existing index. Indexes have an overhead in terms of taking up space in the database and maintenance during DML operations.
Automatic indexing is designed to take on this challenge. Oracle has provided a procedure to drop secondary indexes, DBMS_AUTO_INDEX.DROP_SECONDARY_INDEXES.

DROP_SECONDARY_INDEXES doesn't check the status of the constraint. Foreign key columns should be indexed to avoid TM locking when updating or deleting the parent record in a primary key. The index would not be needed if the foreign key constraint was not validated. You might make a constraint disabled, not validated, but reliable because you want to take advantage of foreign key join elimination. In this case, the index would not be necessary, but it would not be dropped by this procedure.

#eeeeee; font-family: courier new; font-size: 100%; line-height: 95%; overflow: auto; width: 95%;">EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('SOE','');

When this is run on the SOE schema, I am left with 15 indexes that are either unique or on foreign key columns.

#eeeeee; line-height: 95%; overflow: auto; width: 95%;">Table                      Index                                     Cons
Owner TABLE_NAME Owner INDEX_NAME UNIQUENES Type STATUS VISIBILIT AUT INDEX_KEYS
----- -------------------- ----- ------------------------- --------- ---- -------- --------- --- ----------------------------
SOE ADDRESSES SOE ADDRESS_PK UNIQUE P VALID VISIBLE NO ADDRESS_ID
SOE ADDRESS_CUST_IX NONUNIQUE R VALID VISIBLE NO CUSTOMER_ID

SOE CARD_DETAILS SOE CARD_DETAILS_PK UNIQUE P VALID VISIBLE NO CARD_ID

SOE CUSTOMERS SOE CUSTOMERS_PK UNIQUE P VALID VISIBLE NO CUSTOMER_ID

SOE INVENTORIES SOE INV_PRODUCT_IX NONUNIQUE R VALID VISIBLE NO PRODUCT_ID
SOE INV_WAREHOUSE_IX NONUNIQUE R VALID VISIBLE NO WAREHOUSE_ID
SOE INVENTORY_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID,WAREHOUSE_ID

SOE ORDERS SOE ORD_CUSTOMER_IX NONUNIQUE R VALID VISIBLE NO CUSTOMER_ID
SOE ORDER_PK UNIQUE P VALID VISIBLE NO ORDER_ID

SOE ORDER_ITEMS SOE ITEM_PRODUCT_IX NONUNIQUE R VALID VISIBLE NO PRODUCT_ID
SOE ORDER_ITEMS_PK UNIQUE P VALID VISIBLE NO ORDER_ID,LINE_ITEM_ID
SOE ITEM_ORDER_IX NONUNIQUE R VALID VISIBLE NO ORDER_ID

SOE PRODUCT_DESCRIPTIONS SOE PRD_DESC_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID,LANGUAGE_ID

SOE PRODUCT_INFORMATION SOE PRODUCT_INFORMATION_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID

SOE WAREHOUSES SOE WAREHOUSES_PK UNIQUE P VALID VISIBLE NO WAREHOUSE_ID

Results

Unsurprisingly, the effect on Swingbench is to severely degrade performance.

#BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1248;">
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Transaction
#9CC2E5 1.5pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: solid #BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-alt: solid #9CC2E5 1.5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 153; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Average Response (ms)
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1: Delivered Indexes
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2: Drop Secondary Indexes
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Update Customer Details
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1.18
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.30
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Browse Products
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.03
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
409.21
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Browse Orders
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.38
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.05
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Customer Registration
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.50
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
78.51
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Order Products
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.67
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
40.97
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Warehouse Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
6.20
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.82
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Process Orders
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
13.42
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
247.80
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Warehouse Activity Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
14.89
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
274.19
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Sales Rep Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
31.76
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
268.51
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TPS
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1060.81
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
81.30

Enabling Automatic Indexing

There are several configuration settings that are made via the DBMS_AUTO_INDEX.CONFIGURE procedure.

  • I have created a tablespace AUTO_INDEXES_TS and configured Automatic Indexing to create its indexes there.  It is permitted to use 100% of that tablespace.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">CREATE TABLESPACE AUTO_INDEXES_TS DATAFILE SIZE 10M AUTOEXTEND ON NEXT 1M;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET','100');
  • Automatic indexes will be retained until they have not been used for 1 day (the default was 373 days).  This unrealistically low value is so that I can test that they will be dropped later.
  • Manual indexes, the ones created when Swingbench was installed, are not deleted.  
  • The automatic indexing logs, visible in the various DBA_AUTO_INDEX% views, are retained for 7 days.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO','7');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL','');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION','7');
  • Automatic indexing is configured only to apply to the SOE schema.
#eeeeee; border: 0px solid rgb(0, 0, 0); font-family: "courier new"; line-height: 95%; overflow: auto; width: 95%;">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SOE', allow => TRUE);
  • Finally, I enable Automatic Indexing and permit it to create indexes.
#eeeeee; border: 0px solid rgb(0, 0, 0); font-family: "courier new"; line-height: 95%; overflow: auto; width: 95%;">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

You can validate the current parameters by querying DBA_AUTO_INDEX_CONFIG.  This view is based on smb$config.  There are other hidden and undocumented parameters visible in smb$config.

#eeeeee; line-height: 95%; overflow: auto; width: 95%;">                                  Auto Index Config
Modified
PARAMETER_NAME PARAMETER_VALUE LAST_MODIFIED By
-------------------------------- ------------------------------ ----------------------------- ----------
AUTO_INDEX_COMPRESSION OFF 27-MAR-20 07.42.36.000000 AM SYSTEM
AUTO_INDEX_DEFAULT_TABLESPACE AUTO_INDEXES_TS 27-MAR-20 10.28.24.000000 AM SYSTEM
AUTO_INDEX_MODE IMPLEMENT 27-MAR-20 10.28.24.000000 AM SYSTEM
AUTO_INDEX_REPORT_RETENTION 7 27-MAR-20 10.28.24.000000 AM SYSTEM
AUTO_INDEX_RETENTION_FOR_AUTO 7 27-MAR-20 10.28.24.000000 AM SYSTEM
AUTO_INDEX_RETENTION_FOR_MANUAL 27-MAR-20 10.28.24.000000 AM SYSTEM
AUTO_INDEX_SCHEMA schema IN (SOE) 27-MAR-20 10.28.24.000000 AM SYSTEM
AUTO_INDEX_SPACE_BUDGET 100 27-MAR-20 10.28.24.000000 AM SYSTEM

DBA_AUTOTASK_SCHEDULE_CONTROL shows the two scheduled automatic tasks that form Automatic Indexing.  The Auto Index Task runs when Automatic Indexing is enabled in either implement or report only mode. The Auto SQL Tuning Set (STS) Capture Task runs from when Automatic Indexing is first enabled, but it is not stopped when Automatic Indexing is disabled. Both jobs run every 15 minutes.

#eeeeee; border: 0px solid rgb(0, 0, 0); font-family: "courier new"; line-height: 95%; overflow: auto; width: 95%;">           Task                                                      Max Run       Elapsed
DBID ID TASK_NAME STATUS INTERVAL Time ENABL Time LAST_SCHEDULE_TIME
---------- ---- -------------------------------- ---------- -------- ------- ----- ------- --------------------------------
1400798553 3 Auto Index Task SUCCEEDED 900 3600 TRUE 3 17-MAR-20 03.18.26.997 PM -05:00
1400798553 5 Auto STS Capture Task SUCCEEDED 900 900 TRUE 0 17-MAR-20 03.17.31.051 PM -05:00

Test 3: Creating Automatic Indexes

When I ran Swingbench again the poor performance continued until halfway through the test when Automatic Indexing decided to create some indexes and make them visible. There was a step improvement in performance, although it was nowhere near the 1000 TPS that we started with!

At the end of the test, there are 5 new indexes, 3 of which are visible, 2 are invisible.

#eeeeee; line-height: 95%; overflow: auto; width: 95%;">Table                      Index                                     Cons
Owner TABLE_NAME Owner INDEX_NAME UNIQUENES Type STATUS VISIBILIT AUT INDEX_KEYS
----- -------------------- ----- ------------------------- --------- ---- ------------ --------- --- -------------------------------------------------
SOE ADDRESSES SOE ADDRESS_CUST_IX NONUNIQUE R VALID VISIBLE NO CUSTOMER_ID
SOE ADDRESS_PK UNIQUE P VALID VISIBLE NO ADDRESS_ID

SOE CARD_DETAILS SOE CARD_DETAILS_PK UNIQUE P VALID VISIBLE NO CARD_ID
SOE SYS_AI_dt4w4vr174j9m NONUNIQUE VALID VISIBLE YES CUSTOMER_ID <-reinstated secondary

SOE CUSTOMERS SOE CUSTOMERS_PK UNIQUE P VALID VISIBLE NO CUSTOMER_ID

SOE INVENTORIES SOE INVENTORY_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID,WAREHOUSE_ID
SOE INV_PRODUCT_IX NONUNIQUE R VALID VISIBLE NO PRODUCT_ID
SOE INV_WAREHOUSE_IX NONUNIQUE R VALID VISIBLE NO WAREHOUSE_ID

SOE ORDERS SOE ORDER_PK UNIQUE P VALID VISIBLE NO ORDER_ID
SOE ORD_CUSTOMER_IX NONUNIQUE R VALID VISIBLE NO CUSTOMER_ID
SOE SYS_AI_3z00frhp9vd91 NONUNIQUE VALID VISIBLE YES WAREHOUSE_ID <-original also order_status
SOE SYS_AI_gbwwy984mc1ft NONUNIQUE VALID VISIBLE YES SALES_REP_ID <-reinstated secondary

SOE ORDER_ITEMS SOE ORDER_ITEMS_PK UNIQUE P VALID VISIBLE NO ORDER_ID,LINE_ITEM_ID
SOE ITEM_PRODUCT_IX NONUNIQUE R VALID VISIBLE NO PRODUCT_ID
SOE ITEM_ORDER_IX NONUNIQUE R VALID VISIBLE NO ORDER_ID

SOE PRODUCT_DESCRIPTIONS SOE PRD_DESC_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID,LANGUAGE_ID
SOE SYS_AI_20tjdcuwznyhx NONUNIQUE VALID INVISIBLE YES PRODUCT_ID <-redundant

SOE PRODUCT_INFORMATION SOE PRODUCT_INFORMATION_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID
SOE SYS_AI_b9k5zyq0mjwf5 NONUNIQUE VALID INVISIBLE YES CATEGORY_ID <-reinstated invisible secondary

SOE WAREHOUSES SOE WAREHOUSES_PK UNIQUE P VALID VISIBLE NO WAREHOUSE_ID <-reinstated redundant reinstated original
The names of the indexes are determined by applying the SYS_OP_COMBINED_HASH function to the table owner, table name, and indexed column list.

The various dictionary views reveal some of what has happened.

Note that my invisible indexes are usable.  Automatic Indexes start out as unusable and invisible.  Here Automatic Indexes has rebuilt them as usable, but they are still invisible because they do not reduce logical I/O.  So, I am still bearing the overhead of maintaining them during DML.
DBA_AUTO_INDEX_STATISTICS reports a summary of the automatic indexing task.  Confirming the number of indexes built. 

#eeeeee; line-height: 95%; overflow: auto; width: 95%;">Tue Mar 17                                                                          page    1
Auto Index Statistics

EXECUTION_NAME STAT_NAME VALUE
-------------------------- ----------------------------- ----------
SYS_AI_2020-03-17/15:48:28 Space used in bytes 129105920
SQL plan baselines created 2
Index candidates 5
Indexes created (visible) 3
Indexes created (invisible) 2
Improvement percentage 88.92
SQL statements verified 10
SQL statements improved 4
SQL statements managed by SPM 2
DBA_AUTO_INDEX_SQL_ACTIONS shows the commands issued to build the tuning set SYS_AUTO_STS SQL.  Automatic indexing only uses this one tuning set and keeps adding statements to it.  Even if I drop and recreate the SOE schema the SQL Tuning set remains.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">Tue Mar 17                                                                                                                             page    1
Auto Index SQL Actions

SQL Plan
EXECUTION_NAME ACTION_ID SQL_ID Hash Value COMMAND
-------------------------- ---------- ------------- ---------- ------------------------------
STATEMENT START_TIME END_TIME ERROR#
-------------------------------------------------------------------------------- ------------------- ------------------- ----------
SYS_AI_2020-03-17/15:48:28 12 dy8cxyd3mv1as 2679498789 DISALLOW AUTO INDEX FOR SQL
declare 15:50:01 17.03.2020 15:50:02 17.03.2020 0
load_cnt pls_integer;
begin
load_cnt := dbms_spm_internal.load_plans_from_sqlset('SYS_AUTO_STS','S
YS','sql_id = ''dy8cxyd3mv1as''','NO','YES',1000,FALSE,'SYS',FALSE,TRUE); end;

SYS_AI_2020-03-17/15:48:28 11 dunt7pwuax92s 1878158884 DISALLOW AUTO INDEX FOR SQL
declare 15:50:01 17.03.2020 15:50:01 17.03.2020 0
load_cnt pls_integer;
begin
load_cnt := dbms_spm_internal.load_plans_from_sqlset('SYS_AUTO_STS','S
YS','sql_id = ''dunt7pwuax92s''','NO','YES',1000,FALSE,'SYS',FALSE,TRUE); end;

Initially, the automatic indexes are created unusable and invisible.  Later, the indexes will recreated as usable and invisible is they are judged to be beneficial.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">Fri Mar 20                                                                                                                                   page    1
Auto Index Indexing Actions

Action Index Table
EXECUTION_NAME ID INDEX_NAME Owner TABLE_NAME Owner COMMAND
-------------------------- ------ ------------------------- ----- -------------------- ----- ------------------------------
STATEMENT START_TIME END_TIME Error#
-------------------------------------------------------------------------------- ------------------- ------------------- ------
SYS_AI_2020-03-20/13:56:03 5 SYS_AI_3z00frhp9vd91 SOE ORDERS SOE CREATE INDEX
CREATE INDEX "SOE"."SYS_AI_3z00frhp9vd91" ON "SOE"."ORDERS"("WAREHOUSE_ID") TA 13:56:08 20.03.2020 13:56:08 20.03.2020 0
BLESPACE "AUTO_INDEXES_TS" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED LOW ONLINE

SYS_AI_2020-03-20/13:56:03 6 SYS_AI_20tjdcuwznyhx SOE PRODUCT_DESCRIPTIONS CREATE INDEX
CREATE INDEX "SOE"."SYS_AI_20tjdcuwznyhx" ON "SOE"."PRODUCT_DESCRIPTIONS"("PRO 13:56:08 20.03.2020 13:56:08 20.03.2020 0
DUCT_ID") TABLESPACE "AUTO_INDEXES_TS" UNUSABLE INVISIBLE AUTO COMPRESS ADVANCED
LOW ONLINE
DBA_AUTO_INDEX_VERIFICATIONS reports on the tests that were made on statements before and after the index changes were made.  You can see some have improved and some have regressed.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">Tue Mar 17                                                                                        page    1
Auto Index Verifications

Original Auto Index Original Auto Index
EXECUTION_NAME SQL_ID Plan Hash Plan Hash Buffer Gets Buffer Gets STATUS
-------------------------- ------------- ---------- ---------- ----------- ----------- ------------
SYS_AI_2020-03-17/15:48:28 0sh0fn7r21020 3619984409 3900469033 37784 130 IMPROVED
SYS_AI_2020-03-17/16:18:29 3900469033 3900469033 1316 135 UNCHANGED

SYS_AI_2020-03-17/15:48:28 200mw76ta6n1r 2844209861 2671811931 37769 3555 IMPROVED
SYS_AI_2020-03-17/16:18:29 2671811931 2671811931 3278 3596 UNCHANGED

SYS_AI_2020-03-17/15:48:28 28tr1bjf4t2uh 2692802960 3836151239 37764 3238 IMPROVED
SYS_AI_2020-03-17/16:18:29 3836151239 3836151239 3272 3442 UNCHANGED

SYS_AI_2020-03-17/15:48:28 9dt3dqym1tqzw 3954032495 1068597273 46 4 UNCHANGED

SYS_AI_2020-03-17/15:48:28 a90pbxt8zukdr 1513149408 3900469033 67 1 UNCHANGED

SYS_AI_2020-03-17/15:48:28 amaapqt3p9qd0 2597291669 1494990609 14645 23 IMPROVED
SYS_AI_2020-03-17/16:18:29 1494990609 1494990609 3 23 UNCHANGED

SYS_AI_2020-03-17/15:48:28 b4p66t3uznnuc 3551246360 463531433 4038 4406 UNCHANGED

SYS_AI_2020-03-17/15:48:28 dunt7pwuax92s 1878158884 2671811931 13 2965 REGRESSED

SYS_AI_2020-03-17/15:48:28 dy8cxyd3mv1as 2679498789 2126884530 155 298 REGRESSED

SYS_AI_2020-03-17/15:48:28 g1znkya370htg 3571181773 896069541 74 42 UNCHANGED

This testing mechanism generally prevents Automatic Indexing from creating indexes that are not used.  However, Richard Foote has found an exception where the number of buffer gets goes down, but the optimizer cost goes up.
The decision by the Tuning Advisor to propose the index is determined by optimizer cost, the decision to use a valid visible index is also determined by optimizer cost.  I think it is slightly incongruous that the decision whether to make a candidate index visible and therefore available to the application, is determined by logical I/O, CPU consumption, and elapsed time but not at all optimiser cost.

Results

The entirety of this test was run with the automatically created indexes in place.
#BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1248;">
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Transaction
#9CC2E5 1.5pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: solid #BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-alt: solid #9CC2E5 1.5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 153; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Average Response (ms)
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1: Delivered Indexes
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2: Drop Secondary Indexes
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3: Automatic Indexing
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Update Customer Details
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1.18
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.30
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.32
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Browse Products
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.03
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
409.21
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
478.52
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Browse Orders
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.38
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.05
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.01
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Customer Registration
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.50
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
78.51
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.91
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Order Products
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.67
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
40.97
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
50.34
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Warehouse Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
6.20
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.82
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.85
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Process Orders
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
13.42
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
247.80
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.39
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Warehouse Activity Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
14.89
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
274.19
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
11.43
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Sales Rep Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
31.76
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
268.51
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
14.45
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TPS
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1060.81
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
81.30
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
137.40

Comparison with No Secondary Indexes

I have used the execution statistics in DBA_HIST_SQLSTAT for statements captured by AWR during each test and compared the execution plans and average elapsed time for each.
Where the plans change, they do change for the better, so Automatic Indexing is doing its job
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">                          Average                                                              Average       %    %Num
Test SQL Plan Opt. Num Elapsed Elapsed Test SQL Plan Opt. Num Elapsed Elapsed Time Execs
ID SQL_ID Hash Value Cost Execs Time Time ? ID SQL_ID Hash Value Cost Execs Time Time Diff Diff
---- ------------- ---------- -------- -------- --------- -------- - ---- ------------- ---------- -------- -------- --------- -------- ------- -------
2 g9wsbkb2jag3j 1005345217 229 15028 6108.13 .4064 = 3 g9wsbkb2jag3j 1005345217 229 31523 15046.99 .4773 17 110
2 34mt4skacwwwd 235854103 73 7547 295.26 .0391 = 3 34mt4skacwwwd 235854103 73 15601 766.31 .0491 26 107
2 g1znkya370htg 3571181773 45 224885 59.99 .0003 = 3 g1znkya370htg 3571181773 45 470063 111.48 .0002 -11 109
2 djj5txv2dzwb6 3241608609 1 263982 38.19 .0001 = 3 djj5txv2dzwb6 3241608609 1 550563 77.90 .0001 -2 109
2 09pzy8x10gjkg 0 1 139639 24.96 .0002 = 3 09pzy8x10gjkg 0 1 292179 53.20 .0002 2 109
2 200mw76ta6n1r 2844209861 10151 1514 405.73 .2680 ! 3 200mw76ta6n1r 2671811931 3257 3268 46.67 .0143 -95 116
2 a6hdpzrqqhc7d 0 1 70858 26.01 .0004 = 3 a6hdpzrqqhc7d 0 1 148211 41.21 .0003 -24 109
2 28tr1bjf4t2uh 2692802960 10140 1575 430.72 .2735 ! 3 28tr1bjf4t2uh 3836151239 3245 3118 35.58 .0114 -96 98
2 982zxphp8ht6c 1666523684 2 407633 14.10 .0000 = 3 982zxphp8ht6c 1666523684 2 849104 30.01 .0000 2 108
2 csasr8ct2051v 900611645 3 263976 13.77 .0001 = 3 csasr8ct2051v 900611645 3 550572 29.06 .0001 1 109
2 0sh0fn7r21020 3619984409 15124 3019 747.00 .2474 ! 3 0sh0fn7r21020 3900469033 4695 5030 25.60 .0051 -98 67
2 0sh0fn7r21020 3619984409 15124 3019 747.00 .2474 ! 3 0sh0fn7r21020 2629004565 14875 1208 6.04 .0050 -98 -60
2 5g00dq4fxwnsw 2141863993 3 95832 7.13 .0001 = 3 5g00dq4fxwnsw 2141863993 3 292176 21.40 .0001 -2 205
2 2yp5w5a36s5xv 1628223527 3 48610 5.50 .0001 = 3 2yp5w5a36s5xv 1628223527 3 148215 12.84 .0001 -23 205
2 4a7nqf7k0ztyc 0 1 30356 6.03 .0002 = 3 4a7nqf7k0ztyc 0 1 63339 12.33 .0002 -2 109
2 49d9qhgsr8w9h 0 1 20825 3.40 .0002 = 3 49d9qhgsr8w9h 0 1 63339 10.44 .0002 1 204
2 8uk8bquk453q8 3072215225 2 48612 5.61 .0001 = 3 8uk8bquk453q8 3072215225 2 134571 8.51 .0001 -45 177
2 cr72yp489p3jw 0 1 20824 2.57 .0001 = 3 cr72yp489p3jw 0 1 44297 6.97 .0002 27 113
2 g3kf1ppky3627 2480532011 8 67021 3.00 .0000 = 3 g3kf1ppky3627 2480532011 6 143326 6.57 .0000 2 114
2 0t61wk161zz87 1544532951 2 20823 2.26 .0001 = 3 0t61wk161zz87 1544532951 2 13799 1.64 .0001 9 -34
2 amaapqt3p9qd0 2597291669 4276 75096 5348.00 .0712 ! 3 amaapqt3p9qd0 1494990609 7 34857 1.40 .0000 -100 -54
2 8xqdxjkbt9ghg 0 1 5681 1.93 .0003 = 3 8xqdxjkbt9ghg 0 1 4129 1.34 .0003 -4 -27
2 6k3uuf3g8pwh6 1628223527 3 5167 1.43 .0003 = 3 6k3uuf3g8pwh6 1628223527 3 3527 1.13 .0003 16 -32
2 a9cv97h3dazfh 1197098199 3 11144 1.48 .0001 = 3 a9cv97h3dazfh 1197098199 3 7665 1.09 .0001 7 -31
2 0c11vprf4881w 856749079 6 11370 .85 .0001 = 3 0c11vprf4881w 856749079 7 10487 .85 .0001 9 -8
2 3rxkss61q68su 1322380957 5 4821 .31 .0001 = 3 3rxkss61q68su 1322380957 5 9281 .64 .0001 8 93
2 9v9ky32fg9hy7 104664550 2 4140 .61 .0001 = 3 9v9ky32fg9hy7 104664550 2 4121 .55 .0001 -11 -0
2 4abyshv6jmtdk 140963536 123 15 .05 .0036 = 3 4abyshv6jmtdk 140963536 123 20 .08 .0039 9 33

Comparison with Delivered Indexes

However, if we compare the delivered indexes against just the primary indexes and those created by Automatic Indexing, a number of statements have degraded, one particularly severely.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">                                                  Average                                                              Average       %    %Num
Test SQL Plan Opt. Num Elapsed Elapsed Test SQL Plan Opt. Num Elapsed Elapsed Time Execs
ID SQL_ID Hash Value Cost Execs Time Time ? ID SQL_ID Hash Value Cost Execs Time Time Diff Diff
---- ------------- ---------- -------- -------- --------- -------- - ---- ------------- ---------- -------- -------- --------- -------- ------- -------
1 g9wsbkb2jag3j 574689976 5 148925 9.33 .0001 ! 3 g9wsbkb2jag3j 1005345217 229 31523 15046.99 .4773 761882 -79
1 34mt4skacwwwd 235854103 74 90568 2884.16 .0318 = 3 34mt4skacwwwd 235854103 73 15601 766.31 .0491 54 -83
1 g1znkya370htg 124060720 26 2725529 331.81 .0001 ! 3 g1znkya370htg 3571181773 45 470063 111.48 .0002 95 -83
1 djj5txv2dzwb6 3241608609 1 3179667 435.37 .0001 = 3 djj5txv2dzwb6 3241608609 1 550563 77.90 .0001 3 -83
1 09pzy8x10gjkg 0 1 1687520 285.05 .0002 = 3 09pzy8x10gjkg 0 1 292179 53.20 .0002 8 -83
1 200mw76ta6n1r 1448083145 1437 18129 367.09 .0202 ! 3 200mw76ta6n1r 2671811931 3257 3268 46.67 .0143 -29 -82
1 a6hdpzrqqhc7d 0 1 857616 244.55 .0003 = 3 a6hdpzrqqhc7d 0 1 148211 41.21 .0003 -2 -83
1 28tr1bjf4t2uh 2220165490 1425 17921 167.57 .0094 ! 3 28tr1bjf4t2uh 3836151239 3245 3118 35.58 .0114 22 -83
1 982zxphp8ht6c 1666523684 2 4903566 171.31 .0000 = 3 982zxphp8ht6c 1666523684 2 849104 30.01 .0000 1 -83
1 csasr8ct2051v 900611645 3 3179610 159.11 .0001 = 3 csasr8ct2051v 900611645 3 550572 29.06 .0001 5 -83
1 0sh0fn7r21020 1055577880 1258 36654 175.46 .0048 ! 3 0sh0fn7r21020 3900469033 4695 5030 25.60 .0051 6 -86
1 5g00dq4fxwnsw 2141863993 3 1687532 120.78 .0001 = 3 5g00dq4fxwnsw 2141863993 3 292176 21.40 .0001 2 -83
1 2yp5w5a36s5xv 1628223527 3 857624 114.81 .0001 = 3 2yp5w5a36s5xv 1628223527 3 148215 12.84 .0001 -35 -83
1 4a7nqf7k0ztyc 0 1 363873 109.76 .0003 = 3 4a7nqf7k0ztyc 0 1 63339 12.33 .0002 -35 -83
1 49d9qhgsr8w9h 0 1 363871 55.61 .0002 = 3 49d9qhgsr8w9h 0 1 63339 10.44 .0002 8 -83
1 8uk8bquk453q8 3072215225 2 857622 51.75 .0001 = 3 8uk8bquk453q8 3072215225 2 134571 8.51 .0001 5 -84
1 cr72yp489p3jw 0 1 363878 52.63 .0001 = 3 cr72yp489p3jw 0 1 44297 6.97 .0002 9 -88
1 g3kf1ppky3627 2480532011 8 1180857 51.46 .0000 = 3 g3kf1ppky3627 2480532011 6 143326 6.57 .0000 5 -88
1 0sh0fn7r21020 1055577880 1258 36654 175.46 .0048 ! 3 0sh0fn7r21020 2629004565 14875 1208 6.04 .0050 4 -97
1 0t61wk161zz87 1544532951 2 363871 37.74 .0001 = 3 0t61wk161zz87 1544532951 2 13799 1.64 .0001 14 -96
1 amaapqt3p9qd0 3722429161 8 908901 32.04 .0000 ! 3 amaapqt3p9qd0 1494990609 7 34857 1.40 .0000 14 -96
1 8xqdxjkbt9ghg 0 1 69829 14.61 .0002 = 3 8xqdxjkbt9ghg 0 1 4129 1.34 .0003 56 -94
1 6k3uuf3g8pwh6 1628223527 3 90569 28.00 .0003 = 3 6k3uuf3g8pwh6 1628223527 3 3527 1.13 .0003 4 -96
1 a9cv97h3dazfh 1197098199 3 147637 18.88 .0001 = 3 a9cv97h3dazfh 1197098199 3 7665 1.09 .0001 11 -95
1 0c11vprf4881w 856749079 8 223512 15.24 .0001 = 3 0c11vprf4881w 856749079 7 10487 .85 .0001 19 -95
1 3rxkss61q68su 1322380957 5 176508 11.20 .0001 = 3 3rxkss61q68su 1322380957 5 9281 .64 .0001 9 -95
1 9v9ky32fg9hy7 104664550 2 43191 2.69 .0001 = 3 9v9ky32fg9hy7 104664550 2 4121 .55 .0001 113 -90
1 4h624tuydrjnh 3828985807 3 62578 4.69 .0001 = 3 4h624tuydrjnh 3828985807 3 4131 .46 .0001 50 -93
1 95hgbb2kkcvvg 3419397814 12934 1 4.09 4.0858 !
1 3gs4005kgkhxu 296924608 6423 1 4.05 4.0539 !

Test 4: Manual Tuning

Then I looked at whether I could get back to the original performance by manually tuning the top SQL statements rather than reinstating all the indexes that I had dropped.  I found I needed to create just four more indexes.  
The first two are reinstated indexes that were originally part of the SOE schema but were dropped as secondary indexes.  
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">CREATE INDEX SOE.CUST_FUNC_LOWER_NAME_IX 
ON SOE.CUSTOMERS (LOWER(CUST_LAST_NAME), LOWER(CUST_FIRST_NAME))
TABLESPACE SOE PARALLEL 8
/
CREATE INDEX SOE.PROD_CATEGORY_IX ON SOE.PRODUCT_INFORMATION (CATEGORY_ID)
TABLESPACE SOE PARALLEL 8
/
The other two are new indexes that were not originally present.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">CREATE INDEX SOE.DMK_ORDER_STATUS ON SOE.ORDERS (ORDER_STATUS) 
TABLESPACE SOE PARALLEL 8
/
CREATE INDEX SOE.DMK_WAREHOUSE_ORDER_DATE ON SOE.ORDERS (WAREHOUSE_ID, ORDER_DATE)
TABLESPACE SOE PARALLEL 8
/

Results

I now have 22 visible indexes instead of the original 27, and the performance is better than with the delivered indexes.

#BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-padding-alt: 0cm 5.4pt 0cm 5.4pt; mso-yfti-tbllook: 1248;">
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Transaction
#9CC2E5 1.5pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: solid #BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-alt: solid #9CC2E5 1.5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 153; mso-border-bottom-themetint: 153; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Average Response (ms)
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1: Delivered Indexes
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2: Drop Secondary Indexes
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3: Automatic Indexing
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
4: Manual Tuning
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Update Customer Details
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1.18
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.30
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.32
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.51
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Browse Products
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.03
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
409.21
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
478.52
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1.93
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Browse Orders
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.38
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.05
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.01
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.12
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Customer Registration
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.50
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
78.51
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.91
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.92
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Order Products
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.67
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
40.97
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
50.34
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1.99
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Warehouse Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
6.20
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.82
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
2.85
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.00
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Process Orders
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
13.42
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
247.80
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
5.39
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
4.95
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Warehouse Activity Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
14.89
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
274.19
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
11.43
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
20.29
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
Sales Rep Query
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
31.76
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
268.51
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
14.45
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: solid #BDD6EE .5pt; mso-border-top-themecolor: accent5; mso-border-top-themetint: 102; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
3.74
#BDD6EE 1.0pt; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-themecolor: accent5; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
TPS
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1060.81
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
81.30
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
137.40
#BDD6EE 1.0pt; border-left: none; border-right: solid #BDD6EE 1.0pt; border-top: none; height: 1.0pt; mso-border-alt: solid #BDD6EE .5pt; mso-border-bottom-themecolor: accent5; mso-border-bottom-themetint: 102; mso-border-left-alt: solid #BDD6EE .5pt; mso-border-left-themecolor: accent5; mso-border-left-themetint: 102; mso-border-right-themecolor: accent5; mso-border-right-themetint: 102; mso-border-themecolor: accent5; mso-border-themetint: 102; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-alt: double #9CC2E5 .75pt; mso-border-top-themecolor: accent5; mso-border-top-themecolor: accent5; mso-border-top-themetint: 153; mso-border-top-themetint: 153; padding: 0cm 5.4pt 0cm 5.4pt;" valign="top">
1166.49

Comparison with Delivered Indexes

We can see from the SQL statistics comparison that most of the original plans have been reinstated, and elsewhere there are both improvements and regressions.

#eeeeee; line-height: 95%; overflow: auto; width: 95%;">                                                           Average                                                              Average       %    %Num
Test SQL Plan Opt. Num Elapsed Elapsed Test SQL Plan Opt. Num Elapsed Elapsed Time Execs
ID SQL_ID Hash Value Cost Execs Time Time ? ID SQL_ID Hash Value Cost Execs Time Time Diff Diff
---- ------------- ---------- -------- -------- --------- -------- - ---- ------------- ---------- -------- -------- --------- -------- ------- -------
1 djj5txv2dzwb6 3241608609 1 3179667 435.37 .0001 = 4 djj5txv2dzwb6 3241608609 1 3787684 533.89 .0001 3 19
1 g1znkya370htg 124060720 26 2725529 331.81 .0001 ! 4 g1znkya370htg 684158979 19 3250699 491.48 .0002 24 19
1 28tr1bjf4t2uh 2220165490 1425 17921 167.57 .0094 ! 4 28tr1bjf4t2uh 3836151239 6155 21756 435.75 .0200 114 21
1 09pzy8x10gjkg 0 1 1687520 285.05 .0002 = 4 09pzy8x10gjkg 0 1 2011130 357.96 .0002 5 19
1 a6hdpzrqqhc7d 0 1 857616 244.55 .0003 = 4 a6hdpzrqqhc7d 0 1 1021001 304.66 .0003 5 19
1 982zxphp8ht6c 1666523684 2 4903566 171.31 .0000 = 4 982zxphp8ht6c 1666523684 2 5846476 215.40 .0000 5 19
1 csasr8ct2051v 900611645 3 3179610 159.11 .0001 = 4 csasr8ct2051v 900611645 3 3787526 197.96 .0001 4 19
1 0sh0fn7r21020 1055577880 1258 36654 175.46 .0048 ! 4 0sh0fn7r21020 3900469033 11026 43379 195.10 .0045 -6 18
1 5g00dq4fxwnsw 2141863993 3 1687532 120.78 .0001 = 4 5g00dq4fxwnsw 2141863993 3 2011090 148.96 .0001 3 19
1 2yp5w5a36s5xv 1628223527 3 857624 114.81 .0001 = 4 2yp5w5a36s5xv 1628223527 3 1020995 115.62 .0001 -15 19
1 4a7nqf7k0ztyc 0 1 363873 109.76 .0003 = 4 4a7nqf7k0ztyc 0 1 432444 95.85 .0002 -27 19
1 200mw76ta6n1r 1448083145 1437 18129 367.09 .0202 ! 4 200mw76ta6n1r 437111724 371 21657 72.86 .0034 -83 19
1 49d9qhgsr8w9h 0 1 363871 55.61 .0002 = 4 49d9qhgsr8w9h 0 1 432448 67.47 .0002 2 19
1 g3kf1ppky3627 2480532011 8 1180857 51.46 .0000 = 4 g3kf1ppky3627 2480532011 6 1406867 67.09 .0000 9 19
1 cr72yp489p3jw 0 1 363878 52.63 .0001 = 4 cr72yp489p3jw 0 1 432449 64.74 .0001 4 19
1 8uk8bquk453q8 3072215225 2 857622 51.75 .0001 = 4 8uk8bquk453q8 3072215225 2 1020941 63.69 .0001 3 19
1 34mt4skacwwwd 235854103 74 90568 2884.16 .0318 ! 4 34mt4skacwwwd 1567979920 74 108274 48.63 .0004 -99 20
1 0t61wk161zz87 1544532951 2 363871 37.74 .0001 = 4 0t61wk161zz87 1544532951 2 432449 46.49 .0001 4 19
1 8xqdxjkbt9ghg 0 1 69829 14.61 .0002 = 4 8xqdxjkbt9ghg 0 1 195205 41.44 .0002 1 180
1 amaapqt3p9qd0 3722429161 8 908901 32.04 .0000 ! 4 amaapqt3p9qd0 1494990609 5 1082090 39.24 .0000 3 19
1 a9cv97h3dazfh 1197098199 3 147637 18.88 .0001 = 4 a9cv97h3dazfh 1197098199 3 269481 35.83 .0001 4 83
1 3rxkss61q68su 1322380957 5 176508 11.20 .0001 = 4 3rxkss61q68su 1322380957 5 293179 32.62 .0001 75 66
1 6k3uuf3g8pwh6 1628223527 3 90569 28.00 .0003 = 4 6k3uuf3g8pwh6 1628223527 3 98133 20.24 .0002 -33 8
1 0c11vprf4881w 856749079 8 223512 15.24 .0001 = 4 0c11vprf4881w 856749079 6 213021 17.96 .0001 24 -5
1 g9wsbkb2jag3j 574689976 5 148925 9.33 .0001 = 4 g9wsbkb2jag3j 574689976 7 54410 4.41 .0001 29 -63

Test 5: Managing Manual Indexing

Finally, in this test, I started with all the delivered SOE indexes and have configured Automatic Indexing to consider dropping both automatic and manual indexes that have not been used for an hour (the default is 373 days, I have set this absurdly low value just to demonstrate the behaviour of this feature).  Initially, Automatic Indexing is running in report only mode when I started Swingbench running.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_DEFAULT_TABLESPACE','AUTO_INDEXES_TS');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SPACE_BUDGET','100');

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_COMPRESSION','OFF');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_AUTO','.041666');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_RETENTION_FOR_MANUAL','.041666');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_REPORT_RETENTION','1');
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SOE', allow => TRUE);

EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','REPORT ONLY');
After half an hour I switched to 'implement' mode.
#eeeeee; line-height: 95%; overflow: auto; width: 95%;">EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');

Very quickly (because I had previously run this test and the statements were already in the SQL Tuning set) I was left with just 17 indexes.

#eeeeee; line-height: 95%; overflow: auto; width: 95%;">Table                      Index                                     Cons
Owner TABLE_NAME Owner INDEX_NAME UNIQUENES Type STATUS VISIBILIT AUT INDEX_KEYS
----- -------------------- ----- ------------------------- --------- ---- ------------ --------- --- -------------------------------------------------
SOE ADDRESSES SOE ADDRESS_CUST_IX NONUNIQUE R VALID VISIBLE NO CUSTOMER_ID
SOE ADDRESS_PK UNIQUE P VALID VISIBLE NO ADDRESS_ID

SOE CARD_DETAILS SOE CARDDETAILS_CUST_IX NONUNIQUE VALID VISIBLE NO CUSTOMER_ID
SOE CARD_DETAILS_PK UNIQUE P VALID VISIBLE NO CARD_ID

SOE CUSTOMERS SOE CUSTOMERS_PK UNIQUE P VALID VISIBLE NO CUSTOMER_ID
SOE CUST_FUNC_LOWER_NAME_IX NONUNIQUE VALID VISIBLE NO SYS_NC00017$,SYS_NC00018$

SOE INVENTORIES SOE INVENTORY_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID,WAREHOUSE_ID

SOE ORDERS SOE ORDER_PK UNIQUE P VALID VISIBLE NO ORDER_ID
SOE ORD_CUSTOMER_IX NONUNIQUE R VALID VISIBLE NO CUSTOMER_ID
SOE ORD_SALES_REP_IX NONUNIQUE VALID VISIBLE NO SALES_REP_ID
SOE ORD_WAREHOUSE_IX NONUNIQUE VALID VISIBLE NO WAREHOUSE_ID,ORDER_STATUS

SOE ORDER_ITEMS SOE ITEM_ORDER_IX NONUNIQUE R VALID VISIBLE NO ORDER_ID
SOE ORDER_ITEMS_PK UNIQUE P VALID VISIBLE NO ORDER_ID,LINE_ITEM_ID

SOE PRODUCT_DESCRIPTIONS SOE PRD_DESC_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID,LANGUAGE_ID

SOE PRODUCT_INFORMATION SOE PRODUCT_INFORMATION_PK UNIQUE P VALID VISIBLE NO PRODUCT_ID
SOE PROD_CATEGORY_IX NONUNIQUE VALID VISIBLE NO CATEGORY_ID

SOE WAREHOUSES SOE WAREHOUSES_PK UNIQUE P VALID VISIBLE NO WAREHOUSE_ID

17 rows selected.

The automatic indexing actions only report actions on automatic indexes.  It does not report decisions to drop or not drop manual indexes.  I only know the indexes have gone because I manually compared with the initial set of indexes.
It has left 5 secondary indexes, but it has removed 3 of the 6 indexes on foreign keys that DROP_SECONDARY_INDEXES left intact.
We can see from the performance chart that there is a significant drop in the performance of the test after about 30 minutes when the automatic indexing job dropped the indexes.

Conclusion

Automatic Indexing does what it claims, but I think it doesn't go far enough when it comes to identifying new indexes.  In particular, it did not recreate the function-based index (on the lower-case customer names) that makes the most significant difference in performance to Swingbench.
Oracle makes bold claims for improvements in performance via automatically created indexes.  However, my experience across the SOE benchmark as a whole was that I saw only modest performance gains relative to the point where I dropped the secondary indexes.  The performance of the SQL statements that made use of the automatic indexes certainly did improve, and significantly.  Automatic Indexing generally doesn't create indexes that are not used, but Richard Foote has shown that there are exceptions where the number of buffer gets goes down but the optimizer cost goes up.
As Tim Hall says, you have to be 'particularly brave' to DROP_SECONDARY_INDEXES.  My experience was that doing so significantly degraded performance, and then Automatic Indexing did not fully mitigate that.  You will be left trying to work out which indexes you have to put back yourself.
In the current release, I think allowing Automatic Indexing to remove manual indexes would be extremely dangerous.  You wouldn't know when manual indexes, including those on foreign keys, were removed and again you could be left dealing with performance issues.  If, as you should, you use foreign keys to enforce referential integrity you could get TM locking issues.
I think the SOE benchmark is a fair test of Automatic Indexing.  My manual tuning, that not only restored original performance but improved upon it, was not significantly different to anything I have seen on a typical ERP or other OLTP systems.  It was limited to adding indexes, and I still ended up with fewer indexes.
It is possible to rebuild, coalesce or shrink automatic indexes, however, you cannot drop or otherwise alter them.  Although you can rebuild an index in another tablespace and then drop the tablespace.

Procedure DROP_AUTO_INDEXES in DBMS_AUTO_INDEX is not documented and does not currently work (in 19.3-20.2).  I think it would be very difficult to let Automatic Indexing do some of the work and then do some manual tuning alongside it.  You would just get in each other's way. The activity reports and the index verification information may be a useful source of information during manual tuning, but that is using the feature as another tuning advisor.  Automatic Indexing is clearly intended to be an autonomous feature.  Either you turn it on and let it do its thing, or not.
Added 6.5.2020: Richard Foote has blogged on this point since I first wrote this article:

To be fair this is the initial release (though testing on 20c on a Virtual Machine produced the same behaviour), and like other Oracle database features before it, it will mature with time.  However, at the moment, I think we are a long way from being able to just turn it on and walk away.

AWS Aurora vs. RDS PostgreSQL on frequent commits

This post is the second part of https://blog.dbi-services.com/aws-aurora-xactsync-batch-commit/ where I’ve run row-by-row inserts on AWS Aurora with different size of intermediate commit. Without surprise the commit-each-row anti-pattern has a negative effect on performance. And I mentioned that this is even worse in Aurora where the session process sends directly the WAL to the network storage and waits, at commit, that it is acknowledged by at least 4 out of the 6 replicas. An Aurora specific wait event is sampled on these waits: XactSync. At the end of the post I have added some CloudWatch statistics about the same running in RDS but with the EBS-based PostgreSQL rather than the Aurora engine. The storage is then an EBS volume mounted on the EC2 instance. Here is the result, in this post, with a Single AZ storage, and a second run where the volume is synchronized in Multi-AZ.

TL;DR: I have put all that in a small graph showing the elapsed time for all runs from the previous and current post. Please keep in mind that this is not a benchmark. Just one test with one specific workload. I’ve put everything to reproduce it below. Remember that the procedure my_insert(1e7,1e2) inserts 10 million rows with a commit every 100 rows.

https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/Aurora-... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/Aurora-... 1024w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/Aurora-... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/Aurora-... 1536w" sizes="(max-width: 1848px) 100vw, 1848px" />

Before explaining in the results here the detail. I put it quickly as it is the same as in the last post: the Aurora PostgreSQL engine has the same SQL API so I changed only the endpoint in the $db environment variable.

RDS PostgreSQL

I have created a database on RDS with the real PostgreSQL the same as I did in the previous post on Aurora:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/rds-pg-... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/rds-pg-... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/rds-pg-... 1536w" sizes="(max-width: 1024px) 100vw, 1024px" />
In order to run on the same EC2 shape, I selected the “Memory Optimized classes” db.r5large and in this test I disabled Multi-AZ.

Here is the single run in one transaction:


[opc@b aws]$ PGPASSWORD=FranckPachot psql -p 5432 -h $db -U postgres demo -e < /tmp/run.sql
truncate table demo;
TRUNCATE TABLE
vacuum demo;
VACUUM
Expanded display is on.
select * from pg_stat_database where datname='demo'
Timing is on.
call my_insert(1e7::int,10000000);
CALL
Time: 55908.947 ms (00:55.909)
Timing is off.
select tup_inserted-1000070"+ tup_inserted",xact_commit-1063"+ xact_commit" from pg_stat_database where datname='demo';
-[ RECORD 1 ]--+---------
+ tup_inserted | 10000000
+ xact_commit  | 14

Here with my quick active session history sample:


[opc@b aws]$ while true ; do echo "select pg_sleep(0.1);select state,to_char(clock_timestamp(),'hh24:mi:ss'),coalesce(wait_event_type,'-null-'),wait_event,query from pg_stat_activity where application_name='psql' and pid != pg_backend_pid();" ; done | PGPASSWORD=FranckPachot psql -p 5432 -h $db -U postgres demo | awk -F "|" '/^ *active/{$2="";c[$0]=c[$0]+1}END{for (i in c){printf "%8d %s\n", c[i],i}}' | sort -n & PGPASSWORD=FranckPachot psql -p 5432 -h $db -U postgres demo -e < /tmp/run.sql ; pkill psql
[1] 29583
truncate table demo;
TRUNCATE TABLE
vacuum demo;
VACUUM
Expanded display is on.
select * from pg_stat_database where datname='demo'
Timing is on.
call my_insert(1e7::int,10000000);
CALL
Time: 56175.302 ms (00:56.175)
Timing is off.
select tup_inserted-11000070"+ tup_inserted",xact_commit-1102"+ xact_commit" from pg_stat_database where datname='demo';
-[ RECORD 1 ]--+---------
+ tup_inserted | 10000000
+ xact_commit  | 346

       3  active    IO         DataFileExtend   call my_insert(1e7::int,10000000);
     160  active    -null-                  call my_insert(1e7::int,10000000);
[opc@b aws]$

No samples for waiting except the table file extension.

Now running the loop with decreasing commit size:


call my_insert(1e7::int,1e7::int);
CALL
Time: 50659.978 ms (00:50.660)

       2  active    IO         DataFileExtend   call my_insert(1e7::int,1e7::int);
     139  active    -null-                  call my_insert(1e7::int,1e7::int);

call my_insert(1e7::int,1e6::int);
CALL
Time: 51760.251 ms (00:51.760)

       2  active    IO         DataFileExtend   call my_insert(1e7::int,1e6::int);
     154  active    -null-                  call my_insert(1e7::int,1e6::int);

call my_insert(1e7::int,1e5::int);
CALL
Time: 50694.917 ms (00:50.695)

       3  active    IO         DataFileExtend   call my_insert(1e7::int,1e5::int);
     139  active    -null-                  call my_insert(1e7::int,1e5::int);

call my_insert(1e7::int,1e4::int);
CALL
Time: 52569.108 ms (00:52.569)

       1  active    IO         WALWrite     call my_insert(1e7::int,1e4::int);
       3  active    IO         DataFileExtend   call my_insert(1e7::int,1e4::int);
     151  active    -null-                  call my_insert(1e7::int,1e4::int);

call my_insert(1e7::int,1e3::int);
CALL
Time: 60799.896 ms (01:00.800)

       4  active    IO         DataFileExtend   call my_insert(1e7::int,1e3::int);
       5  active    IO         WALWrite     call my_insert(1e7::int,1e3::int);
     186  active    -null-                  call my_insert(1e7::int,1e3::int);

call my_insert(1e7::int,1e2::int);
CALL
Time: 108391.218 ms (01:48.391)

       1  active    IO         WALWrite     call my_insert(1e7::int,1e2::int);
       1  active    LWLock     WALWriteLock   call my_insert(1e7::int,1e2::int);
       1  active    -null-                  vacuum demo;
       2  active    IO         DataFileExtend   call my_insert(1e7::int,1e2::int);
     315  active    -null-                  call my_insert(1e7::int,1e2::int);

When the commit frequency increase we start to see WALWrite samples, but still not a lot. Please, remember that this is sampling. WALWrite occurs often especially with frequent commits but the low latency storage (SSD with 1000 Provisioned IOPS here) makes it infrequently sampled.

The CloudWatch statistics are in the previous post so that it is easier to compare.

Multi AZ

I’ve created the same except that I’ve enabled “Multi-AZ deployment” where the storage is synchronously replicated into another data centre. This adds a lot for reliability: failover within a few minutes with no data loss. And a bit of performance as the backups can be offloaded on the replica. But of course, being synchronous, it increases the write latency. Here is the result with the same run with multiple commit size:


call my_insert(1e7::int,1e7::int);
CALL
Time: 55902.530 ms (00:55.903)

       1  active    -null-                  select * from pg_stat_database where datname='demo'
       3  active    IO         DataFileExtend   call my_insert(1e7::int,1e7::int);
     158  active    -null-                  call my_insert(1e7::int,1e7::int);

call my_insert(1e7::int,1e6::int);
CALL
Time: 64711.467 ms (01:04.711)

       3  active    Lock       relation     truncate table demo;
       6  active    IO         DataFileExtend   call my_insert(1e7::int,1e6::int);
     176  active    -null-                  call my_insert(1e7::int,1e6::int);

call my_insert(1e7::int,1e5::int);
CALL
Time: 55480.628 ms (00:55.481)

       1  active    IO         WALWrite     call my_insert(1e7::int,1e5::int);
       5  active    IO         DataFileExtend   call my_insert(1e7::int,1e5::int);
     155  active    -null-                  call my_insert(1e7::int,1e5::int);

call my_insert(1e7::int,1e4::int);
CALL
Time: 69491.522 ms (01:09.492)

       1  active    IO         DataFileImmediateSync   truncate table demo;
       1  active    IO         WALWrite     call my_insert(1e7::int,1e4::int);
       6  active    IO         DataFileExtend   call my_insert(1e7::int,1e4::int);
     192  active    -null-                  call my_insert(1e7::int,1e4::int);

call my_insert(1e7::int,1e3::int);
CALL
Time: 82964.170 ms (01:22.964)

       2  active    IO         DataFileExtend   call my_insert(1e7::int,1e3::int);
       2  active    IO         WALWrite     call my_insert(1e7::int,1e3::int);
     233  active    -null-                  call my_insert(1e7::int,1e3::int);

call my_insert(1e7::int,1e2::int);
CALL
Time: 188313.372 ms (03:08.313)

       1  active    -null-                  truncate table demo;
       2  active    IO         WALWrite     call my_insert(1e7::int,1e2::int);
       6  active    IO         DataFileExtend   call my_insert(1e7::int,1e2::int);
     562  active    -null-                  call my_insert(1e7::int,1e2::int);

The overhead in performance is really small, so this Multi-AZ is a good recommendation for production if cost allows it.

Reliability vs. Performance

Without a surprise, writing the redo log to remote storage increases the latency. We see a small difference between Single and Multi-AZ. And we see a big difference with Aurora which replicates to three AZs. There are 5 pillars in the AWS Well-Architected Framework: Operational Excellence, Security, Reliability, Performance Efficiency and Cost Optimization. I like the 5 axes, but calling them “pillars” supposes that they have all the same height and weight, which is not the case. When you go Multi-AZ you increase the reliability, but the cost as well. When going to Aurora you push the reliability even further but the performance decreases as well for writes. This is no surprise: Aurora is single-master. You can scale reads to multiple replicas but the write endpoint goes to one single instance.

And finally a larger conclusion. You can get better agility with microservices (Aurora is one database platform service built on multiple infrastructure services: compute, buffer cache, storage). You can get better scalability with replicas. And reliability with synchronization. And better performance with high-performance disks and low-latency network. But in 2020 as well as 40 years ago your best investment is in application design. In this example, once you avoid row-by-row commit and be ready to bulk commit, you are free to choose any option for your availability and cost requirements. If you don’t opt for a scalable code design, just re-using a crude row-by-row API, you will have mediocre performance which will constrain your availability and scalability options.

Cet article AWS Aurora vs. RDS PostgreSQL on frequent commits est apparu en premier sur Blog dbi services.

AWS Aurora IO:XactSync is not a PostgreSQL wait event

By Franck Pachot

.
In AWS RDS you can run two flavors of the PostgreSQL managed service: the real PostgreSQL engine, compiled from the community sources, and running on EBS storage mounted by the database EC2 instance, and the Aurora which is proprietary and AWS Cloud only, where the upper layer has been taken from the community PostgreSQL. The storage layer in Aurora is completely different.

In PostgreSQL, as in most RDBMS except for exclusive fast load operations, the user session backend process writes to shared memory buffers. The write to disk, by the session process or a background writer process, is asynchronous. Because the buffer writes are usually random, they would add latency that would slow the response time if synced immediately. By doing them asynchronously, the user doesn’t wait on them. And doing them at regular intervals (checkpoints), they can be re-ordered to reduce the seek time and avoid writing the same buffer multiple times. As you don’t want to lose your committed changes or see the uncommitted ones in case of instance failure, the memory buffer changes are protected by the WAL (Write-Ahead Logging). Even when the session process is performing the write, there’s no need to sync that to the storage until the end of the transaction. The only place where the end-user must wait for a physical write sync is at commit because we must ensure that the WAL went to durable storage before acknowledging the commit. But those writes being sequential, this log-at-commit should be fast. A well-designed application should not have more than one commit per user interaction and then a few milliseconds is not perceptible. That’s the idea: write to memory only, without high latency system calls (visible through wait events), and it is only at commit that we can wait for the latency of a physical write, usually on local storage because the goal is to protect from memory loss only, and fast disks because the capacity is under control (only the WAL between two checkpoints is required).

Aurora is completely different. For High Availability reasons, the storage is not local but distributed to multiple data centers (3 Availability Zones in the database cluster region). And in order to stay in HA even in case of a full AZ failure, the blocks are mirrored in each AZ. This means that each buffer write is actually written to 6 copies on remote storage. That would make the backend, and the writer, too busy to complete the checkpoints. And for this reason, AWS decided to offload this work to the Aurora storage instances. The database instances ships only the WAL (redo log). They apply it locally to maintain their shared buffer, but that stays in memory. All checkpoint and recovery is done by the storage instances (which contains some PostgreSQL code for that). In addition to High Availability, Aurora can expose the storage to some read replicas for performance reasons (scale out the reads). And in order to reduce the time to recover, an instance can be immediately opened even when there’s still some redo to apply to recover to the point of failure. As the Aurora storage tier is autonomous for this, the redo is applied on the flow when a block is read.

For my Oracle readers, here is how I summarized this once:

A consequence of this Aurora design is the session backend process sending directly the changes (WAL) and wait for sync acknowledge on COMMIT. This obviously adds more latency because all goes on the network, a trade-off between performance and availability. In this blog I’m showing an example of row-by-row insert running in RDS PostgreSQL and in Aurora PostgreSQL. And new design for writes means new wait event: the XactSync is not a PostgreSQL wait event but an Aurora-only one when synching at commit.

It is usually a bad idea to design an application with too frequent commits. When we have lot of rows to change, better do it in one transaction, or several transactions with intermediate commits, rather than commit each row. The recommendation is even more important in Aurora (and that’s the main goal of this post). I’ll run my example with different size of intermediate commit.

Aurora instance

I have created an Aurora PostgreSQL 11.6 database running on db.r5large in my lab account. From the default configuration here are the settings I changed: I made it publicly accessible (that’s a temporary lab), I disabled automatic backups, encryption, performance insights, enhanced monitoring, and deletion protection as I don’t need them here. A few screenshots from that:
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/aur-pg-... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/aur-pg-... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/aur-pg-... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/04/aur-pg-... 1662w" sizes="(max-width: 1024px) 100vw, 1024px" />
I have created a ‘demo’ database and set an easy master password.

Create table

I’ll create a demo table and a my_insert procedure where the first parameter is the number of rows to insert and the second parameter is the commit count (I commit only after this number of rows is inserted):


cat > /tmp/cre.sql<<'SQL'
drop table if exists demo;
create table demo (id int primary key,n int);
create or replace procedure my_insert(rows_per_call int,rows_per_xact int) as $$
declare
 row_counter int :=0;
begin for i in 1..rows_per_call loop
   insert into demo values (i,i);
   row_counter:=row_counter+1;
   if row_counter>=rows_per_xact then commit; row_counter:=0; end if;
 end loop; commit; end;
$$ language plpgsql;
\timing on
truncate table demo;
\x
select * from pg_stat_database where datname='demo';
select * from pg_stat_database where datname='demo' \gset
call my_insert(1e6::int,1000);
select *,tup_inserted-:tup_inserted"+ tup_inserted",xact_commit-:xact_commit"+ xact_commit" from pg_stat_database where datname='demo';
select * from pg_stat_database where datname='demo';
select * from pg_stat_wal_receiver;
select * from pg_stat_bgwriter;
create extension pg_stat_statements;
select version();
SQL

I saved that into /tmp/cre.sql and run it:


db=aur-pg.cluster-crtkf6muowez.us-east-1.rds.amazonaws.com
PGPASSWORD=FranckPachot psql -p 5432 -h $db -U postgres demo -e < /tmp/cre.sql

Here is the result:


select * from pg_stat_database where datname='demo';
-[ RECORD 1 ]--+------------------------------
datid          | 16400
datname        | demo
numbackends    | 1
xact_commit    | 4
xact_rollback  | 0
blks_read      | 184
blks_hit       | 1295
tup_returned   | 773
tup_fetched    | 697
tup_inserted   | 23
tup_updated    | 0
tup_deleted    | 0
conflicts      | 0
temp_files     | 0
temp_bytes     | 0
deadlocks      | 0
blk_read_time  | 270.854
blk_write_time | 0
stats_reset    | 2020-04-26 15:03:30.796362+00

Time: 176.913 ms
select * from pg_stat_database where datname='demo'
Time: 110.739 ms
call my_insert(1e6::int,1000);
CALL
Time: 17780.118 ms (00:17.780)
select *,tup_inserted-23"+ tup_inserted",xact_commit-4"+ xact_commit" from pg_stat_database where datname='demo';
-[ RECORD 1 ]--+------------------------------
datid          | 16400
datname        | demo
numbackends    | 1
xact_commit    | 1017
xact_rollback  | 0
blks_read      | 7394
blks_hit       | 2173956
tup_returned   | 5123
tup_fetched    | 839
tup_inserted   | 1000023
tup_updated    | 2
tup_deleted    | 0
conflicts      | 0
temp_files     | 0
temp_bytes     | 0
deadlocks      | 0
blk_read_time  | 317.762
blk_write_time | 0
stats_reset    | 2020-04-26 15:03:30.796362+00
+ tup_inserted | 1000000
+ xact_commit  | 1013

That validates the statistics I gather: one million insert with a commit every 1000. And the important point: 17.7 seconds to insert those 1000000 rows on Aurora.

Run on RDS Aurora

Ok, 17 seconds is not enough for me. In the following tests I’ll insert 10 million rows. And let’s start doing that in one big transaction:


cat > /tmp/run.sql <<'SQL'
truncate table demo;
vacuum demo;
\x
select * from pg_stat_database where datname='demo' \gset
\timing on
call my_insert(1e7::int,10000000);
\timing off
select tup_inserted-:tup_inserted"+ tup_inserted",xact_commit-:xact_commit"+ xact_commit" from pg_stat_database where datname='demo';
SQL

I run this on my Aurora instance.


[opc@b aws]$ PGPASSWORD=FranckPachot psql -p 5432 -h $db -U postgres demo -e < /tmp/run.sql
truncate table demo;
TRUNCATE TABLE
vacuum demo;
VACUUM
Expanded display is on.
select * from pg_stat_database where datname='demo'
Timing is on.
call my_insert(1e7::int,10000000);
CALL
Time: 133366.305 ms (02:13.366)
Timing is off.
select tup_inserted-35098070"+ tup_inserted",xact_commit-28249"+ xact_commit" from pg_stat_database where datname='demo';
-[ RECORD 1 ]--+---------
+ tup_inserted | 10000000
+ xact_commit  | 62

This runs the call with 10 million rows with commit every 10 million, which means all in one transaction. The database stats show very few commits (there may be some irrelevant background jobs at the same time). Elapsed time for the inserts: 2:13 minutes.

I did not enable Performance Insight so I’ll run a dirty active sessions sampling:


while true ; do echo "select pg_sleep(0.1);select state,to_char(clock_timestamp(),'hh24:mi:ss'),coalesce(wait_event_type,'-null-'),wait_event,query from pg_stat_activity where application_name='psql' and pid != pg_backend_pid();" ; done | PGPASSWORD=FranckPachot psql -p 5432 -h $db -U postgres demo | awk -F "|" '/^ *active/{print > "/tmp/ash.txt";$2="";c[$0]=c[$0]+1}END{for (i in c){printf "%8d %s\n", c[i],i}}' | sort -n & PGPASSWORD=FranckPachot psql -p 5432 -h $db -U postgres demo -e < /tmp/run.sql ; pkill psql

This runs in the background a query on PG_STAT_ACTIVITY piped to an AWK script to get the number of samples per wait even. No wait event means not waiting (probably ON CPU) and I label it as ‘-null-‘.

Here is the result for the same run. 2:15 which proves that there’s no overhead with my sampling running in another session with plenty of CPU available:


Expanded display is on.
select * from pg_stat_database where datname='demo'
Timing is on.
call my_insert(1e7::int,10000000);
CALL
Time: 135397.016 ms (02:15.397)
Timing is off.
select tup_inserted-45098070"+ tup_inserted",xact_commit-28338"+ xact_commit" from pg_stat_database where datname='demo';
-[ RECORD 1 ]--+----
+ tup_inserted | 0
+ xact_commit  | 819

[opc@b aws]$
       1  active    IO         XactSync     call my_insert(1e7::int,10000000);
      30  active    IO         WALWrite     call my_insert(1e7::int,10000000);
     352  active    -null-                  call my_insert(1e7::int,10000000);
[opc@b aws]$

Most of the time (92% of pg_stat_activity samples) my session is not on a wait event, which means running in CPU. 8% of the samples are “WALWrite” and only one sample “XactSync”.

This “XactSync” is something that you cannot find in PostgreSQL documentation but in Aurora documentation:
IO:XactSync – In this wait event, a session is issuing a COMMIT or ROLLBACK, requiring the current transaction’s changes to be persisted. Aurora is waiting for Aurora storage to acknowledge persistence.

The WALWrite is the session process sending the WAL but not waiting for acknowledge (this is my guess from the small wait samples as I’ve not seen this internal Aurora behaviour documented).

I’ll now run with different commit size, starting from the maximum (all in one transaction) down to commit every 100 rows. I’m copying only the interesting parts: the elapsed time and the wait event sample count:


call my_insert(1e7::int,1e7::int);
CALL
Time: 135868.757 ms (02:15.869)

       1  active    -null-                  vacuum demo;
      44  active    IO         WALWrite     call my_insert(1e7::int,1e7::int);
     346  active    -null-                  call my_insert(1e7::int,1e7::int);

call my_insert(1e7::int,1e6::int);
CALL
Time: 136080.102 ms (02:16.080)

       3  active    IO         XactSync     call my_insert(1e7::int,1e6::int);
      38  active    IO         WALWrite     call my_insert(1e7::int,1e6::int);
     349  active    -null-                  call my_insert(1e7::int,1e6::int);

call my_insert(1e7::int,1e5::int);
CALL
Time: 133373.694 ms (02:13.374)


      30  active    IO         XactSync     call my_insert(1e7::int,1e5::int);
      35  active    IO         WALWrite     call my_insert(1e7::int,1e5::int);
     315  active    -null-                  call my_insert(1e7::int,1e5::int);

call my_insert(1e7::int,1e4::int);
CALL
Time: 141820.013 ms (02:21.820)

      32  active    IO         WALWrite     call my_insert(1e7::int,1e4::int);
      91  active    IO         XactSync     call my_insert(1e7::int,1e4::int);
     283  active    -null-                  call my_insert(1e7::int,1e4::int);

call my_insert(1e7::int,1e3::int);
CALL
Time: 177596.155 ms (02:57.596)

       1  active    -null-                  select tup_inserted-95098070"+ tup_inserted",xact_commit-33758"+ xact_commit" from pg_stat_database where datname='demo';
      32  active    IO         WALWrite     call my_insert(1e7::int,1e3::int);
     250  active    IO         XactSync     call my_insert(1e7::int,1e3::int);
     252  active    -null-                  call my_insert(1e7::int,1e3::int);

call my_insert(1e7::int,1e2::int);
CALL
Time: 373504.413 ms (06:13.504)

      24  active    IO         WALWrite     call my_insert(1e7::int,1e2::int);
     249  active    -null-                  call my_insert(1e7::int,1e2::int);
     776  active    IO         XactSync     call my_insert(1e7::int,1e2::int);

Even if the frequency of XactSync is increasing with the number of commits, the elapsed is nearly the same when the commit size is higher than ten thousand rows. Then it quickly increases and committing every 100 rows only brings the elapsed time to 6 minutes with 74% of the time waiting on XactSync.

This wait event being specific to the way Aurora foreground process sends the redo to the storage, through the network, I’ll run the same with a normal PostgreSQL (RDS) running on mounted block storage (EBS).

I’m adding here some CloudWatch metrics during those runs, starting at 11:30 with large commit size down to row-by-row commit still running at 12:00
https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/CloudWa... 300w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/CloudWa... 768w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/CloudWa... 1536w, https://blog.dbi-services.com/wp-insides/uploads/sites/2/2020/05/CloudWa... 1767w" sizes="(max-width: 1024px) 100vw, 1024px" />.

As you can see I’ve added the ones for the next test with RDS PostgreSQL running the same between 12:15 and 12:30 which is detailed in the next post: https://blog.dbi-services.com/aws-aurora-vs-rds-postgresql/. Interpreting those results is beyond the scope of this blog post. We have seen that in the frequent commit run most of the time is on IO:XactSync wait event. However, CloudWatch shows 50% CPU utilization which means, for r5.large, one thread always in CPU. This is not exactly what I expect with a wait event. But, quoting Kevin Closson, everything is a CPU problem, isn’t it?

Cet article AWS Aurora IO:XactSync is not a PostgreSQL wait event est apparu en premier sur Blog dbi services.