Oracle Database Administration/Performance

This lesson introduces Oracle database performance management.

Objectives and Skills

Objectives and skills for the performance management portion of Oracle Database Administration I certification include:

• Performance Management
  • Use Automatic Memory Management
  • Use Memory Advisors
  • Troubleshoot invalid and unusable objects

Readings

1. Oracle: Database 2-Day DBA Monitoring and Tuning the Database

Multimedia

1. YouTube: Oracle Database Performance Tuning for Admins and Architects

Activities

Automatic Memory Management

1. Use Enterprise Manager Database Control / Server / Memory Advisors to view and configure Automatic Memory Management.
2. Disable Automatic Memory Management.
3. View available SGA and PGA settings.
4. Enable Automatic Memory Management.

Memory Advisors

1. Use the following queries to view memory advisor values:
   SELECT * FROM V$PGA_TARGET_ADVICE;
SELECT * FROM V$SGA_TARGET_ADVICE;
SELECT * FROM V$MEMORY_TARGET_ADVICE;

Invalid and Unusable Objects

1. Use the following query to identify invalid objects:
   SELECT OWNER, OBJECT_TYPE, OBJECT_NAME FROM DBA_OBJECTS WHERE STATUS = 'INVALID';
2. Use the following query to identify unusable indexes:
   SELECT OWNER, INDEX_NAME FROM DBA_INDEXES WHERE STATUS = 'UNUSABLE';

See Also

• sql-bench A standalone SQL Benchmark test tools that comes with MySQL.

References

← Maintenance

Oracle Database Administration

Moving Data →