DOAG Conferenc 2020, Nuremburg

Wednesday, June 03, 2020

Loading a Flat File from OCI Object Storage into an Autonomous Database. Part 3. Copying data from Object Storage to a Regular Table

This blog is the third in a series of three that looks at transferring a file to Oracle Cloud Infrastructure (OCI) Object Storage, and then reading it into the database with an external table or copying it into a regular table.

Copy Data into Table 

Alternatively, we can copy the data into a normal table. The table needs to be created in advance. This time, I am going to run the copy as user SOE rather than ADMIN.  I need to:
  • Grant connect and resource privilege and quota on the data tablespace.
  • Grant execute on DBMS_CLOUD to SOE, so it can execute the command.
  • Grant READ and WRITE access on the DATA_PUMP_DIR directory – the log and bad files created by this process are written to this database directory.
connect admin/Password2020!@gofaster1b_tp 
CREATE USER soe IDENTIFIED BY Password2020;
GRANT CONNECT, RESOURCE TO soe;
GRANT EXECUTE ON DBMS_CLOUD TO soe;
GRANT READ, WRITE ON DIRECTORY data_pump_dir TO soe;
ALTER USER soe QUOTA UNLIMITED ON data;
I am now going to switch to user SOE and create my table.
connect soe/Password2020@gofaster1b_tp
Drop table soe.ash_hist purge;
CREATE TABLE soe.ASH_HIST
   (    SNAP_ID NUMBER,
        DBID NUMBER,
        INSTANCE_NUMBER NUMBER,
        SAMPLE_ID NUMBER,
        SAMPLE_TIME TIMESTAMP (3),
--      SAMPLE_TIME_UTC TIMESTAMP (3),
--      USECS_PER_ROW NUMBER,
        SESSION_ID NUMBER,
        SESSION_SERIAL# NUMBER,
        SESSION_TYPE VARCHAR2(10),
        FLAGS NUMBER,
        USER_ID NUMBER,
-----------------------------------------
        SQL_ID VARCHAR2(13),
        IS_SQLID_CURRENT VARCHAR2(1),
        SQL_CHILD_NUMBER NUMBER,
        SQL_OPCODE NUMBER,
        SQL_OPNAME VARCHAR2(64),
        FORCE_MATCHING_SIGNATURE NUMBER,
        TOP_LEVEL_SQL_ID VARCHAR2(13),
        TOP_LEVEL_SQL_OPCODE NUMBER,
        SQL_PLAN_HASH_VALUE NUMBER,
        SQL_FULL_PLAN_HASH_VALUE NUMBER,
-----------------------------------------
        SQL_ADAPTIVE_PLAN_RESOLVED NUMBER,
        SQL_PLAN_LINE_ID NUMBER,
        SQL_PLAN_OPERATION VARCHAR2(64),
        SQL_PLAN_OPTIONS VARCHAR2(64),
        SQL_EXEC_ID NUMBER,
        SQL_EXEC_START DATE,
        PLSQL_ENTRY_OBJECT_ID NUMBER,
        PLSQL_ENTRY_SUBPROGRAM_ID NUMBER,
        PLSQL_OBJECT_ID NUMBER,
        PLSQL_SUBPROGRAM_ID NUMBER,
-----------------------------------------
        QC_INSTANCE_ID NUMBER,
        QC_SESSION_ID NUMBER,
        QC_SESSION_SERIAL# NUMBER,
        PX_FLAGS NUMBER,
        EVENT VARCHAR2(64),
        EVENT_ID NUMBER,
        SEQ# NUMBER,
        P1TEXT VARCHAR2(64),
        P1 NUMBER,
        P2TEXT VARCHAR2(64),
-----------------------------------------
        P2 NUMBER,
        P3TEXT VARCHAR2(64),
        P3 NUMBER,
        WAIT_CLASS VARCHAR2(64),
        WAIT_CLASS_ID NUMBER,
        WAIT_TIME NUMBER,
        SESSION_STATE VARCHAR2(7),
        TIME_WAITED NUMBER,
        BLOCKING_SESSION_STATUS VARCHAR2(11),
        BLOCKING_SESSION NUMBER,
-----------------------------------------
        BLOCKING_SESSION_SERIAL# NUMBER,
        BLOCKING_INST_ID NUMBER,
        BLOCKING_HANGCHAIN_INFO VARCHAR2(1),
        CURRENT_OBJ# NUMBER,
        CURRENT_FILE# NUMBER,
        CURRENT_BLOCK# NUMBER,
        CURRENT_ROW# NUMBER,
        TOP_LEVEL_CALL# NUMBER,
        TOP_LEVEL_CALL_NAME VARCHAR2(64),
        CONSUMER_GROUP_ID NUMBER,
-----------------------------------------
        XID RAW(8),
        REMOTE_INSTANCE# NUMBER,
        TIME_MODEL NUMBER,
        IN_CONNECTION_MGMT VARCHAR2(1),
        IN_PARSE VARCHAR2(1),
        IN_HARD_PARSE VARCHAR2(1),
        IN_SQL_EXECUTION VARCHAR2(1),
        IN_PLSQL_EXECUTION VARCHAR2(1),
        IN_PLSQL_RPC VARCHAR2(1),
        IN_PLSQL_COMPILATION VARCHAR2(1),
-----------------------------------------
        IN_JAVA_EXECUTION VARCHAR2(1),
        IN_BIND VARCHAR2(1),
        IN_CURSOR_CLOSE VARCHAR2(1),
        IN_SEQUENCE_LOAD VARCHAR2(1),
        IN_INMEMORY_QUERY VARCHAR2(1),
        IN_INMEMORY_POPULATE VARCHAR2(1),
        IN_INMEMORY_PREPOPULATE VARCHAR2(1),
        IN_INMEMORY_REPOPULATE VARCHAR2(1),
        IN_INMEMORY_TREPOPULATE VARCHAR2(1),
--      IN_TABLESPACE_ENCRYPTION VARCHAR2(1),
        CAPTURE_OVERHEAD VARCHAR2(1),
-----------------------------------------
        REPLAY_OVERHEAD VARCHAR2(1),
        IS_CAPTURED VARCHAR2(1),
        IS_REPLAYED VARCHAR2(1),
--      IS_REPLAY_SYNC_TOKEN_HOLDER VARCHAR2(1),
        SERVICE_HASH NUMBER,
        PROGRAM VARCHAR2(64),
        MODULE VARCHAR2(64),
        ACTION VARCHAR2(64),
        CLIENT_ID VARCHAR2(64),
        MACHINE VARCHAR2(64),
        PORT NUMBER,
-----------------------------------------
        ECID VARCHAR2(64),
        DBREPLAY_FILE_ID NUMBER,
        DBREPLAY_CALL_COUNTER NUMBER,
        TM_DELTA_TIME NUMBER,
        TM_DELTA_CPU_TIME NUMBER,
        TM_DELTA_DB_TIME NUMBER,
        DELTA_TIME NUMBER,
        DELTA_READ_IO_REQUESTS NUMBER,
        DELTA_WRITE_IO_REQUESTS NUMBER,
        DELTA_READ_IO_BYTES NUMBER,
-----------------------------------------
        DELTA_WRITE_IO_BYTES NUMBER,
        DELTA_INTERCONNECT_IO_BYTES NUMBER,
        PGA_ALLOCATED NUMBER,
        TEMP_SPACE_ALLOCATED NUMBER,
        DBOP_NAME VARCHAR2(64),
        DBOP_EXEC_ID NUMBER,
        CON_DBID NUMBER,
        CON_ID NUMBER,
-----------------------------------------
        CONSTRAINT ash_hist_pk PRIMARY KEY (dbid, instance_number, snap_id, sample_id, session_id)
   ) 
COMPRESS FOR QUERY LOW
/
As Autonomous Databases run on Exadata, I have also specified Hybrid Columnar Compression (HCC) for this table.
Credentials are specific to the database user.  I have to create an additional credential, for the same cloud user, but owned by SOE.
ALTER SESSION SET nls_date_Format='hh24:mi:ss dd.mm.yyyy';
set serveroutput on timi on
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'SOE_BUCKET',
    username=> 'oraclecloud1@go-faster.co.uk',
    password=> 'K7xfi-mG<1Z:dq#88;1m'
  );
END;
/
column owner format a10
column credential_name format a20
column comments format a80
column username format a40
SELECT * FROM dba_credentials;

OWNER      CREDENTIAL_NAME      USERNAME                                 WINDOWS_DOMAIN
---------- -------------------- ---------------------------------------- ------------------------------
COMMENTS                                                                         ENABL
-------------------------------------------------------------------------------- -----
ADMIN      MY_BUCKET            oraclecloud1@go-faster.co.uk
{"comments":"Created via DBMS_CLOUD.create_credential"}                          TRUE

SOE        SOE_BUCKET           oraclecloud1@go-faster.co.uk
{"comments":"Created via DBMS_CLOUD.create_credential"}                          TRUE
The COPY_DATA procedure is similar to CREATE_EXTERNAL_TABLE described in the previous post, but it doesn't have a column list. The field names much match the column names. It is sensitive to field names with a trailing #.  These must be enclosed in double-quotes.
TRUNCATE TABLE soe.ash_hist;
DECLARE
  l_operation_id NUMBER;
BEGIN
  DBMS_CLOUD.COPY_DATA(
    table_name =>'ASH_HIST',
    credential_name =>'SOE_BUCKET',
    file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucket-20200505-1552/o/dba_hist_active_sess_history.txt.gz',
    schema_name => 'SOE',
    format => json_object('blankasnull'      value 'true'
                         ,'compression'      value 'gzip'
                         ,'dateformat'       value 'YYYY-MM-DD/HH24:mi:ss'
                         ,'timestampformat'  value 'YYYY-MM-DD/HH24:mi:ss.ff'
                         ,'delimiter'        value '<,>'
                         ,'ignoreblanklines' value 'true'
                         ,'rejectlimit'      value '10'
                         ,'removequotes'     value 'true'
                         ,'trimspaces'       value 'lrtrim'
                         ),
    field_list=>'SNAP_ID,DBID,INSTANCE_NUMBER,SAMPLE_ID,SAMPLE_TIME ,SESSION_ID,"SESSION_SERIAL#",SESSION_TYPE,FLAGS,USER_ID
,SQL_ID,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,SQL_OPCODE,SQL_OPNAME,FORCE_MATCHING_SIGNATURE,TOP_LEVEL_SQL_ID,TOP_LEVEL_SQL_OPCODE,SQL_PLAN_HASH_VALUE,SQL_FULL_PLAN_HASH_VALUE
,SQL_ADAPTIVE_PLAN_RESOLVED,SQL_PLAN_LINE_ID,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS,SQL_EXEC_ID,SQL_EXEC_START,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_OBJECT_ID,PLSQL_SUBPROGRAM_ID
,QC_INSTANCE_ID,QC_SESSION_ID,"QC_SESSION_SERIAL#",PX_FLAGS,EVENT,EVENT_ID,"SEQ#",P1TEXT,P1,P2TEXT
,P2,P3TEXT,P3,WAIT_CLASS,WAIT_CLASS_ID,WAIT_TIME,SESSION_STATE,TIME_WAITED,BLOCKING_SESSION_STATUS,BLOCKING_SESSION
,"BLOCKING_SESSION_SERIAL#",BLOCKING_INST_ID,BLOCKING_HANGCHAIN_INFO,"CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#",TOP_LEVEL_CALL_NAME,CONSUMER_GROUP_ID
,XID,"REMOTE_INSTANCE#",TIME_MODEL,IN_CONNECTION_MGMT,IN_PARSE,IN_HARD_PARSE,IN_SQL_EXECUTION,IN_PLSQL_EXECUTION,IN_PLSQL_RPC,IN_PLSQL_COMPILATION
,IN_JAVA_EXECUTION,IN_BIND,IN_CURSOR_CLOSE,IN_SEQUENCE_LOAD,IN_INMEMORY_QUERY,IN_INMEMORY_POPULATE,IN_INMEMORY_PREPOPULATE,IN_INMEMORY_REPOPULATE,IN_INMEMORY_TREPOPULATE,CAPTURE_OVERHEAD
,REPLAY_OVERHEAD,IS_CAPTURED,IS_REPLAYED,SERVICE_HASH,PROGRAM,MODULE,ACTION,CLIENT_ID,MACHINE,PORT
,ECID,DBREPLAY_FILE_ID,DBREPLAY_CALL_COUNTER,TM_DELTA_TIME,TM_DELTA_CPU_TIME,TM_DELTA_DB_TIME,DELTA_TIME,DELTA_READ_IO_REQUESTS,DELTA_WRITE_IO_REQUESTS,DELTA_READ_IO_BYTES 
,DELTA_WRITE_IO_BYTES,DELTA_INTERCONNECT_IO_BYTES,PGA_ALLOCATED,TEMP_SPACE_ALLOCATED,DBOP_NAME,DBOP_EXEC_ID,CON_DBID,CON_ID',
    operation_id=>l_operation_id
  );
  dbms_output.put_line('Operation ID:'||l_operation_id||' finished successfully');
EXCEPTION WHEN OTHERS THEN
  dbms_output.put_line('Operation ID:'||l_operation_id||' raised an error');
  RAISE;
END;
/
The copy data takes slightly longer than the query on the external table.
Operation ID:31 finished successfully

PL/SQL procedure successfully completed.

Elapsed: 00:02:01.11
The status of the copy operation is reported in USER_LOAD_OPERATIONS.  This includes the number of rows loaded and the names of external tables that are created for the log and bad files.
set lines 120
column type format a10
column file_uri_list format a64
column start_time format a32
column update_time format a32
column owner_name format a10
column table_name format a10
column partition_name format a10
column subpartition_name format a10
column logfile_table format a15
column badfile_table format a15
column tempext_table format a30
select * from user_load_operations where id = &operation_id;

        ID TYPE              SID    SERIAL# START_TIME                       UPDATE_TIME                      STATUS
---------- ---------- ---------- ---------- -------------------------------- -------------------------------- ---------
OWNER_NAME TABLE_NAME PARTITION_ SUBPARTITI FILE_URI_LIST                                                    ROWS_LOADED
---------- ---------- ---------- ---------- ---------------------------------------------------------------- -----------
LOGFILE_TABLE   BADFILE_TABLE   TEMPEXT_TABLE
--------------- --------------- ------------------------------
        31 COPY            19965      44088 07-MAY-20 17.03.20.328263 +01:00 07-MAY-20 17.05.36.157680 +01:00 COMPLETED
SOE        ASH_HIST                         https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu     1409305
                                            /b/bucket-20200505-1552/o/dba_hist_active_sess_history.txt.gz
COPY$31_LOG     COPY$31_BAD     COPY$Y2R021UKPJ5F75JCMSKL
An external table is temporarily created by the COPY_DATA procedure but is then dropped before the procedure completes.  The bad file is empty because the copy operation succeeded without error, but we can query the copy log.
select * from COPY$31_LOG;

RECORD                                                                                                                  
------------------------------------------------------------------------------------------------------------------------
 LOG file opened at 05/07/20 16:03:21                                                                                   
                                                                                                                        
Total Number of Files=1                                                                                                 
                                                                                                                        
Data File: https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucket-20200505-1552/o/dba_hist_active_sess_history.txt.gz                                                                                                        
                                                                                                                        
Log File: COPY$31_105537.log                                                                                            
                                                                                                                        
 LOG file opened at 05/07/20 16:03:21                                                                                   

Total Number of Files=1                                                                                                 

Data File: https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucket-20200505-1552/o/dba_hist_active_sess_history.txt.gz                                                                                                        

Log File: COPY$31_105537.log                                                                                            
                                                                                                                        
 LOG file opened at 05/07/20 16:03:21                                                                                   

KUP-05014:   Warning: Intra source concurrency disabled because the URLs specified for the Cloud Service map to compressed data.                                                                                                                
                                                                                                                                                                                                                                                
Bad File: COPY$31_105537.bad                                                                                            
                                                                                                                        
Field Definitions for table COPY$Y2R021UKPJ5F75JCMSKL                                                                   
  Record format DELIMITED BY                                                                                            
  Data in file has same endianness as the platform                                                                      
  Rows with all null fields are accepted                                                                                
  Table level NULLIF (Field = BLANKS)                                                                                   
  Fields in Data Source:                                                                                                
                                                                                                                        
    SNAP_ID                         CHAR (255)                                                                          
      Terminated by "<,>"                                                                                               
      Trim whitespace from left and right                                                                               
    DBID                            CHAR (255)                                                                          
      Terminated by "<,>"                                                                                               
      Trim whitespace from left and right                                                                               
    INSTANCE_NUMBER                 CHAR (255)                                                                          
      Terminated by "<,>"                                                                                               
      Trim whitespace from left and right                                                                               
    SAMPLE_ID                       CHAR (255)                                                                          
      Terminated by "<,>"                                                                                               
      Trim whitespace from left and right                                                                               
    SAMPLE_TIME                     CHAR (255)                                                                          
      Date datatype TIMESTAMP, date mask YYYY-MM-DD/HH24:mi:ss.ff                                                       
      Terminated by "<,>"                                                                                               
      Trim whitespace from left and right                                                                               
…
    CON_ID                          CHAR (255)                                                                          
      Terminated by "<,>"                                                                                               
      Trim whitespace from left and right                                                                               

Date Cache Statistics for table COPY$Y2R021UKPJ5F75JCMSKL                                                               
  Date conversion cache disabled due to overflow (default size: 1000)                                                   

365 rows selected.
These files are written to the DATA_DUMP_DIR database directory.  We don't have access to the database file system in Autonomous, so Oracle has provided the LIST_FILES procedure in DBMS_CLOUD so that we can see what files are in a directory.
Set pages 99 lines 150
Column object_name format a32
Column created format a32
Column last_modified format a32
Column checksum format a20
SELECT * FROM DBMS_CLOUD.LIST_FILES('DATA_PUMP_DIR');

OBJECT_NAME                           BYTES CHECKSUM             CREATED                          LAST_MODIFIED
-------------------------------- ---------- -------------------- -------------------------------- --------------------------------
…
COPY$31_dflt.log                          0                      07-MAY-20 16.03.20.000000 +00:00 07-MAY-20 16.03.20.000000 +00:00
COPY$31_dflt.bad                          0                      07-MAY-20 16.03.20.000000 +00:00 07-MAY-20 16.03.20.000000 +00:00
COPY$31_105537.log                    13591                      07-MAY-20 16.03.21.000000 +00:00 07-MAY-20 16.05.35.000000 +00:00
Statistics are automatically collected on the table by the copy process because it was done in direct-path mode.  We can see the number of rows retrieved corresponds with the number of rows imported by the COPY_DATA procedure.
Set pages 99 lines 140
Column owner format a10
Column IM_STAT_UPDATE_TIME format a30
Select * 
from all_tab_statistics
Where table_name = 'ASH_HIST';

OWNER      TABLE_NAME PARTITION_ PARTITION_POSITION SUBPARTITI SUBPARTITION_POSITION OBJECT_TYPE    NUM_ROWS     BLOCKS EMPTY_BLOCKS
---------- ---------- ---------- ------------------ ---------- --------------------- ------------ ---------- ---------- ------------
 AVG_SPACE  CHAIN_CNT AVG_ROW_LEN AVG_SPACE_FREELIST_BLOCKS NUM_FREELIST_BLOCKS AVG_CACHED_BLOCKS AVG_CACHE_HIT_RATIO IM_IMCU_COUNT
---------- ---------- ----------- ------------------------- ------------------- ----------------- ------------------- -------------
IM_BLOCK_COUNT IM_STAT_UPDATE_TIME             SCAN_RATE SAMPLE_SIZE LAST_ANALYZED       GLO USE STATT STALE_S SCOPE
-------------- ------------------------------ ---------- ----------- ------------------- --- --- ----- ------- -------
SOE        ASH_HIST                                                                  TABLE           1409305      19426            0
         0          0         486                         0                   0
                                                             1409305 15:16:14 07.05.2020 YES NO        NO      SHARED
I can confirm that the data is compressed because the compression type of every row is type 8 (HCC QUERY LOW).  See also DBMS_COMPRESSION Compression Types
WITH x AS (
select dbms_compression.get_compression_type('SOE', 'ASH_HIST', rowid) ctype
from soe.ash_hist sample (.1))
Select ctype, count(*) From x group by ctype;

     CTYPE   COUNT(*)
---------- ----------
         8      14097
I can find this SQL Statement in the Performance Hub. 
INSERT /*+ append enable_parallel_dml */ INTO "SOE"."ASH_HIST" SELECT * FROM COPY$Y2R021UKPJ5F75JCMSKL
Therefore, the data was queried from the temporary external table into the permanent table, in direct path mode and in parallel.
I can also look at the OCI Performance Hub and see that mode of the time was spent on CPU.  I can see the SQL_ID of the insert statement and the call to the DBMS_CLOUD procedure.
I can drill in further to the exact SQL statement.
When I query the table I get exactly the same data as previously with the external table.
set autotrace on timi on lines 180 trimspool on
break on report
compute sum of ash_secs on report
column min(sample_time) format a22
column max(sample_time) format a22
select event, sum(10) ash_Secs, min(sample_time), max(sample_time)
from soe.ash_hist
group by event
order by ash_Secs desc
;

EVENT                                                              ASH_SECS MIN(SAMPLE_TIME)       MAX(SAMPLE_TIME)
---------------------------------------------------------------- ---------- ---------------------- ----------------------
                                                                   10304530 22-MAR-20 09.59.51.125 07-APR-20 23.00.30.395
direct path read                                                    3258500 22-MAR-20 09.59.51.125 07-APR-20 23.00.30.395
SQL*Net more data to client                                          269220 22-MAR-20 10.00.31.205 07-APR-20 22.59.30.275
direct path write temp                                                32400 22-MAR-20 11.39.53.996 07-APR-20 21.43.47.329
gc cr block busy                                                      24930 22-MAR-20 10.51.33.189 07-APR-20 22.56.56.804

latch free                                                               10 28-MAR-20 20.26.11.307 28-MAR-20 20.26.11.307
                                                                 ----------
sum                                                                14093050

86 rows selected.

Elapsed: 00:00:00.62
I can see that the execution plan is now a single serial full scan of the table.
Execution Plan
----------------------------------------------------------
Plan hash value: 1336681691

----------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |    84 |  1428 |  1848   (9)| 00:00:01 |
|   1 |  SORT ORDER BY              |          |    84 |  1428 |  1848   (9)| 00:00:01 |
|   2 |   HASH GROUP BY             |          |    84 |  1428 |  1848   (9)| 00:00:01 |
|   3 |    TABLE ACCESS STORAGE FULL| ASH_HIST |  1409K|    22M|  1753   (4)| 00:00:01 |
----------------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
         11  recursive calls
         13  db block gets
      19255  consistent gets
      19247  physical reads
       2436  redo size
       5428  bytes sent via SQL*Net to client
        602  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
         86  rows processed

Tuesday, June 02, 2020

Loading a Flat File from OCI Object Storage into an Autonomous Database. Part 2. Reading from Object Storage with an External Table

This blog is the second in a series of three that looks at transferring a file to Oracle Cloud Infrastructure (OCI) Object Storage, and then reading it into the database with an external table or copying it into a regular table.

Create A Credential 

First, I need to create a credential that the database will use to connect to the OCI Object Storage. This is not the same as the credential that the OCI CLI used to connect.
In the OCI interface navigate to Identity ➧ Users ➧ User Details, and create an Authentication Token.
It is important to copy the token at this point because you will not see it again.
Now you can put the token into a database credential.
connect admin/Password2020@gofaster1b_tp 
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL (
    credential_name => 'MY_BUCKET',
    username=> 'oraclecloud1@go-faster.co.uk',
    password=> 'K7xfi-mG<1z:dq code="" end="" m="">
Note: The visibility of the bucket that I created earlier is private by default. Therefore, I can only access it with an authenticated user. If I were to create a credential for an unauthenticated user, it could only be accessed as public bucket. Otherwise, I would obtain an error.
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-20404: Object not found -
https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucket-202005
05-1552/o/dba_hist_active_sess_history.txt.gz
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD", line 964
ORA-06512: at "C##CLOUD$SERVICE.DBMS_CLOUD_INTERNAL", line 3891
ORA-06512: at line 1

Create an External Table

In my blog Reading the Active Session History Compressed Export File in eDB360/SQLd360 as an External Table, I showed how to create an external table to read a compressed file.  Now I am going to do the same thing as, except that now I am going to read it from OCI Object Storage into an external table created with DBMS_CLOUD.
  • I have to provide a list of columns in the external table and a list of fields in the flat file.
  • N.B. Some column names end in a # symbol.  These must be put in double-quotes in the field list though this is not needed in the column list.
  • The Access Parameters section of the ORACLE_LOADER access driver that I used to create the external table becomes contents the format parameter.  I have created a JSON object to hold the various parameters.  The parameters are not exactly the same, in fact, I have added some.  See also DBMS_CLOUD Package Format Options
DROP TABLE ash_hist PURGE;
BEGIN
   DBMS_CLOUD.CREATE_EXTERNAL_TABLE(
    table_name =>'ASH_HIST',
    credential_name =>'MY_BUCKET',
    file_uri_list =>'https://objectstorage.uk-london-1.oraclecloud.com/n/lrndaxjjgnuu/b/bucket-20200505-1552/o/dba_hist_active_sess_history.txt.gz',
    format => json_object('blankasnull'      value 'true'
                         ,'compression'      value 'gzip'
                         ,'dateformat'       value 'YYYY-MM-DD/HH24:mi:ss'
                         ,'timestampformat'  value 'YYYY-MM-DD/HH24:mi:ss.ff'
                         ,'delimiter'        value '<,>'
                         ,'ignoreblanklines' value 'true'
                         ,'rejectlimit'      value '10'
                         ,'removequotes'     value 'true'
                         ,'trimspaces'       value 'lrtrim'
                         ),
    column_list => 'SNAP_ID NUMBER
,DBID NUMBER
,INSTANCE_NUMBER NUMBER
,SAMPLE_ID NUMBER
,SAMPLE_TIME TIMESTAMP(3)
,SESSION_ID NUMBER
,SESSION_SERIAL# NUMBER
,SESSION_TYPE VARCHAR2(10)
,FLAGS NUMBER
,USER_ID NUMBER
-----------------------------------------
,SQL_ID VARCHAR2(13)
,IS_SQLID_CURRENT VARCHAR2(1)
,SQL_CHILD_NUMBER NUMBER
,SQL_OPCODE NUMBER
,SQL_OPNAME VARCHAR2(64)
,FORCE_MATCHING_SIGNATURE NUMBER
,TOP_LEVEL_SQL_ID VARCHAR2(13)
,TOP_LEVEL_SQL_OPCODE NUMBER
,SQL_PLAN_HASH_VALUE NUMBER
,SQL_FULL_PLAN_HASH_VALUE NUMBER 
-----------------------------------------
,SQL_ADAPTIVE_PLAN_RESOLVED NUMBER
,SQL_PLAN_LINE_ID NUMBER
,SQL_PLAN_OPERATION VARCHAR2(64)
,SQL_PLAN_OPTIONS VARCHAR2(64)
,SQL_EXEC_ID NUMBER
,SQL_EXEC_START DATE
,PLSQL_ENTRY_OBJECT_ID NUMBER
,PLSQL_ENTRY_SUBPROGRAM_ID NUMBER
,PLSQL_OBJECT_ID NUMBER
,PLSQL_SUBPROGRAM_ID NUMBER
-----------------------------------------
,QC_INSTANCE_ID NUMBER
,QC_SESSION_ID NUMBER
,QC_SESSION_SERIAL# NUMBER
,PX_FLAGS NUMBER
,EVENT VARCHAR2(64)
,EVENT_ID NUMBER
,SEQ# NUMBER
,P1TEXT VARCHAR2(64)
,P1 NUMBER
,P2TEXT VARCHAR2(64)
-----------------------------------------
,P2 NUMBER
,P3TEXT VARCHAR2(64)
,P3 NUMBER
,WAIT_CLASS VARCHAR2(64)
,WAIT_CLASS_ID NUMBER
,WAIT_TIME NUMBER
,SESSION_STATE VARCHAR2(7)
,TIME_WAITED NUMBER
,BLOCKING_SESSION_STATUS VARCHAR2(11)
,BLOCKING_SESSION NUMBER
-----------------------------------------
,BLOCKING_SESSION_SERIAL# NUMBER
,BLOCKING_INST_ID NUMBER
,BLOCKING_HANGCHAIN_INFO VARCHAR2(1)
,CURRENT_OBJ# NUMBER
,CURRENT_FILE# NUMBER
,CURRENT_BLOCK# NUMBER
,CURRENT_ROW# NUMBER
,TOP_LEVEL_CALL# NUMBER
,TOP_LEVEL_CALL_NAME VARCHAR2(64)
,CONSUMER_GROUP_ID NUMBER
-----------------------------------------
,XID RAW(8)
,REMOTE_INSTANCE# NUMBER
,TIME_MODEL NUMBER
,IN_CONNECTION_MGMT VARCHAR2(1)
,IN_PARSE VARCHAR2(1)
,IN_HARD_PARSE VARCHAR2(1)
,IN_SQL_EXECUTION VARCHAR2(1)
,IN_PLSQL_EXECUTION VARCHAR2(1)
,IN_PLSQL_RPC VARCHAR2(1)
,IN_PLSQL_COMPILATION VARCHAR2(1)
-----------------------------------------
,IN_JAVA_EXECUTION VARCHAR2(1)
,IN_BIND VARCHAR2(1)
,IN_CURSOR_CLOSE VARCHAR2(1)
,IN_SEQUENCE_LOAD VARCHAR2(1)
,IN_INMEMORY_QUERY VARCHAR2(1) /*added 12.1*/
,IN_INMEMORY_POPULATE VARCHAR2(1) /*added 12.1*/
,IN_INMEMORY_PREPOPULATE VARCHAR2(1) /*added 12.1*/
,IN_INMEMORY_REPOPULATE VARCHAR2(1) /*added 12.1*/
,IN_INMEMORY_TREPOPULATE VARCHAR2(1) /*added 12.1*/
,CAPTURE_OVERHEAD VARCHAR2(1)
-----------------------------------------
,REPLAY_OVERHEAD VARCHAR2(1)
,IS_CAPTURED VARCHAR2(1)
,IS_REPLAYED VARCHAR2(1)
,SERVICE_HASH NUMBER
,PROGRAM VARCHAR2(64)
,MODULE VARCHAR2(64)
,ACTION VARCHAR2(64)
,CLIENT_ID VARCHAR2(64)
,MACHINE VARCHAR2(64)
,PORT NUMBER
-----------------------------------------
,ECID VARCHAR2(64)
,DBREPLAY_FILE_ID NUMBER /*added 12.1*/
,DBREPLAY_CALL_COUNTER NUMBER /*added 12.1*/
,TM_DELTA_TIME NUMBER
,TM_DELTA_CPU_TIME NUMBER
,TM_DELTA_DB_TIME NUMBER
,DELTA_TIME NUMBER
,DELTA_READ_IO_REQUESTS NUMBER
,DELTA_WRITE_IO_REQUESTS NUMBER
,DELTA_READ_IO_BYTES NUMBER
-----------------------------------------
,DELTA_WRITE_IO_BYTES NUMBER
,DELTA_INTERCONNECT_IO_BYTES NUMBER
,PGA_ALLOCATED NUMBER
,TEMP_SPACE_ALLOCATED NUMBER
,DBOP_NAME VARCHAR2(64) /*added 12.1*/
,DBOP_EXEC_ID NUMBER /*added 12.1*/
,CON_DBID NUMBER /*added 12.1*/
,CON_ID NUMBER /*added 12.1*/'
-----------------------------------------
,field_list=>'SNAP_ID,DBID,INSTANCE_NUMBER,SAMPLE_ID,SAMPLE_TIME ,SESSION_ID,"SESSION_SERIAL#",SESSION_TYPE,FLAGS,USER_ID
,SQL_ID,IS_SQLID_CURRENT,SQL_CHILD_NUMBER,SQL_OPCODE,SQL_OPNAME,FORCE_MATCHING_SIGNATURE,TOP_LEVEL_SQL_ID,TOP_LEVEL_SQL_OPCODE,SQL_PLAN_HASH_VALUE,SQL_FULL_PLAN_HASH_VALUE
,SQL_ADAPTIVE_PLAN_RESOLVED,SQL_PLAN_LINE_ID,SQL_PLAN_OPERATION,SQL_PLAN_OPTIONS,SQL_EXEC_ID,SQL_EXEC_START ,PLSQL_ENTRY_OBJECT_ID,PLSQL_ENTRY_SUBPROGRAM_ID,PLSQL_OBJECT_ID,PLSQL_SUBPROGRAM_ID
,QC_INSTANCE_ID,QC_SESSION_ID,"QC_SESSION_SERIAL#",PX_FLAGS,EVENT,EVENT_ID,"SEQ#",P1TEXT,P1,P2TEXT
,P2,P3TEXT,P3,WAIT_CLASS,WAIT_CLASS_ID,WAIT_TIME,SESSION_STATE,TIME_WAITED,BLOCKING_SESSION_STATUS,BLOCKING_SESSION
,"BLOCKING_SESSION_SERIAL#",BLOCKING_INST_ID,BLOCKING_HANGCHAIN_INFO,"CURRENT_OBJ#","CURRENT_FILE#","CURRENT_BLOCK#","CURRENT_ROW#","TOP_LEVEL_CALL#",TOP_LEVEL_CALL_NAME,CONSUMER_GROUP_ID
,XID,"REMOTE_INSTANCE#",TIME_MODEL,IN_CONNECTION_MGMT,IN_PARSE,IN_HARD_PARSE,IN_SQL_EXECUTION,IN_PLSQL_EXECUTION,IN_PLSQL_RPC,IN_PLSQL_COMPILATION
,IN_JAVA_EXECUTION,IN_BIND,IN_CURSOR_CLOSE,IN_SEQUENCE_LOAD,IN_INMEMORY_QUERY,IN_INMEMORY_POPULATE,IN_INMEMORY_PREPOPULATE,IN_INMEMORY_REPOPULATE,IN_INMEMORY_TREPOPULATE,CAPTURE_OVERHEAD
,REPLAY_OVERHEAD,IS_CAPTURED,IS_REPLAYED,SERVICE_HASH,PROGRAM,MODULE,ACTION,CLIENT_ID,MACHINE,PORT
,ECID,DBREPLAY_FILE_ID,DBREPLAY_CALL_COUNTER,TM_DELTA_TIME,TM_DELTA_CPU_TIME,TM_DELTA_DB_TIME,DELTA_TIME,DELTA_READ_IO_REQUESTS,DELTA_WRITE_IO_REQUESTS,DELTA_READ_IO_BYTES 
,DELTA_WRITE_IO_BYTES,DELTA_INTERCONNECT_IO_BYTES,PGA_ALLOCATED,TEMP_SPACE_ALLOCATED,DBOP_NAME,DBOP_EXEC_ID,CON_DBID,CON_ID'
);
END;
/
This file contains 1.4M rows in a 200Mb compressed file. If uncompressed it would be 4.6Gb. It takes about 81 seconds to perform a full scan on it.
set autotrace on timi on pages 99 lines 160
break on report
compute sum of ash_secs on report
column event format a40
column min(sample_time) format a22
column max(sample_time) format a22
select event, sum(10) ash_Secs, min(sample_time), max(sample_time)
from ash_hist
--where rownum <= 1000
group by event
order by ash_Secs desc
;
EVENT                                      ASH_SECS MIN(SAMPLE_TIME)       MAX(SAMPLE_TIME)
---------------------------------------- ---------- ---------------------- ----------------------
                                           10304530 22-MAR-20 09.59.51.125 07-APR-20 23.00.30.395
direct path read                            3258500 22-MAR-20 09.59.51.125 07-APR-20 23.00.30.395
SQL*Net more data to client                  269220 22-MAR-20 10.00.31.205 07-APR-20 22.59.30.275
direct path write temp                        32400 22-MAR-20 11.39.53.996 07-APR-20 21.43.47.329
gc cr block busy                              24930 22-MAR-20 10.51.33.189 07-APR-20 22.56.56.804
…
latch: gc element                                10 30-MAR-20 18.42.51.748 30-MAR-20 18.42.51.748
                                         ----------
sum                                        14093050

86 rows selected.

Elapsed: 00:01:21.17
We can see from the plan that it full scanned the external table in parallel.
Execution Plan
----------------------------------------------------------
Plan hash value: 4220750095

------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |  8344K|   374M|  1417  (33)| 00:00:01 |        |      |            |
|   1 |  PX COORDINATOR                      |          |       |       |            |          |        |      |            |
|   2 |   PX SEND QC (ORDER)                 | :TQ10002 |  8344K|   374M|  1417  (33)| 00:00:01 |  Q1,02 | P->S | QC (ORDER) |
|   3 |    SORT ORDER BY                     |          |  8344K|   374M|  1417  (33)| 00:00:01 |  Q1,02 | PCWP |            |
|   4 |     PX RECEIVE                       |          |  8344K|   374M|  1417  (33)| 00:00:01 |  Q1,02 | PCWP |            |
|   5 |      PX SEND RANGE                   | :TQ10001 |  8344K|   374M|  1417  (33)| 00:00:01 |  Q1,01 | P->P | RANGE      |
|   6 |       HASH GROUP BY                  |          |  8344K|   374M|  1417  (33)| 00:00:01 |  Q1,01 | PCWP |            |
|   7 |        PX RECEIVE                    |          |  8344K|   374M|  1417  (33)| 00:00:01 |  Q1,01 | PCWP |            |
|   8 |         PX SEND HASH                 | :TQ10000 |  8344K|   374M|  1417  (33)| 00:00:01 |  Q1,00 | P->P | HASH       |
|   9 |          HASH GROUP BY               |          |  8344K|   374M|  1417  (33)| 00:00:01 |  Q1,00 | PCWP |            |
|  10 |           PX BLOCK ITERATOR          |          |  8344K|   374M|  1089  (13)| 00:00:01 |  Q1,00 | PCWC |            |
|  11 |            EXTERNAL TABLE ACCESS FULL| ASH_HIST |  8344K|   374M|  1089  (13)| 00:00:01 |  Q1,00 | PCWP |            |
------------------------------------------------------------------------------------------------------------------------------

Note
-----
   - automatic DOP: Computed Degree of Parallelism is 2 because of degree limit


Statistics
----------------------------------------------------------
       2617  recursive calls
          3  db block gets
       2751  consistent gets
          0  physical reads
        728  redo size
       5428  bytes sent via SQL*Net to client
        602  bytes received via SQL*Net from client
          7  SQL*Net roundtrips to/from client
        346  sorts (memory)
          0  sorts (disk)
         86  rows processed
In the next post, I will explain how to copy the data directly from Object Storage into a regular table.

Monday, June 01, 2020

Loading a Flat File from OCI Object Storage into an Autonomous Database. Part 1. Upload to Object Storage

This blog is the first in a series of three that looks at transferring a file to Oracle Cloud Infrastructure (OCI) Object Storage, and then reading it into the database with an external table or copying it into a regular table.
Last year I wrote a blog titled Reading the Active Session History Compressed Export File in eDB360/SQLd360 as an External Table.  I set myself the challenge of doing the same thing with an Autonomous database.  I would imagine that these techniques are commonly used Oracle Cloud operations, yet I found the documentation was spread over a number of places, and it took me a while to get it right. So, I hope you find this series helpful.

Install OCI

I could just upload my data file through the browser directly into an object storage bucket, but I don't want to copy it to a Windows desktop.  That is not a good option for very large files.  Instead, I am going to install the OCI Command Line Interface onto the Linux VM where my data file resides (see OCI CLI Quickstart Guide).
I am installing this into the oracle user on a Linux VM where the Oracle database has previously been installed, so I just accepted all the defaults.
bash -c "$(curl -L https://raw.githubusercontent.com/oracle/oci-cli/master/scripts/install/install.sh)"

Set up Token-Based Authentication for OCI

I couldn't get the instructions for generating a token without a browser to work.  Instead, installed OCI on a Windows machine and generated a token there and transferred it to my Linux VM (see Token-based Authentication for the CLI).
C:\Users\david.kurtz>oci session authenticate
Enter a region (e.g. ap-mumbai-1, ap-seoul-1, ap-sydney-1, ap-tokyo-1, ca-toronto-1, eu-frankfurt-1, eu-zurich-1, sa-saopaulo-1, uk-london-1, us-ashburn-1, us-gov-ashburn-1, us-gov-chicago-1, us-gov-phoenix-1, us-langley-1, us-luke-1, us-phoenix-1): uk-london-1
    Please switch to newly opened browser window to log in!
    Completed browser authentication process!
Config written to: C:\Users\david.kurtz\.oci\config

    Try out your newly created session credentials with the following example command:

    oci iam region list --config-file C:\Users\david.kurtz\.oci\config --profile DEFAULT --auth security_token
If I run the suggested example command, I get this response with the list of OCI regions.
{
  "data": [
…
    {
      "key": "LHR",
      "name": "uk-london-1"
    },
…
  ]
}

Export OCI Profile

Now I can export the profile to a zip file
C:\Users\david.kurtz>oci session export --profile DEFAULT --output-file DEFAULT
File DEFAULT.zip already exists, do you want to overwrite it? [y/N]: y
Exporting profile: DEFAULT from config file: C:\Users\david.kurtz\.oci\config
Export file written to: C:\Users\david.kurtz\DEFAULT.zip

Import OCI Profile

I can transfer this zip file to my Linux VM and import it.
[oracle@oracle-database .oci]$ oci session import --session-archive ./DEFAULT.zip --force
Config already contains a profile with the same name as the archived profile: DEFAULT. Provide an alternative name for the imported profile: myprofile
Imported profile myprofile written to: /home/oracle/.oci/config

    Try out your newly imported session credentials with the following example command:

    oci iam region list --config-file /home/oracle/.oci/config --profile myprofile --auth security_token
I can test it by again getting the list of OCI regions.

Upload a File

I have created a bucket on OCI.
I could upload a file through the OCI web interface, but I want to use a command-line from my Linux VM
[oracle@oracle-database ~]$ oci os object put --bucket-name bucket-20200505-1552 --file /media/sf_temp/dba_hist_active_sess_history.txt.gz --disable-parallel-uploads --config-file /home/oracle/.oci/config --profile myprofile --auth security_token
Upload ID: 1ad452f7-ab49-a24b-2fe9-f55f565cdf40
Split file into 2 parts for upload.
Uploading object  [####################################]  100%
{
  "etag": "66681c40-4e11-4b73-baf9-cc1e4c3ebd5f",
  "last-modified": "Wed, 06 May 2020 15:17:03 GMT",
  "opc-multipart-md5": "MFdfU7vGZlJ5Mb4nopxtpw==-2"
}
I can see the file in the bucket via the web interface, and I can see that the size and the MD5 checksum are both correct.
In the next post, I will explain how to read the file from Object Storage using an External Table.

Monday, May 04, 2020

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