latch: cache buffers chains – Oracle wait event

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

Oracle Locking Mechanism – Latches & Enqueues

This article is part of Oracle Performance Tuning Series and is created to describe Oracle’s locking mechanism. A lock is a resource that you need to hold to get access to the resource. Oracle has two kind of locks: enqueues and latches, we would now focus on these locking mechanism individually. Enqueues Enqueues are sophisticated…

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…

enq: TM – contention – Oracle wait event

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…

Oracle TKProf Usage

The TKProf facility accepts as input a SQL trace file and produces a formatted output file. TKPROF filename_source filename_output EXPLAIN=[username/password] SYS=[YES/No] TABLE=[tablename] filename_source: The trace file that needs to be formatted.filename_output: The output file where the formatted file will be written.EXPLAIN: This option causes TKProf to generate an execution plan for each SQL statement in…

How to enable 10046 Trace (SQL_TRACE) to troubleshoot performance issues

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…

Queries to Monitor Oracle Sessions

This post is to provide a set of queries to monitor any user session on an Oracle Instance more efficiently. Below query (Lets name it Query1 for the sake of discussion) gives you an overall status of what all sessions are active, what wait event they are waiting on and what are they executing. set…