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;yum install javacurl http://www.dominicgiles.com/swingbench/swingbench261082.zip -o swingbench.zip
unzip swingbench.zipexecute dbms_workload_repository.modify_snapshot_settings(interval => 15);CREATE TABLESPACE SOE DATAFILE SIZE 10M AUTOEXTEND ON NEXT 1M;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 -vTest 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_IDEXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','OFF');./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.>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.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.
- Frank Pachot: An Oracle Auto Index function to drop secondary indexes - what is a “secondary” index?
- "documented as 'Deletes all the indexes, except the ones used for constraints, from a schema or a table.'"
- Tim Hall: Automatic Indexing (DBMS_AUTO_INDEX) in Oracle Database 19c: Drop Secondary Indexes
- "If you are feeling particularly brave, the DROP_SECONDARY_INDEXES procedure will drop all indexes except those used for constraints…This leaves you with a clean slate, so automatic indexing can make all your indexing decisions for you."
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('SOE','');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'); 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
- see also Richard Foote: Oracle 19c Automatic Indexing: Configuration
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:00Test 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!
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.
DBA_AUTO_INDEX_STATISTICS reports a summary of the automatic indexing task. Confirming the number of indexes built.
- See Frank Pachot: How 19c Auto Indexes are named?
- See Franck Pachot: 19c Auto Index: the dictionary views
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.
- see Richard Foote: Oracle 19c Automatic Indexing: Index Created But Not Actually Used
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.
- Frank Pachot: How to drop an index created by Oracle 19c Auto Indexing?
- Julian Dontchef: Automatic Index Optimization in Oracle Database 20c
- Richard Foote: Oracle 19c Automatic Indexing: Dropping Automatic Indexes
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.
@go-faster.co.uk on BlueSky
Posts
