| Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-01 |
|
|
View PDF |
| Oracle® Streams Concepts and Administration 10g Release 2 (10.2) Part Number B14229-01 |
|
|
View PDF |
This chapter provides sample queries that you can use to monitor rules, rule sets, and evaluation contexts.
This chapter contains these topics:
Displaying the Streams Rules Used by a Specific Streams Client
Displaying Information About the Tables Used by an Evaluation Context
Displaying Information About the Variables Used in an Evaluation Context
Listing Each Rule that Contains a Specified Pattern in Its Condition
Displaying Aggregate Statistics for All Rule Set Evaluations
Determining the Resources Used by Evaluation of Each Rule Set
|
Note: The Streams tool in the Oracle Enterprise Manager Console is also an excellent way to monitor a Streams environment. See the online help for the Streams tool for more information. |
|
See Also:
|
Streams rules are created using the DBMS_STREAMS_ADM package or the Streams tool in the Oracle Enterprise Manager Console. Streams rules in the rule sets for a Streams client determine the behavior of the Streams client. Streams clients include capture processes, propagations, apply processes, and messaging clients. The rule sets for a Streams client can also contain rules created using the DBMS_RULE_ADM package, and these rules also determine the behavior of the 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 Streams clients, including 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 Streams clients in a database:
The name of each Streams client that uses the rule
The type of each Streams client that uses the rule, either CAPTURE for capture process, PROPAGATION for propagation, APPLY for apply process, or DEQUEUE for messaging client
The name of the rule
The type of rule set that contains the rule for the Streams client, either POSITIVE or NEGATIVE
For Streams rules, the Streams rule level, either GLOBAL, SCHEMA, or TABLE
For Streams rules, the name of the schema for schema rules and table rules
For Streams rules, the name of the table for table rules
For Streams rules, the rule type, either DML or DDL
Run the following query to display this information:
COLUMN STREAMS_NAME HEADING 'Streams|Name' FORMAT A14
COLUMN STREAMS_TYPE HEADING '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 '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:
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 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 a Streams client, the current and original rule condition for Streams rules, whether the rule condition has been changed, the subsetting operation and DML condition for each Streams subset rule, the source database specified for each Streams rule, and information about the message type and message variable for Streams messaging rules.
The following data dictionary views also display 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 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.
To determine which rules are in a rule set used by a particular 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 Streams client:
Displaying the Rules in the Positive Rule Set for a Streams Client
Displaying the Rules in the Negative Rule Set for a 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 '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:
Streams Apply
Rule Rule Schema Object Rule Tagged
Rule Owner Name Level Name Name Type Source LCRs?
---------- --------------- ------- ------ ----------- ---- ---------- ---------
STRMADMIN HR20 SCHEMA HR DML DBS1.NET NO
STRMADMIN HR21 SCHEMA HR DDL DBS1.NET NO
Assuming the rule conditions for the 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.net 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 a Streams rule has been modified, then you must check the current rule condition to determine the effect of the rule on a Streams client. Streams rules whose rule condition has been modified have NO for the SAME_RULE_CONDITION column.
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 '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:
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.NET YES
STRMADMIN JOB_HISTORY23 TABLE HR JOB_HISTORY DDL DBS1.NET YES
Assuming the rule conditions for the 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.net 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 a Streams rule has been modified, then you must check the current rule condition to determine the effect of the rule on a Streams client. Streams rules whose rule condition has been modified have NO for the SAME_RULE_CONDITION column.
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 Streams Rules Used by a Specific 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.is_null_tag() = 'Y' and :dml.get_source_database_name() = 'DBS1.NET' )
It is possible to modify the rule condition of a Streams rule. These modifications can change the behavior of the Streams clients using the 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 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 Streams rules. It does not apply to rules created using theDBMS_RULE_ADM package because these rules always show NULL for the ORIGINAL_RULE_CONDITION column and NULL for the SAME_RULE_CONDITION column. |
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
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
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
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
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'
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
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. |
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 999999
COLUMN SQL_EXECUTIONS HEADING 'SQL|Executions' FORMAT 999999
COLUMN SQL_FREE_EVALUATIONS HEADING 'SQL Free|Evaluations' FORMAT 999999
COLUMN TRUE_RULES HEADING 'True|Rules' FORMAT 999999
COLUMN MAYBE_RULES HEADING 'Maybe|Rules' FORMAT 999999
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 --------- ----------- ----------- ---------- ----------- ------- ------- STRMADMIN RULESET$_18 403 0 403 0 200 STRMADMIN RULESET$_9 3454 0 3454 5 64
|
Note: Querying theV$RULE_SET view can have a negative impact on performance if a database has a large library cache. |
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 --------------- --------------- ----------- ----------- ---------- STRMADMIN RULESET$_18 .840 8.550 444497 STRMADMIN RULESET$_9 .700 1.750 444496
|
Note: Querying theV$RULE_SET view can have a negative impact on performance if a database has a large library cache. |
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 999999
COLUMN MAYBE_HITS HEADING 'Maybe Evaluations' FORMAT 999999
COLUMN SQL_EVALUATIONS HEADING 'SQL Evaluations' FORMAT 999999
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
