Buffer busy wait event
Buffer busy wait
Oracle
has several types of buffer classes, such as data block, segment
header, undo header, and undo block. How you fix a buffer busy wait
situation will depend on the types of buffer classes that are causing
the problem.
Buffer
Busy Waits usually happen on Oracle 10 and 11 mainly because of insert
contention into tables or Indexes. There are a few other rare cases of
contention on old style RBS segments, file headers blocks and freelists
Before
Oracle 10 and 11 there was one other major reason which was readers
waiting for readers, ie one user does a phyiscal IO of a block into
memory and a second user want to read that block. The second user waits
until the IO is finished by the first user. Starting in 10g this wait
has been given the name "read by other session". Before Oracle 10g this
was also a "buffer busy wait".
How can we find the block contention?
àTo find the block on which currently suffering from read by other session wait event.
SELECT sw.sql_id ,sw.p1 "file#", sw.p2 "block#", sw.p3 "class#" ,event
FROM v$session sw WHERE event= 'buffer busy wait';
àUsing blok_id or file_id we can find the segment
SELECT relative_fno, owner, segment_name, segment_type
FROM dba_extents
WHERE file_id = &FILE
AND &BLOCK BETWEEN block_id AND block_id + blocks - 1;
àFind the sql causing issue using the sql_id we find in first query
SELECT
s.p1 file_id, s.p2 block_id,o.object_name obj,
o.object_type otype,
s.SQL_ID,
w.CLASS,event
FROM v$session s,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE
event IN ('buffer busy wait')
and
w.CLASS#(+)=s.p3
AND o.object_id (+)= s.row_wait_OBJ#
ORDER BY 1;
SELECT sql_text FROM v$sqltext WHERE sql_id=&sq_id ORDER BY piece
àWe can also find block and sql on which causing “read by other session” using v$active_session_history
SELECT
p1 file_id , p2 block_id ,o.object_name obj,
o.object_type otype,
ash.SQL_ID,
w.CLASS
FROM v$active_session_history ash,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE event='buffer busy wait'
AND w.CLASS#(+)=ash.p3
AND o.object_id (+)= ash.CURRENT_OBJ#
AND ash.sample_time > SYSDATE - &MIN/(60*24)
ORDER BY 1;
&Min : time you want to see history
SELECT sql_text FROM v$sqltext WHERE sql_id=&sq_id ORDER BY piece
SELECT
bbw.cnt,
bbw.obj,
bbw.otype,
bbw.sql_id,
bbw.block_type,
NVL(tbs.NAME,TO_CHAR(bbw.p1)) TBS,
tbs_defs.assm ASSM
FROM (
SELECT
COUNT(*) cnt,
NVL(object_name,CURRENT_OBJ#) obj,
o.object_type otype,
ash.SQL_ID sql_id,
NVL(w.CLASS,'usn '||TO_CHAR(CEIL((ash.p3-18)/2))||' '||
DECODE(MOD(ash.p3,2),
1,'header',
0,'block')) block_type,
--nvl(w.class,to_char(ash.p3)) block_type,
ash.p1 p1
FROM v$active_session_history ash,
( SELECT ROWNUM CLASS#, CLASS FROM v$waitstat ) w,
all_objects o
WHERE event='buffer busy wait'
AND w.CLASS#(+)=ash.p3
AND o.object_id (+)= ash.CURRENT_OBJ#
AND ash.session_state='WAITING'
AND ash.sample_time > SYSDATE - &min/(60*24)
--and w.class# > 18
GROUP BY o.object_name, ash.current_obj#, o.object_type,
ash.sql_id, w.CLASS, ash.p3, ash.p1
) bbw,
(SELECT file_id,
tablespace_name NAME
FROM dba_data_files
) tbs,
(SELECT
tablespace_name NAME,
extent_management LOCAL,
allocation_type EXTENTS,
segment_space_management ASSM,
initial_extent
FROM dba_tablespaces
) tbs_defs
WHERE tbs.file_id(+) = bbw.p1
AND tbs.NAME=tbs_defs.NAME
ORDER BY bbw.cnt
The preceding queries will reveal the specific type of buffer causing the high buffer waits. Your fix
will depend on which buffer class causes the buffer waits, as summarized in the following subsections.
Contention for Segment Header
Every table and index segment has a header block. This block contains the following metadata:
information about the high watermark of the segment, a list of the extents making up the segment,
and information about the free space. To manage the free space, the header block contains
(depending on the type of segment space management that is in use) either freelists or a list of
blocks containing automatic segment space management information. Typically, contention
for a segment header block is experienced when its content is modified by several processes
concurrently. Note that the header block is modified in the following situations:
• If INSERT statements make it necessary to increase the high watermark
• If INSERT statements make it necessary to allocate new extents
• If DELETE, INSERT, and UPDATE statements make it necessary to modify a freelist
A possible solution for these situations is to partition the segment in order to spread the
load over several segment header blocks. Most of the time, this might be achieved with hash
partitioning, although, depending on the load and the partition key, other partitioning methods
might work as well. However, if the problem is because of the second or third situation, other
solutions exist. For the second, you should use bigger extents. In this way, new extents would
seldom be allocated. For the third, which does not apply to tablespaces using automatic segment
space management, freelists can be moved into other blocks by means of freelist groups. In
fact, when several freelist groups are used, the freelists are no longer located in the segment
header block (they are spread on a number of blocks equal to the value specified with the
parameter FREELIST GROUPS, so you will have less contention on them—you are not simply
moving the
contention to another place!). Another possibility is to use a
tablespace with automaticsegment space management instead of freelist
segment space management
In short
If your
queries show that the buffer waits are being caused by contention on the
segment header, there’s free list contention in the database, due to
several processes attempting to insert into the same data block—each of
these processes needs to obtain a free list before it can insert data
into that block. If you aren’t already using it, you must switch from
manual space management to automatic segment space management
(ASSM)—under ASSM, the database doesn’t use free lists. However, note
that moving to ASSM may not be easily feasible in most cases. In cases
where you can’t implement ASSM, you must increase the free lists for the
segment in question. You can also try increasing the free list groups
as well.
Contention for Undo Header and Undo Block
Contention for these types of blocks occurs in two situations. The first, and only for undo
header blocks, is when few undo segments are available and lots of transactions are concurrently
committed (or rolled back). This should be a problem only if you are using manual undo
management. In other words, it usually happens if the database administrator has manually
created the rollback segments. To solve this problem, you should use automatic undo management.
The second situation is when several sessions modify and query the same blocks at the
same time. As a result, lots of consistent read blocks have to be created, and this requires you
to access both the block and its associated undo blocks. There is little that can be done about
this situation, other than reducing the concurrency for the data blocks, thereby reducing the
ones for the undo blocks at the same time.
Contention for Extent Map Blocks
As discussed in the section “Contention for Segment Header Blocks,” the segment header
blocks contain a list of the extents that make up the segment. If the list does not fit in the
segment header, it is distributed over several blocks: the segment header block and one or
more extent map blocks. Contention for a segment header block is experienced when concurrent
INSERT statements have to constantly allocate new extents. To solve this problem, you
should use bigger extents.
Contention for Freelist Blocks
As discussed in the section “Contention for Segment Header Blocks,” freelists can be moved
into other blocks, called freelist blocks, by means of freelist groups. Contention for a freelist
block is experienced when concurrent DELETE, INSERT, or UPDATE statements
have to modify the freelists. To solve this problem, you should
increase the number of freelist groups. Another possibility is to use a
tablespace with automatic segment space management instead of freelist
segment space management.
Contention for Data Blocks
All the blocks that make up a table or index which stor actual data are called data blocks. Contention for them has two main causes.
The first is
when the frequency of table or index scans on a given segment is very
high. This problem is because of inefficient execution plans causing
frequent table or index scans over the same blocks. Usually it is
because of inefficient related-combine operations (for example, nested
loop joins). Here, even two or three SQL statements executed
concurrently might be enough to cause contention.
The second is
when the frequency of executions is very high. This problem is the
execution of several SQL statements accessing the same block at the same
time. In other words, it is the number of SQL statements executed
concurrently against (few) blocks that is the problem. It might be that
both happen at the same time. If this is the case, take care of solving
the first problem before facing the second one. In fact, the second
problem might disappear when the first is gone.
To solve the first problem, SQL tuning is necessary. An efficient execution plan must be
executed in place of the inefficient one.
To solve the second problem, several approaches are available. Which one you have to use
depends on the type of the SQL statement (that is, DELETE, INSERT, SELECT,1 and UPDATE) and on the type of the segment (that is, table or index).
However,
before starting, you should always ask one question when the frequency
of execution is high: is it really necessary to execute those SQL
statements against the same data so often? Actually, it is not unusual
to see applications that unnecessarily execute the same SQL
statement too often
. If the frequency of execution cannot be reduced, there are the following
possibilities.
• If there is contention for a table’s blocks because of DELETE, SELECT, and UPDATE statements, you should reduce the number of rows per block. Note that this is the opposite of
the common best practice to fit the maximum number of rows per block. To store fewer
rows per block, either a higher PCTFREE or a smaller block size can be used.
• If there is contention for a table’s blocks because of INSERT statements and freelist segment
space management is in use, the number of freelists can be increased. In fact, the goal of
having several freelists is precisely to spread concurrent INSERT statements over several
blocks. Another possibility is to move the segment into a tablespace with automatic
segment storage management.
• If there is contention for an index’s blocks, there are two possible solutions. First, the
index can be created with the option REVERSE. Note, however, that this method does not
help if the contention is on the root block of the index. Second, the index can be hash
partitioned, based on the leading column of the index key (this creates multiple root
blocks and so helps with root block contention if a single partition is accessed). Because
global hash-partitioned indexes are available as of Oracle Database 10g only, this is not
an option with Oracle9i.
The important thing to note about reverse indexes is that range scans on them cannot
apply restrictions based on range conditions (for example, BETWEEN, >, or <=). Of course, equality predicates are supported
A buffer
busy wait indicates that more than one process is simultaneously
accessing the same data block. One of the reasons for a high number of
buffer busy waits is that an inefficient query is reading too many data
blocks into the buffer cache, thus potentially keeping in wait other
sessions that want to access one or more of those same blocks. Not only
that, a query that reads too much data into the buffer cache may lead to
the aging out of necessary blocks from the cache. You must investigate
queries that involve the segment causing the buffer busy waits with a
view to reducing the number of data blocks they’re reading into the
buffer cache.
If your
investigation of buffer busy waits reveals that the same block or set of
blocks is involved most of the time, a good strategy would be to delete
some of these rows and insert them back into the table, thus forcing
them onto different data blocks.
Check your current memory allocation to the buffer cache, and, if necessary, increase it. A larger
buffer cache can reduce the waiting by sessions to read data from disk, since more of the data will
already be in the buffer cache. You can also place the offending table in memory by using the KEEP POOL in the buffer cache By making the hot block always available in memory, you’ll
avoid the high buffer busy waits.
Indexes that have a very low number of unique values are called low cardinality indexes. Low
cardinality indexes generally result in too many block reads. Thus, if several DML operations are
occurring concurrently, some of the index blocks could become “hot” and lead to high buffer busy waits.
As a long-term solution, you can try to reduce the number of the low cardinality indexes in your
database.
Each Oracle data segment such as a table or an index contains a header
block that records information such as free blocks available. When
multiple sessions are trying to insert or delete rows from the same
segment, you could end up with contention for the data segment’s header
block.
Summary
data block
IF OTYPE =
INDEX , then the insert index leaf block is probably hot, solutions are
Hash partition the index
Use reverse key index
TABLE, then insert block is hot,solutions
Use free lists
Put Object in ASSM tablespace
Segment header - If "segment header"
occurs at the same time as CLASS= "data block" on the same object and
the object is of OTYPE= "TABLE" then this is just a confirmation that
the TABLE needs to use free lists or ASSM.
File Header Block - Most likely extent
allocation problems, look at extent size on tablespace and increase the
extent size to there are few extent allocations and less contention on
the File Header Block.
free lists - Add free list groups to the object
undo header - Not enough UNDO segments, if using old RBS then switch to AUM
undo block - Hot spot in UNDO, application issue
Reference:-
Apress.Troubleshooting.Oracle.Performance.Jun.2008
oracle_database_11g_performance_tuning_recipes
沒有留言:
張貼留言