Oracle Database Administration/Performance

This lesson introduces Oracle database performance management.

Objectives and Skills

edit

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

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

Readings

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

Multimedia

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

Activities

edit

Automatic Memory Management

edit
  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

edit
  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

edit
  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

edit
  • sql-bench[2] A standalone SQL Benchmark test tools that comes with MySQL.

References

edit