Scripts:
select interval, enabled from DBA_AUTOTASK_SCHEDULE_CONTROL where TASK_NAME='Auto Index Task';
exec dbms_auto_task_admin.modify_autotask_setting('Auto Index Task', 'INTERVAL',180);
EXEC DBMS_AUTO_INDEX.drop_secondary_indexes('SOE',null);
EXEC DBMS_AUTO_INDEX.drop_auto_indexes('SOE',NULL,true);
select * from dba_indexes where owner='SOE';
commit;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_SCHEMA','SOE');
select * from DBA_AUTO_INDEX_CONFIG;
EXEC DBMS_AUTO_INDEX.CONFIGURE('AUTO_INDEX_MODE','IMPLEMENT');
select * from dba_auto_index_executions;
select * from dba_auto_index_ind_actions;
SET LONG 1000000 LONGCHUNKSIZE 1000000
SET LINESIZE 1000 PAGESIZE 0
SET TRIM ON TRIMSPOOL ON
SET ECHO OFF FEEDBACK OFF
SELECT DBMS_AUTO_INDEX.report_activity(
activity_start => SYSTIMESTAMP-1/48,
activity_end => SYSTIMESTAMP,
type => 'TEXT',
section => 'ALL',
"LEVEL" => 'ALL')
FROM dual;
SELECT DBMS_AUTO_INDEX.report_activity(
activity_start => TO_TIMESTAMP('202009261630','YYYYMMDDHH24MI'),
activity_end => TO_TIMESTAMP('202009261730','YYYYMMDDHH24MI'),
type => 'TEXT',
section => 'ALL',
"LEVEL" => 'ALL')
FROM dual;
SQL> SET LONG 1000000 LONGCHUNKSIZE 1000000SQL> SET LINESIZE 1000 PAGESIZE 0
SQL> SET TRIM ON TRIMSPOOL ON
SQL> SET ECHO OFF FEEDBACK OFF
GENERAL INFORMATION
Activity start : 26-SEP-2020 16:30:00
Activity end : 26-SEP-2020 17:30:00
Executions completed : 4
Executions interrupted : 0
Executions with fatal error : 0
SUMMARY (AUTO INDEXES)
Index candidates : 8
Indexes created (visible / invisible) : 8 (5 / 3)
Space used (visible / invisible) : 150.08 MB (134.22 MB / 15.86 MB)
Indexes dropped : 0
SQL statements verified : 14
SQL statements improved (improvement factor) : 6 (55.3x)
SQL plan baselines created : 0
Overall improvement factor : 19.5x
SUMMARY (MANUAL INDEXES)
Unused indexes : 0
Space used : 0 B
Unusable indexes : 0
INDEX DETAILS
1. The following indexes were created:
*: invisible
| Owner | Table | Index | Key | Type | Properties |
| SOE | PRODUCT_INFORMATION | * SYS_AI_b9k5zyq0mjwf5 | CATEGORY_ID | B-TREE | NONE |
VERIFICATION DETAILS
1. The performance of the following statements improved:
Parsing Schema Name : SOE
SQL ID : 29qp10usqkqh0
SQL Text : SELECT TT.ORDER_TOTAL, TT.SALES_REP_ID, TT.ORDER_DATE,
CUSTOMERS.CUST_FIRST_NAME, CUSTOMERS.CUST_LAST_NAME FROM
(SELECT ORDERS.ORDER_TOTAL, ORDERS.SALES_REP_ID,
ORDERS.ORDER_DATE, ORDERS.CUSTOMER_ID, RANK() OVER (ORDER
BY ORDERS.ORDER_TOTAL DESC) SAL_RANK FROM ORDERS WHERE
ORDERS.SALES_REP_ID = :B1 ...
Improvement Factor : 2578.6x
Execution Statistics:
Original Plan Auto Index Plan
Elapsed Time (s): 84415551 24028
CPU Time (s): 40248498 8008
Buffer Gets: 8257774 27
Optimizer Cost: 8947 9
Disk Reads: 0 2
Direct Writes: 0 0
Rows Processed: 10543 0
Executions: 385 1
PLANS SECTION
- Original
Plan Hash Value : 3619984409
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | | | 8443 | |
| 1 | HASH JOIN | | 1459 | 124015 | 8443 | 00:00:01 |
| 2 | NESTED LOOPS | | 1459 | 124015 | 8443 | 00:00:01 |
| 3 | NESTED LOOPS | | 1459 | 124015 | 8443 | 00:00:01 |
| 4 | STATISTICS COLLECTOR | | | | | |
| 5 | VIEW | | 1459 | 94835 | 5524 | 00:00:01 |
| 6 | WINDOW SORT PUSHED RANK | | 1459 | 35016 | 5524 | 00:00:01 |
| 7 | TABLE ACCESS FULL | ORDERS | 1459 | 35016 | 5523 | 00:00:01 |
| 8 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 | 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 20 | 2 | 00:00:01 |
| 10 | TABLE ACCESS FULL | CUSTOMERS | 1 | 20 | 2 | 00:00:01 |
Notes
- optimizer_use_stats_on_conventional_dml = yes
- This is an adaptive plan
- With Auto Indexes
Plan Hash Value : 3900469033
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 2 | 170 | 9 | 00:00:01 |
| 1 | NESTED LOOPS | | 2 | 170 | 9 | 00:00:01 |
| 2 | NESTED LOOPS | | 2 | 170 | 9 | 00:00:01 |
| * 3 | VIEW | | 2 | 130 | 5 | 00:00:01 |
| * 4 | WINDOW SORT PUSHED RANK | | 2 | 48 | 5 | 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS | 2 | 48 | 4 | 00:00:01 |
| * 6 | INDEX RANGE SCAN | SYS_AI_gbwwy984mc1ft | 1 | | 3 | 00:00:01 |
| * 7 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 20 | 2 | 00:00:01 |
Predicate Information (identified by operation id):
* 3 - filter("TT"."SAL_RANK"<=10)
* 4 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("ORDERS"."ORDER_TOTAL") DESC )<=10)
* 6 - access("ORDERS"."SALES_REP_ID"=:B1)
* 7 - access("CUSTOMERS"."CUSTOMER_ID"="TT"."CUSTOMER_ID")
Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )
- This is an adaptive plan
Parsing Schema Name : SOE
SQL ID : 7hk2m2702ua0g
SQL Text : WITH NEED_TO_PROCESS AS (SELECT ORDER_ID, CUSTOMER_ID
FROM ORDERS WHERE ORDER_STATUS <= 4 AND WAREHOUSE_ID =
:B1 AND ROWNUM < 10 ) SELECT O.ORDER_ID, OI.LINE_ITEM_ID,
OI.PRODUCT_ID, OI.UNIT_PRICE, OI.QUANTITY, O.ORDER_MODE,
O.ORDER_STATUS, O.ORDER_TOTAL, O.SALES_REP_ID,
O.PROMOTION_ID, C.CUSTOMER_ID...
Improvement Factor : 1.9x
Execution Statistics:
Original Plan Auto Index Plan
Elapsed Time (s): 3638001 267113
CPU Time (s): 1098401 28911
Buffer Gets: 185829 157
Optimizer Cost: 197 40
Disk Reads: 543 16
Direct Writes: 0 0
Rows Processed: 835 50
Executions: 835 1
PLANS SECTION
- Original
Plan Hash Value : 2307454521
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | | | 199 | |
| 1 | HASH JOIN OUTER | | 45 | 5580 | 199 | 00:00:01 |
| 2 | NESTED LOOPS OUTER | | 45 | 5580 | 199 | 00:00:01 |
| 3 | STATISTICS COLLECTOR | | | | | |
| 4 | HASH JOIN | | 9 | 936 | 163 | 00:00:01 |
| 5 | NESTED LOOPS | | 9 | 936 | 163 | 00:00:01 |
| 6 | STATISTICS COLLECTOR | | | | | |
| 7 | HASH JOIN | | 9 | 504 | 145 | 00:00:01 |
| 8 | NESTED LOOPS | | 9 | 504 | 145 | 00:00:01 |
| 9 | STATISTICS COLLECTOR | | | | | |
| 10 | VIEW | | 9 | 117 | 127 | 00:00:01 |
| 11 | COUNT STOPKEY | | | | | |
| 12 | TABLE ACCESS FULL | ORDERS | 10 | 130 | 127 | 00:00:01 |
| 13 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 43 | 2 | 00:00:01 |
| 14 | INDEX UNIQUE SCAN | ORDER_PK | 1 | | 1 | 00:00:01 |
| 15 | TABLE ACCESS FULL | ORDERS | 1 | 43 | 2 | 00:00:01 |
| 16 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 48 | 2 | 00:00:01 |
| 17 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 | 00:00:01 |
| 18 | TABLE ACCESS FULL | CUSTOMERS | 1 | 48 | 2 | 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS | 5 | 100 | 4 | 00:00:01 |
| 20 | INDEX RANGE SCAN | ORDER_ITEMS_PK | 5 | | 2 | 00:00:01 |
| 21 | TABLE ACCESS FULL | ORDER_ITEMS | 5 | 100 | 4 | 00:00:01 |
Notes
- optimizer_use_stats_on_conventional_dml = yes
- This is an adaptive plan
- With Auto Indexes
Plan Hash Value : 420986395
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 5 | 620 | 40 | 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 5 | 620 | 40 | 00:00:01 |
| 2 | NESTED LOOPS | | 1 | 104 | 36 | 00:00:01 |
| 3 | NESTED LOOPS | | 1 | 56 | 34 | 00:00:01 |
| 4 | VIEW | | 1 | 13 | 32 | 00:00:01 |
| * 5 | COUNT STOPKEY | | | | | |
| * 6 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS | 1 | 13 | 32 | 00:00:01 |
| * 7 | INDEX RANGE SCAN | SYS_AI_3z00frhp9vd91 | 1436 | | 3 | 00:00:01 |
| 8 | TABLE ACCESS BY INDEX ROWID | ORDERS | 1 | 43 | 2 | 00:00:01 |
| * 9 | INDEX UNIQUE SCAN | ORDER_PK | 1 | | 1 | 00:00:01 |
| 10 | TABLE ACCESS BY INDEX ROWID | CUSTOMERS | 1 | 48 | 2 | 00:00:01 |
| * 11 | INDEX UNIQUE SCAN | CUSTOMERS_PK | 1 | | 1 | 00:00:01 |
| 12 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDER_ITEMS | 5 | 100 | 4 | 00:00:01 |
| * 13 | INDEX RANGE SCAN | ORDER_ITEMS_PK | 5 | | 2 | 00:00:01 |
Predicate Information (identified by operation id):
* 5 - filter(ROWNUM<10)
* 6 - filter("ORDER_STATUS"<=4)
* 7 - access("WAREHOUSE_ID"=:B1)
* 9 - access("NTP"."ORDER_ID"="O"."ORDER_ID")
* 11 - access("C"."CUSTOMER_ID"="O"."CUSTOMER_ID")
* 13 - access("OI"."ORDER_ID"="O"."ORDER_ID")
Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )
- This is an adaptive plan
Parsing Schema Name : SOE
SQL ID : 7t0959msvyt5g
SQL Text : SELECT ORDER_ID, ORDER_DATE, ORDER_MODE, CUSTOMER_ID,
ORDER_STATUS, ORDER_TOTAL, SALES_REP_ID, PROMOTION_ID,
WAREHOUSE_ID, DELIVERY_TYPE, COST_OF_DELIVERY,
WAIT_TILL_ALL_AVAILABLE, DELIVERY_ADDRESS_ID,
CUSTOMER_CLASS, CARD_ID, INVOICE_ADDRESS_ID FROM ORDERS
WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1
Improvement Factor : 1243.5x
Execution Statistics:
Original Plan Auto Index Plan
Elapsed Time (s): 617460332 1319853
CPU Time (s): 285638621 7876
Buffer Gets: 65646755 34
Optimizer Cost: 5798 12
Disk Reads: 547 1
Direct Writes: 0 0
Rows Processed: 4776 0
Executions: 3084 1
PLANS SECTION
- Original
Plan Hash Value : 335441244
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | | | 5520 | |
| 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL | ORDERS | 9 | 846 | 5520 | 00:00:01 |
Notes
- optimizer_use_stats_on_conventional_dml = yes
- With Auto Indexes
Plan Hash Value : 1119417062
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 9 | 846 | 12 | 00:00:01 |
| * 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS | 9 | 846 | 12 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | SYS_AI_5p2zapcmkj174 | 9 | | 3 | 00:00:01 |
Predicate Information (identified by operation id):
* 1 - filter(ROWNUM<:B1)
* 3 - access("CUSTOMER_ID"=:B2)
Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )
Parsing Schema Name : SOE
SQL ID : 7ws837zynp1zv
SQL Text : SELECT CARD_ID, CUSTOMER_ID, CARD_TYPE, CARD_NUMBER,
EXPIRY_DATE, IS_VALID, SECURITY_CODE FROM CARD_DETAILS
WHERE CUSTOMER_ID = :B2 AND ROWNUM < :B1
Improvement Factor : 752.3x
Execution Statistics:
Original Plan Auto Index Plan
Elapsed Time (s): 1058848442 797551
CPU Time (s): 483284263 7401
Buffer Gets: 83307704 31
Optimizer Cost: 2755 4
Disk Reads: 210 2
Direct Writes: 0 0
Rows Processed: 12452 0
Executions: 8267 1
PLANS SECTION
- Original
Plan Hash Value : 2597291669
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | | | 2755 | |
| 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL | CARD_DETAILS | 9 | 360 | 2755 | 00:00:01 |
- With Auto Indexes
Plan Hash Value : 1494990609
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 2 | 80 | 4 | 00:00:01 |
| * 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | CARD_DETAILS | 2 | 80 | 4 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | SYS_AI_dt4w4vr174j9m | 1 | | 3 | 00:00:01 |
Predicate Information (identified by operation id):
* 1 - filter(ROWNUM<:B1)
* 3 - access("CUSTOMER_ID"=:B2)
Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )
Parsing Schema Name : SOE
SQL ID : g81cbrq5yamf5
SQL Text : SELECT ADDRESS_ID, CUSTOMER_ID, DATE_CREATED,
HOUSE_NO_OR_NAME, STREET_NAME, TOWN, COUNTY, COUNTRY,
POST_CODE, ZIP_CODE FROM ADDRESSES WHERE CUSTOMER_ID =
:B2 AND ROWNUM < :B1
Improvement Factor : 1327.8x
Execution Statistics:
Original Plan Auto Index Plan
Elapsed Time (s): 1944433630 952727
CPU Time (s): 950376552 7150
Buffer Gets: 195665481 31
Optimizer Cost: 4691 4
Disk Reads: 15 2
Direct Writes: 0 0
Rows Processed: 17204 0
Executions: 11353 1
PLANS SECTION
- Original
Plan Hash Value : 1286489376
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | | | 4690 | |
| 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS FULL | ADDRESSES | 9 | 657 | 4690 | 00:00:01 |
- With Auto Indexes
Plan Hash Value : 1527416462
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 2 | 146 | 4 | 00:00:01 |
| * 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED | ADDRESSES | 2 | 146 | 4 | 00:00:01 |
| * 3 | INDEX RANGE SCAN | SYS_AI_4bz3nuupj3kt5 | 1 | | 3 | 00:00:01 |
Predicate Information (identified by operation id):
* 1 - filter(ROWNUM<:B1)
* 3 - access("CUSTOMER_ID"=:B2)
Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )
Parsing Schema Name : SOE
SQL ID : gkxxkghxubh1a
SQL Text : SELECT ORDER_MODE, ORDERS.WAREHOUSE_ID,
SUM(ORDER_TOTAL), COUNT(1) FROM ORDERS, WAREHOUSES WHERE
ORDERS.WAREHOUSE_ID = WAREHOUSES.WAREHOUSE_ID AND
WAREHOUSES.WAREHOUSE_ID = :B1 GROUP BY
CUBE(ORDERS.ORDER_MODE, ORDERS.WAREHOUSE_ID)
Improvement Factor : 7.5x
Execution Statistics:
Original Plan Auto Index Plan
Elapsed Time (s): 31754462 247888
CPU Time (s): 14927141 12874
Buffer Gets: 2926232 1235
Optimizer Cost: 5528 1223
Disk Reads: 0 3
Direct Writes: 0 0
Rows Processed: 1152 8
Executions: 144 1
PLANS SECTION
- Original
Plan Hash Value : 2692802960
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | | | 5527 | |
| 1 | SORT GROUP BY | | 2 | 36 | 5527 | 00:00:01 |
| 2 | GENERATE CUBE | | 2 | 36 | 5527 | 00:00:01 |
| 3 | SORT GROUP BY | | 2 | 36 | 5527 | 00:00:01 |
| 4 | NESTED LOOPS | | 1431 | 25758 | 5526 | 00:00:01 |
| 5 | INDEX UNIQUE SCAN | WAREHOUSES_PK | 1 | 4 | 1 | 00:00:01 |
| 6 | TABLE ACCESS FULL | ORDERS | 1431 | 20034 | 5525 | 00:00:01 |
Notes
- optimizer_use_stats_on_conventional_dml = yes
- With Auto Indexes
Plan Hash Value : 3836151239
| Id | Operation | Name | Rows | Bytes | Cost | Time |
| 0 | SELECT STATEMENT | | 2 | 36 | 1223 | 00:00:01 |
| 1 | SORT GROUP BY | | 2 | 36 | 1223 | 00:00:01 |
| 2 | GENERATE CUBE | | 2 | 36 | 1223 | 00:00:01 |
| 3 | SORT GROUP BY | | 2 | 36 | 1223 | 00:00:01 |
| 4 | NESTED LOOPS | | 1436 | 25848 | 1222 | 00:00:01 |
| * 5 | INDEX UNIQUE SCAN | WAREHOUSES_PK | 1 | 4 | 1 | 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID BATCHED | ORDERS | 1436 | 20104 | 1221 | 00:00:01 |
| * 7 | INDEX RANGE SCAN | SYS_AI_3z00frhp9vd91 | 1261 | | 4 | 00:00:01 |
Predicate Information (identified by operation id):
* 5 - access("WAREHOUSES"."WAREHOUSE_ID"=:B1)
* 7 - access("ORDERS"."WAREHOUSE_ID"=:B1)
Notes
- optimizer_use_stats_on_conventional_dml = yes
- Dynamic sampling used for this statement ( level = 11 )
ERRORS
No errors found.
