Author: mac

This latch comes into play when user processes are attempting to access or update the cached data dictionary values. Solutions Problem: To determine if the row cache is being used efficiently, execute the following SQL. If the ratio is not close to 1 then some tuning required. SELECT parameter, sum(gets), sum(getmisses), 100*sum(gets – getmisses) /…

Library cache pins are used to manage library cache concurrence. 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…

This event controls the concurrency between clients of the library cache. It acquires 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 (for example, no other client can change the object) This lock is…

This latch serializes access to the objects in the library cache. Every time a SQL statement, a PL/SQL block or a stored object (procedure, package, function, trigger) is parsed or executed, this latch is acquired. Problem – Fragmentation of the shared pool Excessive execution of a statement can cause library cache contention. Many versions of…

Often a write to disk. Solutions Consistent Slow Performance Improve I/O Investigate if the application can be changed to perform fewer writes. Abnormal Variations in Performance Investigate potential problems with the operating systems or I/O sub-system. Investigate contention at the disk/controller level.

When a request for rows from a table is issued, Oracle may determine through the cost-based optimizer, which index access path is best for finding rows in a table. During this index lookup process, if another session is inserting or updating data, which causes updates to that index and an index block split, the first…

The session is looking for a consistent read version of data but cannot find it in the local cache. The local instance has made a request to other RAC instances for the data and is waiting on its return. Solutions Collect more information about the average time the instances are waiting for this type of…

The session is looking for a consistent read version of data but cannot find it in the local cache. The local instance has made a request to other RAC instances for the data and is waiting on its return. Solutions Collect more information about the average time the instances are waiting for this type of…

This wait, also known as global cache buffer busy prior to Oracle 10g, specifies the time the remote instance locally spends accessing the requested data block. This wait event is very similar to the buffer busy waits wait event in a single-instance database. Problem High wait times on this event are often the result of:…

Problem This will happen if there is no free buffer in the buffer cache for the session to read a new block into. All buffer gets have been suspended. This will frequently occur because DBWR is not writing out buffers fast enough. This could happen when a file was read-only and is now read-write. All…