Tuning

The biggest responsibility of a dba is to ensure that the Oracle database is tuned properly.
The database should be tuned for the following reasons:

  • The reponses times might be waisting the users valuable time (users waiting for responses).
  • Optimize hardware usage in order to reduce the Total Cost of Ownership.
  •  Enable the system to keep up with the speed business is conducted.

The response times off course also depend on the underlying hardware (computer and storage subsystem) so tuning will not solve all problms.

Towards what should the tuning effort be directed?

  1. Database design (of course this can't be changed for packaged solutions) although flaws can be communicated to the vendors.
    But poor system performance is usually the result of a poor database design.
  2. Application tuning
    Approximately 80% of all Oracle system performance problems are resolved by coding optimal SQL.
  3. Memory tuning.
    Properly size the database buffers and shared memory (hared_pool, buffer cache, log buffer, etc) by looking at your wait events, buffer hit ratios, system swapping and paging, etc. You may also want to pin large objects into memory to prevent frequent reloads.
  4. Disk I/O tuning.
    Database files needs to be properly sized and placed to provide maximum disk subsystem throughput. Also look for frequent disk sorts, full table scans, missing indexes, row chaining, data fragmentation, etc.
  5. Eliminate Database Contention.
    Study database locks, latches and wait events carefully and eliminate the contention  where possible.
  6. Tune the Operating System:
    Monitor and tune operating system CPU, I/O and memory utilization.

What tools/utilities does Oracle provide to assist with performance tuning?

Oracle provide the following tools/ utilities to assist with performance monitoring and tuning:

  • ADDM (Automated Database Diagnostics Monitor) introduced in Oracle 10g
  • TKProf
  • Statspack
  • Oracle Enterprise Manager - Tuning Pack (cost option)