Top 5 Timed Foreground Events - control file sequential read

control file sequential read

This wait events occurs If one has all their control files on a disk with high disk I/O then access to control file for updating the SCN etc may result in this wait.Reading from the control file. This happens in many cases. For example, while
1)Making a backup of the control files
2)Sharing information (between instances) from the control file
3)Reading other blocks from the control files
4)Reading the header block

Wait Time: The wait time is the elapsed time of the read

In the above u see control file sequential read is on the top 5 list with wait class as System I/O. In this case if has come on the top as the no of transactions hitting the system have crossed 1000 Transactions Per Second(TPS) and the database is an high commit oriented OLTP system. That is the reason we see log file sync as one of the top 5 wait events.

Solution: Identify the Control file locations and try to place them on faster disks or less activity disks.

Top 5 Timed Foreground Events - Buffer Exterminate

Buffer Exterminate - Buffer exterminate wait event is caused when using Oracle's "automatic memory management" (AMM) when the MMON process shrinks the data buffer cache to re-allocate that RAM for another SGA region. The experience has indicated that AMM resize operations can hurt overall database performance especially the OLTP environments, and you may want to consider turning off AMM which will relieve the buffer exterminate waits, and manually adjusting your SGA regions.


If you see this in the TOP 5 Times Events, One has to look into v$sga_resize_ops and v$memory_resize_ops and see how many times it is occurring and effecting the performance of your database. If one sees more events of these and especially during your peak times of database one has to turn of the feature adjusting manually the corresponding SGA and PGA sizes.

If you want to analyze Oracle's use of memory and look at various memory resizing operations you can use the V$MEMORY_RESIZE_OPS view. This view contains a list of the last 800 SGA resize requests handled by Oracle. Here is an example:

SELECT parameter, initial_size, target_size, start_time FROM v$memory_resize_ops WHERE initial_size > = 0 and final_size > = 0 ORDER BY parameter, start_time;

This  shows that Oracle has made a number of changes to the database cache and the shared pool, over a pretty short period of time. These changes will often decrease as the database stays up for a longer period of time, and you will often see changes as the load profile of the database changes, say from being report heavy to OLTP heavy.

Please find below the various tables and thier descriptions to check the information in the database.


V$MEMORY_DYNAMIC_COMPONENTS  - Displays information on the current size of all automatically tuned and static memory components, with the last operation (for example, grow or shrink) that occurred on each.


V$SGA_DYNAMIC_COMPONENTS  --  Displays the current sizes of all SGA components, and the last operation for each component.

V$SGA_DYNAMIC_FREE_MEMORY   -- Displays information about the amount of SGA memory available for future dynamic SGA resize operations.

V$MEMORY_CURRENT_RESIZE_OPS   --  Displays information about resize operations that are currently in progress. A resize operation is an enlargement or reduction of the SGA, the instance PGA, or a dynamic SGA component.

V$SGA_CURRENT_RESIZE_OPS  --  Displays information about dynamic SGA component resize operations that are currently in progress.


V$MEMORY_RESIZE_OPS -- Displays information about the last 800 completed memory component resize operations, including automatic grow and shrink operations for SGA_TARGET and PGA_AGGREGATE_TARGET.

V$SGA_RESIZE_OPS -- Displays information about the last 800 completed SGA component resize operations.

Top 5 Timed Events - gc cr failure

gc cr failure – This wait event is triggered when a CR ( Consistent Read) block is requested from the holder of the block and a failure status message is received. This happens where there are unforeseen events such as lost block or checksum or an invalid block request or when the holder cannot process the request. One will see multiple timeouts for the place holder wait like gc cr request before receiving gc cr failure event. One can query system statistics view v$sysstat for gc blocks lost or gc claim blocks lost.

Failure is not an option in cluster communications because lot messages or block may potentially trigger node evictions.

 
In the above case this wait event is because of gc buffer busy as the node holding the block requested is busy and cannot process the request.

Let us understand how Consistent Read (CR) requests are handled in RAC to get more clarity and why the nodes get busy fulfilling the requests. When an instance needs to generate a CR version of the current block, the block can be either in the local or remote cache. If the latter, then LMS ( Lock Manager Server) on the other instance will try to create the CR block, when the former, the foreground process executing the query will perform the CR block generation. When a CR version is created, the instance or instances needs to read the transaction table and undo blocks from the rollback /undo segment that are referenced in the active transaction table of the block. Sometimes this cleanout/rollback process may cause several lookups of remote undo headers and undo blocks. The remote undo header and undo block lookups will result in a gc cr request . Also as undo headers are frequently accessed, a buffer wait may also occur.

We got rid of these kind of wait events after reducing the traffic between the nodes by pointing the applications which are depended on each specific tables  to specific nodes.

Top 5 Timed Foreground Events - Library Cache Lock & Library Cache Pin

 Library Cache Lock & Library Cache Pin


Library cache lock will be obtained on database objects referenced during parsing or compiling of SQL or PL/SQL statements (table, view, procedure, function, package, package body, trigger, index, cluster, and synonym). The lock will be released at the end of the parse or compilation.

I am not discussing more on the theory part of the this wait event as I have discussed more about them in my previous blog. You can find the much detailed explanation of possible reasons and solutions with the below link

 http://orakhoj.blogspot.com/2011/10/top-5-timed-foreground-events-library_17.html

Here I am just discussing about the recent issue we faced in our load test environment. When we ran the load with X amount of SGA we see DB Time increased to very high value and found Library Cache Lock and Library Cache Pin as TOP 2 Wait events.

Solutions: We just increased the SGA to Y amount and re ran the same load which has given wonderful results. The DB time has reduced to a considerable amount and Library Cache Lock has disappeared from the TOP wait event. So the solutions which I discussed in the above link, here it are the practical implementation.  The increase in Shared Pool via increasing the SGA has solved the problem to get rid of this wait event.

gc cr block lost / gc current block lost

TOP 5 Timed Events - gc cr block lost / gc current block lost

Global cache lost blocks statistics ("gc cr block lost" and/or "gc current block lost") for each node in the cluster as well as aggregate statistics for the cluster represent a problem or inefficiencies in packet processing for the interconnect traffic. These statistics should be monitored and evaluated regularly to guarantee efficient interconnect Global Cache and Enqueue Service (GCS/GES) and cluster processing. Any block loss indicates a problem in network packet processing and should be investigated.

The vast majority of escalations attributed to RDBMS global cache lost blocks can be directly related to faulty or mis-configured interconnects. “lost blocks” at the RDBMS level, responsible for 64% of escalations.

Misconfigured or Faulty Interconnect Can Cause: 

•          Dropped packets/fragments

•          Buffer overflows

•          Packet reassembly failures or timeouts

•          Ethernet Flow control kicks in

•          TX/RX errors

 “Lost Blocks”: NIC Receive Errors

Db_block_size = 8K

ifconfig –a:

eth0 Link encap:Ethernet  HWaddr 00:0B:DB:4B:A2:04

            inet addr:130.35.25.110  Bcast:130.35.27.255  Mask:255.255.252.0

            UP BROADCAST RUNNING MULTICAST  MTU:1500  Metric:1

            RX packets:21721236 errors:135 dropped:0 overruns:0 frame:95

            TX packets:273120 errors:0 dropped:0 overruns:0 carrier:0

“Lost Blocks”: IP Packet Reassembly Failures

            netstat –s

Ip:
   84884742 total packets received
   …

  1201 fragments dropped after timeout
   …
   3384 packet reassembles failed

Detailed Explanation of the this wait event can be found at Metalink - gc block lost diagnostics [ID 563566.1]

gc current block busy & gc cr block busy

Top 5 Wait Events (RAC) - gc current block busy & gc cr block busy

gc current block busy  - When a request needs a block in current mode, it sends a request to the master instance. The requestor evenutally gets the block via cache fusion transfer. However sometimes the block transfer  is delayed due to either the block was being used by a session on another instance or the block transfer was delayed because the holding instance could not write the corresponding redo records to the online logfile immediately.

One can use the session level dynamic performance views v$session and v$session_event to find the programs or sesions causing the most waits on this events

select a.sid , a.time_waited , b.program , b.module from v$session_event a , v$session b where a.sid=b.sid and a.event='gc current block busy' order by a.time_waited;

gc cr block busy - When a request needs a block in CR mode , it sends a request to the master instance. The requestor evenutally gets the block via cache fusion transfer. However sometimes the block transfer is delayed due to either the block was being used by a session on another instance or the block transfer was delayed because the holding instance could not write the corresponding redo records to the online logfile immediately.

One can use the session level dynamic performance views v$session and v$session_event to find the programs or sesions causing the most waits on this events

select a.sid , a.time_waited , b.program , b.module from v$session_event  a , v$session b where a.sid=b.sid and a.event='gc cr block busy' order by a.time_waited;

This event indicates significant write/write contention. If it appears like the below in TOP 5 list of AWR

Ensure that the log writer (lgwr) is tuned. In our situation planning for appropriate application partitioning to multiple instances avoided the contention. We will see more of the RAC realted wait events in the other posts.

gc current block 2 way - gc current block 3 way

Block oriented waits are the most common wait events in the cluster wait events. The block oriented wait event statistics indicate that the requested block was served from the other instances. In a two – node cluster environment a message is transferred to the current holder of the block and holder ships the block to the requestor. In a cluster environment with more than two nodes, the request for the block is sent to the holder of the block through the resource master and includes an additional message.

 

The average wait time and total wait time should be considered when being alerted to performance issues where these particular waits have a high impact. Usually, either interconnects or load issues of SQL execution against a large shared working ser can be found the root cause. The following are the most common block oriented waits

gc current block 2 – way ( 2 Node RAC Environment)

gc current block 3 – way ( 3 or More  Node RAC Environment )

Pictorial Description of the steps involved in the above wait events in exclusive mode and shared mode.

gc current block 2 – way ( Exclusive Mode)

gc current block 2 – way ( Shared Mode)

gc current block 3 – way ( Exclusive Mode)

gc current block 3 – way ( Shared Mode)

More Events Shortly :)

TOP 5 Timed Events - ARCH wait on SENDREQ

ARCH wait on SENDREQ -- Is Network related Wait event at Primary database side when a standby database is configured. ARCH wait on SENDREQ monitors the amount of time spent by all archiver processes to write the received redo to disk as well as open and close the remote archived redo logs

There are three things one can do if you have high wait time against this wait event.

1) Optimize the Network -

Overall, the goal for all Data Guard configurations is to ship redo data to the remote disaster recovery site fast enough to meet recovery time and recovery point objectives. If there is insufficient bandwidth available to handle the required volume however, no amount of tuning can achieve this goal. In order to figure out how much bandwidth is needed, the volume of data that is generated by the production database will need to be determined. Ideally, this can be found by measuring an existing production database or a database which has been set up in a test environment.

SDU (SESSION DATA UNIT)
Before sending data across the network, Oracle Net buffers data into the Session Data Unit (SDU). When large amounts of data are being transmitted or when the message size is consistent, increasing the size of the SDU buffer can improve performance and network utilization. You can configure SDU size within an Oracle Net connect descriptor or globally within the sqlnet.ora.
For Data Guard broker configurations configure the DEFAULT_SDU_SIZE parameter in the sqlnet.ora file:
DEFAULT_SDU_SIZE=32767

For non Data Guard broker configurations that use a connect descriptor, you can override the current settings in the primary database sqlnet.ora file. If you are setting the SDU in a connect descriptor you must use a static SID. Using a dynamic service registration will use the default SDU size defined by DEFAULT_SDU_SIZE. This example uses a connect descriptor with the SDU parameter in the description.
sales.us.acme.com=
(DESCRIPTION=
(SDU=32767)
(ADDRESS=(PROTOCOL=tcp)
(HOST=sales-server)
(PORT=1521))
(CONNECT_DATA=
(SID=sales.us.acme.com))
)
On the standby database, set SDU in the SID_LIST of the listener.ora file:
SID_LIST_listener_name=
(SID_LIST=
(SID_DESC=
(SDU=32767)
(GLOBAL_DBNAME=sales.us.acme.com)
(SID_NAME=sales)
(ORACLE_HOME=/usr/oracle)))
2) Multiple archive processes can transmit a redo log in parallel to the standby database, reducing the time for the redo transmission to the secondary. The MAX_CONNECTIONS attribute of the LOG_ARCHIVE_DEST_n control the number of these processes. This can be very beneficial during batch loads.

3)A system's network queues sizes can also be adjusted to optimize performance. You can regulate the size of the queue between the kernel network subsystems and the driver for network interface card. Any queue should be sized so that losses do not occur due to local buffer overflows. Therefore, careful tuning is required to ensure that the sizes of the queues are optimal for your network connection, particularly for high bandwidth networks.

These settings are especially important for TCP because losses on local queues cause TCP to fall into congestion control, which limits the TCP sending rates.
For Linux there are two queues to consider, the interface transmit queue and the network receive queue. The transmit queue size is configured with the network interface option txqueuelen. The network receive queue size is configured with the kernel parameter netdev_max_backlog
----- Find below more events related to Standby Database on Primary and Standby sides.----

WAIT EVENTS ON THE PRIMARY

On the primary database there are two categories of wait events which are either related to the ARC process or the LGWR process. The descriptions of these events are below.
1. ARCH PROCESS WAIT EVENTS
ARCH wait on ATTACH monitors the amount of time spent by all archiver processes to spawn an RFS connection.
ARCH wait on SENDREQ monitors the amount of time spent by all archiver processes to write the received redo to disk as well as open and close the remote archived redo logs.
ARCH wait on DETACH monitors the amount of time spent by all archiver processes to delete an
RFS connection.

2. LGWR SYNC WAIT EVENTS
LGWR wait on ATTACH monitors the amount of time spent by all log writer processes to spawn an RFS connection.
LGWR wait on SENDREQ monitors the amount of time spent by all log writer processes to write the received redo to disk as well as open and close the remote archived redo logs.
LGWR wait on DETACH monitors the amount of time spent by all log writer processes to delete an RFS connection.

3. LGWR ASYNC WAIT EVENTS
LNS wait on ATTACH monitors the amount of time spent by all network servers to spawn an RFS connection. 
LNS wait on SENDREQ monitors the amount of time spent by all network servers to write the received redo to disk as well  as open and close the remote archived redo logs.
LNS wait on DETACH monitors the amount of time spent by all network servers to delete an RFS connection.

LGWR wait on full LNS buffer monitors the amount of time spent by the log writer (LGWR) process waiting for the network server (LNS) to free up ASYNC buffer space. If buffer space has not been freed in a reasonable amount of time, availability of the primary database is not compromised by allowing the archiver process (ARCn) to transmit the redo log data. This wait event is not relevant for destinations configured with the LGWR SYNC=PARALLEL attributes.

WAIT EVENTS ON THE SECONDARY

RFS Write is the elapsed time for the write to standby redo log or archive log to occur as well as non I/O work such as redo   block checksum validation.

RFS Random I/O is the elapsed time for the write to a standby redo log to occur.

RFS Sequential I/O is the elapsed time for the write to an archive log to occur.

SQL ordered by Version Count

This time i want to discuss about the SQL ordered by Version Count under Sql Statistics of AWR as it is hard finding the different TOP Wait Events in AWRs. This needs a little explanation as most of us worry what the queries under this section fall into.

In the above we see query has 204 version count and has more than 165 executions. First thing to start with looking at the sql text and see if the bind variables are being used or not. If they are being used then we need to look for the cuase of having many child cursors for that query.

As the definition SQL Ordered by Version Count - AWR reports statements with more than child cursors than the threshold value which defaults to 20.

There are so many reasons why Oracle does not share the same statement. Oracle records the reason in v$sql_shared_cursor view. In 10.2.0.1 database, v$sql_shared_cursor has 53 reasons of unshareability.

V$SQL_SHARED_CURSOR explains why a particular child cursor is not shared with existing child cursors. Each column identifies a specific reason why the cursor cannot be shared.

I have done the same found that the reason for above sql in the image is due to Bind Mismatch. I  have gone through many blogs including the blog below of Jonathan which beautifully explains the reasons behind the bind mismatch for columns with varchar.

http://jonathanlewis.wordpress.com/2007/01/05/bind-variables/

Unfortunately my column in the query ( Above Image ) is a number field and explanation of the above link does not suite my situation. But i found that this is one of the busy queries hit by my application and the reason for Bind Mismatch in this situation is due to the heavy activity in shared pool. This is becuase when a session attempts to validate a cursor and finds it has to wait for a pin then it may simply generate a new child rather than waiting which is causing for creation of new child cursor.

Other reasons might be -  if the optimizer environments are different you get different child cursors - even if the final plan is the same - check the column on v$sql, and the content of  $sql_optimizer_env or

 there are various bugs that cause child cursors to be created unnecessarily in various versions from 10.2.0.1.

Find below the little explanation on Parent and Child Cursors which is very interesting in this context from Steve Adams.

"For each SQL statement the library cache contains a "parent" cursor for the text of the SQL statement.

The parent cursor is comprised of a "handle" that can be looked up by hash value via the library cache hash table, and an "object" that contains pointers to each of its "child" cursors. Each child cursor is also comprised of a handle and an object. The child object is comprised of two heaps numbered 0 and 6. Heap 0 contains all the identifying information for a particular version of the SQL statement and heap 6 contains the execution plan. This distinction between parent and child cursors is maintained even when there is only one version of each SQL statement.

For parent cursors the convention used in the X$ tables is that the parent address is the same as the handle address. V$OPEN_CURSORS, V$SQL and so on are only interested in child cursors and so they exclude parent cursors by requiring that the two addresses are different. The columns that you mentioned are of course the two addresses."

Top 5 Timed Foreground Events - direct path read temp

Direct Path Read Temp

When a session reads buffers from disk directly into the PGA, the wait is on direct path read temp. This is closely related to the direct path read wait. If the I/O subsystem doesn’t support asynchronous I/Os, then each wait corresponds to a physical read request. If the I/O subsystem supports asynchronous I/O, then the process overlaps read requests with processing the blocks already in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it issues a wait call and updates the statistics for this event. So, the number of waits is not always the same as the number of read requests.

Similarly, the direct path write temp wait event and direct path write wait event can occur when a process is writing buffers directly from PGA. The process will wait on this event for the write call to complete. Operations that could perform direct path writes include when a sort goes to disk, parallel DML operations, direct-path INSERTs, parallel create table as select, and some LOB operations. Like direct path reads, the number of waits is not the same as number of write calls issued if the I/O subsystem supports asynchronous writes. The session will wait if it has processed the buffers in the PGA and can’t continue work until an I/O request completes.

Causes for the direct path read temp wait

To reduce the direct path read wait event and direct path read temp wait event:

§  High disk sorts – If the sorts are too large to fit in memory and get sent to disk, this wait can occur.

§  Parallel slaves – Parallel slaves are used for scanning data or parallel DML may be used to create and populate objects. These may lead to direct path read wait and direct path write wait respectively.

§  Direct path loads – The direct path API is used to pass data to the load engine in the server and can cause the related direct path write wait.

§  Server process ahead of I/O – The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system

§  Data Warehouse – Sorts in a data warehouse environment may always go to disk leading to high waits on direct path read temp and/or direct path write temp.

§  Hash area size – For query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small.

One can go down the AWR to the IO Stats Section to see what is causing these wait events.

Under IO Stats , IO Stat by FileType Summary we see that Temp is on the TOP with Major Volume of Data Writes and Reads.

      High disk sorts – The disk sort distribution can be checked: It is recommended to use pga_aggregate_target. This area is used both for sorts and the hash join area. If    possible the PGA can be sized larger to fit more in-memory sorts reducing the disk sorts. Memory_Target can be increased if required.

   Full Table Scans – If tables are defined with a high degree of parallelism, the optimizer leans towards using full table scans with parallel slaves. For a direct path read, locate the object being loaded. Consider using disk striping or Automatic Storage Management (ASM) which can stripe for you.

  Parallel DML – Check the I/O distribution across the disks and make sure your I/O is configured for the parallelism being used. Verify that the parameter DISK_ASYNCH_IO is set to true.

The direct path read temp wait is most often encountered when the PGA is not able to support the size of the sorts. The closely related wait events of direct path read, direct path write temp, and direct path write can occur due to parallel operations, direct path inserts and overloaded I/O. But tuning the PGA, the I/O placement, and SQL tuning can reduce or eliminate this wait.