Peformance Tuning Basic Concepts

Notes from Chap. 6 Oracle Performance. (Rick book: Oracle Essentials, O'REILLY)

bad performance: recognized easily
good performace: absence of bad performance

Three basic steps:
- Define performance problems
- Oracle Server software
- Server machine

Beyond Oracel server software and machive level, performance problem may lie in the design of
application or the database itself

Oracle Server Performance

Looking bottlenecks in Oracle server software itself
- OEM; lower than optimal use of internal resource
- own scripts: monitoring or auditing
- session waiting unnecessarily
- dynamic perform views: V$SYSTEM_EVENT; V$SESSION_WAIT;
pinpoit; foucusing on ; the largest source of contension
- source of problem: e.g.:
* lower-than-expected database buffer cache hit ratio
- simply increase the initialization parameter DB_BLOCK_BUFFERS
* long time to fetch database rows from the disk: calls for more investigation into the usage of the overall resource, more difficult to locate the specific problem

Machine Resource Usage
CPU
Memory
DISK I/O
Network bandwidth

E.g.
run out of physical memory; swap areas of memory out to the disk; cause I/O bottleneck

- identify machine resource usage
UNIX: sar, iostat, vmstat