Isolating GC Buffer Busy Waits in Oracle Database 12c

Isolating GC Buffer Busy Waits in OracleThe 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.


Table of contents[Show]


 

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.

 Top 5 Timed Events in an AWR report

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.

Segments waiting for gc buffer busy in an AWR report 

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.

Problems in global cache receive time 

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.36

 While 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.

Вас заинтересует / Intresting for you:

Handling GC Buffer Busy Wait E...
Handling GC Buffer Busy Wait E... 904 views Александров Попков Fri, 24 Aug 2018, 05:25:07
Troubleshooting and Tuning LOB...
Troubleshooting and Tuning LOB... 5160 views Aaz24 Wed, 18 Sep 2019, 11:09:34
What Does Java Mean to an Orac...
What Does Java Mean to an Orac... 1102 views Александров Попков Tue, 27 Feb 2018, 18:52:19
View all users currently logge...
View all users currently logge... 549 views Antoni Mon, 14 May 2018, 04:45:04
Viewer
Author: Viewer

Comments on Isolating GC Buffer Busy Waits in Oracle Database 12c

Be the first to comment
Please login to comment