27 Monitoring Rules

The following topics describe monitoring rules, rule sets, and evaluation contexts:

Note:

The Oracle Streams tool in Oracle Enterprise Manager Cloud Control is also an excellent way to monitor an Oracle Streams environment. See the online Help for the Oracle Streams tool for more information.

Displaying All Rules Used by All Oracle Streams Clients

Oracle Streams rules are created using the DBMS_STREAMS_ADM package or the Oracle Streams tool in Oracle Enterprise Manager Cloud Control. Oracle Streams rules in the rule sets for an Oracle Streams client determine the behavior of the Oracle Streams client. Oracle Streams clients include capture processes, propagations, apply processes, and messaging clients. The rule sets for an Oracle Streams client can also contain rules created using the DBMS_RULE_ADM package, and these rules also determine the behavior of the Oracle Streams client.

For example, if a rule in the positive rule set for a capture process evaluates to TRUE for DML changes to the hr.employees table, then the capture process captures DML changes to this table. However, if a rule in the negative rule set for a capture process evaluates to TRUE for DML changes to the hr.employees table, then the capture process discards DML changes to this table.

You query the following data dictionary views to display all rules in the rule sets for Oracle Streams clients, including Oracle Streams rules and rules created using the DBMS_RULE_ADM package:

  • ALL_STREAMS_RULES

  • DBA_STREAMS_RULES

In addition, these two views display the current rule condition for each rule and whether the rule condition has been modified.

The query in this section displays the following information about all of the rules used by Oracle Streams clients in a database:

  • The name of each Oracle Streams client that uses the rule

  • The type of each Oracle Streams client that uses the rule, either CAPTURE for a capture process, SYNCHRONOUS CAPTURE for a synchronous capture, PROPAGATION for a propagation, APPLY for an apply process, or DEQUEUE for a messaging client

  • The name of the rule

  • The type of rule set that contains the rule for the Oracle Streams client, either POSITIVE or NEGATIVE

  • For Oracle Streams rules, the Oracle Streams rule level, either GLOBAL, SCHEMA, or TABLE

  • For Oracle Streams rules, the name of the schema for schema rules and table rules

  • For Oracle Streams rules, the name of the table for table rules

  • For Oracle Streams rules, the rule type, either DML or DDL

Run the following query to display this information:

COLUMN STREAMS_NAME HEADING 'Oracle|Streams|Name' FORMAT A14
COLUMN STREAMS_TYPE HEADING 'Oracle|Streams|Type' FORMAT A11
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A12
COLUMN RULE_SET_TYPE HEADING 'Rule Set|Type' FORMAT A8
COLUMN STREAMS_RULE_TYPE HEADING 'Oracle|Streams|Rule|Level' FORMAT A7
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A11
COLUMN RULE_TYPE HEADING 'Rule|Type' FORMAT A4

SELECT STREAMS_NAME, 
       STREAMS_TYPE,
       RULE_NAME,
       RULE_SET_TYPE,
       STREAMS_RULE_TYPE,
       SCHEMA_NAME,
       OBJECT_NAME,
       RULE_TYPE
  FROM DBA_STREAMS_RULES;

Your output looks similar to the following:

                                                 Oracle
Oracle         Oracle                            Streams
Streams        Streams     Rule         Rule Set Rule    Schema Object      Rule
Name           Type        Name         Type     Level   Name   Name        Type
-------------- ----------- ------------ -------- ------- ------ ----------- ----
STRM01_CAPTURE CAPTURE     JOBS4        POSITIVE TABLE   HR     JOBS        DML
STRM01_CAPTURE CAPTURE     JOBS5        POSITIVE TABLE   HR     JOBS        DDL
DBS1_TO_DBS2   PROPAGATION HR18         POSITIVE SCHEMA  HR                 DDL
DBS1_TO_DBS2   PROPAGATION HR17         POSITIVE SCHEMA  HR                 DML
APPLY          APPLY       HR20         POSITIVE SCHEMA  HR                 DML
APPLY          APPLY       JOB_HISTORY2 NEGATIVE TABLE   HR     JOB_HISTORY DML
OE             DEQUEUE     RULE$_28     POSITIVE

This output provides the following information about the rules used by Oracle Streams clients in the database:

  • The DML rule jobs4 and the DDL rule jobs5 are both table rules for the hr.jobs table in the positive rule set for the capture process strm01_capture.

  • The DML rule hr17 and the DDL rule hr18 are both schema rules for the hr schema in the positive rule set for the propagation dbs1_to_dbs2.

  • The DML rule hr20 is a schema rule for the hr schema in the positive rule set for the apply process apply.

  • The DML rule job_history2 is a table rule for the hr schema in the negative rule set for the apply process apply.

  • The rule rule$_28 is a messaging rule in the positive rule set for the messaging client oe.

The ALL_STREAMS_RULES and DBA_STREAMS_RULES views also contain information about the rule sets used by an Oracle Streams client, the current and original rule condition for Oracle Streams rules, whether the rule condition has been changed, the subsetting operation and DML condition for each Oracle Streams subset rule, the source database specified for each Oracle Streams rule, and information about the message type and message variable for Oracle Streams messaging rules.

The following data dictionary views also display Oracle Streams rules:

  • ALL_STREAMS_GLOBAL_RULES

  • DBA_STREAMS_GLOBAL_RULES

  • ALL_STREAMS_MESSAGE_RULES

  • DBA_STREAMS_MESSAGE_RULES

  • ALL_STREAMS_SCHEMA_RULES

  • DBA_STREAMS_SCHEMA_RULES

  • ALL_STREAMS_TABLE_RULES

  • DBA_STREAMS_TABLE_RULES

These views display Oracle Streams rules only. They do not display any manual modifications to these rules made by the DBMS_RULE_ADM package, and they do not display rules created using the DBMS_RULE_ADM package. These views can display the original rule condition for each rule only. They do not display the current rule condition for a rule if the rule condition was modified after the rule was created.

Displaying the Oracle Streams Rules Used by a Specific Oracle Streams Client

To determine which rules are in a rule set used by a particular Oracle Streams client, you can query the DBA_STREAMS_RULES data dictionary view. For example, suppose a database is running an apply process named strm01_apply. The following sections describe how to determine the rules in the positive rule set and negative rule set for this apply process.

The following sections describe how to determine which rules are in a rule set used by a particular Oracle Streams client:

Displaying the Rules in the Positive Rule Set for an Oracle Streams Client

The following query displays all of the rules in the positive rule set for an apply processs named strm01_apply:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A12
COLUMN STREAMS_RULE_TYPE HEADING 'Oracle Streams|Rule|Level' FORMAT A7
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A11
COLUMN RULE_TYPE HEADING 'Rule|Type' FORMAT A4
COLUMN SOURCE_DATABASE HEADING 'Source' FORMAT A10
COLUMN INCLUDE_TAGGED_LCR HEADING 'Apply|Tagged|LCRs?' FORMAT A9

SELECT RULE_OWNER,
       RULE_NAME,
       STREAMS_RULE_TYPE,
       SCHEMA_NAME,
       OBJECT_NAME,
       RULE_TYPE,
       SOURCE_DATABASE,
       INCLUDE_TAGGED_LCR
  FROM DBA_STREAMS_RULES
  WHERE STREAMS_NAME  = 'STRM01_APPLY' AND
        RULE_SET_TYPE = 'POSITIVE';

If this query returns any rows, then the apply process applies LCRs containing changes that evaluate to TRUE for the rules.

Your output looks similar to the following:

                           Oracle Streams                                    Apply
           Rule            Rule    Schema Object      Rule            Tagged
Rule Owner Name            Level   Name   Name        Type Source     LCRs?
---------- --------------- ------- ------ ----------- ---- ---------- ---------
STRMADMIN  HR20            SCHEMA  HR                 DML   DBS1.EXAM NO
                                                            PLE.COM
STRMADMIN  HR21            SCHEMA  HR                 DDL   DBS1.EXAM NO
                                                            PLE.COM

Assuming the rule conditions for the Oracle Streams rules returned by this query have not been modified, these results show that the apply process applies LCRs containing DML changes and DDL changes to the hr schema and that the LCRs originated at the dbs1.example.com database. The rules in the positive rule set that instruct the apply process to apply these LCRs are owned by the strmadmin user and are named hr20 and hr21. Also, the apply process applies an LCR that satisfies one of these rules only if the tag in the LCR is NULL.

If the rule condition for an Oracle Streams rule has been modified, then you must check the current rule condition to determine the effect of the rule on an Oracle Streams client. Oracle Streams rules whose rule condition has been modified have NO for the SAME_RULE_CONDITION column.

Displaying the Rules in the Negative Rule Set for an Oracle Streams Client

The following query displays all of the rules in the negative rule set for an apply process named strm01_apply:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule|Name' FORMAT A15
COLUMN STREAMS_RULE_TYPE HEADING 'Oracle Streams|Rule|Level' FORMAT A7
COLUMN SCHEMA_NAME HEADING 'Schema|Name' FORMAT A6
COLUMN OBJECT_NAME HEADING 'Object|Name' FORMAT A11
COLUMN RULE_TYPE HEADING 'Rule|Type' FORMAT A4
COLUMN SOURCE_DATABASE HEADING 'Source' FORMAT A10
COLUMN INCLUDE_TAGGED_LCR HEADING 'Apply|Tagged|LCRs?' FORMAT A9

SELECT RULE_OWNER,
       RULE_NAME,
       STREAMS_RULE_TYPE,
       SCHEMA_NAME,
       OBJECT_NAME,
       RULE_TYPE,
       SOURCE_DATABASE,
       INCLUDE_TAGGED_LCR
  FROM DBA_STREAMS_RULES
  WHERE STREAMS_NAME  = 'APPLY' AND
        RULE_SET_TYPE = 'NEGATIVE';

If this query returns any rows, then the apply process discards LCRs containing changes that evaluate to TRUE for the rules.

Your output looks similar to the following:

                           Oracle Streams                                    Apply
           Rule            Rule    Schema Object      Rule            Tagged
Rule Owner Name            Level   Name   Name        Type Source     LCRs?
---------- --------------- ------- ------ ----------- ---- ---------- ---------
STRMADMIN  JOB_HISTORY22   TABLE   HR     JOB_HISTORY DML  DBS1.EXAMP YES
                                                           LE.COM
STRMADMIN  JOB_HISTORY23   TABLE   HR     JOB_HISTORY DDL  DBS1.EXAMP YES
                                                           LE.COM

Assuming the rule conditions for the Oracle Streams rules returned by this query have not been modified, these results show that the apply process discards LCRs containing DML changes and DDL changes to the hr.job_history table and that the LCRs originated at the dbs1.example.com database. The rules in the negative rule set that instruct the apply process to discard these LCRs are owned by the strmadmin user and are named job_history22 and job_history23. Also, the apply process discards an LCR that satisfies one of these rules regardless of the value of the tag in the LCR.

If the rule condition for an Oracle Streams rule has been modified, then you must check the current rule condition to determine the effect of the rule on an Oracle Streams client. Oracle Streams rules whose rule condition has been modified have NO for the SAME_RULE_CONDITION column.

Displaying the Current Condition for a Rule

If you know the name of a rule, then you can display its rule condition. For example, consider the rule returned by the query in "Displaying the Oracle Streams Rules Used by a Specific Oracle Streams Client". The name of the rule is hr1, and you can display its condition by running the following query:

SET LONG  8000
SET PAGES 8000
SELECT RULE_CONDITION "Current Rule Condition"
  FROM DBA_STREAMS_RULES 
  WHERE RULE_NAME  = 'HR1' AND
        RULE_OWNER = 'STRMADMIN';

Your output looks similar to the following:

Current Rule Condition
--------------------------------------------------------------------------------
((((:dml.get_object_owner() = 'HR') and :dml.get_source_database_name() = 'DA.EX
AMPLE.COM' )) and (:dml.get_compatible() <= dbms_streams.compatible_11_2))

Displaying Modified Rule Conditions for Oracle Streams Rules

It is possible to modify the rule condition of an Oracle Streams rule. These modifications can change the behavior of the Oracle Streams clients using the Oracle Streams rule. In addition, some modifications can degrade rule evaluation performance.

The following query displays the rule name, the original rule condition, and the current rule condition for each Oracle Streams rule whose condition has been modified:

COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A12
COLUMN ORIGINAL_RULE_CONDITION HEADING 'Original Rule Condition' FORMAT A33
COLUMN RULE_CONDITION HEADING 'Current Rule Condition' FORMAT A33

SET LONG  8000
SET PAGES 8000
SELECT RULE_NAME, ORIGINAL_RULE_CONDITION, RULE_CONDITION
  FROM DBA_STREAMS_RULES 
  WHERE SAME_RULE_CONDITION = 'NO';

Your output looks similar to the following:

Rule Name    Original Rule Condition           Current Rule Condition
------------ --------------------------------- ---------------------------------
HR20         ((:dml.get_object_owner() = 'HR') ((:dml.get_object_owner() = 'HR')
              and :dml.is_null_tag() = 'Y' )    and :dml.is_null_tag() = 'Y' and
                                                :dml.get_object_name() != 'JOB_H
                                               ISTORY')

In this example, the output shows that the condition of the hr20 rule has been modified. Originally, this schema rule evaluated to TRUE for all changes to the hr schema. The current modified condition for this rule evaluates to TRUE for all changes to the hr schema, except for DML changes to the hr.job_history table.

Note:

The query in this section applies only to Oracle Streams rules. It does not apply to rules created using the DBMS_RULE_ADM package because these rules always show NULL for the ORIGINAL_RULE_CONDITION column and NULL for the SAME_RULE_CONDITION column.

Displaying the Evaluation Context for Each Rule Set

The following query displays the default evaluation context for each rule set in a database:

COLUMN RULE_SET_OWNER HEADING 'Rule Set|Owner' FORMAT A10
COLUMN RULE_SET_NAME HEADING 'Rule Set Name' FORMAT A20
COLUMN RULE_SET_EVAL_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A12
COLUMN RULE_SET_EVAL_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A30

SELECT RULE_SET_OWNER, 
       RULE_SET_NAME, 
       RULE_SET_EVAL_CONTEXT_OWNER,
       RULE_SET_EVAL_CONTEXT_NAME
  FROM DBA_RULE_SETS;

Your output looks similar to the following:

Rule Set                        Eval Context
Owner      Rule Set Name        Owner        Eval Context Name
---------- -------------------- ------------ ------------------------------
STRMADMIN  RULESET$_2           SYS          STREAMS$_EVALUATION_CONTEXT
STRMADMIN  STRM02_QUEUE_R       STRMADMIN    AQ$_STRM02_QUEUE_TABLE_V
STRMADMIN  APPLY_OE_RS          STRMADMIN    OE_EVAL_CONTEXT
STRMADMIN  OE_QUEUE_R           STRMADMIN    AQ$_OE_QUEUE_TABLE_V
STRMADMIN  AQ$_1_RE             STRMADMIN    AQ$_OE_QUEUE_TABLE_V
SUPPORT    RS                   SUPPORT      EVALCTX
OE         NOTIFICATION_QUEUE_R OE           AQ$_NOTIFICATION_QUEUE_TABLE_V

Displaying Information About the Tables Used by an Evaluation Context

The following query displays information about the tables used by an evaluation context named evalctx, which is owned by the support user:

COLUMN TABLE_ALIAS HEADING 'Table Alias' FORMAT A20
COLUMN TABLE_NAME HEADING 'Table Name' FORMAT A40

SELECT TABLE_ALIAS,
       TABLE_NAME
  FROM DBA_EVALUATION_CONTEXT_TABLES
  WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND
        EVALUATION_CONTEXT_NAME = 'EVALCTX';

Your output looks similar to the following:

Table Alias          Table Name
-------------------- ----------------------------------------
PROB                 problems

Displaying Information About the Variables Used in an Evaluation Context

The following query displays information about the variables used by an evaluation context named evalctx, which is owned by the support user:

COLUMN VARIABLE_NAME HEADING 'Variable Name' FORMAT A15
COLUMN VARIABLE_TYPE HEADING 'Variable Type' FORMAT A15
COLUMN VARIABLE_VALUE_FUNCTION HEADING 'Variable Value|Function' FORMAT A20
COLUMN VARIABLE_METHOD_FUNCTION HEADING 'Variable Method|Function' FORMAT A20

SELECT VARIABLE_NAME,
       VARIABLE_TYPE,
       VARIABLE_VALUE_FUNCTION,
       VARIABLE_METHOD_FUNCTION
  FROM DBA_EVALUATION_CONTEXT_VARS
  WHERE EVALUATION_CONTEXT_OWNER = 'SUPPORT' AND
        EVALUATION_CONTEXT_NAME = 'EVALCTX';

Your output looks similar to the following:

                                Variable Value       Variable Method
Variable Name   Variable Type   Function             Function
--------------- --------------- -------------------- --------------------
CURRENT_TIME    DATE            timefunc

Displaying All of the Rules in a Rule Set

The query in this section displays the following information about all of the rules in a rule set:

  • The owner of the rule.

  • The name of the rule.

  • The evaluation context for the rule, if any. If a rule does not have an evaluation context, and no evaluation context is specified in the ADD_RULE procedure when the rule is added to a rule set, then it inherits the evaluation context of the rule set.

  • The evaluation context owner, if the rule has an evaluation context.

For example, to display this information for each rule in a rule set named oe_queue_r that is owned by the user strmadmin, run the following query:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A10
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A20
COLUMN RULE_EVALUATION_CONTEXT_NAME HEADING 'Eval Context Name' FORMAT A27
COLUMN RULE_EVALUATION_CONTEXT_OWNER HEADING 'Eval Context|Owner' FORMAT A11

SELECT R.RULE_OWNER, 
       R.RULE_NAME, 
       R.RULE_EVALUATION_CONTEXT_NAME,
       R.RULE_EVALUATION_CONTEXT_OWNER
  FROM DBA_RULES R, DBA_RULE_SET_RULES RS 
  WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND 
        RS.RULE_SET_NAME = 'OE_QUEUE_R' AND 
  RS.RULE_NAME = R.RULE_NAME AND 
  RS.RULE_OWNER = R.RULE_OWNER;

Your output looks similar to the following:

                                                            Eval Contex
Rule Owner Rule Name            Eval Context Name           Owner
---------- -------------------- --------------------------- -----------
STRMADMIN  HR1                  STREAMS$_EVALUATION_CONTEXT SYS
STRMADMIN  APPLY_LCRS           STREAMS$_EVALUATION_CONTEXT SYS
STRMADMIN  OE_QUEUE$3
STRMADMIN  APPLY_ACTION

Displaying the Condition for Each Rule in a Rule Set

The following query displays the condition for each rule in a rule set named hr_queue_r that is owned by the user strmadmin:

SET LONGCHUNKSIZE 4000
SET LONG 4000
COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A15
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A15
COLUMN RULE_CONDITION HEADING 'Rule Condition' FORMAT A45

SELECT R.RULE_OWNER, 
       R.RULE_NAME, 
       R.RULE_CONDITION
  FROM DBA_RULES R, DBA_RULE_SET_RULES RS 
  WHERE RS.RULE_SET_OWNER = 'STRMADMIN' AND 
        RS.RULE_SET_NAME = 'HR_QUEUE_R' AND 
  RS.RULE_NAME = R.RULE_NAME AND 
  RS.RULE_OWNER = R.RULE_OWNER;

Your output looks similar to the following:

Rule Owner      Rule Name       Rule Condition
--------------- --------------- ---------------------------------------------
STRMADMIN       APPLY_ACTION     hr.get_hr_action(tab.user_data) = 'APPLY'
STRMADMIN       APPLY_LCRS      :dml.get_object_owner() = 'HR' AND  (:dml.get
                                _object_name() = 'DEPARTMENTS' OR 
                                :dml.get_object_name() = 'EMPLOYEES')

STRMADMIN       HR_QUEUE$3      hr.get_hr_action(tab.user_data) != 'APPLY'

Listing Each Rule that Contains a Specified Pattern in Its Condition

To list each rule in a database that contains a specified pattern in its condition, you can query the DBMS_RULES data dictionary view and use the DBMS_LOB.INSTR function to search for the pattern in the rule conditions. For example, the following query lists each rule that contains the pattern 'HR' in its condition:

COLUMN RULE_OWNER HEADING 'Rule Owner' FORMAT A30
COLUMN RULE_NAME HEADING 'Rule Name' FORMAT A30

SELECT RULE_OWNER, RULE_NAME FROM DBA_RULES 
  WHERE DBMS_LOB.INSTR(RULE_CONDITION, 'HR', 1, 1) > 0;

Your output looks similar to the following:

Rule Owner                     Rule Name
------------------------------ ------------------------------
STRMADMIN                      DEPARTMENTS4
STRMADMIN                      DEPARTMENTS5
STRMADMIN                      DEPARTMENTS6

Displaying Aggregate Statistics for All Rule Set Evaluations

You can query the V$RULE_SET_AGGREGATE_STATS dynamic performance view to display statistics for all rule set evaluations since the database instance last started.

The query in this section contains the following information about rule set evaluations:

  • The number of rule set evaluations.

  • The number of rule set evaluations that were instructed to stop on the first hit.

  • The number of rule set evaluations that were instructed to evaluate only simple rules.

  • The number of times a rule set was evaluated without issuing any SQL. Generally, issuing SQL to evaluate rules is more expensive than evaluating rules without issuing SQL.

  • The number of centiseconds of CPU time used for rule set evaluation.

  • The number of centiseconds spent on rule set evaluation.

  • The number of SQL executions issued to evaluate a rule in a rule set.

  • The number of rule conditions processed during rule set evaluation.

  • The number of TRUE rules returned to the rules engine clients.

  • The number of MAYBE rules returned to the rules engine clients.

  • The number of times the following types of functions were called during rule set evaluation: variable value function, variable method function, and evaluation function.

Run the following query to display this information:

COLUMN NAME HEADING 'Name of Statistic' FORMAT A55
COLUMN VALUE HEADING 'Value' FORMAT 999999999

SELECT NAME, VALUE FROM V$RULE_SET_AGGREGATE_STATS;

Your output looks similar to the following:

Name of Statistic                                            Value
------------------------------------------------------- ----------
rule set evaluations (all)                                    5584
rule set evaluations (first_hit)                              5584
rule set evaluations (simple_rules_only)                      3675
rule set evaluations (SQL free)                               5584
rule set evaluation time (CPU)                                 179
rule set evaluation time (elapsed)                            1053
rule set SQL executions                                          0
rule set conditions processed                                11551
rule set true rules                                             10
rule set maybe rules                                           328
rule set user function calls (variable value function)         182
rule set user function calls (variable method function)      12794
rule set user function calls (evaluation function)            3857

Note:

A centisecond is one-hundredth of a second. So, for example, this output shows 1.79 seconds of CPU time and 10.53 seconds of elapsed time.

Displaying Information About Evaluations for Each Rule Set

You can query the V$RULE_SET dynamic performance view to display information about evaluations for each rule set since the database instance last started. The query in this section contains the following information about each rule set in a database:

  • The owner of the rule set.

  • The name of the rule set.

  • The total number of evaluations of the rule set since the database instance last started.

  • The total number of times SQL was executed to evaluate rules since the database instance last started. Generally, issuing SQL to evaluate rules is more expensive than evaluating rules without issuing SQL.

  • The total number of evaluations on the rule set that did not issue SQL to evaluate rules since the database instance last started.

  • The total number of TRUE rules returned to the rules engine clients using the rule set since the database instance last started.

  • The total number of MAYBE rules returned to the rules engine clients using the rule set since the database instance last started.

Run the following query to display this information for each rule set in the database:

COLUMN OWNER HEADING 'Rule Set|Owner' FORMAT A9
COLUMN NAME HEADING 'Rule Set|Name' FORMAT A11
COLUMN EVALUATIONS HEADING 'Total|Evaluations' FORMAT 99999999
COLUMN SQL_EXECUTIONS HEADING 'SQL|Executions' FORMAT 99999999
COLUMN SQL_FREE_EVALUATIONS HEADING 'SQL Free|Evaluations' FORMAT 99999999
COLUMN TRUE_RULES HEADING 'True|Rules' FORMAT 999999999
COLUMN MAYBE_RULES HEADING 'Maybe|Rules' FORMAT 99999999

SELECT OWNER, 
       NAME, 
       EVALUATIONS,
       SQL_EXECUTIONS,
       SQL_FREE_EVALUATIONS,
       TRUE_RULES,
       MAYBE_RULES
  FROM V$RULE_SET;

Your output looks similar to the following:

Rule Set  Rule Set          Total        SQL    SQL Free       True     Maybe
Owner     Name        Evaluations Executions Evaluations      Rules     Rules
--------- ----------- ----------- ---------- ----------- ---------- ---------
SYS       ALERT_QUE_R           3          0           0          2         0
STRMADMIN RULESET$_4           86          0           0         43         1
STRMADMIN RULESET$_11         458          0           0         11         0
STRMADMIN RULESET$_9           87          0           0          1        42
STRMADMIN RULESET$_7           87          0           0         44         1

Note:

Querying the V$RULE_SET view can have a negative impact on performance if a database has a large library cache.

Determining the Resources Used by Evaluation of Each Rule Set

You can query the V$RULE_SET dynamic performance view to determine the resources used by evaluation of a rule set since the database instance last started. If a rule set was evaluated more than one time since the database instance last started, then some statistics are cumulative, including statistics for the amount of CPU time, evaluation time, and shared memory bytes used.

The query in this section contains the following information about each rule set in a database:

  • The owner of the rule set

  • The name of the rule set

  • The total number of seconds of CPU time used to evaluate the rule set since the database instance last started

  • The total number of seconds used to evaluate the rule set since the database instance last started

  • The total number of shared memory bytes used to evaluate the rule set since the database instance last started

Run the following query to display this information for each rule set in the database:

COLUMN OWNER HEADING 'Rule Set|Owner' FORMAT A15
COLUMN NAME HEADING 'Rule Set Name' FORMAT A15
COLUMN CPU_SECONDS HEADING 'Seconds|of CPU|Time' FORMAT 999999.999
COLUMN ELAPSED_SECONDS HEADING 'Seconds of|Evaluation|Time' FORMAT 999999.999
COLUMN SHARABLE_MEM HEADING 'Bytes|of Shared|Memory' FORMAT 999999999

SELECT OWNER, 
       NAME, 
       (CPU_TIME/100) CPU_SECONDS,
       (ELAPSED_TIME/100) ELAPSED_SECONDS,
       SHARABLE_MEM
  FROM V$RULE_SET;

Your output looks similar to the following:

                                    Seconds  Seconds of      Bytes
Rule Set                             of CPU  Evaluation  of Shared
Owner           Rule Set Name          Time        Time     Memory
--------------- --------------- ----------- ----------- ----------
SYS             ALERT_QUE_R            .230        .490      25120
STRMADMIN       RULESET$_4             .060        .970      25097
STRMADMIN       RULESET$_11            .040        .030      25098
STRMADMIN       RULESET$_9             .220       3.040      25505
STRMADMIN       RULESET$_7             .040        .380      21313

Note:

Querying the V$RULE_SET view can have a negative impact on performance if a database has a large library cache.

Displaying Evaluation Statistics for a Rule

You can query the V$RULE dynamic performance view to display evaluation statistics for a particular rule since the database instance last started. The query in this section contains the following information about each rule set in a database:

  • The total number of times the rule evaluated to TRUE since the database instance last started.

  • The total number of times the rule evaluated to MAYBE since the database instance last started.

  • The total number of evaluations on the rule that issued SQL since the database instance last started. Generally, issuing SQL to evaluate a rule is more expensive than evaluating the rule without issuing SQL.

For example, run the following query to display this information for the locations25 rule in the strmadmin schema:

COLUMN TRUE_HITS HEADING 'True Evaluations' FORMAT 99999999999
COLUMN MAYBE_HITS HEADING 'Maybe Evaluations' FORMAT 99999999999
COLUMN SQL_EVALUATIONS HEADING 'SQL Evaluations' FORMAT 99999999999

SELECT TRUE_HITS, MAYBE_HITS, SQL_EVALUATIONS 
  FROM V$RULE
  WHERE RULE_OWNER = 'STRMADMIN' AND
        RULE_NAME  = 'LOCATIONS25';

Your output looks similar to the following:

True Evaluations Maybe Evaluations SQL Evaluations
---------------- ----------------- ---------------
            1518               154               0