Database Reference
In-Depth Information
This differentiation is useful to identify the instance that initiated a block request. Next, we need to identify
the object suffering from an excessive amount of
gc buffer busy
events. SQL in Listing 10-3 is handy to identify
the objects inducing this wait event. The following output shows that index
lines_u1
is suffering from an excessive
number of
gc buffer busy acquire
wait events. Depending upon the type of object, a different troubleshooting
method will be employed. Again, if the problem is constrained to a specific time interval, then you should review the
AWR report for that time frame and focus on the section listing segments suffering from gc buffer busy waits.
@ash_gcwait_to_obj.sql
Enter value for event: gc buffer busy acquire
Enter value for threshold: 1000
/
INST OWNER OBJECT_NAME OBJECT_TYP CNT
---------- ---------- -------------------------------- ---------- ----------
1 RS LINES_U1 INDEX 1205710
2 AR RA_CUSTOMER_TRX_ALL TABLE 4399
1 AR RA_CUSTOMER_TRX_ALL TABLE 4241
1 WSH WSH_DELIVERY_DETAILS_IDX1 INDEX 4106
...
1 Undo Header 1481
/Undo block
The following section discusses the common object types causing the gc buffer busy wait event and a method to
diagnose further.
Unique Indexes
In a B-tree index, indexed column values are stored in (key[s], rowid) format in ascending or descending key column
order. If the column values are populated using sequence-generated values, then all new rows will be populated in the
rightmost leaf block of the index. This type of index growth is
right-hand index growth
. If many sessions are trying to
insert rows into the table, then the rightmost leaf block will be a hot block. In the case of a single-instance database,
this concurrency issue will cause waits for read by other session and buffer busy wait events. In a RAC database, this
problem will be magnified as gc buffer busy waits. If the application inserts into that table from all instances, then the
rightmost leaf block has to be transferred between the instances aggressively, similar to a Hot Potato game played by
children in the United States.
Almost-unique indexes can cause
gc buffer busy
waits too; indexes on timestamp column are an example.
Indexed column values inserted concurrently will have the same timestamp or timestamp values close enough that
those values will be stored in fewer leaf blocks of the index B-tree. So, concurrent inserts into that table will induce
contention for few leaf blocks.
You will also notice that contention will be moving from one block to another block, as the leaf block will be
filled, and new blocks added to the index structure of their rightmost part of the index.
Right-hand growth index contention can be resolved by hash partitioning the index on key column values. By
hash partitioning the index, values are distributed among multiple leaf blocks, thereby effectively spreading the
activity to many blocks. From 10g onward, indexes can be partitioned even if the table is not partitioned.
Index unique scan
and
index range scan
access paths on hash-partitioned indexes specifying, at least, all
partitioning key column values will perform similar to a nonpartitioned index. However, if your application performs
numerous index range scans without providing all partitioning key columns, then hash partitioning will increase
logical I/O per execution.
Reverse key indexes also can be used to combat right-hand growth contention. But reverse key indexes do not allow
a range scan access path, and so there are only a very few reasons as to why you would choose a reverse key index over
hash-partitioning indexes. One of those reasons is that partitioning requires a license for partitioning option.
