Oracle Database Administration/Maintenance
Appearance
This lesson introduces Oracle database maintenance.
Objectives and Skills
[edit | edit source]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 | edit source]- 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
[edit | edit source]Activities
[edit | edit source]Tutorial
[edit | edit source]- Complete the tutorial Oracle: Monitoring and Tuning the Database.
Optimizer Statistics
[edit | edit source]- 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 | edit source]- 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 | edit source]- 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 | edit source]- 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
[edit | edit source]- Oracle: Managing Optimizer Statistics
- Oracle: Overview of the Automatic Workload Repository
- Oracle: DBA_AUTOTASK_CLIENT
References
[edit | edit source]