Writing redo records to the redo log files from the log buffer. Wait Time: Time it takes for the I/Os to complete. Even though redo records are written in parallel, the parallel write is not complete until the last I/O is on disk. Parameter Description files Number of files to be written blocks Number of…
A session waits on this event when it is requesting an exclusive mutex pin for a cursor object and it must wait because the resource is busy. The mutex pin for a cursor object can be busy either because a session is already holding it exclusive, or there are one or more sessions which are holding…
Processes enter “wait for broadcast mesg recovery queue transition” when cleaning up a publisher channel handle to a RELIABLE broadcast channel. The broadcasted message is in the recovery queue of another channel handle (for example, ch2). Process enters this wait, if the message is yet to be removed from the recovery queue of the ch2…
Processes enter “wait for broadcast mesg queue transition” when cleaning up a publisher channel handle to a RELIABLE broadcast channel. The publisher is responsible for moving the message to the free queue, but it cannot do so until the message is in the done queue. If the message is still not in the done queue,…
A session is waiting on another process to allocate a system change number (SCN). If the foreground timed out waiting on a process to get the SCN, the foreground will get the SCN.
A session has issued a statement ALTER SYSTEM SET DISPATCHER = string and is waiting for the dispatchers to get started. Wait Time: The session will wait 1 / 100 of a second and verify if the new dispatchers have started, else the session will wait again
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.…
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…
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 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…
Oracle Database Internal Mechanism 