Monday, November 11, 2019

Consumer Group Mapping Rules Use Pattern Matching from 12.1

I recently noticed a small, but I think significant, change in the way consumer group mapping rules behave from Oracle 11.2.04.  Session attributes can be matched to resource groups using LIKE expressions and simple regular expressions specified in the matching rules, though only for certain attributes.
(Updated 12.11.2019) I am grateful to Mikhail Velikikh for his comment.  It depends on which version of Oracle's documentation for 11.2 you read.  Pattern matching does work in for the attributes listed in the 12.1 documentation. My testing indicates that pattern matching does not happen in
You cannot pattern match the SERVICE_NAME in 11.2.  The attribute value is validated against the list of valid services.
ORA-00042: Unknown Service name WIBBLE
However, you can pattern match SERVICE_NAME in 12.1, although SERVICE_NAME is not in the list of attributes for which the documentation says pattern matching is available.  This may be a documentation bug (see Oracle support note 1992704.1).
   attribute        IN VARCHAR2, 
   value            IN VARCHAR2, 
   consumer_group   IN VARCHAR2 DEFAULT NULL); 

11.2 Documentation (c)2010

You use the SET_CONSUMER_GROUP_MAPPING procedure to map a session attribute/value pair to a consumer group. The parameters for this procedure are the following:
attributeThe session attribute type, specified as a package constant
valueThe value of the attribute
consumer groupThe consumer group to map to for this attribute/value pair

11.2 Documentation (c)2015

This procedure adds, deletes, or modifies entries that map sessions to consumer groups, based on the session's login and runtime attributes.
attributeMapping attribute to add or modify. It can be one of the Constants listed.
valueAttribute value to match. This includes both absolute mapping and regular expressions.
consumer groupName of the mapped consumer group, or NULL to delete a mapping

Usage Notes 
If no mapping exists for the given attribute and value, a mapping to the given consumer group will be created. If a mapping already exists for the given attribute and value, the mapped consumer group will be updated to the one given. If the consumer_group argument is NULL, then any mapping from the given attribute and value will be deleted.
The subprogram supports simple regex expressions for the value parameter. It implements the same semantics as the SQL 'LIKE' operator. Specifically, it uses '%' as a multi-character wildcard and '_' as a single character wildcard. The '\' character can be used to escape the wildcards. Note that wildcards can only be used if the attribute is one of the following:

*Consumer group mapping comparisons for DBMS_RESOURCE_MANAGER.CLIENT_PROGRAM are performed by stripping the @ sign and following characters from V$SESSION.PROGRAM before comparing it to the CLIENT_PROGRAM value supplied.

Autonomous Database Example

The mappings are reported in the view DBA_RSRC_GROUP_MAPPINGS. An example of using a LIKE expression in a mapping can be found in the standard configuration of autonomous cloud databases. There are 5 standard mappings for 5 services to 5 consumer groups. The automatically created service names are prefixed with the database name, then HIGH, LOW, MEDIUM, TP or TPURGENT., and are suffixed
SELECT * FROM v$services ORDER BY 1;

However, the mappings are defined using pattern matching rather than referencing the explicit name of the service, thus exactly the same mappings can be defined on every autonomous database.
select * from DBA_RSRC_GROUP_MAPPINGS order by 1,2;

ATTRIBUTE    VALUE           Group      STATUS
------------ --------------- ---------- ----------
SERVICE_NAME %\_TP.%         TP
  • By default, the ORACLE_USER attribute's priority 6 takes precedence over SERVICE_NAME whose priority is 7. Therefore, GGADMIN, the GoldenGate user, always uses the LOW consumer group irrespective of the service that it connects to. The default priorities can be changed.
  • Wildcards cannot be used for the ORACLE_USER attribute.

1 comment :

Mikhail Velikikh said...

Hi David,

You are referring to this 11.2 link:
which should be based on the What's new section in the contents:
Oracle Database 11g Release 2 ( New Features in the Administrator's Guide

The default 11.2 documentation on points to:
that has notes about those wildcards:
You can use wildcards for the value of most attributes in the value parameter in the SET_CONSUMER_GROUP_MAPPING procedure.

It should be at least based on the What's new section (I guess they may have not updated the documentation for
Oracle Database 11g Release 2 ( New Features in the Administrator's Guide

There is also a note that says that those wildcards were available in 11.2, and it mentions a documentation bug:
Not able to use wildcards to specify service_name value Of Resource Consumer Group Mapping Rule (Doc ID 1992704.1)

Those wildcards might even work in since that version is mentioned in the MOS note above, but I have neither 10.2 nor 11.1/11.2 in my shop to confirm that.