Search

Top 60 Oracle Blogs

Recent comments

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.