enq: TX – index contention – Oracle wait event

If you performed lot of DMLs (specially Deletes) on a table, you may have lots of empty leaf blocks in the associated indexes on that table. These empty leaf blocks are left in the b-tree structure AND attached to the freelist. On subsequent inserts, the process might need to split a block so it takes…

Buffer busy waits – Oracle Wait Event

Buffer busy wait happens when a session wants to access a database block in the buffer cache but it cannot as the buffer is “busy”. The two main cases where this can occur are: Another session is reading the block into the buffer Another session holds the buffer in an incompatible mode to our request…

Table Access by Rowid

Rowid is an internal representation of the storage location of data. The rowid of a row specifies the data file and data block containing the row and the location of the row in that block. Locating a row by specifying its rowid is the fastest way to retrieve a single row because it specifies the…