A view at the Oracle Database

1
ltInsert Picture Heregt
Event Processing Using Database
Technology Dieter Gawlick and Shailendra
Mishra Oracle, Mani Chandy, California Institute
of Technology
2
Agenda

• Overview of event driven applications (Mani
  Chandy)
• 5 parts
• 1030 to 11 40
• Event processing using database technology
  (Shailendra Mishra)
• 1130 to 1200
• 130 to 245
• Summary next steps for you and challenges
• 245 to 300

3
Agenda Part 1

• What are events, event-driven applications,
  architectures and platforms?
• Event-driven applications
• Why use them?
• Examples from different areas
• A unifying framework for design and analysis
• Designing event-driven systems
• Database technology in event systems

4
Agenda Part 2

• Introduction 10 minutes
• Event processing 60 minutes
• Event creation - 15
• Event dissemination - 20
• Event composition and enrichment - 20
• The feedback loop 5
• Conclusion 5 minutes

5
Part 1

• Benefits of event-driven applications
• What is an event?
• Components of event-driven systems
• Most human organizations have been event-driven
  since the dawn of civilization. Why study
  event-driven IT now? What changed?

6
Benefits of Event Driven Systems

• Proactive response respond to changes before
  they unfold.
• Timely accurate response to changes.

7
What is an event?

• Event a significant state change that requires a
  response.
• The response may merely be registering the state
  change in memory. An insignificant state change
  is not remembered.
• An event is a state change that often signals an
  opportunity or a threat
• Event driven systems respond to events timely,
  accurately and (where possible) proactively

8
What is an event?

• Event a significant state change that requires a
  response.
• Change in time is a change in state.
• So absence of change in a parameter over a time
  can be an event.
• Example No new deliveries after item was ordered
  with overnight shipping.

9
Components Event Driven Systems
10
Components Event Driven Systems

• Event sources
• sensors stock tickers news stories
• Dissemination network
• Subscriptions role registration
• Dissemination protocols push, pull, schedule
• Processing agents
• Databases BI tools rules engines
• Responders
• Event consumers people, processes, actuators
• System management
• Registration of components, self-monitoring,
  ilities

11
Most Systems are Event Driven

• Nature
• Single animal
• Groups of animals
• Human organizations
• Why is their a need for event-driven systems now?
  What changed?

12
Animals Are Event Driven

• Streams of data sight, sound, touch, smell,
  taste
• Central nervous system detects the rare event
  that is a threat or an opportunity
• Organism responds appropriately to threat or
  opportunity
• Too many false positives, a false negative, or
  too many delayed or inappropriate responses
  results in death

13
Animal Groups are Event Driven

• Three enterprises lions, hyenas, zebras
• fuse information from inside and outside the
  enterprise

14
Human Organizations are Event Driven

• Examples from
• Healthcare
• Supply chain
• Customer resource management
• Manufacturing
• Since most organizations have been event driven
  for decades, why is their new interest in event
  processing systems? What changed?

15
Risk Management
16
What changed?

• Pressure to
• Respond even more proactively and rapidly
• React to conditions outside the organization
• Handle huge data volumes and creation rates
• Opportunity
• Availability of inexpensive computing elements
• Ubiquity of sensors, wireless miniaturization
• Software and design technologies

17
Why consider EDA now?

• Most systems are event-driven.
• Proactive timely responses have always been
  important.
• Why consider EDA technologies now?

18
Part 1 finished

• Benefits of event-driven applications
• What is an event?
• Components of event-driven systems
• Most human organizations have been event-driven
  since the dawn of civilization. Why study
  event-driven IT now? What changed?

19
Part 2

• Monitoring state-changes outside the enterprise.
• Consequences of external monitoring.
• Technology trends
• THE SCARCE RESOURCE
• Impact of the scarce resource on the future

1050 AM
20
Agents outside the enterprise

• Customers relationship management trends
• Suppliers pricing quality delays
• Competitors behaviors
• Government regulators
• External trends delays weather accidents

21
Information Acquisition
Software agent pulls and then normalizes informat
ion
Human inputs data in HTML forms
XML
Repository Matching Engine
Monitor chat rooms and bulletin boards
Seamless integration w/legacy information
22
An Energy Trading Application
Multiple concurrent threads insert into
environment interface tables
Weather
Pipelines
Power prices
Electricity grid utilization
DB
Event Detector
Event handling
Put into MQ
Event or message
23
External Agents for Individuals

• Entertainment and information
• You Tube
• Face Book
• Second Life
• wikipedia
• eBay
• Amazon
• Flickr
• Google
• Last.fm
• myspace
• msn games yahoo games

24
Monitoring External Agents Consequences

• Asynchronous data external agencies not you
  determines when data becomes available.
• Noisy data Schemas may not exist and may change
  at any time.
• Volume of external relevant data is growing
  rapidly.

25
Monitoring External Agents Consequences

• Responses may be wrong because
• external data is inaccurate.
• the appropriate external data wasnt monitored
• Changes in external data werent detected in a
  timely way

26
Technology Trends

• Costs of computers, storage, communication
  dropping rapidly, exponentially and continuously.
• Sensors becoming much more widely available.
• Responder mechanisms widely available whom to
  communicate with, how, when
• Miniaturization
• Location sensing geographical data

27
What are the scarce resources?

• Given rapid exponential decrease in costs of
• Storage
• Bandwidth
• Computing capacity
• Energy requirements
• Given pressure to
• Respond even more proactively and rapidly
• React to conditions outside the organization
• Handle huge data volumes and creation rates

28
What are the scarce resources?

• Time and attention

29
Managing Scarce Resources
Right information to right people at right times
30
Future impact of the scarce resource

• The biggest software companies 10 years from now
  will not think of themselves primarily as vendors
  of
• Databases
• Application servers
• Operating systems
• The will be companies that help you manage your
  time and attention.

31
Scare Resources and Technology
Time and Attention EDA
Integration and Services The Web
Scarce resource
CommunicationThe Internet
Technology
Computation Mainframe then PC
Time
32
Monitoring Externals
Interaction outside and inside the organization
Interaction within the organization
Event-Driven Architecture
Service Oriented Architecture
Remote procedure calls Client-Server
Procedure calls
Time
33
Isnt the Enterprise Stack Enough?
Whats needed
Focus of the current enterprise stack
Time and Attention EDA
Integration and Services The Web
CommunicationThe Internet
Computation Mainframe then PC
Time
34
Part 2 finished

• Monitoring state-changes outside the enterprise.
• Consequences of external monitoring.
• Technology trends
• THE SCARCE RESOURCE
• Impact of the scarce resource on the future

35
Part 3

• Types of communication
• Schedule-driven pull push
• Advantages and disadvantages of each type
• Every organization uses all three types
• Lead in to relationship between EDA (Event Driven
  Architectures) and SOA (Service Oriented
  Architectures)

1105 AM
36
Types of Communication

• Communication types Schedule, pull and push.
• Most organizations have all 3 types of
  communication.
• IT has been slow to support push.

37
Types of Communication

• CEO and VP meet Monday at 9AM Schedule Driven
  timed
• CEO calls VP to check on status of manufacturing
  SOA pull
• VP manufacturing calls CEO when there is a fire
  in a factory. EDA push

38
Schedule-Driven Communication Advantages

• Checks health of agents.
• if an agent doesnt participate then that agent
  is probably not functional.
• Energy spent when needed, i.e., when woken up
  by schedule
• important for certain types of wireless devices.
• Effective interaction with a group of agents
  scheduled to meet at the same time

39
Schedule-Driven CommunicationDisadvantages

• CEO and VP have group meetings every Monday
  morning.
• There is a fire in a factory on Monday evening.
  Does the CEO hear about the fire only next Monday
  morning?

40
Push

• Advantage
• Service monitors reality data sources and
  proactively informs consumer when necessary.
• Disadvantage
• Some messages may be irrelevant (false positives)
  and some conditions may be missed (false
  negatives)

41
Pull

• Advantage
• Requestor often has a good idea of information
  that the requestor needs.
• e.g., Doctor asks nurse for specific information
  about blood sugar
• Disadvantage
• Requestor doesnt know when to pull the
  information.

42
Ideal Combination of Communication Types

• Example of a hospital
• Push From sensors to nurses to residents to
  specialist doctors
• Pull Doctors ask for specific information from
  specific people and databases to help with
  determining the doctors actions
• Schedule Every morning specialists, residents,
  nurses meet to discuss cases

43
Database Interaction Types

• Push
• Trigger continuous query
• Pull Typical database operations
• Query, Updates
• Schedule
• Time-driven queries and updates

44
Key Points

• Most systems benefit from a combination of
  schedule, push and pull
• what information is communicated with each type
  of operation, and when?
• Human organizations use combinations of schedule,
  push and pull. Consider
• healthcare, supply chain, trading
• IT has been slow to support integration of push
  with pull and schedule-driven interaction

45
Part 3 Finished

• Types of communication
• Schedule-driven pull push
• Advantages and disadvantages of each type
• Every organization uses all three types
• Lead in to relationship between EDA (Event Driven
  Architectures) and SOA (Service Oriented
  Architectures)

46
Part 4

• SOA (Service Oriented Architecture) and EDA
  (Event Driven Architecture)
• Difference in emphasis between SOA and EDA
• Trends in SOA and EDA
• Composition and contracts in SOA and EDA
• SOA and EDA are complementary

1115 AM
47
SOA and EDA

• IT already supports SOA (Service Oriented
  Architecture)
• What does IT have to do differently from SOA to
  support events?

48
SOA and EDA Difference in Emphasis

• SOA
• client makes request in well-defined schema
• Server responds in well-defined format
• Clear relationship between call and response
• Natural progression of request-reply /
  client-server form of module composition

49
SOA and EDA Difference in Emphasis

• EDA
• Data from sources outside and inside the
  enterprise
• Data arrives asynchronously
• Respond proactively
• Workflow, business process management, agile
  supply chain management, timely business
  intelligence are all event driven.
• EDA Feedback loop analyzing flow of events
  helps to optimize event processing.

50
Difference in Emphasis
Users request specific information from specific
services
Users are sent relevant information based on
their interests, roles, subscriptions
Time and Attention EDA
Integration and Services The Web
CommunicationThe Internet
Computation Mainframe then PC
Time
51
Trends EDA and SOA
Event-Driven Architecture
Events Loose coupling and BAM
Service Oriented Architecture

• Event-Mainstream Apps
• C4I
• Program Trading
• Process Control

Process

• Event Meta-Apps
• Fraud
• Management
• Security
• Alerts
• BPM
• CRM
• SCM

Client/Server
Transactions
Procedural/ Legacy
time
1965
now
52
Composition in SOA and EDA

• SOA Sequences
• Call service to compute minimum cost in dollars
  for ball bearings to be delivered to LA.
• Service calls other services to obtain costs in
  local currencies.
• Computes best dollar price and sends reply

• EDA Monitors, integrates
• Alert manager if potential delay in shipment to a
  customer can result in penalties.
• Continuously monitor
• Suppliers
• Manufacturing process
• New contracts deadlines
• Integrate data from multiple sources send alert

53
EDA Contracts between Components

• What is the contract between producers and
  consumers of information in EDA?
• Producer monitors and estimates the state of some
  aspect of the world.
• Contract When there is a state change that is
  significant for the consumer, the producer alerts
  the consumer.
• How is this contract implemented?

54
Implementation of EDA Contracts

• Producer and consumer share a model of what state
  changes are significant for the consumer.
• Two types of shared models
• Deviation Send alert when reality deviates from
  model
• Match Send alert when reality matches model
• Conceptual mismatch when models of producer and
  consumer are not identical.
• Conceptual drift may require new shared model.

55
Detect when reality deviates from model

• Hospital systems
• Specialist doctors have shared models with
  hospital staff regarding normal behavior of
  patients recovering from appendix operations.
• Hospital staff calls doctor at home at night when
  actual behavior deviates from normal model.

56
Detect when reality matches model

• Tsunami warning system
• There are models of wave patterns in the Pacific
  that indicate a probable tsunami.
• When reality matches this model then send alert
  to the tsunami-warning center

57
EDA Contracts between Components

• Client (CEO) and VP (server) have a shared model.
• Server monitors situations that are inaccessible
  to the client
• If the situation satisfies the shared model then
  the client is identified.
• What is the shared model?

58
SOA Contracts between Components

• Server advertises the specification of a service
  which relates a call to a reply.
• Contract
• If a client calls the server, where the call has
  a specified schema,
• then the server will reply with a specified
  schema and the call and reply are related by the
  service specification

59
SOA and EDA are complementary

• Both will exist and will become increasingly
  integrated.

60
Part 4 finished

• SOA (Service Oriented Architecture) and EDA
  (Event Driven Architecture)
• Difference in emphasis between SOA and EDA
• Trends in SOA and EDA
• Composition and contracts in SOA and EDA
• SOA and EDA are complementary

61
Part 5

• Design of EDA applications
• Design of EDA as constrained optimization
• Objective functions
• Constraints
• Network structures for different types of
  communication schedule, push, pull
• Should you develop an EDA application?

1125 AM
62
Design

• Design
• distributed event-directed system
• Topology of dissemination network
• When to use schedule/push/pull
• What computations to perform
• To maximize benefits or minimize costs of false
  positives, false negatives, inaccurate responses,
  tardy respones

63
What is EDA?

• System that manages and executes rules of the
  form
• WHEN condition becomes true
• THEN initiate action.
• Rules also treated as event-condition-action
  (ECA)
• ON message WHEN condition THEN action

64
What is EDA?

• System that manages and executes rules of the
  form
• WHEN reality matches model or deviates from model
• THEN initiate action.
• Rules also treated as event-condition-action
  (ECA)
• ON message WHEN condition THEN action

65
Errors, QoS, Costs

• Rule when reality matches model then action
• False positive Reality doesnt match model, but
  the action is executed
• False negative Reality does match model but
  action isnt executed.
• QoS Value of response decreases with delay
• e.g., tsunami warning
• Costs Accuracy of response,
• e.g., height of expected tsunami

66
Tsunami Warning System Costs

• Costs of false positives? False negatives?
  Inaccurate responses?
• Sensor network responder network processing
  agents

67
Supply Chain Application Costs

• Design Issues
• What are costs of false positives? False
  negatives? Inaccurate responses?
• Sensor network? Responder network? Event
  processing agents?
• Application
• Monitor logistics, manufacturing and customer
  delivery contracts
• Take action when critical customers contract
  cannot be met.

68
Information flow for Schedule-Driven Actions

• Response is often executed by a group of agents,
• e.g, specialist doctors, residents, nurses, aids

doctor
doctor
outputs
node index
inputs
69
D-Dataflow for Push

• Sensors at lowest level generate false positives
  at high rate, e.g., sensors on patients in
  hospitals
• Level 1 filters out false positives by using
  richer models and more work, e.g., nurses
  responsible for hospital wards filter sensor data
• Level 2 filters even more false positives using
  even richer models and more work e.g., residents
  in hospitals
• Level 3 Send alert to specialist at home at
  night.

doctor
doctor
outputs
node index
inputs
70
Information flow for Pull

• Agents at higher levels are alerted rarely.
• Some alerts may be false positives.
• Agents at higher levels pull additional
  information from agents at lower levels to solve
  specific problems.
• Pull often follows push.

doctor
doctor
outputs
node index
inputs
71
Event and Database Servers
72
Features

• Types of data sources
• Complexity of analysis
• Quality of service issues
• Ilities
• Costs of false positives, false negatives, poor
  decisions
• Tracking of responses

73
Should you develop EDA?

• Is there a pressing business need to
• Respond more rapidly? Proactively?
• Query what might be, not merely what is?
• Understand evolution of data?
• Monitor even more data sources?
• Deal with higher data rates?
• Respond to conditions outside the enterprise?
• Detect more complex conditions?

74
Value across many environments

• Business Activity Monitoring
• Supply Chain Management
• Sarbanes-Oxley Compliance
• Financial Trading
• Logistics/Delivery
• Credit Risk
• RFID
• Securities Processing

• Intrusion Detection
• Fraud Detection / Security
• Homeland Security
• Suspicious Activity Tracking
• Self-healing Systems
• Command Control
• Event-Driven CRM

75
Part 5 finished

• Design of EDA applications
• Design of EDA as constrained optimization
• Objective functions
• Constraints
• Network structures for different types of
  communication schedule, push, pull
• Should you develop an EDA application?

76
Agenda

• Overview of event driven applications (Mani
  Chandy)
• 5 parts
• 1030 to 11 40
• Event processing using database technology
  (Shailendra Mishra)
• 1140 to 1200
• 130 to 245
• Summary next steps for you and challenges
• 245 to 300

77
Integrating Event Servers into Existing
Applications
Stream time-ordered information flow
Info source ordered or keyed info
View derived quantities (fusion correlation)
Subscription condition/ action
Function external action/ consumer
Dynamically Pluggable Event Server
78
Event Processing Using Database Technology

• Dieter Gawlick Architect (dieter.gawlick_at_oracle.
  com)
• Shailendra Mishra - Director (CEP)
  (shailendra.mishra_at_oracle.com)

79
Agenda

• Introduction
• Event processing
• Event creation
• Event dissemination
• Event composition and enrichment
• The feedback loop
• Challenges

80
ltInsert Picture Heregt
Introduction
81
About This Presentation

• Many companies offer support for Event Processing
  using database technology,
• There are no standards yet
• This presentation uses the Oracle notations and
  refers to the Oracle implementation technology

82

• Event Processing
• Identify,
• Disseminate,
• Recognize Event Patterns,
• Process time critical information,
• Classify Event Data

83
Event Processing A High Level View

• Event
• Processing

Event Processing Services
publish
notify
Consumers may become Publishers vice versa
Subscribers (Subscriptions)
Registry
Tools
84
Event Processing Publishers
Event Data Processing

• Event Data
• Processing
• Email
• Messaging
• RSS
• Streams
• CEP

• Event Data
• Processing
• Analytics
• Correlation
• Composition
• Selection/enrichment
• Storage/retention
• Distribution

Event Processing Services
Publishers
Consumers
publish
notify
Files/DBs
People
Applications
Sensors
Feeds
85
Event Processing Services

• Event
• Data
• Processing

• Event Data
• Processing
• Email
• Messaging
• RSS
• Streams
• CEP

Event Processing Services
Publishers
Consumers
publish
notify
86
Event Processing Consumers
Event Data Processing

• Event Data
• Processing
• Email
• Messaging
• RSS
• Streams
• CEP

• Event Data
• Processing
• Analytics
• Correlation
• Composition
• Selection/enrichment
• Storage/retention
• Distribution

Event Processing Services
Publishers
Consumers
publish
notify
Files/DBs
People
Applications
Actuators
Feeds
87
Event Processing Distribution
Event Processing Services
Publishers
Consumers
publish
notify
EPS
EPS Service
EPS
Propagation
EPS
EPS
E Event P Processing S Service
88
Covered Technologies Publishers
Event Data Processing

• Event Data
• Processing
• Email
• Messaging
• RSS
• Streams
• CEP

• Event Data
• Processing
• Analytics
• Correlation
• Composition
• Selection/enrichment
• Storage/retention
• Distribution

Event Processing Services
Publishers
Consumers
publish
notify
89
Covered Technologies Services
Event Processing Services
Publishers
Consumers
publish
consume
The Feedback Loop Continuous Analytics, Machine
Learning
ECE (Event Composition and Enrichment) Streams,
ECA-based
Event Dissemination Messaging, Rules technology
90
Event Technologies

• Event creation
• Triggers
• Log mining
• Transaction history
• Event dissemination
• Messaging
• Rules technology
• Event Processing
• Data Streams Continuous queries
• ECA Technologies
• The feedback loop
• Analytics/machine learning

91
ltInsert Picture Heregt

• Event Creation
• Triggers
• Log Mining
• Database History

92
How Are Events and Messages Created?
States
Events
Messages

• Event Instance of a pattern in the database
  history
• Message Information associated with an event

Frequent consumer/service view Event Message
States are not part of that model The creation
of events is ignored
93
Technologies for Creating Messages
Event Data Processing

• Event Data
• Processing
• Email
• Messaging
• RSS
• Streams
• CEP

• Event Data
• Processing
• Analytics
• Correlation
• Composition
• Selection/enrichment
• Storage/retention
• Distribution

• Event
• Processing
• Services

Publishers
Consumers
publish
notify
Triggers Log-mining Database History
Files/DBs
People
Services
Sensors
Feeds
94
Triggers

• DML Triggers
• Defined on tables the triggering action is the
  execution of a DML statement on a table
• Types of DML triggers
• before DML statement
• before each row (can modify new row changes)
• after each row
• after DML statement
• WHEN clause - simple filter condition to avoid
  executing the trigger body
• DDL triggers

95
Trigger Definition
CREATE OR REPLACE TRIGGER schema. trigger
BEFORE AFTER INSTEAD OF
dml_event_clause ddl_event OR
ddl_event ... database_event OR
database_event ... ON schema.
SCHEMA DATABASE WHEN
(condition) pl/sql_block call_procedure_stat
ement DML event clause DELETE
INSERT UPDATE OF column , column ...
OR DELETE INSERT UPDATE OF column
, column... ... ON schema.
table NESTED TABLE
nested_table_column OF schema. view
referencing_clause FOR EACH ROW
REFERENCING OLD AS old NEW
AS new PARENT AS parent
96
An Example
CREATE TABLE emp_audit ( emp_audit_id
NUMBER(6), up_date DATE, new_sal NUMBER(8,2),
old_sal NUMBER(8,2) ) -- Assuming an employee
table with a column salary exists CREATE OR
REPLACE TRIGGER audit_sal AFTER UPDATE OF
salary ON employees FOR EACH ROW BEGIN INSERT
INTO emp_audit VALUES( old.employee_id,
SYSDATE, new.salary, old.salary ) END
97
Triggers

• Pros
• Full database context is available
• Constraints can be verified procedurally
• Notifications/messages can be created
• Transactional or non-transactional
• Persistent/non-persistent
• Cons
• Procedure has to deal with dirty data
• Procedure has to process commit/abort information
• Execution of trigger logic is inline,
• Creation of notifications has performance impact

98
LogMiner Redo Logs

• Databases create a set of online redo logs for
  durability
• Redo records are flushed in batches
• A batch may contain committed and uncommitted
  data
• Complete rolled back transactions leave a
  footprint
• A transaction starts to generate redo from
  (close) to the time it makes changes to a
  database
• Online redo logs get archived when full
• Redo records may span multiple archived redo logs

99
LogMiner Access to Redo Log

• LogMiner hides intricacies of redo information
  and return changes as Logical Change Records
  (LCR)
• LCR is associated to the modifications of one row
• LCR may not reflect the original DML, but its
  effect on the database
• User may change 5 rows with 1 DML statement,
  LogMiner will generate 5 LCRs
• LogMiner allows to view only committed data
• LogMiner can mine logs in real time (lt 1 second)
• LogMiner allows multiple clients to mine the same
  redo stream without having to be in lock step or
  blocking each other.

100
Log Mining
Utilities
SQL Access
Logical Standby CDC (Change Data Capture)
Streams/Replication Audit Vault
LCRs grouped into txns in commit order
Logminer
Redo Logs
101
Log Mining Creation of Streams
Queues/ Streams
Capture Process Capture Rules
Redo Logs
102
Database History

• Automatically stores all changes to selected
  tables in an Archive
• Transparent to applications
• Low/no impact on OLTP performance
• Data cannot be modified
• Old data purged per retention policy per object
  (table)
• Long term history months/ years
• Uses
• Auditing
• Event creation
• and much more .

ORDERS
Archive Tables
Archiving
User Tablespaces
Flashback Data Archive
Oracle Database
103
Investigating the Database History

• Flashback AS OF Query
• Query all data at point in time

select from Emp AS OF 200 P.M. where
Tx 3
Tx 2
Tx 1
104
Events from the Past, Present ( Future)
ORDERS
Archive Tables
OLTP
Changes
Data Archive
User Tablespaces
Data
Data
Notifications Delivered to Application or User
Select from orderswhere
price gt 1000,000
QN Real Time Engine
Oracle Database
105
Query Registration

• Users and Applications register Queries in the DB
• DBMS.CQ_NOTIFICATION_REGSTART
• (procedure_name,
• policies,
• SELECT (I.id, I.quantity, O.date, C.name, )
• FROM Orders O, Order_items I, Customers C
• WHERE (c.name customer
• AND O.Cid C.Cid AND I.Orderid O.Orderid
• AND I.quantity gt 1000))
• DBMS.CQ_NOTIFICATION_REGEND
• Procedure_name
• A procedure for processing query results e.g.,
  notification of specific people fixed or based
  on message content
• Policies
• Exactly once
• Registration stored persistently ltAvailable
  across all RAC instancesgt
• Scalability
• Designed to scale from 1 to millions of queries,
  Scales with RAC

106
Query 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
• In Band Evaluation
• On the fly processing done PURELY IN MEMORY
• Uses Specialized In Memory Index structures
• Converts Query subscriptions into predicates and
  join conditions
• Extracts predicate and join filters and indexes
  them in memory

107
Query 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
• 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

108
Database History

• Pros
• Access to visible data
• Extended SQL semantics
• Reference information is easily available
• Cons with the current Oracle implementation as
  reference
• Synchronous processing
• Events cannot be defined after the fact
• Complexity and number of queries may reduce OLTP
  performance

can be asynchronous
see above

• can be fixed - high
  scalability with complex queries can be achieved
  by off-loading and out-scaling this can be done
  without measurable impact on the OLTP performance

109
ltInsert Picture Heregt

• Event Dissemination
• Messaging and Queuing
• Rules technology

110
Technologies for Event Dissemination

• Event
• Processing
• Services

Publishers
Consumers
publish
consume
Event Dissemination Messaging, Rules technology
111
Messaging and Queuing

• Think JMS as a base and add
• Multi-consumers queues directed dissemination
• Delay
• Retention
• Querying, Auditing, mining
• Sweet spot Business messages
• You need to create/consume them exactly once
• You cant afford to lose 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

112
Messaging and Queuing

• Queues are new first class objects
• 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 database security support
• Management fully integrated in database e.g.,
  recovery and restart, only queue specific things
  need to be done
• Implementation issues
• SKIP LOCK - easy
• Index Balancing difficult
• Sensitivity to timing issues very hard
• Messaging integrated in database is used in many
  highly visible mission critical applications

113
Dissemination in Communities of Interest

• Event
• Data
• Processing

publish
consume
subscribe
Subscriptions are captured in registries. These
registries determine eligible publishers
consumers
Subscribers (Subscriptions)
Subscriptions specify Publications of
interest The properties of publishers The
properties of consumers
Registries
114
Rules Technology

• Once a message is created, the dissemination
  services needs to know
• Who wants to know (is interested)
• Who should know (benefits from the information)
• Who can best respond based on a set of criteria
• Subscriptions are used to specify who gets which
  messages a subscription information may look
  like this
• How to process many, many entries?
• How to deal with properties?

115
The Traditional Database Problem
Inventory
SELECT FROM Inventory WHERE Model
Taurus AND Price lt 15000 AND Mileage lt
25000
SELECT FROM Inventory WHERE Model
Mustang AND Year gt 1999 AND Price lt
20000
SELECT FROM Inventory WHERE Model
Mustang AND Year lt 1970

• Input A SQL query with a conditional expression
  in the WHERE clause.
• Evaluated against a table of records.
• Output Records that satisfy the conditions.

116
The Inverse Problem
Consumer
ltModel gt Mustang, Year gt 2000,
Mileage gt 20000, Price gt 19000gt

• Input A record or a data item.
• Evaluated against a set of conditional
  expressions stored in a table.
• Output Expressions that satisfy the record.

117
The SQL Approach - Expression Filter
Consumer
Evaluate the expressions stored in a column using
the EVALUATE operator in a standard SQL
statement.
SELECT CId FROM Consumer c WHERE EVALUATE
(c.Interest, Model gt
Mustang, Year gt 2000,
Mileage gt 20000,
Price gt 19000) 1 AND c.Zipcode
03060
118
Expression Filter Components

• Expression Data Type
• To manage conditional expressions as data in
  relational tables.
• SQL EVALUATE Operator
• To evaluate expressions stored in a relational
  table using standard SQL statements
• Expression Filter Indextype
• To process the queries with EVALUATE operator
  efficiently for large expression sets.

119
Expression Management
Elementary Attributes Car4Sale (Model
VARCHAR(10), Year NUMBER,
Mileage NUMBER, Price NUMBER) Oracle Supplied
Functions UPPER, LOWER, .. User-Defined
Functions HorsePower, CrashTestRating, ..

Expression Metadata Car4Sale
Consumer Table
120
Operational Characteristics

• Supports SQL, XPATH, text, spatial, extensibility
• Operational characteristics
• Lots of rules - 106
• 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 of expression
  filters not a technology

121
ltInsert Picture Heregt
Event Data Streams Processing
122
Technologies for Event Composition Enrichment

• Event
• Processing
• Services

Publishers
Consumers
publish
consume
ECE (Event Composition and Enrichment) Streams,
ECA-based
Event Dissemination Messaging, Rules technology
123
Complex Event Processing (CEP)

• Generic data management infrastructure for
  processing in-flight data before data is
  potentially stored to deliver results in near
  real-time
• Service engine that allows users to Aggregate,
  Correlate, Enrich and Detect Patterns in high
  speed streaming data, in near real time through a
  
• Uniform Declarative Framework which provides
• Data Definition Language (DDL) to define streams,
  relations, views, register event sources and
  destinations
• Query Language to model complex events as
  continuous queries with strong temporal focus
• Data Manipulation Language (DML) to insert event
  data into streams
• System DDL and relational views to manage streams

124
Streams Time varying Relations

• Assume a discrete time domain DT
• Also, assume tuple of schema R of data
  represented by a set of elements (s1, s2,..sn).
  Each element si is termed attribute
• Stream S is a bag multi-set of elements (s,T)
  where s ? Schema of S and T ? DT
• Base streams denote input streams and are totally
  ordered relative to time
• Derived streams or views denote streams produced
  by queries
• Time varying Relation R is a mapping from DT to
  an unbounded bag of tuples to Schema of R
• A relation R defines an unordered bag of tuples
  at time instant T ? DT, denoted by R(t) a.k.a
  instantaneous relation
• Base relations denote input relations
• Derived relations or views to denote relations
  produced by queries

125
Continuous Query Language CQL

• Start with SQL then add
• Stream as a new first class object type
• Continuous instead of one-time semantics
• Operators
• Windows on streams - Stream to Relation Operator
  
• Time Row Partition Predicate Landmark
• Relation to Stream Operator IStream DStream
  RStream
• Syntactic shortcuts and defaults
• So easy queries are easy to write
• Equivalences
• Basis for query-rewrite optimizations
• Includes all relational equivalences, plus new
  stream-based ones
• Output result is a ltstream relationgt, updated
  as stream elements arrive
• SELECT FROM S1 RANGE 10 MIN, SLIDE 1 MIN,
• S2 RANGE 2 MIN, SLIDE 1 MIN
• WHERE S1.A S2.A AND S1.A gt 1000

126
Continuous Query Operators

• Relation-to-relation
• Well understood and apply without change to time
  varying relations
• Stream-to-Relation
• S W is a relation at time T it contains all
  tuples in window W applied to stream S up to T
• When W ? unbounded SQL 99, it contains all
  tuples in stream S up to T
• When W NOW, it contains all tuples bearing
  timestamp T
• Relation-to-stream
• IStream(R) contains all (r,T ) where r?R at time
  T but r?R at time T1
• DStream(R) contains all (r,T ) where r?R at time
  T1 but r?R at time T
• RStream(R) contains all (r,T ) where r?R at time
  T
• Stream-to-stream
• Operators can be composed from these other
  operators

127
Example CQL Queries

• Filter stream S emits an IStream
• IStream( SELECT FROM S ROWS UNBOUNDED
• WHERE S.A gt 10)
• Windowed join of streams S1 and S2 emits a
  relation
• SELECT FROM S1 RANGE 10, SLIDE 1MIN,
• S2 RANGE 2 MIN, SLIDE 1 MIN
• WHERE S1.A S2.A AND S1.A gt 1000
• Probe a stored table R based on each tuple in
  Stream S emits an Rstream
• RStream(SELECT S.A, R.B FROM S NOW, R
• WHERE S.A R.A)

128
Query Execution

• Generate a query plan
• Merge new plan with existing plans, if any
• Plans are composed of three main components
• Operators
• Queues (input and inter-operator) are of two
  types
• carrying streams
• carrying relations
• State or Synopses (windows, operators requiring
  history)
• Stores
• Single global plan across all registered queries

129
(No Transcript)
130
Computation Sharing
QUERY
QUERY
LEGENDS
Output
Output
-synopsis
Project
Aggr
- store
- q carrying relation
- q carrying stream
Select
Time Win.
Stream Src
SELECT MAX(c1) FROM S RANGE 1 HR WHERE C2 gt
20 SELECT c110 FROM S RANGE 1HR WHERE C2 gt 20
131
Patterns

• Expressed as regular expressions over correlation
  names
• Correlation names are defined as boolean
  predicates over event schema and event stream.
• Events satisfying the predicate associated with a
  correlation name can be bound to that correlation
  name
• Support aggregation functions and special
  functions such as PREV, FIRST, LAST

132
An example of pattern matching. Find double
bottom (W)
google-stock
Z
Y
W
X
days
1
9
12
19

• SELECT FIRST(x.time), LAST(z.time)
• FROM ticker MATCH_RECOGNIZE (ROW PER MATCH PBY
  name
• PATTERN (X Y W Z)
• DEFINE X AS (price lt PREV(price))
• Y AS (price gt PREV(price))
• W AS (price lt PREV(price))
• Z AS (price gt PREV(price)) )

133
Pattern Query With ONE ROW PER MATCH

• SELECT a_symbol, a_tstamp, / start time /,
  a_price, / start price /,
• max_c_tstamp, / inflection time /,
  last_c_price, / low price /,
• max_f_tstamp, / end time /, last_c_price, /
  end price /, Matchno
• FROM Ticker MATCH_RECOGNIZE (PARTITION BY Symbol
• MEASURES A.Symbol AS a_symbol, A.Tstamp AS
  a_tstamp,
• A.Price AS a_price, MAX (C.Tstamp) AS
  max_c_tstamp,
• LAST (C.Price) AS last_c_price, MAX (F.Tstamp) AS
  max_f_tstamp
• MATCH_NUMBER AS matchno
• ONE ROW PER MATCH
• AFTER MATCH SKIP PAST LAST ROW
• MAXIMAL MATCH
• PATTERN (A B C D E F)
• DEFINE B AS (B.price lt PREV(B.price)),
• C AS (C.price lt PREV(C.price)),
• D AS (D.Price gt PREV(D.price)),
• E AS (E.Price gt PREV(E.Price)),
• F AS (F.Price gt PREV(F.price)
• AND F.price gt A.price))

134
MATCH_RECOGNIZE syntax

• The full syntax of the MATCH_RECOGNIZE clause is
  as under
• PARTITION BY optional
• MEASURES - optional, but we expect this will
  always be used
• ONE ROW ALL ROWS PER MATCH default to ONE
  ROW
• AFTER MATCH SKIP TO NEXT ROW PAST LAST ROW
  TO LAST
• ltvariablegt TO FIRST ltvariablegt
• - default AFTER MATCH SKIP PAST LAST ROW
• MAXIMAL INCREMENTAL MATCH - defaults to
  MAXIMAL MATCH
• PATTERN mandatory
• SUBSET optional
• DEFINE mandatory
• CLASSIFIER - optional (ALL ROWS PER MATCH only)
• MATCH_NUMBER - optional

135
ltInsert Picture Heregt
ECA Rules Processing
136
Database Technology for ECA

• Commercial ECA applications have data management
  problems too
• Event histories
• Reference data
• Large number of rules
• Archive for audit and non-repudiation
• Incremental states for efficient rule evaluation
• Enrichment of data from non-event sources

137
ECA Processing

• 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.

138
ECA Rule - 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
  gt 30000 AND fundFrom ! USA
• THEN PerformAction (ADD2WATCHLIST,
  NYPD, subjectId)
• Event Enrichment through user-defined
  functions accessing reference data, with
  temporal logic when necessary.
• IsRestrictedCountry(location gt
  fundFrom, AS OF gt
  01-Jan-2007) 1

139
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.

140
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
• ltconditiongt
• ltand equalbank.subjectId,
  tranpsort.subjectId,
  fldrpt.subjectIdgt
• ltobject name bankgt ... lt/objectgt
• ltobject name transportgt ...
  lt/objectgt
• ltnot bysystimestamp1gt
• ltobject namefldrptgt rptType
  BGCheck lt/objectgt lt/notgt
• lt/andgt
• lt/conditiongt
• THEN PerformAction(ADD2WATCHLIST,TE
  MP, bank.subjectId)

141
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 ltconditiongt
• ltcollection name bank
• groupby subjectId
• windowsize 10
• having sum(amount) gt
  100000gt
• transType Wire Transfer
• lt/collectiongt
• lt/conditiongt
• THEN PerformAction(MARK_HIGH_ACTIVITY,
  bank.subjectId)

142
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.

143
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
144
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

145
ltInsert Picture Heregt
Identifying Classifying Events Continuous
Analytics/Machine Learning
146
Technologies for Continuous Analytics

• Event
• Processing
• Services

Publishers
Consumers
publish
consume
The Feedback Loop Continuous Analytics, Machine
Learning
ECE (Event Composition and Enrichment) Streams,
ECA-based
Event Dissemination Messaging, Rules technology
147
The Feedback Loop
Event Processing Services
Publisher
Consumers
consume
publish
148
The Feedback Loop - Questions

• What is valuable information?
• Is it directly available ?
• How can it be derived ?
• Can it be classified ?
• Can the classification be improved ?
• What are good publishers ?
• Who needs to know what at what time?

149
Technologies Creating new information

• Creating valuable information from events through
  simple, predictive SQL
• Example
• Select all customers who have a high
• propensity to attrite (gt 80 chance).
• SELECT A.cust_name, A.contact_info
• FROM customers A