ATP auto indexing consumes more than 16GB in SYSAUX
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 ...
2