SET SERVEROUTPUT ON SIZE 1000000 declare num number := 1; v_object_id number; begin while num <= 500 loop select object_id into v_object_id from tts_binhu.auto_idx_test where object_id=trunc(dbms_random.value(0,10000)); dbms_output.put_line(‘v_object_id:’||nvl(v_object_id,0)); num := num + 1; end loop; end;
select * from dba_indexes where owner =’TTS_BINHU’ and table_name=’AUTO_IDX_TEST’
測試
SELECT DBMS_AUTO_INDEX.report_activity() FROM dual;
GENERAL INFORMATION ------------------------------------------------------------------------------- Activity start : 29-10月-2019 14:29:44 Activity end : 30-10月-2019 14:29:44 Executions completed : 3 Executions interrupted : 0 Executions with fatal error : 0 -------------------------------------------------------------------------------
SUMMARY (AUTO INDEXES) ------------------------------------------------------------------------------- Index candidates : 2 Indexes created (visible / invisible) : 2 (2 / 0) Space used (visible / invisible) : 7.34 MB (7.34 MB / 0 B) Indexes dropped : 0 SQL statements verified : 6 SQL statements improved (improvement factor) : 5 (1419.1x) SQL plan baselines created : 0 Overall improvement factor : 1183x -------------------------------------------------------------------------------
SUMMARY (MANUAL INDEXES) ------------------------------------------------------------------------------- Unused indexes : 0 Space used : 0 B Unusable indexes : 0 -------------------------------------------------------------------------------
INDEX DETAILS ------------------------------------------------------------------------------- 1. The following indexes were created: ------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------- | Owner | Table | Index | Key | Type | Properties | ---------------------------------------------------------------------------------------------------- | TTS_BINHU | AUTO_IDX_TEST | SYS_AI_0nfqz048chrwz | OBJECT_ID | B-TREE | NONE | | TTS_BINHU | AUTO_IDX_TEST | SYS_AI_5afppmx4u3fjp | OBJECT_NAME,OBJECT_TYPE | B-TREE | NONE | ---------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------
VERIFICATION DETAILS ------------------------------------------------------------------------------- 1. The performance of the following statements improved: ------------------------------------------------------------------------------- Parsing Schema Name : SYS SQL ID : 6h02v1c3jm99z SQL Text : select * from tts_binhu.auto_idx_test where object_name = 'AUTO_IDX_TEST' and object_type='TABLE' Improvement Factor : 1423.7x
Execution Statistics: ----------------------------- Original Plan Auto Index Plan ---------------------------- ---------------------------- Elapsed Time (s): 715640 36386 CPU Time (s): 492167 127 Buffer Gets: 59872 3 Optimizer Cost: 395 4 Disk Reads: 1422 2 Direct Writes: 0 0 Rows Processed: 0 0 Executions: 42 1
- With Auto Indexes ----------------------------- Plan Hash Value : 1880695109
------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 132 | 4 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | AUTO_IDX_TEST | 1 | 132 | 4 | 00:00:01 | | * 2 | INDEX RANGE SCAN | SYS_AI_5afppmx4u3fjp | 1 | | 3 | 00:00:01 | -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("OBJECT_NAME"='AUTO_IDX_TEST' AND "OBJECT_TYPE"='TABLE')
Notes ----- - Dynamic sampling used for this statement ( level = 11 )
------------------------------------------------------------------------------- Parsing Schema Name : SYS SQL ID : 6mmkppzn4pyd8 SQL Text : SELECT OBJECT_ID FROM TTS_BINHU.AUTO_IDX_TEST WHERE OBJECT_ID=TRUNC(DBMS_RANDOM.VALUE(0,10000)) Improvement Factor : 1418x
Execution Statistics: ----------------------------- Original Plan Auto Index Plan ---------------------------- ---------------------------- Elapsed Time (s): 720172 22908 CPU Time (s): 717822 9823 Buffer Gets: 2838 2 Optimizer Cost: 412 1 Disk Reads: 0 1 Direct Writes: 0 0 Rows Processed: 1 1 Executions: 2 1
Predicate Information (identified by operation id): ------------------------------------------ * 1 - access("OBJECT_ID"=TRUNC("DBMS_RANDOM"."VALUE"(0,10000)))
Notes ----- - Dynamic sampling used for this statement ( level = 11 )
------------------------------------------------------------------------------- Parsing Schema Name : SYS SQL ID : acdnsvfpdjnw0 SQL Text : select * from tts_binhu.auto_idx_test where object_name = 'MV_WH_COMPANY' and object_type='TABLE' Improvement Factor : 1418x
Execution Statistics: ----------------------------- Original Plan Auto Index Plan ---------------------------- ---------------------------- Elapsed Time (s): 535958 874 CPU Time (s): 469385 0 Buffer Gets: 60976 4 Optimizer Cost: 395 4 Disk Reads: 0 1 Direct Writes: 0 0 Rows Processed: 43 1 Executions: 43 1
- With Auto Indexes ----------------------------- Plan Hash Value : 1880695109
------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 264 | 4 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | AUTO_IDX_TEST | 2 | 264 | 4 | 00:00:01 | | * 2 | INDEX RANGE SCAN | SYS_AI_5afppmx4u3fjp | 1 | | 3 | 00:00:01 | -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("OBJECT_NAME"='MV_WH_COMPANY' AND "OBJECT_TYPE"='TABLE')
Notes ----- - Dynamic sampling used for this statement ( level = 11 )
------------------------------------------------------------------------------- Parsing Schema Name : SYS SQL ID : c3c69a1dkwucj SQL Text : select * from tts_binhu.auto_idx_test where object_id=trunc(dbms_random.value(0,10000)) Improvement Factor : 1418x
Execution Statistics: ----------------------------- Original Plan Auto Index Plan ---------------------------- ---------------------------- Elapsed Time (s): 397948 17176 CPU Time (s): 397844 158 Buffer Gets: 1420 3 Optimizer Cost: 412 2 Disk Reads: 0 7 Direct Writes: 0 0 Rows Processed: 2 1 Executions: 1 1
- With Auto Indexes ----------------------------- Plan Hash Value : 3717726382
------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost | Time | ------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 132 | 2 | 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED | AUTO_IDX_TEST | 1 | 132 | 2 | 00:00:01 | | * 2 | INDEX RANGE SCAN | SYS_AI_0nfqz048chrwz | 1 | | 1 | 00:00:01 | -------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id): ------------------------------------------ * 2 - access("OBJECT_ID"=TRUNC("DBMS_RANDOM"."VALUE"(0,10000)))
Notes ----- - Dynamic sampling used for this statement ( level = 11 )
------------------------------------------------------------------------------- Parsing Schema Name : SYS SQL ID : dt1tymnjfa8m6 SQL Text : select * from tts_binhu.auto_idx_test where object_id=trunc(dbms_random.value(0,10000)) Improvement Factor : 1418x
Execution Statistics: ----------------------------- Original Plan Auto Index Plan ---------------------------- ---------------------------- Elapsed Time (s): 5006616 190 CPU Time (s): 4992671 190 Buffer Gets: 17018 3 Optimizer Cost: 412 2 Disk Reads: 0 0 Direct Writes: 0 0 Rows Processed: 9 1 Executions: 12 1
