René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback -

Tuning Oracle

Tuning tries to identify bottlenecks that take too long and tries to reduce these bottleneck's time. These bottlenecks can be in the hardware (network, memory, cpu) and the software (application, operating system, database).
Generally, Oracle's time used for an operation (such as a select statement) to complete is composed of time executing (=CPU time) and time spent waiting (=Waiting time).
The aim of tuning is to reduce the sum of CPU time and waiting time. This sum is the response time for an operation.

CPU time

CPU time can further be divided into
  • User time
    CPU used for Oracle.
  • System time
    CPU used for operating system time
Due to the granularity of measurement, OS User time + OS System time might be greater than CPU used..
User time then can again be divided into:
  • Parsing time
    stats: parse count, execute count, session curser cache count, session cursor cache hits. ( v$sysstat, v$sesstat)
  • recursive cpu usage
    How much CPU is used for executing row cache statements (data dictionary lookup, PL/SQL programms).
  • Other
Used CPU time can be found through v$sesstat, v$sysstat and v$mystat (name = 'CPU used by this session').

Waiting time

Oracle has a bunch of events that it can wait for.
Wait time is recorded in v$system_event and v$session_event.