Oracle Database Administration/Maintenance

This lesson introduces Oracle database maintenance.

Objectives and Skills

edit

Objectives and skills for the database maintenance portion of Oracle Database Administration I certification include:[1]

  • Database Maintenance
    • Use and manage optimizer statistics
    • Use and manage Automatic Workload Repository (AWR)
    • Use advisory framework
    • Manage Alerts and Thresholds

Readings

edit
  1. Oracle: Database 2-Day DBA Monitoring and Tuning the Database
  2. Oracle: Database 2-Day + Performance Tuning Guide Oracle Database Performance Method
  3. Oracle: Database 2-Day + Perforamce Tuning Guide Automatic Database Performance Monitoring

Multimedia

edit
  1. YouTube: Oracle Performance Tuning - Oracle Enterprise Manager - AWR

Activities

edit

Tutorial

edit
  1. Complete the tutorial Oracle: Monitoring and Tuning the Database.

Optimizer Statistics

edit
  1. Use the following query to verify that automatic optimizer statistics collection is enabled:
    SELECT * FROM DBA_AUTOTASK_CLIENT;
  2. 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';
  3. Gather statistics for the HR.REGIONS table using the following query:
    EXEC DBMS_STATS.GATHER_TABLE_STATS('HR', 'REGIONS');
  4. Verify that statistics were updated using the following query:
    SELECT OWNER, TABLE_NAME, LAST_ANALYZED FROM DBA_TABLES WHERE OWNER = 'HR';
  5. Gather statistics for all objects in the HR schema using the following query:
    EXEC DBMS_STATS.GATHER_SCHEMA_STATS('HR');
  6. 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';
  7. 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)

edit
  1. Use Enterprise Manager Database Control / Server / Automatic Workload Repository / Snapshots to select a snapshot. Review the corresponding snapshot report.
  2. 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

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

Alerts and Thresholds

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

See Also

edit

References

edit