Automatic Index Report

GENERAL INFORMATION

Activity start	: 05-AUG-2019 17:54:19
Activity end	: 06-AUG-2019 17:54:19
Executions completed	: 7
Executions interrupted	: 0
Executions with fatal error	: 0

SUMMARY (AUTO INDEXES)

Index candidates	: 1
Indexes created (visible / invisible)	: 1 (1 / 0)
Space used (visible / invisible)	: 11.53_MB (11.53_MB / 0_B)
Indexes dropped	: 0
SQL statements verified	: 7
SQL statements improved (improvement factor)	: 6 (621.4x)
SQL plan baselines created	: 0
Overall improvement factor	: 503.3x

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
DINESH	TEST	SYS_AI_462c33pq4z49n	ID	B-TREE	NONE

VERIFICATION DETAILS

1. The performance of the following statements improv ed:

Parsing Schema Name	: DINESH
SQL ID	: 5dxpkmrqpxcdw
SQL Text	: select /+ DINESH_004 / id,name from test where id=:a
Improvement Factor	: 195.6x

Execution Statistics:

	Original Plan	Auto Index Plan
Elapsed Time (s):	540093	23141
CPU Time (s):	541305	7702
Buffer Gets:	88000	27
Optimizer Cost:	481	4
Disk Reads:	0	5
Direct Writes:	0	0
Rows Processed:	50	1
Executions:	50	1

PLANS SECTION

- Original

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				481
1	. TABLE ACCESS FULL	TEST	1	16	481	00:00:01

Notes

- optimizer_use_stats_on_conventional_dml = yes

- With Auto Indexes

Plan Hash Value	: 530923333

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	16	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	1	16	4	00:00:01
* 2	.. INDEX RANGE SCAN	SYS_AI_462c33pq4z49n	1		3	00:00:01

Predicate Information (identified by operation id):

2 - access("ID"=:A)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax Error (1))

1 -

SEL$1

E -	DINESH_004

Notes

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

Parsing Schema Name	: DINESH
SQL ID	: 5hvvnujzg3vrs
SQL Text	: select /+ DINESH_005 / id,name from test where id=:a
Improvement Factor	: 440x

Execution Statistics:

	Original Plan	Auto Index Plan
Elapsed Time (s):	5548819	21145
CPU Time (s):	5490978	5016
Buffer Gets:	880000	22
Optimizer Cost:	481	4
Disk Reads:	0	0
Direct Writes:	0	0
Rows Processed:	500	1
Executions:	500	1

PLANS SECTION

- Original

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				481
1	. TABLE ACCESS FULL	TEST	1	16	481	00:00:01

Notes

- optimizer_use_stats_on_conventional_dml = yes

- With Auto Indexes

Plan Hash Value	: 530923333

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	16	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	1	16	4	00:00:01
* 2	.. INDEX RANGE SCAN	SYS_AI_462c33pq4z49n	1		3	00:00:01

Predicate Information (identified by operation id):

2 - access("ID"=:A)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax Error (1))

1 -

SEL$1

E -	DINESH_005

Notes

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

Parsing Schema Name	: DINESH
SQL ID	: 7n1q3tz5ta31n
SQL Text	: SELECT ID,NAME FROM TEST WHERE ID=12345
Improvement Factor	: 1760x

Execution Statistics:

	Original Plan	Auto Index Plan
Elapsed Time (s):	357010	108
CPU Time (s):	356396	0
Buffer Gets:	35200	4
Optimizer Cost:	481	4
Disk Reads:	0	1
Direct Writes:	0	0
Rows Processed:	20	1
Executions:	20	1

PLANS SECTION

- Original

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				481
1	. TABLE ACCESS FULL	TEST	1	16	481	00:00:01

Notes

- optimizer_use_stats_on_conventional_dml = yes

- With Auto Indexes

Plan Hash Value	: 530923333

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	16	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	1	16	4	00:00:01
* 2	.. INDEX RANGE SCAN	SYS_AI_462c33pq4z49n	1		3	00:00:01

Predicate Information (identified by operation id):

2 - access("ID"=12345)

Notes

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

Parsing Schema Name	: DINESH
SQL ID	: buvzhfnj0nwxt
SQL Text	: SELECT /+ DINESH_002 / ID,NAME FROM TEST WHERE ID=12345< /td>
Improvement Factor	: 1760x

Execution Statistics:

	Original Plan	Auto Index Plan
Elapsed Time (s):	344772	65
CPU Time (s):	344547	0
Buffer Gets:	35200	4
Optimizer Cost:	481	4
Disk Reads:	0	0
Direct Writes:	0	0
Rows Processed:	20	1
Executions:	20	1

PLANS SECTION

- Original

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				481
1	. TABLE ACCESS FULL	TEST	1	16	481	00:00:01

Notes

- optimizer_use_stats_on_conventional_dml = yes

- With Auto Indexes

Plan Hash Value	: 530923333

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	16	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	1	16	4	00:00:01
* 2	.. INDEX RANGE SCAN	SYS_AI_462c33pq4z49n	1		3	00:00:01

Predicate Information (identified by operation id):

2 - access("ID"=12345)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax Error (1))

1 -

SEL$1

E -	DINESH_002

Notes

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

Parsing Schema Name	: DINESH
SQL ID	: cknq5hxfwuvup
SQL Text	: SELECT /+ DINESH_003 / ID,NAME FROM TEST WHERE ID=12345< /td>
Improvement Factor	: 1760x

Execution Statistics:

	Original Plan	Auto Index Plan
Elapsed Time (s):	342172	68
CPU Time (s):	341097	68
Buffer Gets:	35200	4
Optimizer Cost:	481	4
Disk Reads:	0	0
Direct Writes:	0	0
Rows Processed:	20	1
Executions:	20	1

PLANS SECTION

- Original

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				481
1	. TABLE ACCESS FULL	TEST	1	16	481	00:00:01

Notes

- optimizer_use_stats_on_conventional_dml = yes

- With Auto Indexes

Plan Hash Value	: 530923333

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	16	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	1	16	4	00:00:01
* 2	.. INDEX RANGE SCAN	SYS_AI_462c33pq4z49n	1		3	00:00:01

Predicate Information (identified by operation id):

2 - access("ID"=12345)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (E - Syntax Error (1))

1 -

SEL$1

E -	DINESH_003

Notes

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

Parsing Schema Name	: DINESH
SQL ID	: cxx8g8auaa2jj
SQL Text	: select id,name from test where id=12345
Improvement Factor	: 1764.5x

Execution Statistics:

	Original Plan	Auto Index Plan
Elapsed Time (s):	46281	68
CPU Time (s):	44790	0
Buffer Gets:	3529	4
Optimizer Cost:	481	4
Disk Reads:	0	0
Direct Writes:	0	0
Rows Processed:	2	1
Executions:	2	1

PLANS SECTION

- Original

Plan Hash Value	: 1357081020

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT				481
1	. TABLE ACCESS FULL	TEST	1	16	481	00:00:01

Notes

- optimizer_use_stats_on_conventional_dml = yes

- With Auto Indexes

Plan Hash Value	: 530923333

Id	Operation	Name	Rows	Bytes	Cost	Time
0	SELECT STATEMENT		1	16	4	00:00:01
1	. TABLE ACCESS BY INDEX ROWID BATCHED	TEST	1	16	4	00:00:01
* 2	.. INDEX RANGE SCAN	SYS_AI_462c33pq4z49n	1		3	00:00:01

Predicate Information (identified by operation id):

2 - access("ID"=12345)

Notes

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

ERRORS

No errors found.