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

8.4 Oracle Features for OLTP

Oracle has many features that contribute to OLTP performance, reliability,
scalability, and availability. This section presents the basic
attributes of many of these features. This section is by no means
exhaustive; it's only intended to be an
introduction. Please see your relevant Oracle documentation and
third-party books for more information.

8.4.1 General Concurrency and Performance

Chapter 7,
Oracle has implemented excellent support for concurrency and
performance in OLTP systems. Some of the key features
relevant to OLTP are as follows:

Nonescalating row-level locking

Oracle locks only the rows a transaction
works on and never escalates these locks to page-level or table-level
locks. In some databases, which escalate row locks to page locks when
enough rows have been locked on a page, contention can result from
false lock contention when users want to work on unlocked rows but
contend for locks that have escalated to higher granularity levels.

Multiversion read consistency

Oracle provides statement-level and
transaction-level data consistency without requiring read locks. A
query is guaranteed to see only the data that was committed at the
time the query started. The changes made by transactions that were
in-flight but uncommitted at the time the query started
won't be visible. Transactions that began after the
query started and were committed before the query finishes also
won't be seen by the query. Oracle uses rollback
segments to reproduce the data as it existed at the time the query
started. This capability avoids the unpleasant choice between
allowing queries to see uncommitted data (known as dirty reads) or
having readers block writers (and vice versa). It also provides a
consistent snapshot view of data at a single point in time.

Shared SQL

The parsing of a
SQL statement is fairly CPU-intensive. Oracle caches parsed and
optimized SQL statements in the shared SQL area within the shared
pool. If another user executes a SQL statement
that's cached, the parse and optimize overhead is
avoided. The statements must be exactly identical to be reused; no
extra spaces, line feeds or differences in capitalization are allowed
(see the sidebar). OLTP systems involve a large number of users
executing the same application code. These systems provide an ideal
opportunity for reusing shared SQL statements.

Stored outlines

Oracle8i added
support of execution-plan stability, sometimes referred to as
bound
plans, with stored outlines. The route a SQL statement
takes during execution is critical for high performance. Once
application developers and DBAs have tuned a SQL statement for
maximum efficiency, they can force the Oracle optimizer to use the
same execution plan regardless of environmental changes. This
provides critical stability and predictability in the face of
software upgrades, schema changes, data-volume changes, and so on.
Oracle9i added the capability for administrators
to edit stored outlines.

With Oracle Database 10g, you can select better
execution plans for the optimizer to use in conjunction with poorly
written SQL to improve OLTP performance without having to rewrite the
SQL. The SQL Tuning Advisor performs these advanced optimizations on
SQL statements, and can then create an improved SQL Profile for the
statement. This profile is used instead of the original optimization
plan at runtime.

UPDATE emp SET salary = salary * (1 + 0.1)WHERE empno = 123;
UPDATE emp SET salary = salary * (1 + 0.15)WHERE empno = 456;

UPDATE emp SET salary = salary * (1 + :v_incr)WHERE empno = :v_empno;
UPDATE emp SET salary = salary * (1 + :v_incr)WHERE empno = :v_empno;

8.4.2 Scalability

Both
the shared server (known as the Multi-Threaded Server prior to
Oracle9i) and the Database Resource Manager help
Oracle support larger or mixed user populations.

8.4.2.1 Multi-Threaded Server/shared server

Oracle7 introduced the Multi-Threaded Server (MTS, renamed
shared server in Oracle9i and described in
Chapter 2) to allow Oracle to support larger user populations. While
shared server and MTS reduced the number of server processes, each
client still used its own physical network connection. The resources
for network connections aren't unlimited, so Oracle8
introduced two solutions for increasing the capabilities of the
actual network socket layer at the operating-system level:

Oracle Net connection pooling

Allows the
client
population to share a pool of shared physical network connections.
Idle clients transparently "time
out," and their network connections are returned to
the pool to be used by active clients. Each idle client maintains a
virtual connection with Oracle and will get another physical
connection when activity resumes. With the Oracle security model,
authentication is separate from a specific connection, so a single
pooled connection can represent different users at different times.
Connection pooling is suitable for applications with clients that
connect but aren't highly active (for example, email
systems).

Oracle Net Connection Manager

Reduces the number of network connections used on the database
server. Clients connect to a middle-tier machine running the
Oracle Net Connection Manager (CMAN).
The Connection Manager multiplexes the traffic for multiple clients
into one network connection per Oracle Net dispatcher on the database
server. Unlike connection pooling, there is no notion of
"time-out" for a
client's virtual network connection. The Oracle
network topology can include multiple machines running the Connection
Manager to provide additional scalability and fault-tolerance.

In terms of scalability, you can think of connection pooling as the
middleweight solution and multiplexing via Connection Manager as the
heavyweight solution. Figure 8-5 illustrates these two
network-scaling technologies.

Connection Manager has become more flexible in Oracle Database
10g, with the added ability to dynamically alter
configuration parameters without shutting down Connection Manager and
improved access rules to filter CMAN traffic.

Figure 8-5. Network scaling in Oracle Net

8.4.2.2 Database Resource Manager

Oracle8i introduced the
Database Resource
Manager (DRM) to simplify and automate the management of mixed
workloads in which different users access the same database for
different purposes. You can define different consumer groups to
contain different groups of users. The DRM allocates CPU and
parallelism resources to consumer groups based on resource plans. A
resource plan defines limits for the amount of a particular computer
resource a group of users can use. This allows the DBA to ensure that
certain types of users receive sufficient machine resources to meet
performance requirements.

For example, you can allocate 80% of the CPU resources to order-entry
users, with the remaining 20% allocated to users asking for reports.
This allocation prevents reporting users from dominating the machine
while order-entry users are working. If the order-entry users
aren't using all the allocated resources, the
reporting users can use more than their allotted percentage. If the
order-entry workload increases, the reporting users will be cut back
to respect their 20% allocation. In other words, the order-entry
users will get up to 80% of CPU time, as needed, while the users
asking for reports will get at least 20% of the CPU time, and more
depending on how much the order-entry group is using. With the DRM,
you can dynamically alter the details of the plan without shutting
down the instance.

Oracle9i added a
number of significant improvements to the Database Resource Manager.
The DRM now allows a DBA to specify the number of active sessions
available to a consumer group. Any additional connection requests for
the consumer group are queued. By limiting the number of active
connections, you can start to avoid the situation where a request
comes in that pushes the resource requirements for a group over the
limit and affects all the other users in that group.

Oracle9i also added to the DRM the ability to
proactively estimate the amount of CPU that an operation will
require. If an operation looks as if it will exceed the maximum CPU
time specified for a resource group, the operation will not be
executed, which can prevent inappropriately large operations from
even starting.

Finally, since Oracle9i, the DRM can also
automatically switch a consumer group to another consumer group if
that group is active for too long. This feature could be used to
automatically switch a consumer group oriented towards short OLTP
operations to another group that would be more appropriate for batch
operations.

With Oracle Database 10g, you can now define a
consumer group by the service name, application, host machine, or
operating system user name of a user.

8.4.3 Real Application Clusters

Arguably, the biggest advance in Oracle9i was a
feature called Real Application Clusters
(RAC). Real Application Clusters was a new version of technology
replacing Oracle Parallel Server (OPS).

In the first edition of this book, we described OPS as a feature that
could be used for improving performance and scalability for certain
data warehouse-style applicationsapplications in which data
could be partitioned in logical ways and applications that primarily
supported read activity. The reason why OPS was used only in limited
cases was the phenomenon known as pinging.

In the world of both OPS and RAC, multiple machines access the same
database files on shared disk (either physically attached or
appearing as physically attached through software). This architecture
allows you to add more machines to a cluster of machines, which in
turn adds more overall horsepower to the system. But there was a
problem with the implementation of this architecture for OPS,
stemming from the fact that each machine in a cluster had its own
cache. If one machine tried to execute a write operation that
required a block in the cache of another machine, that block had to
be flushed to the database file on the shared diska scenario
that was termed a
ping.
This chain of events caused extra disk I/O, which in turn decreased
the overall performance of the solution.

The traditional way around this problem was simply to avoid
itto use OPS only when a database would not cause pings with a
lot of write operations, or to segregate writes so that they would
not require data in use on another node. This limitation required you
to carefully consider the type of application to which you would
deploy OPS and sometimes forced you to actually modify the design of
your application to work around OPS's limitations.

With Real Application Clusters, the problem caused by pings was
eliminated. Real Application Clusters fully
support the technology known as Cache Fusion.
Cache Fusion
makes all the data in every cache on every machine in a Real
Application Cluster available to every other machine in the cluster.
If one machine needs a block that is cached by another machine, the
block is directly shipped to the requesting machine, usually over a
very high-speed interconnect.

Cache Fusion means that you do not have to work around the problems
of pinging. With Real Application Clusters you will be able to see
significant scalability improvements for virtually all applications,
without any modifications.

Real Application Clusters also deliver all the availability
advantages that were a part of OPS. Because all the machines in a
Real Application Cluster share the
same disk, the failure of a single machine does not mean that the
database as a whole has failed. The users connected to the failed
machine have to be failed over to another machine in the cluster, but
the database server itself will continue to operate.

Oracle also has partnerships with some hardware vendors that are
designed to increase the integration of both hardware and database
clusters. The goal toward which Oracle and its hardware vendor
partners are working is the nirvana of plug-and-play clusters, in
which you can add another machine to a cluster by simply plugging it
in.

With Oracle
Database 10g, this model is extended beyond
clusters to grid computing. With this release, Oracle offers all the
components you need to use to implement clusters on several operating
system platforms as part of the Oracle software stack, including a
volume manager and clusterware.