Automatic Index Report

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	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))

1 -

SEL$1

E -	USE_AUTO_INDEXES

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	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
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
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		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))

1 -

SEL$1

E -	use_auto_indexes

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	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))

1 -

SEL$1

E -	USE_AUTO_INDEXES

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'
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	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	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	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