Upon logging in to Database Control at the URL for the specified node, such as https://london1.example.com:1158/em, you are presented at the highest level with two tabs to view the database configuration or the cluster configuration, as shown in Figure 12-1. Your browser requires the Adobe Flash plugin to view the EM performance graphs.

Figure 12.1. Cluster information from Database Control

The Cluster configuration provides details on the hosts in the cluster and clicking the Performance link drills down to a view of CPU, Memory, and I/O Utilization on all of the nodes in the cluster. It also provides a summary of the status of those nodes. You can use this Performance page to observe whether the cluster workload is evenly distributed across the cluster, as well as whether particular nodes are more heavily utilized than others. Similarly, the Interconnects link provides information on the Private Interconnect Transfer Rate in MB/sec for the cluster as a whole, and it shows the Total Error rate for the individual nodes. You should use this information in conjunction with the Linux operating system network performance utilities to ensure that the interconnect performance is not having a disproportionate impact on the performance of the cluster.

The Database tab (see Figure 12-2) shows the performance of the cluster from a database, as opposed to the perspective from the host and operating system.

Figure 12.2. The Database tab from Database Control

The first two graphs on the Performance tab show the Cluster Host Load Average and the Global Cache Block Access Latency. The third graph shows the number of Average Active Sessions throughout the reporting period. The final graph on the Performance tab shows performance from the perspective of Database Throughput, I/O, Parallel Execution, Services, and Instances.

At the bottom of the Performance tab, there are six Additional Monitoring Links. The page you should use to monitor your inter-instance communication is the Cluster Cache Coherency page, as shown in Figure 12-3.

Figure 12.3. Cluster Cache Coherency page

The Cluster Cache Coherency page includes information on Global Cache Block Access Latency (this information also appears on the Performance tab), Global Cache Block Transfer Rate, and Global Cache Block Transfers and Physical Reads. Selecting the appropriate radio button makes the information available in both tabular numeric and graphical form. The Global Cache Block Access Latency is a useful initial statistic for observing the performance of your cluster. For releases of RAC, Oracle has typically recommended that the latency should be less than ten milliseconds before investigating potential performance issues; however, ideally you should expect these values to be no more than two milliseconds. If the latency is exceeding more than two milliseconds, we recommend investigating the performance in context of the workload, as well as looking at the latency of the entire hardware components of your cluster (see Chapter 4 for information on processing your workload more efficiently to reduce inter-instance traffic).

In addition to the overall interconnect latency, the metrics you will refer to most when reviewing your RAC's performance are observable within the Global Cache Block Transfer Rate view. In this view, you can observe the operation of Cache Fusion across the cluster. The constituent metrics are GC CR Blocks Received and GC Current Blocks Received.

The first metric, GC CR Blocks Received, reflects the implementation of read consistency across the entire cluster. In other words, when a SELECT statement is in operation on one node in the cluster, the session will request the data block that contained the data when the query began if another session on any node in the cluster has changed those rows since the query started. If the consistent read request cannot be satisfied from the local buffer cache, but it can be satisfied by shipping a data block from another instance in the cluster, then it is recorded in this metric.

The second metric, GC Current Blocks Received, shows the inter-instance transfer of data blocks that are in a current state. A data block is in a current state when it has been created or modified by an INSERT, UPDATE, or DELETE statement. The most recent data reflects that this action resides in the buffer cache of an instance. If this current block is requested and successfully transferred directly to another instance in the cluster, it is recorded in this metric.

The Global Cache Block Transfers and Physical Reads graph compares the number of block transfers across the interconnect with the number of physical reads. Block transfers across the interconnect are usually more efficient than physical reads, so they are favored by RAC, where possible.

The Global Cache Block Transfers and Physical Reads graph is the Active Session History (ASH) from a Cluster Cache Coherency perspective. Figure 12-4 shows an example of this graph. This data can be viewed in realtime or by dragging the shaded box to observe historical data. in the graph directly below on the same page the workload being processed is shown in relation to the events generated. Clicking a particular Cache Coherency event will drill down to a chart and summary data focused upon that particular event.

Figure 12.4. The Active Session History graph.

In addition to viewing the Active Session History dynamically with EM, you can also generate reports for information related to time periods for which you are interested. The Active Session History (ASH) is a component of the AWR repository. In addition to reports that can be viewed with EM, the following types of reports can be generated directly: AWR reports, ASH reports, and Automatic Database Diagnostic Monitor (ADDM) reports. We will detail how to do so in the following sections; doing so will enable you to generate these reports, even if the EM graphical interface is not available in a particular environment.

The "Top 5 Timed Foreground Events" section will normally show the amount of CPU consumed in addition to the top four wait events. For a given workload, this list of events should be relatively stable; you should investigate any significant variation. You will generally see CPU at the top of the list. However, CPU usage is not necessarily an indication of a healthy system, as the application may be CPU-bound. The most common wait events are db file sequential read, db file scattered read, db file parallel read, and log file sync.

For RAC databases, watch for wait events related to Global Cache Service (GCS), which are identified by the prefix gc or the Cluster wait. In a RAC environment, you should expect to see GCS events, but they should not be consuming the majority of the time on the system. The following example shows the Top 5 Timed Foreground Events from a RAC system that is experiencing a degree of inter-instance activity:

Top 5 Timed Foreground Events
˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜
                                                         Avg
                                                        wait   % DB
Event                               Waits     Time(s)   (ms)   time Wait Class
------------------------------ ---------- ----------- ------ ------ ----------
DB CPU                                            588          28.4
enq: TX - row lock contention      41,498         293      7   14.2 Applicatio
log file sync                     126,489         231      2   11.1 Commit
gc current block 2-way            409,784         230      1   11.1 Cluster
gc cr block 2-way                 407,726         220      1   10.6 Cluster

If your global cache events are prominent—and, in particular, if they appear above CPU time in the report—then this is an indication that you should drill down in the RAC statistics further to identify whether the amount of global cache traffic can be reduced.

Immediately after the "Top 5 Timed Foreground Events" section, the first RAC-specific section summarizes the number of instances for the start and end snapshots, as in this example:

Begin   End
                                ----- -----
           Number of Instances:     2     2

Differing values indicate that an instance has been started or stopped during the snapshot period. If the number of instances is equal at the end of the period, this does not mean that one or more instances have joined or left the cluster during the snapshot period. Starting and stopping instances causes a higher than normal level of inter-instance messaging; therefore, the report should be treated with caution.

This section of the AWR report presents a summary of the traffic across the interconnect in terms of blocks exchanged by GCS and messages exchanged by both GCS and Global Enqueue Service (GES). Consider this example:

Global Cache Load Profile
˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜                  Per Second       Per Transaction
                                      ---------------       ---------------
  Global Cache blocks received:              3,510.45                  5.81
    Global Cache blocks served:              3,526.89                  5.84
     GCS/GES messages received:              7,447.04                 12.32
         GCS/GES messages sent:              6,774.80                 11.21
            DBWR Fusion writes:                578.61                  0.96
 Estd Interconnect traffic (KB)             59,076.44

Pay particular attention to the estimated interconnect traffic per second value at the end of this section. You should reference this value against the bandwidth of your network interconnect to ensure that your configuration has sufficient capacity for your requirements. In this example, utilization is 59MB/sec of a gigabit Ethernet interconnect, which has a theoretical maximum limit of 100MB/sec. Thus, this example shows a reasonably high level of utilization.

This section of the AWR report shows the percentage of blocks accessed from local cache, remote cache, and disk. In an optimum system, the percentage of local cache accesses should approach 100%, while the percentage of remote cache accesses and disk accesses should both approach 0%. The following example shows a level of efficiency in terms of the percentages that you should expect to see:

Global Cache Efficiency Percentages (Target local+remote 100%)
˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜
Buffer access -  local cache %:   94.38
Buffer access - remote cache %:    5.52
Buffer access -         disk %:    0.10

Bear in mind that it may take longer to read a block from disk than from a remote cache, although advancements in disk technology such as Solid State Disks (SSDs), are likely to reduce the margin of advantage towards the interconnect (see Chapter 4 for more information on SSDs). Therefore, you should concentrate on reducing both the amount of disk I/O and the global cache communication.

This section of the AWR report describes the average times required to perform various GCS and GES tasks:

Global Cache and Enqueue Services - Workload Characteristics
˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜
                     Avg global enqueue get time (ms):      0.3

Avg global cache cr block receive time (ms):      1.2
     Avg global cache current block receive time (ms):      1.0

            Avg global cache cr block build time (ms):      0.0
             Avg global cache cr block send time (ms):      0.0
      Global cache log flushes for cr blocks served %:      4.5
            Avg global cache cr block flush time (ms):      1.6

         Avg global cache current block pin time (ms):      0.1
        Avg global cache current block send time (ms):      0.0
 Global cache log flushes for current blocks served %:      3.2
       Avg global cache current block flush time (ms):      2.0

Of these statistics, the most significant are the enqueue get time, which should ideally be below 1 ms; and the global cache cr and current block receive times.

The "Global Cache and Enqueue Services" section of the AWR report describes average time to exchange different categories of inter-instance messages, as in this example:

Global Cache and Enqueue Services - Messaging Statistics
˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜
                     Avg message sent queue time (ms):      0.1
             Avg message sent queue time on ksxp (ms):      0.6
                 Avg message received queue time (ms):      0.0
                    Avg GCS message process time (ms):      0.0
                    Avg GES message process time (ms):      0.0

                            % of direct sent messages:    65.85
                          % of indirect sent messages:    33.18
                        % of flow controlled messages:     0.96

The "Cluster Interconnect" section of the report should be used to confirm that the interconnect network is running across the configured private interconnect, as opposed to a public network:

Cluster Interconnect
˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜˜
                                    Begin                             End
             --------------------------------------------------   -----------
Interface    IP Address      Pub Source                           IP  Pub Src
----------   --------------- --- ------------------------------   --- --- ---
eth1         192.168.1.1     N   Oracle Cluster Repository

The "Foreground Wait Class" enables you to observe wait events in a grouped manner, as opposed to individual wait events. Thus it provides a simpler diagnosis on where performance issues may lie. In the following example, nearly 40% of all wait events are related to cluster activity; these events are likely to be impacting performance:

Foreground Wait Class                      DB/Inst: PROD/PROD1  Snaps: 278-279

                                                                 Avg
                                     %Time       Total Wait     wait
Wait Class                     Waits -outs         Time (s)     (ms)  %DB time
-------------------- --------------- ----- ---------------- -------- ---------
Cluster                    1,068,216     0              814        1      39.3
DB CPU                                                  588               28.4
Application                   41,503     0              293        7      14.2
Commit                       126,489     0              231        2      11.1
User I/O                      17,300     0               73        4       3.5
Configuration                    198     0               26      131       1.3
Other                      1,050,015     0               20        0       1.0
Concurrency                    8,493     2                4        0       0.2
Network                      141,279     0                0        0       0.0
System I/O                        87     0                0        0       0.0

The "Wait Event Histogram" section enables you to observe all RAC-related wait events. It shows the time consumed by the wait events, which are grouped into classes by the time consumed. These wait events range from less than 1 millisecond to longer than a second. In the following example, you can see that block busy and congested related events consume the most time on an individual event basis:

Total
Event                      Waits  <1ms  <2ms  <4ms  <8ms <16ms <32ms  <=1s >1s
-------------------------- ----- ----- ----- ----- ----- ----- ----- ----- ---
...
gc cr block 2-way          407.3  91.3   5.9   2.4    .4    .0    .0    .0
gc cr block busy           27.9K  25.4  50.1  16.8   3.3   2.3   1.8    .3
gc cr block congested        644  66.5   9.0  15.5   8.9    .2
gc cr block lost               2                                     100.0
gc cr failure                377  91.5   5.6   2.7    .3
gc cr grant 2-way           7309  91.8   5.6   2.2    .3    .0
gc cr grant congested         13  53.8   7.7  15.4  23.1
gc cr multi block request  77.3K  97.4   1.8    .8    .1    .0    .0    .0
gc current block 2-way     410.2  91.1   6.1   2.4    .3    .0    .0    .0  .0
gc current block busy      18.6K  19.3  31.0  16.7  14.4  13.4   3.0   2.1  .0
gc current block congested   669  68.5   7.9  14.5   9.0                .1
gc current block lost          1                                     100.0
...

The AWR report contains a series of sections listing SQL statements in the library cache that exceed predefined thresholds. The report does not show the statements per se, but it does show their statement ID numbers. Of particular interest to RAC users are the SQL statements ordered in the "By Cluster Wait Time" section, which details the amount of time a statement was involved in waits for cluster resources. The cluster wait time is reported as a percentage of total elapsed time for the statement, along with the elapsed time, CPU time, and number of executions.

For example, the statement being reported on in the following extract has experienced 384.3 seconds of cluster wait time over 61,113 executions, and the cluster wait time represents 47% of total elapsed time, which is significant enough to warrant further investigation. To do so, you should note the SQL Id used as input into the awrsqlrpt.sql script, as explained later in this chapter:

SQL ordered by Cluster Wait Time          DB/Inst: PROD/PROD1  Snaps: 278-279
     Cluster                        Elapsed
 Wait Time (s) Executions %Total    Time(s)   %Clu   %CPU    %IO    SQL Id
-------------- ---------- ------ ---------- ------ ------ ------ -------------
       384.3       61,113   47.8      804.5   47.2   37.8    1.7 16dhat4ta7xs9
Module: wish8.5@loadgen1 (TNS V1-V3)
begin neword(:no_w_id,:no_max_w_id,:no_d_id,:no_c_id,:no_o_ol_cnt,
:no_c_discount,:no_c_last,:no_c_credit,:no_d_tax,:no_w_tax,:no_d_next_o_id,
TO_DATE(:timestamp,'YYYYMMDDHH24MISS')); END;

It is possible to observe the RAC-related segment statistics when viewed in combination with the SQL related data. These statistics include: global cache buffer busy, CR blocks received, and current blocks received. In the following example, you can observe that a large degree of the Global Cache Busy related statistics are generated by SQL running against the WAREHOUSE table and the CR and Current Blocks against the STOCK table:

Segments by Global Cache Buffer Busy       DB/Inst: PROD/PROD1  Snaps: 278-279

                                                                  GC
         Tablespace                      Subobject  Obj.        Buffer    % of
Owner       Name    Object Name            Name     Type          Busy Capture
-------- ---------- -------------------- ---------- ----- ------------ -------
TPCC     TPCCTAB    WAREHOUSE                       TABLE        6,199   55.66
TPCC     TPCCTAB    INORD                           INDEX        3,440   30.89
TPCC     TPCCTAB    ORDERS                          TABLE          630    5.66
TPCC     TPCCTAB    IORDL                           INDEX          441    3.96
TPCC     TPCCTAB    DISTRICT                        TABLE          251    2.25

Segments by CR Blocks Received            DB/Inst: PROD/PROD1  Snaps: 278-279

                                                                 CR
         Tablespace                      Subobject  Obj.       Blocks
Owner       Name    Object Name            Name     Type      Received  %Total
-------- ---------- -------------------- ---------- ----- ------------ -------
TPCC     TPCCTAB    STOCK                           TABLE      321,799   54.74

TPCC     TPCCTAB    CUSTOMER                        TABLE       97,031   16.51
TPCC     TPCCTAB    INORD                           INDEX       61,230   10.42
TPCC     TPCCTAB    IORDL                           INDEX       38,203    6.50
TPCC     TPCCTAB    ORDERS                          TABLE       17,544    2.98


Segments by Current Blocks Received        DB/Inst: PROD/PROD1  Snaps: 278-279

                                                              Current
         Tablespace                      Subobject  Obj.       Blocks
Owner       Name    Object Name            Name     Type      Received  %Total
-------- ---------- -------------------- ---------- ----- ------------ -------
TPCC     TPCCTAB    STOCK                           TABLE      220,543   45.90
TPCC     TPCCTAB    IORDL                           INDEX       57,394   11.94
TPCC     TPCCTAB    INORD                           INDEX       42,573    8.86
TPCC     TPCCTAB    DISTRICT                        TABLE       38,989    8.11
TPCC     TPCCTAB    CUSTOMER                        TABLE       37,746    7.86

For RAC instances, a subsection of the "Dictionary Cache Stats" section reports the number of GES requests, conflicts, and releases for each object type, as in this example:

Dictionary Cache Stats (RAC)               DB/Inst: PROD/PROD1  Snaps: 278-279

                                   GES          GES          GES
Cache                         Requests    Conflicts     Releases
------------------------- ------------ ------------ ------------
dc_awr_control                       2            2            0
dc_objects                          10            0            6
dc_segments                         10           12            0
dc_sequences                        26            3            0

In this report, you should look for an excessive numbers of GES conflicts, especially for sequences.

For RAC instances, a subsection of the "Library Cache Activity" section reports the number of GES lock requests, pin requests, pin releases, invalidation requests, and invalidations. Here's an example of that subsection:

Library Cache Activity (RAC)               DB/Inst: PROD/PROD1  Snaps: 278-279

                    GES Lock      GES Pin      GES Pin   GES Inval GES Invali-
Namespace           Requests     Requests     Releases    Requests     dations
--------------- ------------ ------------ ------------ ----------- -----------
BODY                       0       69,471       69,471           0           0
CLUSTER                    3            3            3           0           0

DBLINK                    20            0            0           0           0
EDITION                   18           18           18           0           0
INDEX                     84           84           84           0           0
QUEUE                      0          223          223           0           0
RULESET                    0            4            4           0           0
SCHEMA                    16            0            0           0           0
SUBSCRIPTION               0            2            2           0           0
TABLE/PROCEDURE        1,089      211,136      211,136           0           0
TRIGGER                    0           89           89           0           0

In the event that any serious Global Messaging problems appear in the "Top 5 Timed Foreground Events" or in the "Wait Events" sections, the "Global Messaging Statistics" section can be used to investigate further, as in this example:

Global Messaging Statistics               DB/Inst: PROD/PROD1  Snaps: 278-279

Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
acks for commit broadcast(actual)           99,813        328.0          0.5
acks for commit broadcast(logical          191,511        629.2          1.0
broadcast msgs on commit(actual)           154,497        507.6          0.8
broadcast msgs on commit(logical)          159,559        524.3          0.9
broadcast msgs on commit(wasted)            36,032        118.4          0.2
broadcast on commit wait time(ms)            6,075         20.0          0.0
broadcast on commit waits                    9,799         32.2          0.1

The "Global CR Served Statistics" section contains the statistics for the number of blocks served by GCS. These statistics give an overview of GCS activity during the reporting period, and they are derived from the V$CR_BLOCK_SERVER dynamic performance view, as in this example:

Global CR Served Stats                    DB/Inst: PROD/PROD1  Snaps: 278-279

Statistic                                   Total
------------------------------ ------------------
CR Block Requests                         566,707
CURRENT Block Requests                     29,833
Data Block Requests                       566,710
Undo Block Requests                         3,317
TX Block Requests                          26,341

The "Global Current Served Statistics" section of the AWR report contains histograms of the GCS operations required to support current read requests, including block pinning, flushing redo to disk, and write operations. The report is derived from the V$CURRENT_BLOCK_SERVER dynamic performance view. You should check this view if you believe that Cache Fusion is causing a bottleneck on systems with high levels of DML activity, as in this example:

Global CURRENT Served Stats                DB/Inst: PROD/PROD1  Snaps: 278-279
-> Pins    = CURRENT Block Pin Operations
-> Flushes = Redo Flush before CURRENT Block Served Operations
-> Writes  = CURRENT Block Fusion Write Operations

Statistic         Total   % <1ms  % <10ms % <100ms    % <1s   % <10s
---------- ------------ -------- -------- -------- -------- --------
Pins            477,147    99.65     0.32     0.02     0.01     0.00
Flushes          15,128    51.65    44.53     3.81     0.00     0.00
Writes          176,102     0.23    15.83    26.38    57.18     0.38

The "Global Cache Transfer Statistics" section of the report gives an overview of Global Cache transfer activity for each class of database buffer, as in the following example. This data is derived from the V$INSTANCE_CACHE_TRANSFER dynamic performance view; it is especially useful in diagnosing whether cache transfer activity is affected by contention or a high level of system load. You can use this data to analyze both circumstances. For Clusters with more than two nodes, these statistics can also be used to determine the impact of messaging between multiple nodes in the cluster:

Global Cache Transfer Stats                DB/Inst: PROD/PROD1  Snaps: 278-279
                               CR                         Current
                 ----------------------------- -----------------------------
Inst Block         Blocks      %      %      %   Blocks      %      %      %
  No Class       Received  Immed   Busy Congst Received  Immed   Busy Congst
---- ----------- -------- ------ ------ ------ -------- ------ ------ ------
   2 data block   556,975   95.6    3.2    1.2  478,247   95.8    3.7     .5
   2 undo header   27,929   66.7   33.2     .1    1,717   52.6   47.4     .0
   2 undo block     2,894   68.6   31.3     .0        0    N/A    N/A    N/A
   2 Others           181   97.8    1.7     .6      653   98.9     .6     .5
...

Global Cache DB/Inst: PROD/PROD1  Snaps: 278-279
-> Blocks Lost, 2-hop and 3-hop Average times in (ms)
-> ordered by CR + Current Blocks Received desc

                            CR Avg Time (ms)           Current Avg Time (ms)
                    ---------------------------- ----------------------------
 Src Block     Lost
Inst Class     Time      Immed     2hop     3hop      Immed     2hop     3hop
---- -------- ----- ---------- -------- -------- ---------- -------- --------
   2 data blo              1.1      1.1      N/A        0.8      0.8      N/A
   2 undo hea              0.6      0.6      N/A        0.7      0.7      N/A
   2 undo blo              0.6      0.6      N/A        N/A      N/A      N/A
   2 others                0.6      0.6      N/A        0.6      0.6      N/A

The "Interconnect Statistics" section enables you to observe the latency and throughput of your interconnect against the potential of the hardware selected for your cluster (see Chapter 4 for more information on this). You can use these statistics to determine whether the physical interconnect infrastructure is a bottleneck in your environment.

The following example shows the Interconnect Statistics section of the report. It provides ping latency statistics, throughput by client statistics, and device statistics:

Interconnect Ping Latency Stats            DB/Inst: PROD/PROD1  Snaps: 278-279
  Target 500B Pin Avg Latency      Stddev  8K Ping Avg Latency      Stddev
 Instance    Count    500B msg    500B msg    Count      8K msg      8K msg
--------- -------- ----------- ----------- -------- ----------- -----------
        1       24         .14         .03       24         .12         .03
        2       24         .49         .56       24         .59         .54

Interconnect Throughput by Client          DB/Inst: PROD/PROD1  Snaps: 278-279
-> Throughput of interconnect usage by major consumers
-> All throughput numbers are megabytes per second

                        Send     Receive
Used By           Mbytes/sec  Mbytes/sec
---------------- ----------- -----------
Global Cache           24.93       24.78
Parallel Query           .00         .00
DB Locks                1.88        2.04
DB Streams               .00         .00
Other                    .00         .00

The "Dynamic Remastering Statistics" section gives you information on the internal Oracle workload in the mastering or ownership of the status of the Database objects. You also get information on Oracle workload reconfiguration, which you can use to help reduce the amount of messaging required by your workload, as in this example:

Dynamic Remastering Stats                  DB/Inst: PROD/PROD1  Snaps: 278-279
-> times are in seconds
-> Affinity objects - objects mastered due to affinity at begin/end snap

                                                 per    Begin      End
Name                             Total   Remaster Op     Snap     Snap
------------------------- ------------ ------------- -------- --------
remaster ops                         1          1.00
remastered objects                   2          2.00
replayed locks received         92,841     92,841.00
replayed locks sent            131,129    131,129.00
resources cleaned                    0          0.00
remaster time (s)                  3.3          3.30
quiesce time (s)                   1.6          1.55

freeze time (s)                    0.0          0.01
cleanup time (s)                   0.4          0.36
replay time (s)                    0.6          0.58
fixwrite time (s)                  0.2          0.19
sync time (s)                      0.6          0.60
affinity objects                                 N/A        7        5

ADDM runs automatically after each AWR snapshot, and the results are saved in the database. If ADDM detects any issues, then alerts are generated that can be inspected in the EM tools.

You can also run an ADDM report manually using the following script:

$ORACLE_HOME/rdbms/admin/addmrpt.sql

This script is similar to those used to generate AWR reports. It prompts for a start and end snapshot, as well as for the name of an output file. If no output file name is specified, then the report will be written to a file called:

addmrpt_<instance>_<start_snapshot>_<stop_snapshot>.txt

For example, the file name with its bracketed information filled in might look like this:

addmrpt_1_278_279.txt

Although the procedure for running ADDM reports is similar to that used with AWR reports, the content of an ADDM report is significantly different.

The DBMS_ADVISOR and DBMS_ADDM packages allow you to control ADDM. The simplest way to access the DBMS_ADVISOR package is from EM, which shows a complete performance overview, including any recommendations, on a single page. The addmprt.sql script also calls the DBMS_ADVISOR package, allowing you to access ADDM manually. You can access the DBMS_ADVISOR API directly using PL/SQL calls. In addition to DBMS_ADVISOR, the DBMS_ADDM package enables the generation of reports for all instances of a RAC environment.

ADDM stores information in a set of tables owned by SYS in the SYSAUX tablespace. The base tables have a prefix of WRI$%. These tables are accessible through a number of views with the prefix, DBA_ADVISOR_%.

ADDM needs at least two snapshots in the AWR before it can perform any analysis. The ADDM report header describes the analysis period, database and instance names, hostname, and database version:

ADDM Report for Task 'TASK_875'
          -------------------------------

Analysis Period
---------------
AWR snapshot range from 278 to 279.
Time period starts at 15-FEB-10 10.39.53 AM
Time period ends at 15-FEB-10 10.58.19 AM

Analysis Target
---------------
Database 'PROD' with DB ID 121667269.
Database version 11.2.0.1.0.
ADDM performed an analysis of instance PROD1, numbered 1 and hosted at
london1.example.com.

Activity During the Analysis Period
-----------------------------------
Total database time was 2068 seconds.
The average number of active sessions was 1.87.

Summary of Findings
-------------------
Description                               Active Sessions      Recommendations
                                          Percent of Activity
----------------------------------------  -------------------  --------------

1  Top SQL Statements                        1.1 | 59.02       8
2  Global Cache Messaging                    .72 | 38.46       1
3  Top Segments by "User I/O" and "Cluster"  .71 | 37.74       5
4  Buffer Busy - Hot Objects                 .29 | 15.73       0
5  Row Lock Waits                            .26 | 14.17       2
6  Commits and Rollbacks                     .22 | 11.72       2

The header specifies the measured time period, and the ADDM report is based on this statistic. The report header also summarizes the average database load during the snapshot period and a summary of the findings.

The DBMS_ADDM package provides similar functionality, except for multiple instances in a RAC environment. For example, calling DBMS_ADDM.ANALYZE_DB with the database name and start and end snapshot ids enables the analysis of all of the instances in the cluster. DBMS_ADDM.ANALYZE_INST does the analysis on one instance in the cluster, while DBMS_ADDM.ANALYZE_PARTIAL does the analysis on a selected number of instances in the cluster. For example, the following excerpt shows the generation of a partial report for instances 1, 2, and 3 of a 4-node cluster:

Begin
   :name := 'Partial ADDM RAC';   dbms_addm.analyze_partial(:name,'1,2,3',1012,1387);
End;

set long 1000000 pagesize 0;
select dbms_addm.get_report(:name) from dual;

An ADDM report is divided up into findings, which are listed in descending order of their perceived impact on database time. The following example shows a finding from an ADDM report. The finding highlights RAC-based wait events under the "Cluster wait" category. In this case, the recommendation was to investigate an UPDATE statement responsible for a significant proportion of the cluster wait time:

Finding 2: Global Cache Messaging
Impact is 2.62 active sessions, 38.46% of total activity.
---------------------------------------------------------
Inter-instance messaging was consuming significant database time on this
instance.

   Recommendation 1: Application Analysis
   Estimated benefit is .72 active sessions, 38.46% of total activity.
   -------------------------------------------------------------------
   Action
      Look at the "Top SQL Statements" finding for SQL statements consuming
      significant time on Cluster waits. For example, the UPDATE statement
      with SQL_ID "82tfppq8s0dc2" is responsible for 20% of Cluster wait
      during the analysis period.

   Symptoms That Led to the Finding:
   ---------------------------------
      Wait class "Cluster" was consuming significant database time.
      Impact is .74 active sessions, 39.33% of total activity.

Before reviewing the SQL*Plus queries, we will briefly review the relationship between the X$ tables, GV$ views, and V$ views. All are compiled into the Oracle executable and exist for both single-instance and RAC databases.

X$ tables present instance or session memory structures as tables. Although they still exist, RAC-specific X$ tables are not populated for single-instance databases; instead, X$ tables are defined in V$FIXED_TABLE.

GV$ views are also built into the Oracle executable. Therefore, they exist for all databases (including single-instance ones), and they reference the X$ tables.

The standard V$ views are also built into the Oracle executable. In general, V$ views do not directly reference X$ tables; instead, they generally use them indirectly through GV$ views, using queries of the following form:

SELECT <column_list>
FROM gv$<view_name>
WHERE inst_id = USERENV ('INSTANCE'),

Both the GV$ and V$ views are defined in V$FIXED_VIEW_DEFINITION.

In a RAC environment, a number of additional synonyms for GV$ and V$ views are created by the script, $ORACLE_HOME/rdbms/admin/catclust.sql.

GV$ views include the instance number. When they are queried, the query is executed separately on all active instances using parallel execution. The results are merged on the instance initiating the query. GV$ views are not meaningful in a few cases because the views are derived from the control files. Because the control files are shared between all instances, the GV$ views return with duplicate sets of rows from each instance. For these views, we recommend using the local V$ dynamic performance views instead.

System statistics are maintained in each instance. They are reported in a number of dynamic performance views, including V$SYSSTAT, V$SESSTAT, and V$MYSTAT. In Oracle 11g, a subset of service statistics is also reported at a session level in V$SERVICE_STATS.

Segment statistics were introduced in Oracle 9.2. They provide a powerful mechanism you can use to identify which objects are subject to I/O or contention. Prior to the introduction of segment statistics, this information could only be obtained using an event 10046 level 8 trace or by polling the V$SYSTEM_WAIT dynamic performance view. The level of granularity is the object, so you may still need to use the 10046 trace if you need to identify a specific block or set of blocks.

Segment statistics are maintained in all databases when the value of the STATISTICS_LEVEL parameter is TYPICAL or ALL. Three statistics report global cache activity at the object level, and you can use these to quickly pinpoint the segments in your database that are experiencing the highest levels of inter-instance activity: gc cr blocks received, gc current blocks received, and gc buffer busy. You should be familiar with the first two statistics from the charts displayed in EM. The additional statistic, gc buffer busy, shows the segments that are experiencing levels of contention between instances.

The following example shows the global cache activity for the table segments of a schema named TPCC:

SQL> li
  1  SELECT
  2    table_name                 AS "Table Name",
  3    gc_buffer_busy             AS "Buffer Busy",
  4    gc_cr_blocks_received      AS "CR Blocks Received",
  5    gc_current_blocks_received AS "Current Blocks Received"
  6  FROM
  7  (
  8    SELECT table_name FROM dba_tables
  9    WHERE owner = 'TPCC'
 10  ) t,
 11  (
 12    SELECT object_name,value AS gc_buffer_busy
 13    FROM v$segment_statistics
 14    WHERE owner = 'TPCC'
 15    AND object_type = 'TABLE'
 16    AND statistic_name = 'gc buffer busy'
 17  ) ss1,
 18  (
 19    SELECT object_name,value AS gc_cr_blocks_received
 20    FROM v$segment_statistics
 21    WHERE owner = 'TPCC'
 22    AND object_type = 'TABLE'
 23    AND statistic_name = 'gc cr blocks received'
 24  ) ss2,
 25  (
 26    SELECT object_name,value AS gc_current_blocks_received
 27    FROM v$segment_statistics
 28    WHERE owner = 'TPCC'
 29    AND object_type = 'TABLE'
 30    AND statistic_name = 'gc current blocks received'
 31  ) ss3
 32  WHERE t.table_name = ss1.object_name
 33  AND t.table_name = ss2.object_name
 34* AND t.table_name = ss3.object_name
SQL> /

Table Name Buffer Busy CR Blocks Received Current Blocks Received
---------- ----------- ------------------ -----------------------
CUSTOMER            16             546039                  180391
DISTRICT           537              10130                  200367
HISTORY            238              11840                    7000
ITEM                 0                  0                       0
WAREHOUSE        13172              88643                  113499
STOCK              159            1649497                 1215391

ORDERS            1775             102103                   94959

7 rows selected.

Pay particular attention to the buffer busy statistics, which can help you identify contention between instances. In this example, the STOCK table is responsible for the highest degree of inter-instance messaging. However, the WAREHOUSE table is responsible for the highest degree of contention, which confirms the findings previously noted in the sections that covered AWR, ASH, and ADDM.

If contention is high, it means there are a large number of rows per block, and sessions are updating multiple rows in that block, as opposed to a single row. Thus you may benefit from distributing the rows across a greater number of blocks by increasing the value of PCTFREE for the object in question.

You can investigate GCS activity in terms of consistent reads and current reads. For example, you can use this formula to monitor the performance of consistent reads:

(gc cr block receive time X 10) / (gc cr blocks received)

The preceding formula returns the average time for consistent read block requests for the instance in milliseconds, and it is the GC CR metric of the Global Cache Block Transfer Rate that you observe within the EM RAC performance views. This is the most important value for determining interconnect performance because it usually reflects the largest constituent component of interconnect traffic.

Requests for global resources for data blocks originate in the buffer cache of the requesting instance. Before a request enters the GCS request queue, Oracle allocates data structures in the SGA to track the state of the request. It also collects statistics on these resource structures. The following query and output shows the consistent reads receive time, the blocks received, and the average latency:

SQL> li
  1  SELECT
  2    gc_cr_block_receive_time AS "Receive Time",
  3    gc_cr_blocks_received AS "Blocks Received",
  4    (gc_cr_block_receive_time * 10) /
  5      gc_cr_blocks_received AS "Average Latency (MS)"
  6  FROM
  7  (
  8      SELECT value AS gc_cr_block_receive_time FROM v$sysstat
  9      WHERE name = 'gc cr block receive time'
 10  ),
 11  (
 12    SELECT value AS gc_cr_blocks_received FROM v$sysstat
 13    WHERE name = 'gc cr blocks received'
 14* )
SQL> /

Receive Time Blocks Received Average Latency (MS)
------------ --------------- --------------------
      365718         3378788           1.08239404

The latency of a consistent block request is the time elapsed between the original request and the receipt of the consistent block image at the local instance. Using a Gigabit Ethernet interconnect, this value should normally be less than 2ms and should not exceed 10ms. However, this value can be affected by the system configuration and volume. In this example, you see that the average latency is 1ms; therefore, it lies within expectations. If latencies exceed 10ms, you should first use the Linux operating system utilities, such as netstat (see Chapter 13 for more information on this utility), to determine whether there are any network configuration issues, such as network packet send and receive errors. You should also use operating system utilities such as top and sar to measure the load on the nodes themselves.

If you have double-checked that the interconnect is configured correctly, and you are still experiencing high average latencies, you might consider reducing the value of DB_FILE_MULTIBLOCK_READ_COUNT. This parameter specifies the number of blocks a process will request in a single operation. The process will have to wait for all blocks to be returned; therefore, higher values may cause longer waits. Before you adjust this parameter, carefully assess its potential impact on the entire workload.

High average latencies may also be caused by a high number of incoming requests or multiple nodes dispatching requests to the LMS process. You can calculate the average LMS service time as follows:

average LMS service time = average latency
        - average time to build consistent read block
        - average time to wait for log flush
        - average time to send completed block

Average latency is calculated as shown previously. Average time to build a consistent read block is calculated as follows:

gc cr block build time / gc cr block served

Similarly, average time spent waiting for a redo log flush is calculated as follows:

gc cr block flush time / gc cr blocks served

Average time to send a completed block is calculated as follows:

gc cr block send time / gc cr blocks served

The following query can be used to calculate the average LMS service time for consistent block reads:

SQL> li
  1  SELECT
  2  average_latency AS "Average Latency",
  3  average_build_time AS "Average Build Time",
  4  average_flush_time AS "Average Flush Time",
  5  average_send_time AS "Average Send Time",
  6  average_latency - average_build_time - average_flush_time - average_send _time
  7       AS "Average LMS Service Time"
  8   FROM

9   (
 10     SELECT
 11 (gc_cr_block_receive_time * 10) / gc_cr_blocks_received AS average_latency,
 12 (gc_cr_block_build_time * 10) / gc_cr_blocks_served AS average_build_time,
 13 (gc_cr_block_flush_time * 10) / gc_cr_blocks_served AS average_flush_time,
 14 (gc_cr_block_send_time * 10) / gc_cr_blocks_served AS average_send_time
 15     FROM
 16     (
 17       SELECT value AS gc_cr_block_receive_time FROM v$sysstat
 18       WHERE name = 'gc cr block receive time'
 19     ),
 20     (
 21       SELECT value AS gc_cr_blocks_received FROM v$sysstat
 22       WHERE name = 'gc cr blocks received'
 23     ),
 24     (
 25       SELECT value AS gc_cr_block_build_time FROM v$sysstat
 26       WHERE name = 'gc cr block build time'
 27     ),
 28     (
 29       SELECT value AS gc_cr_block_flush_time FROM v$sysstat
 30       WHERE name = 'gc cr block flush time'
 31     ),
 32     (
 33       SELECT value AS gc_cr_block_send_time FROM v$sysstat
 34       WHERE name = 'gc cr block send time'
 35     ),
 36     (
 37       SELECT value AS gc_cr_blocks_served FROM v$sysstat
 38       WHERE name = 'gc cr blocks served'
 39     )
 40*  )
SQL> /

Average Latency Average Build Time Average Flush Time Average Send Time
--------------- ------------------ ------------------ -----------------
Average LMS Service Time
------------------------
     1.11967715         .012249408         .113152249        .030138924
              .964136572

The difference between the average latency time and the sum of the average build, flush, and send times represents the time spent in the LMS service and the time spent transmitting the messages across the interconnect. In the example just shown, the LMS Service Time is within expected boundaries.

You can also use SQL*Plus to determine the current block activity, which is the GC Current metric of the Global Cache Block Transfer Rate from the EM RAC performance views. You can calculate the overall average latency involved in processing requests for current blocks using the following formula:

(gc current block receive time X 10) / (gc current blocks received)

The latency is returned using the following query:

SQL> li
  1  SELECT
  2     gc_current_block_receive_time AS "Receive Time",
  3     gc_current_blocks_received AS "Blocks Received",
  4     (gc_current_block_receive_time * 10) / gc_current_blocks_received
  5     AS "Average (MS)"
  6   FROM
  7   (
  8     SELECT value AS gc_current_block_receive_time
  9     FROM v$sysstat
 10     WHERE name = 'gc current block receive time'
 11   ),
 12   (
 13     SELECT value AS gc_current_blocks_received
 14     FROM v$sysstat
 15     WHERE name = 'gc current blocks received'
 16*  )
SQL> /

Receive Time Blocks Received Average (MS)
------------ --------------- ------------
      462110         4514548   1.02360192

You can calculate the amount of overall latency that can be attributed to the LMS process using the following snippet:

average LMS service time =
        average latency
        - average time to pin current blocks
        - average time to wait for log flush
        - average time to send completed block

The average latency is calculated as follows:

gc current block receive time / gc current blocks received

Similarly, the average time to pin current blocks is calculated as follows:

gc current block pin time / gc current block served

The average time spent waiting for a redo log flush is calculated as follows:

gc current block flush time / gc current blocks served

And the average time to send a completed block is calculated as follows:

gc current block send time / gc current blocks served

You can use the following query to calculate the average LMS service time for current block reads:

SQL> li
  1  SELECT
  2    average_latency    AS "Average Latency",
  3    average_pin_time   AS "Average Pin Time",
  4    average_flush_time AS "Average Flush Time",
  5    average_send_time  AS "Average Send Time",
  6    average_latency - average_pin_time - average_flush_time - average_send_time
  7    AS "Average LMS Service Time"
  8  FROM
  9  (
 10    SELECT
 11      (gc_current_block_receive_time * 10) / gc_current_blocks_received
 12        AS average_latency,
 13      (gc_current_block_pin_time * 10) / gc_current_blocks_served
 14        AS average_pin_time,
 15      (gc_current_block_flush_time * 10) / gc_current_blocks_served
 16        AS average_flush_time,
 17      (gc_current_block_send_time * 10) / gc_current_blocks_served
 18        AS average_send_time
 19    FROM
 20    (
 21      SELECT value AS gc_current_block_receive_time FROM v$sysstat
 22      WHERE name = 'gc current block receive time'
 23    ),
 24    (
 25      SELECT value AS gc_current_blocks_received FROM v$sysstat
 26      WHERE name = 'gc current blocks received'
 27    ),
 28    (
 29      SELECT value AS gc_current_block_pin_time FROM v$sysstat
 30      WHERE name = 'gc current block pin time'
 31    ),
 32    (
 33      SELECT value AS gc_current_block_flush_time FROM v$sysstat
 34      WHERE name = 'gc current block flush time'
 35    ),
 36    (
 37      SELECT value AS gc_current_block_send_time FROM v$sysstat
 38      WHERE name = 'gc current block send time'
 39    ),
 40    (
 41      SELECT value AS gc_current_blocks_served FROM v$sysstat
 42      WHERE name = 'gc current blocks served'

43    )
 44* )
SQL> /

Average Latency Average Pin Time Average Flush Time Average Send Time
--------------- ---------------- ------------------ -----------------
Average LMS Service Time
------------------------
     1.06117092       .148803422         .104628541        .028298863
              .779440094

High latency values may indicate server or interconnect performance issues; however, you should review your current block statistics against the possibility of contention for data between instances.

GES manages all the non-Cache Fusion intrainstance and inter-instance resource operations. High GES workload request rates can adversely affect performance. To calculate the average global enqueue time in milliseconds, use the following statement:

SQL> li
  1  SELECT
  2    global_enqueue_get_time AS "Get Time",
  3    global_enqueue_gets_sync AS "Synchronous Gets",
  4    global_enqueue_gets_async AS "Asynchronous Gets",
  5    (global_enqueue_get_time * 10) /
  6    (global_enqueue_gets_sync + global_enqueue_gets_async)
  7    AS "Average (MS)"
  8  FROM
  9  (
 10    SELECT value AS global_enqueue_get_time
 11    FROM v$sysstat
 12    WHERE name = 'global enqueue get time'
 13  ),
 14  (
 15    SELECT value AS global_enqueue_gets_sync
 16    FROM v$sysstat
 17    WHERE name = 'global enqueue gets sync'
 18  ),
 19  (
 20    SELECT value AS global_enqueue_gets_async
 21    FROM v$sysstat
 22    WHERE name = 'global enqueue gets async'
 23* )
SQL> /

  Get Time Synchronous Gets Asynchronous Gets Average (MS)
---------- ---------------- ----------------- ------------
    358597         27946887            115393   .127786124

Synchronous gets are usually locking events, whereas asynchronous gets are usually caused by nonblocking inter-instance process activity.

You can obtain further information about global enqueue activity caused by statement parsing and execution from the V$LIBRARYCACHE dynamic performance view. This view reports GES activity for locks, pins, and invalidations, as in this example:

SQL> li
  1  SELECT
  2    namespace                   AS "Namespace",
  3    dlm_lock_requests           AS "Lock Requests",
  4    dlm_pin_requests            AS "Pin Requests",
  5    dlm_pin_releases            AS "Pin Releases",
  6    dlm_invalidation_requests   AS "Invalidation Requests",
  7    dlm_invalidations           AS "Invalidations"
  8* FROM v$librarycache
SQL> /

Namespace       Lock Requests Pin Requests Pin Releases Invalidation Requests
--------------- -------------  ------------ ------------ --------------------
Invalidations
---------------
SQL AREA                    0             0            0                    0
0

TABLE/PROCEDURE        640720       5551128      5551128                    0
0

BODY                        2       1965888      1965888                    0
0

TRIGGER                     0         70915        70915                    0
0

INDEX                   14849         14849        14849                    0
0

CLUSTER                  2479          2503         2503                    0
0

If you see excessively high values in these columns, solutions include pinning packages in the shared pool using the KEEP procedure in the DBMS_SHARED_POOL package. Also, you might investigate methods of keeping cursors open either within the application or by using the SESSION_CACHED_CURSORS parameter.

You can obtain additional information about global enqueue activity caused by statement pinning dictionary cache objects from the V$ROWCACHE dynamic performance view. This view reports GES lock requests, conflicts, and releases, as in this example:

SQL> li
  1  SELECT
  2    parameter      AS "Cache Name",
  3    dlm_requests   AS "Requests",
  4    dlm_conflicts  AS "Conflicts",
  5    dlm_releases   AS "Releases"
  6* FROM v$rowcache
SQL> /

Cache Name                         Requests  Conflicts   Releases
-------------------------------- ---------- ---------- ----------
dc_rollback_segments                     99          0          0
dc_free_extents                           0          0          0
dc_used_extents                           0          0          0
dc_segments                            7335        469       4158
dc_tablespaces                           20          0          0
dc_tablespace_quotas                      0          0          0
dc_files                                  6          0          0
dc_users                                352          0        152
dc_objects                             8712        106       1177
dc_global_oids                          171          0         27
dc_constraints                         2135          0          0
dc_sequences                            733         43          5
dc_histogram_defs                     56671        103       1828
kqlsubheap_object                         0          0          0
dc_table_scns                           121          0          0
...

You should look for high values in the Conflicts column. In the preceding example, there have been 43 conflicts for sequences. Uncached sequences are one of the most common causes of performance issues in RAC.

In Oracle 11g, most of the statistics related to lock conversions can be obtained from V$GES_CONVERT_LOCAL and V$GES_CONVERT_REMOTE. These views show the number of lock conversions on the local system and on remote systems, respectively:

SQL> li
  1  SELECT convert_type,average_convert_time, convert_count
  2* FROM v$ges_convert_local
SQL> /

CONVERT_TYPE     AVERAGE_CONVERT_TIME CONVERT_COUNT
---------------- -------------------- -------------
NULL -> SS                          0             0
NULL -> SX                          2             3
NULL -> S                           1         24064
NULL -> SSX                         5           627
NULL -> X                           3        166358
SS   -> SX                          0             0
SS   -> S                           0             0
SS   -> SSX                         0             0
SS   -> X                           0             0
SX   -> S                           0             0
SX   -> SSX                         0             0
SX   -> X                           0             0
S    -> SX                          0             0
S    -> SSX                         0             0
S    -> X                           1          1693
SSX  -> X                           1            12

Lock conversions are essential to the efficient operation of a RAC database; moreover, they are not necessarily harmful. However, it is important to check that lock conversions, like lock requests, are not being blocked by instances holding incompatible locks.

To check which instances are currently blocking other instances, use the following query:

SQL> li
  1  SELECT
  2    dl.inst_id,
  3    s.sid,
  4    p.spid,
  5    dl.resource_name1,
  6    decode (substr (dl.grant_level,1,8),
  7      'KJUSERNL','Null',
  8      'KJUSERCR','Row-S (SS)',
  9      'KJUSERCW','Row-X (SX)',
 10      'KJUSERPR','Share',
 11      'KJUSERPW','S/Row-X (SSX)',
 12      'KJUSEREX','Exclusive',
 13    request_level) as grant_level,
 14    decode(substr(dl.request_level,1,8),
 15      'KJUSERNL','Null',
 16      'KJUSERCR','Row-S (SS)',
 17      'KJUSERCW','Row-X (SX)',
 18      'KJUSERPR','Share',
 19      'KJUSERPW','S/Row-X (SSX)',
 20      'KJUSEREX','Exclusive',
 21    request_level) as request_level,
 22    decode(substr(dl.state,1,8),
 23      'KJUSERGR','Granted','KJUSEROP','Opening',
 24      'KJUSERCA','Cancelling',
 25      'KJUSERCV','Converting'
 26    ) as state,

27    sw.event,
 28    sw.seconds_in_wait sec
 29  FROM
 30    gv$ges_enqueue dl,
 31    gv$process p,
 32    gv$session s,
 33    gv$session_wait sw
 34  WHERE blocker = 1
 35  AND (dl.inst_id = p.inst_id AND dl.pid = p.spid)
 36  AND (p.inst_id = s.inst_id AND p.addr = s.paddr)
 37  AND (s.inst_id = sw.inst_id AND s.sid = sw.sid)
 38* ORDER BY sw.seconds_in_wait DESC
SQL> /

   INST_ID        SID SPID                     RESOURCE_NAME1
---------- ---------- ------------------------ ------------------------------
GRANT_LEVEL   REQUEST_LEVEL STATE
------------- ------------- ----------
EVENT                                                                   SEC
---------------------------------------------------------------- ----------
         2        127 5520                 [0x19][0x2],[RS]
Exclusive     Exclusive
rdbms ipc message                                                         2

         1        131 28215                    [0x1c000a][0x18a12],[TX]
Exclusive     Exclusive
gc cr request                                                             0

         2        126 5496                     [0x110012][0x5659b],[TX]
Exclusive     Exclusive
ges remote message

To discover which sessions are currently being blocked, first find this line in the preceding query:

>   WHERE blocker = 1

Once you locate the line, change it to this instead:

>   WHERE blocked = 1