Tuning for Oracle Contention


What Oracle calls contention is where two or more requests are competing for the same thing. The competion could involve I/O, buffers in memory or latches obtained on blocks where a second request has to wait for the release of a latch or lock by a competing request. The V$SYSTEM_EVENT view will show general information about requests waiting for the completion of other requests. Other views shown below provide more detail with respect to specific types of problems.

Contention Analysis Views

V$SYSTEM_EVENT

select substr(event,1,28) "Event",total_waits "Waits",total_timeouts "TimeOuts" from v$system_event;

Event                            Waits  TimeOuts
---------------------------- --------- ---------
Null event                           1         1
pmon timer                       27018     27017	Rollback transaction cleanup.
process startup                      5         1
rdbms ipc reply                      7         0
rdbms ipc message                  261       238
reliable message                     1         0
control file sequential read        66         0
control file parallel write         88         0
refresh controlfile command          1         0
log file sequential read             5         0	Redo log file I/O.
log file single write                5         0
log file parallel write             11         0
log file sync                        1         0
db file sequential read           1212         0	Database file I/O.
db file scattered read             116         0
db file single write                 4         0
db file parallel write               1         0
instance state change                2         0
smon timer                           6         1	Automated coalescence (depending on PCTINCREASE) and temporary segment sort space recovery.
file identify                       20         0
file open                           49         0
SQL*Net message to client           52         0	Network communications.
SQL*Net message from client         51         0

V$RESOURCE_LIMIT

select substr(resource_name,1,24) "Name",current_utilization "Current",max_utilization "Max",initial_allocation "Initial",limit_value "Limit" from v$resource_limit;

Name                       Current       Max Initial    Limit
------------------------ --------- --------- ---------- ----------
processes                        8         9         16         16
sessions                         7         9         22         22
enqueue_locks                    8        10       1307       1307
enqueue_resources                8        10       1140       1140
lm_procs                         0         0          0          0
lm_ress                          0         0          0  UNLIMITED
lm_locks                         0         0          0  UNLIMITED
dml_locks                        0         1         96         96
temporary_table_locks            0         0  UNLIMITED  UNLIMITED
transactions                     0         4         24         24
sort_segment_locks               0         1  UNLIMITED  UNLIMITED
max_rollback_segments            5         5         31         31
distributed_transactions         0         0          6          6
mts_max_servers                  0         0         16         16
parallel_max_servers             0         0          6          6

V$LATCH

select substr(name,1,39) "Name",gets,misses,sleeps from v$latch where gets!=0 or misses!=0 or sleeps!=0;

Name                                         GETS    MISSES    SLEEPS
--------------------------------------- --------- --------- ---------
process allocation                              9         0         0
session allocation                           1076         0         0	Database connection.
session switching                            2527         0         0
process group creation                       5063         0         0
session idle bit                              190         0         0
event group latch                               9         0         0
messages                                      931         0         0
enqueues                                     1251         0         0
enqueue hash chains                           636         0         0
channel handle pool latch                      16         0         0
channel operations parent latch                70         0         0
message pool operations parent latch            3         0         0
file number translation table                   9         0         0
cache buffers chains                        50053         0         0	Cache buffer activity.
cache buffer handles                           13         0         0
multiblock read objects                       280         0         0
cache buffers lru chain                      6115         0         0
Active checkpoint queue latch                 139         0         0
Checkpoint queue latch                        297         0         0	Redo log buffer activity.
redo allocation                               190         0         0
redo copy                                      12         0         0
redo writing                                  583         0         0
loader state object freelist                    5         0         0
dml lock allocation                            18         0         0
list of block allocation                       18         0         0
transaction allocation                        223         0         0
transaction branch allocation                2527         0         0
sort extent pool                               31         0         0	In memory sorting.
undo global data                              112         0         0
ktm global data                                 4         0         0
sequence cache                                  5         0         0	Sequences in cache.
row cache objects                            3352         0         0
user lock                                       2         0         0
shared pool                                  4729         0         0
library cache                                9029         0         0
library cache load lock                       208         0         0
Token Manager                                  44         0         0
Direct I/O Adaptor                              2         0         0
address list                                    1         0         0
ncodef allocation latch                      2527         0         0
job_queue_processes parameter latch          2527         0         0

V$WAITSTAT

select * from v$waitstat;

CLASS                  COUNT      TIME
------------------ --------- ---------
data block                 0         0		Database buffer cache dirty writes or inability to read from disk due to other conpeting requests.
sort block                 0         0		Not enough sort space or temporary sort either non-existent or not used.
save undo block            0         0
segment header             0         0
save undo header           0         0
free list                  0         0		Too much free space maintained for each block.
extent map                 0         0		Poorly sized extents, too much recursion, different sized extents or too small.
bitmap block               0         0		Bitmap contention.
bitmap index block         0         0
unused                     0         0
system undo header         0         0
system undo block          0         0
undo header                0         0		Not enough rollback segments or too small or inconsistent sizes across all rollback segments.
undo block                 0         0

Resolving Contention

In general the views above can be utilised to check for differing types of contention. This includes the items as listed.