The Buffer cache is implemented as a chain of blocks linked together under a hash value known as cache buffer chains (CBC). When a buffer is requested the hash value of the chain where the buffer is, will be calculated and then that chain is scanned by walking the chain until the buffer is found.…
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…
Oracle acquires a table lock on a child table if it’s performing modifications on the primary key column in the parent table that’s referenced by the foreign key of the child table. And these locks on the child table are full table locks (TM) i.e. these locks aren’t restricted to any row but to the…
The Row Cache or Data Dictionary Cache is a memory area in the shared pool that holds data dictionary information to reduce physical I/O on the data dictionary tables. The row cache lock is used primarily to serialize changes to the data dictionary and is waited on when a lock on a data dictionary cache…
When a user session commits, the session’s redo information needs to be flushed from memory to the redo logfile to make it permanent. At the time of commit, the user session will post the LGWR to write the log buffer (containing the current unwritten redo, including this session’s redo information) to the redo log file.…
Library cache pins are used to manage library cache concurrency. Pinning an object causes the heaps to be loaded into memory (if not already loaded). PINS can be acquired in NULL, SHARE or EXCLUSIVE modes and can be considered like a special form of lock. A wait for a “library cache pin” implies some other…
The library cache lock controls the concurrency between clients of the library cache by acquiring a lock on the object handle so that either: One client can prevent other clients from accessing the same object The client can maintain a dependency for a long time (no other client can change the object). This lock is…
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…
When writing posts on performance tuning I felt that there needs to be a my-version of description of Oracle Wait Events along with Access method and Process & Memory Architecture. Thus this series is to start the Oracle Wait events which are common in any Database Environment BUT has a lot of story. Direct Path…
Oracle Database Internal Mechanism 