Oracle Database Administration/Performance

This lesson introduces Oracle database performance management.

Objectives and SkillsEdit

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

ReadingsEdit

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

MultimediaEdit

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

ActivitiesEdit

Automatic Memory ManagementEdit

  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 AdvisorsEdit

  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 ObjectsEdit

  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 AlsoEdit

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

ReferencesEdit