Oracle 19c 新特性详解：自动索引（Automatic Indexing）的输出报告范例

PDB1@ZRP>set serveroutput on 
PDB1@ZRP>declare 
2 report clob := null; 
3 begin 
4 report := DBMS_AUTO_INDEX.REPORT_ACTIVITY( 
5 activity_start => TO_TIMESTAMP(‘2019-02-17 22:51:00‘, ‘yyyy-mm-dd hh24:mi:ss‘), 
6 activity_end => TO_TIMESTAMP(‘2019-02-17 22:53:07‘, ‘yyyy-mm-dd hh24:mi:ss‘), 
7 type => ‘HTML‘, 
8 section => ‘ALL‘, 
9 level => ‘ALL‘); 
10 dbms_output.put_line(report); 
11 end; 
12 /

GENERAL INFORMATION

Activity start

Activity end

Executions completed

Executions interrupted

Executions with fatal error

: 17-FEB-2019 22:51:00

: 17-FEB-2019 22:53:07

: 1

: 0

: 0

SUMMARY (AUTO INDEXES)

Index candidates

Indexes created (visible / invisible)

Space used (visible / invisible)

Indexes dropped

SQL statements verified

SQL statements improved (improvement factor)

SQL plan baselines created

Overall improvement factor

: 1

: 1 (1 / 0)

: 45.09_MB (45.09_MB / 0_B)

: 0

: 2

: 2 (44948.5x)

: 0

: 44948.5x

SUMMARY (MANUAL INDEXES)

Unused indexes

Space used

Unusable indexes

: 0

: 0_B

: 0

INDEX DETAILS

1. The following indexes were created:
*: invisible

VERIFICATION DETAILS

1. The performance of the following statements improved:

Parsing Schema Name

SQL ID

SQL Text

Improvement Factor

: ZRP

: 6664xv8fak0q5

: select sharded from test where object_id=123456

: 44919x

PLANS SECTION

- Original

Plan Hash Value

Notes

- optimizer_use_stats_on_conventional_dml = yes 
- With Auto Indexes

Plan Hash Value

Predicate Information (identified by operation id):

• 2 - access("OBJECT_ID"=123456)

Notes

- Dynamic sampling used for this statement ( level = 11 ) 

Parsing Schema Name

SQL ID

SQL Text

Improvement Factor

: ZRP

: 6d4vdmp6hq73p

: select created from test where object_id=234

: 44978x

PLANS SECTION

- Original

Plan Hash Value

Notes

- optimizer_use_stats_on_conventional_dml = yes 
- With Auto Indexes

Plan Hash Value

Predicate Information (identified by operation id):

• 2 - access("OBJECT_ID"=234)

Notes

- Dynamic sampling used for this statement ( level = 11 ) 

ERRORS

No errors found.

PL/SQL procedure successfully completed.

PDB1@ZRP>spool off