Oracle Database 12c is the most complete and simultaneously complex database in today’s database marketplace. Every time Oracle launches a new release, a lot of new features are made available, and anyone who works as an Oracle database administrator (DBA) knows how difficult it is to stay current on all feature sets. It is entirely possible that an Oracle DBA may be required to manage multiple databases that are running on completely different releases of Oracle. To add to this complexity, each database may have several distinct features enabled, including various versions of table and index partitioning, Oracle Advanced Compression, replication between databases using either Oracle Streams or Oracle GoldenGate, Oracle Real Application Clusters (RAC), and many others. A RAC database is one of the most difficult environments to administer because its different architecture exists mainly to offer high availability for database applications. This article therefore focuses on the global cache (gc) buffer busy wait event, one of the most commonly encountered wait events in a RAC database.
Overview of Buffer Busy Wait Events
As its name implies, any buffer busy wait event in an Oracle database simply means that at least one session is waiting for a buffer in the instance’s database buffer cache to become available. In Oracle Database versions prior to Release 10.1, there was just one event called buffer busy wait, but starting with that release a new event, gc buffer busy, was added to the mix for RAC databases to help monitor buffer busy waits related to cache fusion.
Starting with Oracle Database 12c Release 1, the Oracle Database 12c Reference Guide (Oracle, 2016) now describes four wait events to consider when discussing this type of wait event:
- Buffer busy: A session cannot pin the buffer in the buffer cache because another session has pinned the buffer.
- Read by other session: A session cannot pin the buffer in the buffer cache because another session is reading the buffer from disk.
- GC buffer busy acquire: A session cannot pin the buffer in the buffer cache because another session is reading the buffer from the cache of another database instance in the RAC cluster database.
- GC buffer busy release: A session cannot pin the buffer in the buffer cache because another session on another database instance in the RAC cluster database is bringing that buffer from a different instance’s buffer cache into its own cache so it can be pinned.
This my blog article focuses on some important topics related to the gc buffer busy wait event:
- How to leverage the ORAchk utility to help keep a RAC database healthy
- How to use Automatic Workload Repository, Automatic Database Diagnostic Monitor, and Active Session History to find important information regarding gc buffer busy wait events
- How to investigate whether a RAC database is experiencing an unnecessarily high frequency of the gc buffer busy wait event, and how to handle and repair this situation
Leveraging the ORAchk Utility
The original RACcheck tool was renamed to
ORAchk in early 2015, and for an excellent reason: it now not only works for RAC databases but also supports a lot of new features and products, such as Oracle GoldenGate, Oracle E-Business Suite, Oracle Sun Systems, and Oracle Enterprise Manager Cloud Control. ORAchk has thus been transformed into a serious audit tool for all Oracle database environments.
The following examples show how simple it is to deploy and use the ORAchk tool within a real-world Exadata X4-2 full rack environment. A full rack contains eight database nodes and 14 Exadata storage cells, but in this example, the full rack has been subdivided between two RAC databases, one using six nodes and the other using the remaining two nodes. Both databases leverage the Automatic Storage Management (ASM) disk groups resident across all 14 storage cells.
Complete details about how to download, install, and leverage the ORAchk utility can be found in MOS Note 1268927.2, “ORAchk—Health checks for the Oracle stack.”
- Download the file described in the MOS note previously mentioned, transfer the file to one of the Exadata database nodes, and execute the following commands while logged in as the root user or a user with root permissions. (ORAchk requires that a user with root permissions must install the utility.)
[root@ex01dbadm01 tmp]# cd [root@ex01dbadm01 ~]# mkdir ORAchk [root@ex01dbadm01 ~]# cd ORAchk/ [root@ex01dbadm01 ORAchk]# unzip /tmp/orachk.zip Archive: /tmp/orachk.zip inflating: UserGuide.txt inflating: collections.dat inflating: rules.dat ... inflating: .cgrep/lcgrep6 inflating: .cgrep/profile_only.dat inflating: .cgrep/auto_upgrade_check.pl inflating: .cgrep/diff_collections.pl inflating: CollectionManager_App.sql inflating: orachk
- After unzipping all Exachk files, review the appropriate readme file to see if there are any new instructions pertaining to installing and configuring Exachk.
- Run the ORAchk utility and answer all questions, as shown. Note that the warning about the binary being older than 120 days can be ignored if the file has just been downloaded:
[root@ex01dbadm01 ORAchk]# ./orachk This version of orachk was released on 09-Oct-2014 and its older than 120 days. No new version of orachk is available in RAT_UPGRADE_LOC. It is highly recommended that you download the latest version of orachk from my oracle support to ensure the highest level of accuracy of the data contained within the report. Do you want to continue running this version? [y/n][y] CRS stack is running and CRS_HOME is not set. Do you want to set CRS_HOME to /u01/app/220.127.116.11/grid?[y/n][y] ...
- You can deploy the ORAchk utility so that it will automatically verify whether a newer version is available, as well as send an email to the appropriate account after each regularly scheduled execution, as follows:
AUTORUN_SCHEDULE can be interpreted as follows:
AUTORUN_SCHEDULE * * * * :- Automatic run at specific time in daemon mode. - - - - ? ? ? ? ? ? ? +----- day of week (0 - 6) (0 to 6 are Sunday to Saturday) ? ? +---------- month (1 - 12) ? +--------------- day of month (1 - 31) +-------------------- hour (0 - 23)
- To verify whether ORAchk has been customized in any way, run the following command:
- Finally, you can configure ORAchk to start automatically after any server reboot by running the following command:
Results of ORAchk Execution: Sample Output
After the ORAchk utility finishes its execution, it generates a report in HTML format that lists all information regarding the environment in which the tool was executed. Figure 1 shows the main page of this output that lists any findings with a status of
WARNING that should be investigated.
Figure 1 Summary section of an ORAchk report
Reviewing the details for one of the steps marked as
FAILED is as simple as clicking on the related link, as shown in Figure 2.
Figure 2 Detail section of an ORAchk report
As this ORAchk detailed report section shows, there are issues that the Oracle DBA can research to isolate other potential causes behind a gc buffer busy wait event. In this case, there is an issue with the configuration of the Exadata’s InfiniBand network components that may be contributing to poor application performance.