Library Cache Pin – Oracle Wait Event

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…

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…

Oracle Wait Events

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. db file…

db file scattered read – oracle wait event

DB File scattered read wait event occurs when an oracle process waits for a physical read of multiple oracle blocks from the disk. This wait identifies that a full scan (Full Table Scan or Index fast full scan) is occurring BUT during this read the blocks are read into memory locations that are not physically…

db file sequential reads – Oracle wait event

DB file sequential reads wait event comes under the User I/O wait class. In any busy system this is one of the most common wait event and normally appears in the top 5 wait events. In this post we’ll understand whats happening during this wait event. In simple words, Db file sequential reads wait event…

12c New Features – Invisible Columns

There can be several situations where you want to test the impact of dropping a column from a table, till 11gR2 Oracle provided an option to mark a column as UNUSED before dropping a column, and do ALTER TABLE … DROP UNUSED COLUMNS at a later time but marking a column UNUSED is a irreversible…