Basic Farmer process specification in Oracle DB
Basic process specification includes specification of mandatory objects: view or AQ queue and processing
In Farmer BPS model in Oracle dataset definition is a view. There are following constraints on process view:
There is another semantic constraint which is necessary for multi-threading execution:
- view has distinctive column ID$ of number or integer type
- values in column ID$ are unique
- column ID$ is indexed for range scanning
- view has column OBJECT_ID$ of number or integer type
- column OBJECT_ID$ is indexed for range scanning
- view is sorted in OBJECT_ID$ ASC, ID$ ASC order
Columns ID$ and OBJECT_ID$ are called driving columns.
- records with different value in OBJECT_ID$ column can be processed simultaneously
In case of dynamic dataset process dataset is defined by Oracle type which payload in Oracle AQ queue. All payloads types are owner by Farmer internal user and all are subtypes under special type FARMER_ROW also owned by Farmer internal
CREATE OR REPLACE TYPE FARMER_ROW AS OBJECT
( ID$ NUMBER,
instantiable not final
Due to this fact following constraints are still appropriable for payload type:
Also the semantic constraint is necessary for multi-threading execution:
- type has attribute ID$ of number or integer type
- type has attribute OBJECT_ID$ of number or integer type
Attributes ID$ and OBJECT_ID$ are called driving attributes.
- objects different value in OBJECT_ID$ attribute can be processed simultaneously
In Farmer BPS model in Oracle dataset processing definition is a PL/SQL package. Process package has to meet following specification:
Good practice is not to reference to driving columns in procedure one implementation. Sources of ersatzes of mandatory objects are shown is Appendix A.
- define a type T_ROWTYPE which is subtype of view process rowtype or AQ payload type
- define procedure ONE with at least one argument of T_ROWTYPE
- any database transaction management in one procedure or its call subtree has to be done in autonomously mode.
Besides mandatory interface, process implementation in Farmer BPS model may over-implement following callbacks. By default, implementation of this callbacks is
Farmer Jobs Configuration
|| Application name
|| Job name.
|Type (data source)
|| View or Queue.
|| Start date of the job (as in Oracle
|| Repeat interval (as in Oracle
|| Job description.
|| Number of threads (Oracle sessions) the job is
|| Max amount of batches that jobs is processing in one
|Name of dataset partitioning
|| Partitioned dataset used by job
|| Default execution mode.
|| Flag if job session will not log out in case of no records to
|| Time that process sleeps on waiting for record to process in permanent
|| Interval in execution start between subsequent threads in multi-threading
||Flag, if process is incremental.
Farmer Jobs Runtime Environment
Farmer BPS provides running environment for process execution. Following PL/SQL objects under corresponding
Package logger provides interface to logging module in Farmer BPS. Interface consist of:
- type tt_env declaration. tt_env is used to pass some information about logging context to logging module
- family of procedures to log messages on various level
- some supplemental utility procedures
Procedure PUT_INTO_QUEUE applies only to AQ based processes.
PUT_INTO_QUEUE takes two parameters: full name of process and object typed FARMER_ROW type. PUT_INTO_QUEUE enqueues message with given object as payload to AQ queue associated to the given process.
PUT_INTO_QUEUE call is by default implemented in process package as put procedure for queue
Procedure WRAPPER is a handler to main procedure of Farmer Batch Process Server. It takes three parameters to identify process in Farmer process registry: name of process, name of process application, name of implementing Oracle user.
WRAPPER call is by default implemented in process package as run procedure. Declaration run procedure in process package specification is not obligatory. The one and only result of such declaration will be locking the package object during execution due to run presence on call
FARMER_ENV package provides access to runtime interface of Farmer BPS.
Farmer intereface consist of following:
Technical details of runtime environment objects are to be found in appendix B.
- read only access to execution validity range constant parameters of processes or process instance execution
- read only access to collections of driving identifiers ID$, OBJECT_ID$ for batch that is currently processing; this data has batch range validity and are provided between init_batch and finalize_batch callbacks including both.
- procedure that raise fatal_one_exception – exception which stops execution of process thread
Farmer BPS Aspects
Data set partition is mechanism that allows to precalculate partition into batches a processed dataset. Dataset partitioning is a sequence separated intervals (batches) in form pairs of start and end points.
Using dataset partitioning is not a formal requirement for jobs based on view dataset definition, although using partitioning is very strongly recommended for single-thread jobs and practically indispensable for multi-thread jobs.
To maximize positive impact of using dataset partitions should gives most balanced distribution of records amount among precalculated batches.
Dataset partitioning is calculated on pointed integer or integer number column from selectable database object i.e. table or view. Identifiers in dataset partitioning corresponds to identifiers in OBJECT_ID$ driving column in process
1 Iteration of processed set of data
Farmer BPS provides dataset iteration functionality. Iteration is executed on two levels - final dataset is divided into
batches. Farmer iterates over batches and over elements in current batch. Such approach result that in
extremely, but rare cases using of loop statement in implementation of process is not
Exception and transaction management policy
Farmer jobs may be executed in two modes:
Batch mode indicates that database transaction is committed every batch size
When process is running in batch mode and an exception is thrown from one procedure the rollback operation is performed and current batch starts to be processed in one-by-one mode. In renewed one-by-one processing operation one for erroneous record will probably (but not for sure) throw an exception
Illustration 1: Exception and transaction management policy in Farmer
All other records from current batch will be also processed. Subsequent batches are performed according to default execution mode i.e. batch mode.One-by-one mode
One-by-one mode indicates that database transaction is committed every single record. One-by-one is recommended when database transactions are distributed on over many databases or when transactional and non-transactional operations are mixed. Queue processes are always executed in one-by-one
Above description shows error resistance of processes executed in Farmer BPS. Moreover Farmer provides dedicated exception to complete error resistance mechanism.
When from one execution fatal_one_exception is thrown processing is stopped and the execution thread
Progress control mechanism
Stopping and resuming of executing process
Farmer BPS provides mechanism for gentle stopping execution of running process. No transaction is rolled back during such stopping. To stop a running process you only need to change status of process. For view based processes current values of driving identifiers are written down and next run will be resumption of interrupted one.
Farmer BPS provide API for logging functionality. Logs are ordered into following types:
||Actions performed by server administrator.
||Major Farmer BPS runtime events.
||Severe errors that cause premature
||Other runtime errors or unexpected
conditions. Expect these to be immediately visible on a status
||Use of deprecated APIs, poor use of
API, 'almost' errors, other runtime situations that are undesirable or
unexpected, but not necessarily "wrong".
||Interesting runtime events (startup/shutdown).
||Detailed information on the flow through the system.
||Detailed log of subroutine call in form of PL/SQL
Log record contains following information:
||Log record id.
|| Module which inserted log.
|| Date of log.
|| Object id that log is related to.
|| Type of log.
|| Content of log.
|| SID of logging oracle session.
|| Id of logging jobs
|| Id of logging job run.
||Id of logging job thread.
Performance diagnostic and optimization
Farmer BPS collects performance diagnostic information. For first or each execution thread (Oracle sessions) are collected:
- Oracle wait events statistics based on content v$session_event on the end of session activity
- Oracle SQL trace/tkprof statistics with waits and binds options
- Oracle dbms_profiler statistics
Performance measures and scalability
Farmer BPS collects following information about of absolute performance and scalability opportunities:
- absolute performance given in average amount of records processed in one second ; this value is calculated for each execution thread or for whole execution of process
- time of utilization of CPU in seconds; this value is calculated for each execution thread or for whole execution of process
- load balance between execution thread given in numbers of records processed by execution thread