Performing Database Maintenance
Scope
This domain covers routine maintenance: ADR and alert logs, automatic maintenance tasks, optimizer statistics, segment advisor, health checks, and space monitoring. Questions often distinguish a maintenance task from a tuning task.
Core rules
- The ADR stores alert logs, trace files, incident dumps, and health reports. ADRCI lists homes, shows alerts, and packages incidents. The alert log is text or XML under ADR.
- Automatic Maintenance Tasks run in maintenance windows and include optimizer statistics gathering, Segment Advisor, and automatic SQL tuning. AUTOTASK controls them; DBA_AUTOTASK_TASK shows status.
- DBMS_STATS gathers optimizer statistics; ANALYZE is legacy and should not be used for normal stats gathering. STATISTICS_LEVEL controls statistics collection.
- Space management uses DBA_TABLESPACE_USAGE_METRICS, DBA_SEGMENTS, and DBA_FREE_SPACE. Segment Advisor recommends shrinking or reorganizing segments.
Worked example
EXEC DBMS_STATS.GATHER_TABLE_STATS('HR','EMPLOYEES', cascade=>TRUE); refreshes table and index statistics for the optimizer. Separately, DBMS_ADVISOR or the Segment Advisor task can recommend shrinking a segment with unused space. Gathering statistics does not shrink the segment or tune SQL.
Traps
ANALYZE is not the current statistics tool. Automatic maintenance is not continuous tuning. The alert log is not a performance report. Shrinking a segment is not the same as gathering statistics.