Oracle Essentials [Electronic resources] : Oracle Database 10g, 3rd Edition نسخه متنی

8.1 OLTP Basics

Before we discuss how Oracle specifically handles OLTP,
we'll start by presenting a common definition of
online transaction processing.

8.1.1 What Is a Transaction?

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.

8.1.2 What Does OLTP Mean?

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.

8.1.2.1 General characteristics

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.

8.1.2.2 Online versus batch

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.

8.1.3 OLTP Versus Decision Support

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