Monitoring the Oracle Streams Topology and Performance in 11g Oracle 11g New Features Tips by Donald BurlesonJuly 12, 2015

Oracle 11g New Features Tips

Streams Performance

11g adds new features for the DBA to view the topology and performance of Streams.  Every Oracle database has at least one constraint and it may just be Streams.  To aid in identifying bottlenecks in the Streams process, 11g has added some new views for the DBA which can be utilized via commands or AWR.  First let's cover the command line views for monitoring performance of Streams.

Oracle has new advisors that allow you to interrogate the performance of Streams: 

View	Description
DBA_STREAMS_TP_PATH_BOTTLENECK	Displays components identified by Oracle as a bottleneck
DBA_STREAMS_TP_COMPONENT_STAT	Displays performance statistics for the components in an Oracle Streams topology
DBA_STREAMS_TP_PATH_STAT	Displays path statistics for the stream paths

Before these views can be queried, they must first be populated (well their underlying tables must be?.).  To view the performance of Streams first execute the ANALYZE_CURRENT_PERFORMANCE procedure.

SQL> execute dbms_streams_advisor_adm.analyze_current_performance;

PL/SQL procedure successfully completed.

Running the above procedure updates the Oracle Streams performance tables.

Execute the ANALYZE_CURRENT_PERFORMANCE procedure at any time to gather the most current performance statistics.

Next query the DBA_STREAMS_TP_COMPONENT_STAT table to identify the most recent ID. 

SQL> select max(advisor_run_id) run_id from dba_streams_tp_component_stat order by advisor_run_id;

    RUN_ID
----------
         3

Remember this number as it is used in the next query.  The DBA_STREAMS_TP_PATH_BOTTLENECK view identifies if Oracle has flagged any components as a constraint.  The following script exposes the bottlenecks.  Pay attention to the BOTTLENECK_IDENTIFIED field as it will indicate if the component has been flagged by Oracle as a constraint.

column c0 heading 'Component Id' format 999
column c1 heading 'Name' format a20
column c2 heading 'Type' format a20
column c3 heading 'Database' format a15
column c4 heading 'Bottleneck?' format a15

select component_id,
       component_name,
       component_type,
       component_db,
       bottleneck_identified
from dba_streams_tp_path_bottleneck
where advisor_run_id=3
order by path_id, component_id;

Component Id Name                 Type    Database        Bottleneck?
------------ -------------------- ------- --------------- ---------------------
           3 STREAMS_CAPTURE2     CAPTURE DEV11           YES

If the above query indicates any bottlenecks then first check if the components are enabled.  From there dig into the streams environment to identify the cause of the constraint.

To drill into the statistics of each component in the Oracle Streams topology use the following query:

column component_id heading 'Id' format 999
column component_name heading 'Name' format a15
column component_type heading 'Type' format a10
column statistic_name heading 'Statistics' format a25
column statistic_value heading 'Value' format 9999999.9
column statistic_unit heading 'Unit' format a24

select component_id,
       component_name,
       component_type,
       statistic_name,
       case when comp.statistic_unit='bytes' then comp.statistic_value/1048576
          else comp.statistic_value end statistic_value,
       case when comp.statistic_unit='bytes' then 'megabytes'
          else comp.statistic_unit end statistic_unit
from dba_streams_tp_component_stat  comp
where advisor_run_id=3
  and session_id is null
  and session_serial# is null
order by component_id, component_name, component_type, statistic_name;

  Id Name            Type       Statistics                     Value Unit
----------------------------------------------------------------------------------------
   3 STREAMS_CAPTURE CAPTURE    BYTES SENT VIA SQL*NET TO        1.4 MEGABYTES
     2                           DBLINK 

   3 STREAMS_CAPTURE CAPTURE    CAPTURE RATE                     1.2 MESSAGES PER SECOND
     2

   3 STREAMS_CAPTURE CAPTURE    ENQUEUE RATE                      .0 MESSAGES PER SECOND
     2

   3 STREAMS_CAPTURE CAPTURE    EVENT: ASM file metadata         1.0 PERCENT
     2                          operation

   3 STREAMS_CAPTURE CAPTURE    EVENT: CPU + Wait for CPU       14.0 PERCENT
     2

   3 STREAMS_CAPTURE CAPTURE    EVENT: log file sequentia         .3 PERCENT
     2                          l read

   3 STREAMS_CAPTURE CAPTURE    LATENCY                           .0 SECONDS
     2

   3 STREAMS_CAPTURE CAPTURE    SEND RATE TO APPLY                .0 BYTES PER SECOND
     2

   6 STREAMS_APPLY2  APPLY      EVENT: CPU + Wait for CPU         .7 PERCENT

   6 STREAMS_APPLY2  APPLY      LATENCY                         34.0 SECONDS

   6 STREAMS_APPLY2  APPLY      MESSAGE APPLY RATE                .0 MESSAGES PER SECOND

   6 STREAMS_APPLY2  APPLY      TRANSACTION APPLY RATE            .0 TRANSACTIONS PER                                                                                   SECOND

Finally to display path statistics for the stream paths the DBA can run the following to query the DBA_STREAMS_TP_PATH_STAT view:

column path_id heading 'Path id' format 999
column statistic_name heading 'Statistic' format a25
column statistic_value heading 'Value' format 99999999.9
column statistic_unit heading 'Unit' format a25

select path_id,
       statistic_name,
       statistic_value,
       statistic_unit
from dba_streams_tp_path_stat
where advisor_run_id=2
order by path_id, statistic_name; 

The output below shows the number of transactions and messages happening on the stream every second.

Path ID Statistic                       Value Unit
------- ------------------------- ----------- -------------------------
      2 MESSAGE RATE                      2.1 MESSAGES PER SECOND
      2 TRANSACTION RATE                   .2 TRANSACTIONS PER SECOND

As mentioned above 11g also adds new views to the AWR report for monitoring Streams as shown in Figure 9.11.  This is just one more tool for the DBA of a replicated environment to use for identifying bottlenecks.