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.
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
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
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
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
In general the views above can be utilised to check for differing types of contention. This includes the items as listed.