| This is going to be the real work in progress; humble apologies if you came here looking for answers. I know UNIX tuning inside & out. For instance, there are basically four types of bottlenecks that a UNIX system can suffer from (CPU, Memory, I/O, and network). The trick is identifying and eliminating those bottlenecks. I'm looking for similar knowledge gems for Oracle tuning. Hopefully, through my research, I'll find things of interest and post them here. More than likely, this will end up as a starting point with links to other useful documents, pages, etc. For now, I'll be keeping track of statements (probably no shit ones, like this first one) that I need to keep in mind while studying Oracle: - Poor performance can almost always be tracked back to a process waiting for a resource that another process is using.
Oracle DBA 101, page 318
Potential contention areas: | Area: | Description: | Examined by: |
|---|
| CPU | The CPU is the thing responsible for executing Oracle, application, and sql code. If that's bottlenecked, then work isn't being done. | Excessive number of lru latches. Not sure how likely this is... sar, etc. | | File I/O | Probably the biggest area for contention; try putting an index and its datafile on the same disk and see what happens. | v$filestat, sar | | Latches | A mechanism that prevents multiple processes from accessing specific internal memory (?) resources simultaneously. | v$latch and v$latchname | | Library cache | The area in the shared pool in which sql statements are parsed; reparsed. If the same statement has to be reparsed all the time, performance will suffer. | v$librarycache | | Locks | A mechanism that prevents multiple processes from modifying the same data structure simultaneously. | v$lock | | Dictionary cache | The area in the shared pool that caches data dictionary information | v$rowcache | | | |
Performance examination order: - Buffer size (is that the library cache?)
- File I/O (see v$filestat, sar, etc)
- Latches
|
|