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 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').

Oracle has a bunch of events that it can wait for.

Wait time is recorded in v$system_event and v$session_event.

Statspack

analyze

buffer busy waits

hit ratio

Logical versus physical I/O

The Oracle optimizer