Thank you for supporting the Cloud Customer Connect Community in 2024. It's a gift to work with you!

Look back
You're almost there! Please answer a few more questions for access to the Applications content. Complete registration
Interested in joining? Complete your registration by providing Areas of Interest here. Register

ATP auto indexing consumes more than 16GB in SYSAUX

edited Aug 1, 2023 8:26PM in Autonomous Database 8 comments

Dear valued Oracle DBA community,

after enabling Oracle Autonomous auto indexing feature some months ago for testing purposes, I recently realized that since this time SYSAUX space usage is continously increasing. It is already consuming ~16GB space in a very small testing project with just some tables.


My Analysis:

SELECT  occupant_name, space_usage_kbytes/1048576 spaceused, schema_name FROM v$sysaux_occupants ORDER BY spaceused DESC;
"Item"		"SPACEUSED"		"Schema"
"SM/ADVISOR"	15.99029541015625	"SYS"
"SM/AWR"	1.9677734375		"SYS"
...


SELECT TASK_NAME, COUNT(*) CNT FROM DBA_ADVISOR_OBJECTS GROUP BY TASK_NAME ORDER BY CNT DESC;
"TASK_NAME"			"CNT"
"SYS_AUTO_INDEX_TASK"		1536028
"SYS_AI_VERIFY_TASK"		1314294
"SYS_AUTO_SPM_EVOLVE_TASK"	50
"ADDM:949429955_2_4612"		4
"ADDM:949429955_2_4577"		4
"ADDM:949429955_2_4620"		4
"ADDM:949429955_2_4925"		4
...

Howdy, Stranger!

Log In

To view full details, sign in.

Register

Don't have an account? Click here to get started!