LIN@KKB> SELECT dbms_auto_index.report_activity(TO_TIMESTAMP('2019-06-18 16:40:00', 'YYYY-MM-DD hh24:mi:ss'), -- 开始时间(eg. sysdate-1)
2 null, -- 结束时间
3 'html', -- 报告种类 (TEXT,HTML,XML)
4 'all', -- 出力报告内容 (ALL,SUMMARY,INDEX_DETAILS,VERIFICATION_DETAILS,ERRORS)
5 'all') -- Level (BASIC,TYPICAL,ALL)
6 report FROM dual;
REPORT
------------------------------------------------------------------------------------------------------------------------
Automatic Index Report
GENERAL INFORMATION
Activity start |
: 18-JUN-2019 16:40:00 |
Activity end |
: 18-JUN-2019 17:10:33 |
Executions completed |
: 2 |
Executions interrupted |
: 0 |
Executions with fatal error |
: 0 |
SUMMARY (AUTO INDEXES)
Index candidates |
:
0 |
Indexes created
(visible
/ invisible)
|
:
1
(1
/
0)
|
Space used
(visible
/ invisible)
|
:
3.15_MB
(3.15_MB
/
0_B)
|
Indexes dropped |
:
0 |
SQL statements verified |
:
10 |
SQL statements improved
(improvement factor)
|
:
10
(2337.1x)
|
SQL plan baselines created |
:
0 |
Overall improvement factor |
:
2337.1x |
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 |
LIN |
TEST_OBJ_AI |
SYS_AI_829s8wj1jb4y4 |
OBJECT_ID |
B-TREE |
NONE |
VERIFICATION DETAILS
1. The performance of the followi
ng statements improved:
Parsing Schema Name |
: LIN |
SQL ID |
: 0f8z69z7jcp71 |
SQL Text |
: select /*+ USE_AUTO_INDEXES */ object_name from test_obj_ai
where object_id=1000 |
Improvement Factor |
: 2337x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
9428 |
1202 |
CPU Time (s):
|
8399 |
501 |
Buffer Gets:
|
2337 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
4 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
1 |
1 |
Executions:
|
1 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Oper
ation |
Name |
Row
s |
Bytes |
Cost
th>
| Time |
0 |
SELECT STATEMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id<
/th>
| Operation |
Name
|
Rows |
Bytes
| Cost |
Time |
0 |
SELECT STATEMENT |
|
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=1000)
Hint Report (identified by operation id / Query Block Name / Obje
ct Alias):
Total hints for statement: 1
(E - Syntax Error (1))
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
Parsing Schema Name |
: LIN |
SQL ID |
: 10k4qgkyf2vm9 |
SQL Text |
: select object_name from test_obj_ai where object_
id=101 |
Improvement Factor |
: 2336x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
17979 |
41 |
CPU Time (s):
|
17075 |
41 |
Buffer Gets:
|
4672 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
2 |
1 |
Executions:
|
2 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Operation |
N
ame |
Rows |
Bytes
|
Cost |
Time |
0 |
SELECT STATEMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id |
Operati
on |
Name |
Rows
th>
| Bytes |
Cost |
Time |
0 |
SELECT STATEMENT |
|
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=101)
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
Parsing Schema Name |
: LIN |
SQL ID |
: 1pzm5v0j0w4wh |
SQL Text |
: select object_name from test_obj_ai where object_id=100
td>
|
Improvement Factor |
: 2337x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
9413 |
39 |
CPU Time (s):
|
9068 |
39 |
Buffer Gets:
|
2337 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
1 |
1 |
Executions:
|
1 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Operation |
Name |
Rows |
Bytes |
C
ost |
Time |
0 |
SELECT STATEMEN
T |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id |
Operation |
Name |
Rows |
Byt
es |
Cost |
Time
th>
|
0 |
SELECT STATEMENT |
|
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=100)
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
REPORT
------------------------------------------------------------------------------------------------------------------------
Parsing Schema Name |
: LIN |
SQL ID |
: 4ph5a5m5skqhz |
SQL Text |
: select /*+ use_auto_indexes */ object_name from test_obj_ai where o
bject_id = 10 |
Improvement Factor |
: 2337x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
9425 |
38 |
CPU Time (s):
|
8432 |
38 |
Buffer Gets:
|
2337 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
1 |
1 |
Executions:
|
1 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Operation |
Name |
Rows |
Bytes |
Cost |
Tim
e |
0 |
SELECT STATEMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id |
Op
eration |
Name |
R
ows |
Bytes |
Cost
|
Time |
0 |
SELECT STATEMENT
td>
| |
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=10)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
(E - Syntax Error (1))
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
Parsing Schema Name |
: LIN |
SQL ID |
: 5a41x15j0nz69 |
SQL Text |
: select /*+ USE_AUTO_INDEXES */ object_name from test_obj_ai w
here object_id=1 |
Improvement Factor |
: 2337x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
9477 |
37 |
CPU Time (s):
|
9282 |
37 |
Buffer Gets:
|
2337 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
1 |
1 |
Executions:
|
1 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Operation
th>
| Name |
Rows |
Bytes |
Cost |
Time |
0 |
SELECT STATEMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id |
Operation |
Name |
Rows |
Bytes |
C
ost |
Time |
0 |
SELECT STATEMEN
T |
|
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=1)
Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1
(E - Syntax Error (1))
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
Parsing Schema Name |
: LIN |
SQL ID |
: 8h1r5cs6gtk10 |
SQL Text |
: select object_name from test_obj_ai where object_id='999'
td>
|
Improvement Factor |
: 2338.5x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
19268 |
37 |
CPU Time (s):
|
17271 |
37 |
Buffer Gets:
|
4677 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
2 |
1 |
Executions:
|
2 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Operation |
Name
| Rows |
Bytes |
Cost |
Time |
0 |
SELECT STAT
EMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id |
Operation |
Name |
Rows |
Bytes |
Cost |
Ti
me |
0 |
SELECT STATEMENT |
|
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=999)
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
Parsing Schema Name |
: LIN |
SQL ID |
: 920q656bfbs87 |
SQL Text |
: select object_name from test_obj_ai where object_id='11' |
Improvement Factor |
: 2337x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
9521 |
37 |
CPU Time (s):
|
9293 |
36 |
Buffer Gets:
|
2337 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
1 |
1 |
Executions:
|
1 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Oper
ation |
Name |
Row
s |
Bytes |
Cost
th>
| Time |
0 |
SELECT STATEMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
REPORT
------------------------------------------------------------------------------------------------------------------------
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id<
/th>
| Operation |
Name
|
Rows |
Bytes
| Cost |
Time |
0 |
SELECT STATEMENT |
|
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=11)
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
Parsing Schema Name |
: LIN |
SQL ID |
: 9ffdunca53nfc |
SQL Text |
: select object_name from test_obj_ai where o
bject_id='1' |
Improvement Factor |
: 2337x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
9605 |
35 |
CPU Time (s):
|
8859 |
35 |
Buffer Gets:
|
2337 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
1 |
1 |
Executions:
|
1 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Operation |
Name |
Rows |
B
ytes |
Cost |
Time
|
0 |
SELECT STATEMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id |
Ope
ration |
Name |
Ro
ws |
Bytes |
Cost<
/th>
| Time |
0 |
SELECT STATEMENT
| |
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=1)
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
Parsing Schema Name |
: LIN |
SQL ID |
: 9r4snusbhyn82 |
SQL Text |
: select object_name /*+ USE_AUTO_INDEXES */ from test_ob
j_ai where object_id=1 |
Improvement Factor |
: 2337x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
9391 |
50 |
CPU Time (s):
|
8369 |
31 |
Buffer Gets:
|
2337 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
1 |
1 |
Executions:
|
1 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Opera
tion |
Name |
Rows
|
Bytes |
Cost
| Time |
0 |
SELECT STATEMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id
th>
| Operation |
Name<
/th>
| Rows |
Bytes |
Cost |
Time |
0 |
SELECT ST
ATEMENT |
|
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=1)
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
Parsing Schema Name |
: LIN |
SQL ID |
: gzz3n7h7jmv3n |
SQL Text |
: select object_name from test_obj_ai where obj
ect_id='10' |
Improvement Factor |
: 2337x |
Execution Statistics:
|
Original Plan
|
Auto Index Plan
|
Elapsed Time (s):
|
9473 |
35 |
CPU Time (s):
|
8341 |
35 |
Buffer Gets:
|
2337 |
3 |
Optimizer Cost:
|
32 |
2 |
Disk Reads:
|
0 |
0 |
Direct Writes:
|
0 |
0 |
Rows Processed:
|
1 |
1 |
Executions:
|
1 |
1 |
PLANS SECTION
- Original
Plan Hash Value |
:
1599946467 |
Id |
Operation |
Name |
Rows |
By
tes |
Cost |
Time<
/th>
|
0 |
SELECT STATEMENT |
|
|
|
32 |
|
1 |
.
TABLE ACCESS STORAGE FULL
|
TEST_OBJ_AI |
6 |
474 |
32 |
00:00:01 |
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- With Auto Indexes
Plan Hash Value |
:
3630496781 |
Id |
Oper
ation |
Name |
Row
s |
Bytes |
Cost
th>
| Time |
0 |
SELECT STATEMENT |
|
1 |
79 |
2 |
00:00:01 |
1 |
.
TABLE ACCESS BY INDEX ROWID BATCHED
|
TEST_OBJ_AI |
1 |
79 |
2 |
00:00:01 |
* 2 |
..
INDEX RANGE SCAN
|
SYS_AI_829s8wj1jb4y4 |
1 |
|
1 |
00:00:01 |
Predicate Information (identified by operation id):
- 2 -
access("OBJECT_ID"=10)
Notes
-
optimizer_use_stats_on_conventional_dml =
yes
- Dynamic sampling used for this statement
( level = 11 )
ERRORS
No errors found.
LIN@KKB> spool off