What are buffer busy waits?

If two processes try (almost) simultaneously the same block and the block is not resident in the buffer cache, one process will allocate a buffer in the buffer cache, lock it and read the block into the buffer. The other process is locked until the block is read. This wait is refered to as buffer busy wait.

Buffer busy waits may also have caused latch free waits. This is a side effect of multiple simultan insert tries into the same block. So instead of trying to decrease the latch free waits (which are only symptomatic in these cases) by increasing the SPINCOUNT you should change the concerned object in a way that multiple objects are able to insert into free blocks.

The type of buffer that causes the wait can be queried with v$waitstat, which lists the waits per buffer type for buffer busy waits only (see the "Buffer Waits" block of this report, if you enabled it in the config with MK_BUFFRAT=1).

What types of buffers is waited for?

BlockDescription
segment headerThe problem is probably a freelist contention. Use freelists or increase the amount of freelists. Use freelist groups (this may have markable effects even in single instances).
data blockIncreasing the size of the db_buffer_cache can help to reduce these waits; but this can also point to freelist contention:
Change PCTFREE and/or PCTUSED: Check, whether there are indices where many processes insert into the same point. Increase INITRANS. Define less lines per block.
To find out what tables may need changes to PCT_FREE and/or PCT_USED, turn the MK_FLC option on in your config file and then refer to the FreeList Contention block of the report.
undo headerIf you don't use Undo TableSpaces, you probably have too few rollback segments. In this case, add more rollback segments and/or increase the number of transactions per rollback segment.
undo blockThis may also point to to few rollback segments (see "undo header" above), but as well to their size: you may want to increase the size of the rollback segments. For "Parallel Server", you can also define more PCM locks in <Parameter:GC_ROLLBACK_LOCKS>
free listAdd more freelists or increase the number of free lists. For "Parallel Server", make sure that each instance has its own freelist group(s).

What can I do about FreeList contention?

Beside tuning the FREELISTS and FREELIST GROUPS parameter for each single object (which is a bunch of work and, at least for FREELIST GROUPS, requires a complete rebuild of the affected table), there's something new with Oracle 9i: If you use locally managed tablespaces, you may benefit from a new feature called ASSM, Automatic Segment Space Management. This is defined at tablespace level on creation of the same - which means, you cannot change it for existing tablespaces but must create a new tablespace and move all objects there:

CREATE TABLESPACE index01
DATAFILE '/disk1/oraData/index01.dbf' SIZE 750M
AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO

To ease the move of all objects, there are some scripts available for download at the IzzySoft website: look for the DBAHelpers archive there. Create a new tablespace as described above, then use the tabmove.sh and/or idxmove.sh script from the DBAHelper archive to move all objects to this tablespace. When those scripts successfully finished their work, check for objects that may have remained in the original tablespace:

SELECT *
FROM dba_segments
WHERE tablespace_name='orig_tablespace_name'

Of course you have to replace the string "orig_tablespace_name" with the name of your original tablespace (uppercase!) in the above statement. If no objects remained, use the DROP TABLESPACE orig_tablespace_name statement to remove the original tablespace, delete the datafile that belonged to it, and then create the tablespace anew with ASSM (see above).

If there are any objects left in the original tablespace, check the logs of tabmove.sh and/or idxmove.sh for the reason: may be you have some objects that are not supported by ASSM - LOBs could be one example, see next paragraph.

One side effect to consider is, that at least with Oracle 9i LOB objects are not supported with ASSM. So if you have LOB objects and want to use ASSM, you must create a separate tablespace for the LOB objects that does not use ASSM. For index tablespaces this shouldn't be a restriction, since one normally should not have LOB objects there.