Oracle 12c – Design the database layout for optimal performance

Optimization for performance is normally focused on disk usage because this is by far the slowest component of any RDBMS.    Increasing the rate of reads and writes form disk dramatically increases the performance of most all database suites.  The standard answer to optimize performance in an Oracle 12c build is to use ASM (Automatic Storage Management), because oracle ASM automatically stripes data across all available disk groups.   This elevates the majority of problems with disk performance.   ASM automatically provides the following benefits over traditional storage configuration:

 

  1. Striping
  2. Mirroring
  3. Online storage reconfiguration and dynamic rebalancing.
  4. Managed file creation and deletion

 

If your organization is still use traditional storage options we should consider implement the following to optimize disk performance:

 

  1. Stipe everything across every disk with RAID 1 mirror.
  2. Move Archive Logs to Different Disks – Avoid DW and ARC process disk contention to improve performance.
  3. Move Redo Logs to Separate Disk – High level of updates in most OLTP systems cause a lot of redo log writes. Thus avoid disk contention by moving to separate disks.
  4. Continually monitor database performance to determine disk contention on various database functionality and redirect storage to avoid contention.

 

 

Larry Catt

OCP