Event 10046 is used to gather extended SQL_TRACE information for Oracle sessions. This article describes how to enable event 10046 to get extended information to troubleshoot a performance issue. To gather 10046 trace at the session level: alter session set tracefile_identifier=’10046′; alter session set timed_statistics = true;alter session set statistics_level=all;alter session set max_dump_file_size = unlimited;alter…
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…
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…
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 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 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…
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…
An index skip scan occurs when the initial column of a composite index is “skipped” or not specified in the query. Basically skip scanning lets a composite index be split logically into smaller sub-indexes, making every sub-index a viable candidate to be used by optimizer to come up with an efficient plan. The number of logical…
Index Fast Full Scans are similar to Index Full Scans i.e they read index blocks in unsorted order, as they exist on disk.This scan does not use the index to probe the table, but reads the index instead of the table, essentially using the index itself as a table.
Oracle Database Internal Mechanism 