The three key tools that every Oracle DBA who has worked on a database since Oracle 10g should know about - Automatic Database Diagnostic Monitor (ADDM), Automatic Workload Repository (AWR) reports, and Active Session History (ASH) reports - are also extremely useful for detecting performance issues related to the gc buffer busy wait event. The next sections explain exactly how to locate crucial information about the statements, user sessions, and database objects that are causing an Oracle database to perform poorly because of high occurrences of the gc buffer busy wait event.
Using ADDM to Find Event Information
ADDM is one of the fastest methods that an Oracle DBA can leverage to find specific recommendations about a database application workload that has been executed over a specific period of time, including which SQL statements are encountering a performance bottleneck. ADDM can draw on information retained within the AWR; it can also be executed in real time, in which case the most recent set of ASH data will be used for its analysis.
Listing 1 shows an excerpt from an ADDM report that was run against the database during the time that excessive gc buffer busy wait events were encountered.
Listing 1 ADDM Report Showing the SQL Text
...
RECOMMENDATION 1: Schema, 84.4% benefit (17609 seconds)
ACTION: Consider partitioning the INDEX "MID_B2W_ADMIN.STM_LOG_DATA_IDX"
with object id 131712 in a manner that will evenly distribute
concurrent DML across multiple partitions.
RELEVANT OBJECT: database object with id 131712
RATIONALE: The INSERT statement with SQL_ID "fv4un8f4w6zg8" was
significantly affected by "buffer busy" waits.
RELEVANT OBJECT: SQL statement with SQL_ID fv4un8f4w6zg8
insert into STM_LOG (NM_LOGIN, DS_ROLES, DS_OPERATION, DT_CRIACAO,
CD_MARCA, CD_LOG) values (:1, :2, :3, :4, :5, :6)
...Notice that ADDM was intelligent enough to isolate the specific performance issue to a particular SQL statement and connect that statement to the gc buffer busy wait event. In fact, ADDM even offered a suggestion to partition the affected database object to potentially alleviate the issue. However, we can confirm this suggestion through additional means—AWR and ASH reports—as the next sections demonstrate.
Using AWR to Find Event Information
Creating an AWR report is extremely simple; it can be done by issuing just one command from within SQL*Plus and then responding to the prompts that define which time period(s) the AWR report should span:
$> sqlplus / as sysdba
SQL> ?/rdbms/admin/awrrpt
...Typical AWR report output usually contains an incredible amount of information about an Oracle database’s application workload behavior. When a database instance is suffering from a gc buffer busy wait event during the time period chosen for the AWR report, however, that event will usually surface as one of the Top 5 Timed Events, as shown in Figure 1.
Figure 1 Top 5 Timed Events in an AWR report
As Figure 1 shows, the gc buffer busy event is the third-most frequently occurring wait event; this situation obviously is not optimal because it means the database instance is having to wait excessively for cache fusion to be handled properly. The excessive wait time may indicate that the root cause of this wait event may be a serious issue with the performance of the private interconnect network itself.
Another helpful source of information is the Segments by Global Cache Buffer Busy report, shown in Figure 2.
Figure 2 Segments waiting for gc buffer busy in an AWR report
In this example, two segments - STM_LOG_DATA_IDX and PRC_ITEM - are experiencing the largest amount of gc buffer busy waits. These segments should therefore be investigated to determine why they are experiencing almost 77 percent of all waits in this category.
Figure 3 shows the Global Cache and Enqueue Services – Workload Characteristics report. It indicates that this database instance is experiencing significant interconnect problems because, on average, it is taking almost 0.5 seconds to receive a single buffer across the private interconnect.
Figure 3 Problems in global cache receive time
This event is calculated using the following formula:
gc cr block receive time=
Time to send message to a remote LMS process by FG
+ Time taken by LMS to build block (statistics: gc cr block build time)
+ LMS wait for LGWR latency (statistics:gc cr block flush time)
+ LMS send time (Statistics: gc cr block send time)
+ Wire latency
The AWR report sections therefore offer concrete evidence that something is seriously wrong with this database’s environment. We will present some queries later in this chapter to show how to locate the SQL_ID, statement, block, and other information about the database server process that is causing this problem. It’s important to remember that the root cause of this problem must be verified first at the hardware level—a malfunctioning network interface card (NIC), a failing network switch, misconfigured networking parameters, among many other possibilities—before pointing a finger at any other layers of the application and system as potential root causes of the problem.
Using ASH to Find Event Information
Generating an ASH report can help you locate the specific SQL statements that are experiencing performance problems related to the gc buffer busy wait event. For example, the Top User Events report section shown in Listing 2 from the generated ASH report illustrates that wait event is definitely an issue within the selected 15-minute reporting period.
Listing 2 Top User Events with GC Buffer Busy
Top User EventsDB/Inst: BWMDPR/BWMDPR1(Feb 25 17:40 to 17:55)
Avg Active
Event Event Class % Activity Sessions
----------------------------------- --------------- ---------- ----------
CPU + Wait for CPU CPU 51.49 2.49
db file sequential read User I/O 13.17 0.64
gc buffer busy Cluster 5.63 0.27
direct path read User I/O 3.61 0.17
db file scattered read User I/O 3.17 0.15
-------------------------------------------------------------
Another part of the same ASH report also proves that the database is encountering gc buffer busy waits. Listing 3 shows the Top Blocking Sessions section that identifies which sessions are blocking other sessions and the event that these sessions were most commonly waiting for.
Listing 3 Top Blocking Sessions
Top Blocking Sessions DB/Inst: BWMDPR/BWMDPR1 (Feb 25 17:40 to 17:55)
-> Blocking session activity percentages are calculated with respect to
waits on enqueues, latches and "buffer busy" only
-> '% Activity' represents the load on the database caused by
a particular blocking session
-> '# Samples Active' shows the number of ASH samples in which the
blocking session was found active.
-> 'XIDs' shows the number of distinct transaction IDs sampled in ASH
when the blocking session was found active.
Blocking Sid % Activity Event Caused % Event
--------------- ---------- ------------------------------ ----------
User Program # Samples Active XIDs
-------------------- ------------------------------ ------------------ --------
5074,38287 1.15 gc buffer busy 0.80
MID102_B2W_WL_APP 165/901 [ 18%] 0
4375,33093 1.13 read by other session 0.85
This report identifies that session ID 5074 with serial 38287 is one of the sessions that is waiting for the gc buffer busy wait event and is also blocking or making other sessions wait for that task to finish so that the resource can become available again to another session.
Finally, Listing 4 displays which object is responsible for generating the gc buffer busy wait event.
Listing 4 TOP DB Objects
Top DB Objects DB/Inst: BWMDPR/BWMDPR1 (Feb 25 17:40 to 17:55)
-> With respect to Application, Cluster, User I/O and buffer busy waits only.
Object ID % Activity Event % Event
--------------- ---------- ------------------------------ ----------
Object Name (Type) Tablespace
----------------------------------------------------- -------------------------
131712 4.37 gc buffer busy 2.62
MID_B2W_ADMIN.STM_LOG_DATA_IDX (INDEX) TS_MID_B2W_ADMIN_INDEX_M_
gc current block busy 1.36While ADDM, AWR, and ASH reports are valuable for both historical and real-time analysis, it’s also possible to isolate this information in real time when gc buffer busy wait events are occurring without leveraging these tools, as the final sections of this chapter demonstrate.
