Before we discuss how Oracle specifically handles OLTP, we'll start by presenting a common definition of online transaction processing.
The concept of a transaction and the relevant Oracle mechanics for dealing with transactions are discussed in Chapter 7. To recap that discussion, a transaction is a logical unit of work that must succeed or fail in its entirety. Each transaction typically involves one or more Data Manipulation Language (DML) statements such as INSERT, UPDATE, or DELETE, and ends with either a COMMIT to make the changes permanent or a ROLLBACK to undo the changes.
The industry bible for OLTP, Transaction Processing: Concepts and Techniques, by Appendix B), introduced the notion of the ACID properties of a transaction. A transaction must be the following:
Atomic
The entire transaction succeeds or fails as a complete unit.
Consistent
A completed transaction leaves the affected data in a consistent or correct state.
Isolated
Each transaction executes in isolation and doesn't affect the states of others.
Durable
The changes resulting from committed transactions are persistent.
If transactions execute seriallyone after the othertheir use of ACID properties can be relatively easily guaranteed. Each transaction starts with the consistent state of the previous transaction and, in turn, leaves a consistent state for the next transaction. Concurrent usage introduces the need for sophisticated locking and other coordination mechanisms to preserve the ACID properties of concurrent transactions while delivering throughput and performance.
Online transaction processing can be defined in different ways: as a type of computing with certain characteristics, or as a type of computing in contrast to more traditional batch processing.
Most OLTP systems share some of the following general characteristics:
High transaction volumes and large user populations
The benefits of large, centralized systems have driven the ever-increasing scale of today's modern OLTP systems.
Well-defined performance requirements
OLTP systems often involve Service Level Agreements that state the expected response times.
High availability
These systems are typically deemed mission-critical with significant costs resulting from downtime. The universal availability of the Web clearly increases availability requirements.
Scalability
The ability to increase transaction volumes without significant degradation in performance allows OLTP systems to handle fluctuations in business activity.
With the expansion of user communities based on opening up systems on the Web, OLTP demands have increased, in terms of both overall performance and availability. Web users expect the system to be up all the time and to perform quicklyif your order-entry site is down or slow, web users will go elsewhere.
Online transaction processing implies direct and conversational interaction between the transaction processing system and its users. Users enter and query data using forms that interact with the backend database. Editing and validation of data occur at the time the transactions are submitted by users.
Batch processing occurs without user interaction. Batches of transactions are fed from source files to the operational system. Errors are typically reported in exception files or logs and are reviewed by users or operators later on. Virtually all OLTP systems have a batch component: jobs that can execute in off-peak hours for reporting, payroll runs, posting of accounting entries, and so on.
Many large companies have batch-oriented mainframe systems that are so thoroughly embedded in the corporate infrastructure that they cannot be replaced or removed. A common practice is to "frontend" these legacy systems with OLTP systems. Users interact with the OLTP system to enter transactions. Batch files are extracted from the OLTP system and fed into the downstream legacy applications. Once the batch processing is done, extracts are produced from the batch systems and are used to refresh the OLTP systems. This extraction process provides the users with a more sophisticated interface with online validation and editing, but it preserves the flow of data through the entrenched batch systems. While this process seems costly, it's typically more attractive than the major surgery required to remove the older systems. To compound the difficulty, in some cases the documentation of these older systems is incomplete and the employees who understand the inner workings have retired or moved on.
The financial services industry has been a leader in information technology for transaction processing, so this notion of feeding legacy downstream applications is very common in banks and insurance companies. For example, users enter insurance claims into an online system. Once all the data has been entered, if the claim has been approved it is extracted and fed into legacy systems for further processing and payment.
Oracle features such as transportable tablespaces and Streams, discussed elsewhere in this book, are aimed in part at providing the functionality required by distributed OLTP systems in a more timely fashion than traditional batch jobs.
Mixed workloadsOLTP and reportingare the source of many performance challenges and the topic of intense debate. The data warehousing industry had its genesis in the realization that OLTP systems could not realistically provide the needed transaction throughput while supporting the enormous amount of historical data and ad hoc query workload that business analysts needed for things like multiyear trend analysis.
The issue isn't simply one of adequate machine horsepower; rather, it's the way data is modeled, stored, and accessed, which is typically quite different. In OLTP, the design centers on analyzing and automating business processes to provide consistent performance for a well-known set of transactions and users. While data warehousing certainly involves analysis and requires performance, the nature of the workload isn't easy to predict. Ad hoc queries that can consume significant resources occur along with scheduled reports.
Reporting and query functions are part of an OLTP system, but the scope and frequency are typically more controlled than in a data warehouse environment. For example, a banking OLTP system will include queries for customer status and account balances, but not multiyear transaction patterns.
The OLTP system typically provides forms that allow well-targeted queries that are executed efficiently and don't consume undue resources. However, hard and fast rulesfor example, that OLTP systems don't include extensive query facilities don't necessarily hold true. The I/O performed by most OLTP systems tends to be approximately 70-80% read and 20-30% write. Most transactions involve the querying of data, such as product codes, customer names, account balances, inventory levels, and so on. Users submitting tuned queries for specific business functions are a key part of OLTP. Ad hoc queries across broad data sets are not.
Decision support systems and OLTP systems could access much of the same data, but these types of systems also typically have different requirements in terms of CPU, memory, and data layout, which makes supporting a mixed workload less than optimal for both types of processing. Real Application Clusters, with dynamic service provisioning in Oracle Database 10g, makes it possible to allocate individual nodes for individual workloads. It also makes it more feasible to deploy these mixed workloads to a single database (albeit with multiple database instances).