Event Technologies in the Oracle Database

1. Event Technologies in the Oracle Database Dieter Gawlick Architect

2. Objective: Identify and disseminate time critical information

3. Agenda Event and Rules technology in the Oracle database or things the database can do but you most likely did not know • Triggers – not designed for event processing • Messaging and queuing - for dissemination • Rules engine – for triggers and subscriptions • Expression Filter – for triggers, subscriptions and more • Rules Manager – CEP • Continuous Query Notification – to create events (CEA not ECA)

4. Messaging and Queuing • Sweet spot: Business messages • You need to create/consume them exactly once • You can’t effort to loose them • Only authorized people/agents can see them • You need always to find them • You need to retain them • You need to be able to audit them • Think JMS as a base and add • Multi-consumers queues – directed dissemination • Delay • Retention • SQL access

5. Messaging and Queuing • Queues are mapped to tables • Single or multiple types of messages in a single queue • Payload: SQL objects, XML, text, … • Operational Characteristics • Performance - no distributed 2PC • Scalability - Parallelism SMP, clusters for millions of messages, … • Reliability - recovery, fail over, fault tolerance, site failure protection • Security – all the support of the database • Management – fully integrated in database: e.g., recovery and restart, only queue specific things need to be done

6. Messaging and Queuing • Major implementation issues • SKIP LOCK - easy • Index Balancing – difficult • Sensitivity to timing issues – very hard • Core component of the Oracle software • Database • Application Server • Management • Applications … • Used explicitly in many highly visible mission critical applications

7. Rules Engine • Service to process subscription • Implicit • Reacts to subscriptions on queues • Verifies (SQL) expression against single queue elements • Thousand of subscriptions • Explicit – PL/SQL Interface • Based on special table structure – expression, context, action • Returns rows which evaluate on true for a given data element • Provides generic trigger/subscription support • Can be applied to messages and events

8. Expression Filter • Expressions are supported as data type – using the extensibility framework SELECT ( , , , ) FROM WHERE (EVAL (data/ref))

9. Expression Filter • Add more information SELECT ( , , , ) FROM WHERE (EVAL (data/ref) AND LOC (WITHIN 5 MILES of X) ORDER BY CONTEXT.F WHERE COUNT = 1 Support of Mutual Filtering

10. Expression Filter • Supports SQL, XPATH, text, spatial, extensibility • Operational characteristics • Lots of rules - > 10**6 • Lots of changes - > 10/sec • Reliability – all the support of the database • Atomicity – all the support of the database • Security – all the support of the database, but potentially tricky with user extensions • Auditing and tracking - all the support of the database (Flashback) • Performance – depends on context • Subscription is now a use case not a technology • Expressions allow the specification of interesting of an impotant class of properties • There are many other use cases

11. Rules Manager • Commercial CEP applications do have data management problems • Event histories • Reference data • Large number of CEP Patterns • Archive for audit and non-repudiation • Incremental states for efficient rule evaluation

12. Rules Manager • Rules are processed for events that arise from • Event submissions through programmatic interfaces, message queues, or web-services requests. • Transactional or Non-transactional changes to data • Non-occurrence of some expected change (exceptions) • Rule conditions are expressed using SQL and XML syntax • SQL-WHERE clause for identifying individual events of interest (filtering) using predicates on Scalar, XML, Spatial, or Text data. • XML to correlate events and create complex event patterns • Action preferences determine the exact action for each rule • Pre-packaged actions can be executed in the DB – Alerts, Enqueue, Mail, Web-service requests etc.,. • Results from rule evaluation can be obtained as a SQL result set by querying a view for application level actions.

13. Rules Manager A simple Example If a person receives a money transfer for over 30000 dollars from outside United States, add him to the NYPD watch list. ON BankTransaction (subjectId, transType, amount, fundFrom) IF tranType = ‘Transfer’ and amount > 30000 and fundFrom != ‘USA’ THEN PerformAction (‘ADD2WATCHLIST’, ’NYPD’, subjectId)

14. Composite Events and Rules • A Composite Event consists of two or more primitive events TYPE BankTransaction AS OBJECT ( subjectId NUMBER, transType VARCHAR(30), amount NUMBER, fundFrom VARCHAR(30)); TYPE Transportation AS OBJECT ( subjectId NUMBER, vesselType VARCHAR(30), locFrom VARCHAR(30), locTo VARCHAR(30), startDate DATE, endDate DATE); • Primitive events that constitute a composite event are generated in an application at different points in time (or in different applications). • The complex event pattern in a rule identifies the primitive events of interest and creates a composite event to act on. • E.g: Raise an alert when a person receiving a large amount also rents a truck to a restricted area within 24 hours.

15. Common Types of Event Relationships • Conjunction • All the specified events happened • Sequencing • The events happened in the specified order • Disjunction (any n) • Any n of the m specified events happened • Temporal association • Events happened within n time units of each other • Non-occurrence • An event did not happen within a deadline • Aggregation • Collections of events following sliding window semantics satisfy some criteria.

16. Rules Application with Composite Events • A rule condition is true when a set of primitive events satisfying the SQL predicates on the event data also satisfy the event pattern specification. • Each rule condition acts as state machine that reaches an accepting state when the rule condition evaluates to true. • The outcome of a rule matching a set of primitive events is a higher-level event, or a composite event consisting of all the primitive events. Rules Manager Application App Server App Server Database App Database App  Message Queues Message Queues Event Web Services & Other Web Services & Other Event Consumers Event Sources Events Repository Incremental Results

17. Logical Grouping of Rules • Declarative policies for event lifecycle management • Consumption: Specification for event reuse • SHARED: Event used for multiple rule executions • RULE: Custom consumption policy on a per rule basis • EXCLUSIVE: At most one matching rule executed with the event • Conflict Resolution: To control the order of rule execution • SQL ORDER BY Clause involving some event and/or rule attributes • Duration: Lifetime of unconsumed events • TRANSACTION / SESSION / CALL / n [minutes | hours | days ] • Rules hierarchies: a composite event in one application can be a primitive event in another application

18. Rule Condition with Negation and Deadline If the background check for the person who received a large sum of money from outside US and rented a truck is not available within 24 hours, add him to a Temporary watch list. ON BankTransaction (subjectId, transType, amount, fundFrom) bank, Transportation (subjectId, vesselType, ..., endDate) transport, FieldReport (subjectId, rptType, whoWith, ...) fldrpt IF <condition> <and equal=”bank.subjectId, tranpsort.subjectId, fldrpt.subjectId”> <object name= “bank”> ... </object> <object name= “transport”> ... </object> <not by=“systimestamp+1”> <object name=“fldrpt”> rptType = ‘BGCheck’ </object></not> </and> </condition> THEN PerformAction(‘ADD2WATCHLIST’,‘TEMP’, bank.subjectId)

19. Rule Condition with Event Collections Mark the account as high activity account if the sum of the amounts transferred in last 10 transactions is over 100000 dollars ON BankTransaction (subjectId, transType, amount) bank IF <condition> <collection name = “bank” groupby = “subjectId” windowsize = “10” having = “sum(amount) > 100000”> transType = ‘Wire Transfer’ </collection> </condition> THEN PerformAction(‘MARK_HIGH_ACTIVITY’, bank.subjectId)

20. Continuous Query NotificationorMoving towards anInformation Driven Architecture <Insert Picture Here>

21. Data Investigation Using Flashback • Flashback Query • Query all data at point in time select * from Emp AS OF ‘2:00 P.M.’ where … • Flashback Version Query • See all versions of a row between times • See transactions that changed the row Tx 3 select * from Emp VERSIONS BETWEEN ‘2:00 PM’ and ‘3:00 PM’ where … Tx 2 • Flashback Transaction Query • See all changes made by a transaction Tx 1 select * from FLASHBACK_TRANSACTION_QUERY where xid = ‘000200030000002D’;

22. Flashback Data Archive • Long term history- years • Automatically stores all changes to selected tables in Flashback Data Archive • Archive cannot be modified • Old data purged per retention policy • View table contents as of any time using Flashback SQL • Uses: • Change Tracking, Notification • ILM • Long term history • Auditing • Compliance ORDERS Archive Tables Changes User Tablespaces Flashback Data Archive Oracle Database Total Recall

23. EBS, PeopleSoft, Siebel, Portal, … Transparent to Applications OLTP • No Changes to Application Required • Can be applied to any table in any schema • Work with EBS, PeopleSoft, Siebel, as well as custom apps. • Selective Archive for Schema Objects • Maybe used for high value data • High Performance Capture • Low/No impact on OLTP operations • 100% Out of line archiving ORDERS Archive Tables Changes User Tablespaces Flashback Data Archive Oracle Database

24. Continuous Query Notification • Query Registration • Users and Applications register Queries in the DB • 100% Declarative • Registration stored persistently • Scales from 1 to millions of queries • Supports Queries with Simple Predicates and Joins • Evaluation • Evaluation using In Memory Predicate Filter Index • In band Evaluation • Notification • End points can be OCI, JDBC, Pl/SQL

25. Continuous Query Notification Notifications Delivered to Application or User ORDERS User Tablespaces Data Select * from orderswhere price > 1000,000 CQN Real Time Engine Oracle Database

26. CQN: Registration • Users and Applications register Queries in the DB • 100% Declarative • Registration stored persistently • Available across all RAC instances • Scalability • Designed to scale from 1 to millions of queries • Scales with RAC • Supports Simple Queries • Simple Predicates • Equi-Joins

27. CQN: Evaluation • In Band Evaluation • On the fly processing done PURELY IN MEMORY • Gives significantly high performance over other technologies • Uses Specialized In Memory Index structures • Converts Query subscriptions into predicates and join conditions • Extracts predicate and join filters and indexes them in memory

28. CQN: Evaluation • Predicate Sharing • In Memory structure for evaluation shares predicate elements across Registered queries. • Adaptive Filtering • Autotunes the predicate evaluation order based on selectivity • Single System View on RAC • Registrations propagated on all RAC instances • Evaluation processing distributed to RAC nodes

29. CQN: Notification • Notification to end user or Application • multitude of protocol: HTTP, SQL, SMTP, JDBC etc • Guaranteed Delivery • For mission critical applications • Notifications persisted on disk • In Memory Notification • High Performance • Real-time delivery

30. CQN: Notifications • Application Integration APIs • End points can be OCI, JDBC, Pl/SQL • Group Notification • Notifications collapsed during a period of time • Flow control/Flooding control of notifications • Notification Payload • Provides Query, Table and Row information

31. EBS, PeopleSoft, Siebel, Portal, … Events from the Past, Present (& Future) ORDERS Archive Tables OLTP Changes Flashback Data Archive User Tablespaces Data Data Notifications Delivered to Application or User Select * from orderswhere price > 1000,000 CQN Real Time Engine Oracle Database

32. Related Work • BI: • Identify the valuable queries/patterns • Queries may need to be adjusted every few minutes reacting to emerging conditions and objective • Application server • Streams SQL • BAM • BPEL • … you name