Free Buffer Waits

When a user session requires free buffers, the server process scans the LRU list to a get a free buffer space. After scanning the LRU list up to a threshold, if the server process could not get free space, it requests the DBWr to write the dirty buffer from the LRU list to disk. While the DBWr process writes the dirty buffers the session waits on 'Free Buffer Waits'.

Tuning Options

Poor SQL Statements

Query the V$SQL view for statements that have high DISK_READS. Tune the statements to reduce the physical reads. The poorly written SQL Statements are the main cause of this wait event.

DBWr Processes

Increase the DBWr processes (or)
Decrease the Buffer Cache (or)
Decrease the FAST_START_MTTR_TARGET parameter.

Delayed Block Cleanout

The delayed block cleanout will cause the free buffer wait events. To avoid delayed block cleanout perform a full table scan on a table that has been loaded with a lot of rows before it is released to the application.

Small Buffer Cache

Increase the size of Buffer Cache if you feel that the buffer cache is under sized and check for the wait event.

Slow IO


Thanks
Vinod D

log file sync wait

The log file sync event occurs when a user issues Commit or Rollback. Click here for the difference between log file sync and log parallel write wait event.

When a user issues a commit or rollback command, the redo data in the redo buffer is written to online redo log file. This write is known as sync write. During this synchronization process the user process waits in log file sync event, while the LGWr waits on log file parallel write event.

The log file sync event is very fast and usually unnoticed by the end users. However you may notice that there are very high time waited for this wait event in certain cases. The main cause for such high wait for this event is as follows,

Too many commits

If you notice high waits at session level then it may be due to running batch processes there are commits within a loop. If that is the case then the application logic can be changed by eliminating unneccessary commits and reduce commit frequency.

If you notice high waits at system level then it may be due to short transactions. OLTP databases usually have short transactions and have high log file sync wait events. Only thing you can do to improve the performance, in this case, is to use faster IO subsystem, rawdevices.

Large Log buffer

The redo entries from buffer to log files take place either through sync writes as explained earlier or through background writes (Such as 1/3 full, 1MB redo etc). When redo log buffer is large then more redo data are accumulated in the buffer. The background writes (i.e., when redo becomes 1/3 full ) are limited or delayed. When a user issues a commit or roll back then the sync writes will take more time.

Thanks

Buffer Busy Waits

The 'Buffer Busy Waits' Event occurs due to the following reasons,

1. A user wants to access a data block for read or write operation. The block is present in the Buffer Cache but locked by another session. The user has to wait till the other session releases the lock on that block.

2. A user wants to access a data block for read or write operation. The block is not present in the Buffer Cache. The block has to be read from data files into Buffer Cache. But the same block is being read by another session. Hence the user patiently waits for the IO of the other session to complete. Prior to oracle 10g, this wait is referred to as Buffer busy wait, but from oracle 10g this wait event is referred to as 'read by other session' wait.

Tuning Options,

Run the following query to find whether any block or range of blocks are always responsible for buffer busy waits,

SQL> select p1 "File #", p2 "Block #", p3 "Reason Code" From v$session_wait
where event = 'buffer busy waits';

Use the following query to find the segment the block belongs to,

SQL> select owner,segment_name,segment_type From dba_extents
where file_id = &file#
and &block# between block_id and block_id + blocks -1;

Once the segment name is identified use the V$Segment_Statistics view to monitor the statistics of the segment.

SQL>select * from v$segment_statistics
where owner like 'RACFIN'
and statistic_name like 'buffer busy waits'
and object_name like 'IBM_PARTY_BRANCH' ;

Use the following query to find what kind of contention is causing the buffer busy waits.

SQL> Select * from v$waitstat;

The output shows the sum and total time of all waits for particular class of block such as data block, segment header, undo header block etc.

To avoid the buffer busy waits,

1. Increase the PCTFREE and PCTUSED values to reduce the number of rows per block to avoid data block contention.
2. Increase the INITRANS value to avoid data block contention.
3. Increase the FREELIST and FREELIST GROUPS value to avoid freelist block contention and segment header block contention.

Thanks

Enqueue

Enqueue waits are locking mechanisms that control the access to shared resources. There are various modes of enqueues.

The following query gives you the detail of Sessions holding the lock, the lock type, mode.

SQL> select DECODE(request,0,'Holder: ','Waiter: ')sid sess, id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN (SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request ;

The most common enqueue waits are discussed below,

TYPE: TM (Table Lock)
LMODE: 3
CAUSE: Unindexed Foreign Key
SOLUTION: The holder has to issue commit or rollback. To avoid this kind of lock in first place create indexes on the foreign key columns. You can do this by taking the ID1 column value in v$lock. This ID1 value is the object ID of the child table. Use dba_objects dictionary table and get the object name. Create the index on the foreign key column.


TYPE: TX (Row level lock)
LMODE: 6
CAUSE: Updating or deleting rows that are currently locked by another transaction.
SOLUTION: Application issue. The lock is released when the holding session issues a commit or rollback. Killing the holding session will rollback the transaction.
RESOURCE LOCKED: Issue the following query to find the resource that is locked.

SQL> select c.sid waiter_sid, a.object_name, a.object_typefrom dba_objects a, v$session b, v$session_wait cwhere (a.object_id = b.row_wait_obj# or a.data_object_id = b.row_wait_obj#)and b.sid = c.sidand chr(bitand(c.P1,-16777216)/16777215) chr(bitand(c.P1,16711680)/65535) = ’TX’and c.event = ’enqueue’;


TYPE: TX (ITL Shortage)
LMODE: 4
CAUSE: i) ITL (Interested Transaction List) Shortage. ii) Unique Key Enforcement. iii) Bitmap index Entry.
SOLUTION: To see whether the wait is due to ITL shortage dump the data block and see how many ITL slots are being used.

SQL> Alter system dump datafile block

If it is indeed due to ITL shortage, then increase the INITRANS value of the object. Also increase the PCTFREE value of the objects.

ii) If the Wait is due to the Unique key Enforcement (i.e, if more than one session inserts the same value that has unique or primary key then the insert will not succeed). If the first session that inserted the value commits then the waiting session will receive the unique constraint violation error. If the first session rollsback then the second session succeeds.

iii) Bitmap Index Entry: A bitmap entry covers a range of ROWIDs. When a bitmap entry is locked all the ROWIDs that correspond to the bitmap entry are locked. When multiple users attempt to delete or update different rows that have the same bitmap entry then a wait for TX in mode 4 will occur.

It is difficult to find whether the lock was due to unique key enforcement or bitmap index entry by merely looking in to the V$Lock view. You have to capture the SQL statements that holder and waiter have issued. If the statement is an insert then wait is due to the unique key enforcement. If the statement is update or delete then the wait is due to the bitmap index entry.

Thanks

control file parallel write

The control file parallel write wait event occurs due to some operations that caused the control file to be updated, such as

1. log switches by LGWR process.
2. adding a datafile.
3. removing a datafiles.
4. checkpoint information by CKPT process.
5. archive log information by ARCH process.

To find which sessions cause transactions to controlfile, issue the following statement.

SQL> select a.sid,decode(a.type, 'BACKGROUND', 'BACKGROUND-' || substr
(a.program,instr(a.program,'(',1,1)), 'FOREGROUND') type, b.time_waited,
round(b.time_waited/b.total_waits,4) average_wait, round((sysdate - a.logon_time)*24) hours_connected
from v$session_event b, v$session a
where a.sid = b.sid
and b.event = 'control file parallel write'
order by type, time_waited;

The output of the above statement shows which background process is writing to control file frequently, For eg., if LGWr has more time_waited then it implies that the log switches are more. If the foreground process have more time_waited then it implies that there are more changes to database that requires to update the SCN in control file.

Thanks

log file sync

When a user issues a commit or rollback then the redo data in the redo buffer is written to the online redo log file. The user session waits for this event to finish before continuing with other processing. This wait time is represented as log file sync wait event.

A number of people have asked the question as what is the difference between log file parallel write and log file sync.

The difference is....

log file parallel write occurs when LGWR writes redo records from redo buffer to online redo log file. This may take place very frequently when it meets any one of the following condition,

1. Once in every three seconds.
2. _LOG_IO_SIZE threshold is met.
3. 1MB worth of redo entries are buffered.
4. Commit.
5. Rollback.
6. When DBWr requests.

The user sessions will never experience the log file parallel write wait event.

When the user session issue commit or rollback then it leads to log file sync wait event, which the user will experience by response time.

Thanks

log file parallel write

The 'log file parallel write' event is caused by the Log writer (LGWR) process. The LGWR writes the redo buffer to the online redo log files . It issues a series of write calls to the system IO. The LGWR waits for the writes to complete on log file parallel write. A slow LGWR process can introduce log file sync waits which makes the user to experience wait times during commit or rollback. The log file parallel write and log file sync wait events are interrelated and must be dealt simultaneously.

If the average_wait time is high (above 10 milliseconds) it indicates that the system IO throughput is slow. To improve the average_wait time follow the same techniques used in db file parallel write wait event.

Tuning options:

1. Avoid running hot backups during peak hours.
2. Check for high commit sessions and try to change the application logic to commit less frequently. Use the following queries to find high commit sessions,

SQL> select sid, value from v$sesstat
where statistic# = select statistic# from v$statname where name = 'user commits') order by 2 desc;

A high redo wastage also indicates high frequency commits

SQL> select b.name, a.value, round(sysdate - c.startup_time) days_old
from v$sysstat a, v$statname b, v$instance c
where a.statistic# = b.statistic#
and b.name in ('redo wastage','redo size');

Thanks

AIX-VMM Tuning Parameters

AIX - Virtual Memory Tuning

In AIX, about 80% of the memory is used to buffer file I/O and only 20% is used for oracle processes and programs. This results in the double buffering of file I/O as Oracle SGA is already buffering the file I/O. It causes more Page in and Page out. To avoid this situation you must tune the Virtual Memory Parameters

To see the virtual memory Parameters use the following command.

# vmo -a

The important parameters are

strict_maxperm

strict_maxclient

lru_file_repage

maxperm%

minperm%

minperm%

minperm%

v_pinshm

maxpin%

Check whether the parameters are set to the following values, if not set it to the following values

strict_maxperm=0 (default)

strict_maxclient=1 (default)

lru_file_repage=0

maxperm%=20-30

minperm%=5 (RAM <32>32 GB but <64>64 GB)

v_pinshm=1

maxpin%=80

Thanks