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

[email protected]>set serveroutput on

[email protected]>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 /

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

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

Unused indexes

Space used

Unusable indexes

: 0

: 0_B

: 0

1. The following indexes were created:

*: invisible

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

- Original

Plan Hash Value

: 1357081020

Notes

- optimizer_use_stats_on_conventional_dml = yes- With Auto Indexes

Plan Hash Value

: 159453698

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

- Original

Plan Hash Value

: 1357081020

Notes

- optimizer_use_stats_on_conventional_dml = yes- With Auto Indexes

Plan Hash Value

: 159453698

Predicate Information (identified by operation id):

2 - access("OBJECT_ID"=234)

Notes

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

No errors found.

PL/SQL procedure successfully completed.