Oracle Database Administration/Maintenance
This lesson introduces Oracle database maintenance.
Objectives and Skills
editObjectives 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
editMultimedia
editActivities
editTutorial
edit- Complete the tutorial Oracle: Monitoring and Tuning the Database.
Optimizer Statistics
edit- 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)
edit- 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
edit- 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
edit- 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
editReferences
edit