Monday, May 04, 2020

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

Added 16.10.2020: See also related presentation: Oracle 19c: Automatic Indexing.
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.
alter system set "_exadata_feature_on"=true scope=spfile;
shutdown immediate;
startup;
Swingbench requires a Java 8 in a Java virtual machine.
yum install java
Then, it is simply a matter of downloading and unzipping the distribution.
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.
execute dbms_workload_repository.modify_snapshot_settings(interval => 15);
I have created a dedicated tablespace for the SOE schema
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.
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.
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.
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.
./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.
>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.
Transaction
Average Response (ms)
1: Delivered Indexes
Update Customer Details
1.18
Browse Products
2.03
Browse Orders
2.38
Customer Registration
3.50
Order Products
5.67
Warehouse Query
6.20
Process Orders
13.42
Warehouse Activity Query
14.89
Sales Rep Query
31.76
TPS
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.
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.
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.
Transaction
Average Response (ms)
1: Delivered Indexes
2: Drop Secondary Indexes
Update Customer Details
1.18
3.30
Browse Products
2.03
409.21
Browse Orders
2.38
2.05
Customer Registration
3.50
78.51
Order Products
5.67
40.97
Warehouse Query
6.20
2.82
Process Orders
13.42
247.80
Warehouse Activity Query
14.89
274.19
Sales Rep Query
31.76
268.51
TPS
1060.81
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.
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.
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.
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA', 'SOE', allow => TRUE);
  • Finally, I enable Automatic Indexing and permit it to create indexes.
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.
                                  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.
           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.
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. 
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.
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.
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.
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.
Transaction
Average Response (ms)
1: Delivered Indexes
2: Drop Secondary Indexes
3: Automatic Indexing
Update Customer Details
1.18
3.30
3.32
Browse Products
2.03
409.21
478.52
Browse Orders
2.38
2.05
2.01
Customer Registration
3.50
78.51
5.91
Order Products
5.67
40.97
50.34
Warehouse Query
6.20
2.82
2.85
Process Orders
13.42
247.80
5.39
Warehouse Activity Query
14.89
274.19
11.43
Sales Rep Query
31.76
268.51
14.45
TPS
1060.81
81.30
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
                          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.
                                                  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.  
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.
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.
Transaction
Average Response (ms)
1: Delivered Indexes
2: Drop Secondary Indexes
3: Automatic Indexing
4: Manual Tuning
Update Customer Details
1.18
3.30
3.32
3.51
Browse Products
2.03
409.21
478.52
1.93
Browse Orders
2.38
2.05
2.01
2.12
Customer Registration
3.50
78.51
5.91
5.92
Order Products
5.67
40.97
50.34
1.99
Warehouse Query
6.20
2.82
2.85
3.00
Process Orders
13.42
247.80
5.39
4.95
Warehouse Activity Query
14.89
274.19
11.43
20.29
Sales Rep Query
31.76
268.51
14.45
3.74
TPS
1060.81
81.30
137.40
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.
                                                           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.
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.
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.
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.
Updated 25.8.2020 Oracle has provided a procedure DROP_AUTO_INDEXES in DBMS_AUTO_INDEX (backported to 19.5). 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.
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.

5 comments :

Kyle Hailey said...

nice write up and investigation.
On the Manual Index tuning, what was your method to identify these indexex?
Thanks
Kyle

David Kurtz said...

Good old-fashioned response-time-driven manual tuning. Use ASH to find the statements that accounted for the most time, look at the execution plans, use ASH to confirm which lines of the plan consumed the most time. In this case, I had dropped the secondary indexes, so I knew the problem was likely to be a missing or inadequate index. Then it was just a matter of adding indexes to satisfy the problem statements. I would make one change at a time and then retest. I only needed four changes.
I'd like to think it is more-or-less Millsap Method-R.

GG said...

Indeed, great write up.
Especially the sql performance comparison script looks pretty clean.
Is it relies only on dba_hist_sqlstat ?
Regards.
Greg

David Kurtz said...

Yes, the performance comparison is done with DBA_HIST_SQL_STAT

--define time window for scenarios in PLAN_TABLE--
alter session set nls_date_Format = 'hh24:mi:ss dd.mm.yyyy';
Delete from plan_table where statement_id = 'SCENARIOS';
Commit;
Insert into plan_table (statement_id, plan_id, remarks, object_owner, partition_start, partition_stop)
VALUES ('SCENARIOS', 1, 'With Default Indexes', 'SOE', '20200317-115453', '20200317-125453');
Insert into plan_table (statement_id, plan_id, remarks, object_owner, partition_start, partition_stop)
VALUES ('SCENARIOS', 2, 'With Default Indexes', 'SOE', '20200317-130500', '20200317-140503');

--report on scenarios
Set autotrace off pages 99 lines 160
Column sql_id format a13
column scenario_id heading 'Test|ID' format 999
column plan_hash_value heading 'SQL Plan|Hash Value'
column optimizer_cost heading 'Opt.|Cost' format 9999999
column num_executions heading 'Num|Execs' format 9999999
column elapsed_time heading 'Elapsed|Time' format 99999.99
column avg_elapsed_time heading 'Average|Elapsed|Time' format 99.9999
column pct_time heading '%|Time|Diff' format 999999
column pct_num_execs heading '%Num|Execs|Diff' format 999999
column plan_cf heading '?' format a1
break on report
with s as (
Select plan_id scenario_id, object_owner, remarks
, TO_DATE(partition_start,'yyyymmdd-hh24miss') begin_date
, TO_DATE(partition_stop,'yyyymmdd-hh24miss') end_date
From plan_table
Where statement_id = 'SCENARIOS'
), x as (
select s.scenario_id, t.sql_id, t.plan_hash_value
, MAX(t.optimizer_cost) optimizer_cost
, sum(t.executions_delta) num_executions
, sum(t.elapsed_time_delta)/1e6 elapsed_time
, sum(t.elapsed_time_delta)/sum(t.executions_delta)/1e6 avg_elapsed_time
from s
, dba_hist_Snapshot x
, dba_hist_sqlstat t
where x.begin_interval_time < s.end_date
and x.end_interval_time > s.begin_date
and x.dbid = t.dbid
and x.instance_number = t.instance_number
and x.snap_id = t.snap_id
and t.executions_delta>0
and t.parsing_Schema_name = s.object_owner
group by s.scenario_id, t.sql_id, t.plan_hash_value
)
select x1.*
, CASE WHEN x1.plan_hash_value = x2.plan_hash_value THEN '=' ELSE '!' END plan_cf
, x2.*
, (x2.avg_elapsed_time/NULLIF(x1.avg_elapsed_time,0)-1)*100 pct_time
, (x2.num_executions/NULLIF(x1.num_executions,0)-1)*100 pct_num_execs
from x x1
full outer join x x2
on x1.sql_id = x2.sql_id
and x2.scenario_id = 2 --change this--
where x1.scenario_id = 1 --change this--
order by x2.elapsed_time desc nulls last, x1.elapsed_time desc nulls last
fetch first 30 rows only
/

GG said...

Thank You.
Regards.
GG