# Oracle Database Administration/Maintenance

This lesson introduces Oracle database maintenance.

## Objectives and Skills

Objectives and skills for the database maintenance portion of Oracle Database Administration I certification include:
* Database Maintenance
** Use and manage optimizer statistics
** Use and manage Automatic Workload Repository (AWR)
** Use advisory framework
** Manage Alerts and Thresholds

## Readings

* Oracle: Database 2-Day DBA Monitoring and Tuning the Database
* Oracle: Database 2-Day + Performance Tuning Guide Oracle Database Performance Method
* Oracle: Database 2-Day + Perforamce Tuning Guide Automatic Database Performance Monitoring

## Multimedia

* YouTube: Oracle Performance Tuning - Oracle Enterprise Manager - AWR

## Activities
### Tutorial

* Complete the tutorial Oracle: Monitoring and Tuning the Database.

### Optimizer Statistics

* Use the following query to verify that automatic optimizer statistics collection is enabled: SELECT * FROM DBA_AUTOTASK_CLIENT;
* Use the following queries to determine when statistics were last updated for tables, indexes, and columns in the HR schema: SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR'; SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE OWNER = 'HR'; SELECT OWNER, TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE OWNER = 'HR';
* Gather statistics for the HR.REGIONS table using the following query: EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'REGIONS');
* Verify that statistics were updated using the following query: SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR';
* Gather statistics for all objects in the HR schema using the following query: EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
* Verify that statistics were updated using the following queries: SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR'; SELECT OWNER, INDEX_NAME, LAST_ANALYZED FROM DBA_INDEXES WHERE OWNER = 'HR'; SELECT OWNER, TABLE_NAME, COLUMN_NAME, LAST_ANALYZED FROM DBA_TAB_COLUMNS WHERE OWNER = 'HR';
* If you want to manually update statistics for all objects in the database, you can use the following query, but note that it may take a long time to run and will impact database performance while it runs: EXEC DBMS_STATS.GATHER_DATABASE_STATS;

### Automatic Workload Repository (AWR)

* Use Enterprise Manager Database Control / Server / Automatic Workload Repository / Snapshots to select a snapshot. Review the corresponding snapshot report.
* Use Enterprise Manager Database Control / Server / Automatic Workload Repository / Edit to view options for snapshot collection and retention. If desired, modify snapshot settings.

### Advisory Framework

* Use Enterprise Manager Database Control / Advisor Central to view available advisors and tasks. View the current status for each advisor.
* Use Enterprise Manager Database Control / Server / Automated Maintenance Tasks to view the task schedule. Configure changes if desired.

### Alerts and Thresholds

* Use Enterprise Manager Database Control / Metric and Policy Settings to view current metric thresholds and policies.
* Use Enterprise Manager / Setup to view and configure notification methods.

## See Also

* Oracle: Managing Optimizer Statistics
* Oracle: Overview of the Automatic Workload Repository
* Oracle: DBA_AUTOTASK_CLIENT