Oracle Essentials Oracle Database 11g (42 page)

Multi-Threaded Server/shared server

Oracle7 introduced the Multi-Threaded Server (MTS, renamed the shared server in Oracle9
i
) (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
212

|

Chapter 9: Oracle and Transaction Processing

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, youcan think of connection pooling as the middleweight solution and multiplexing via Connection Manager as the heavyweight solution.

Figure 9-5 illustrates these two network-scaling technologies.

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

CONNECTION POOLING

Shared Server

CONNECTION MULTIPLEXING

Concentrators

Shared Server

Multiplexing

Figure 9-5. Network scaling in Oracle Net

Oracle Features for OLTP

|

213

Bind Variables and Shared SQL

As we’ve mentioned, Oracle’s shared SQL is a key feature for building high-performance applications. In an OLTP application, similar SQL statements may be used repeatedly, but each SQL statement submitted will have different selection criteria contained in the WHERE clause to identify the different sets of rows on which to operate. Oracle can share SQL statements, but the statements must be absolutely identical.

To take advantage of this feature for statements that are identical except for specific values in a WHERE clause, you can use bind variables in your SQL statements. The values substituted for the bind variables in the SQL statement may be different, but the statement itself is the same.

Consider an example application for granting raises to employees. The application submits the following SQL:

UPDATE emp SET salary = salary * (1 + 0.1)

WHERE empno = 123;

UPDATE emp SET salary = salary * (1 + 0.15)

WHERE empno = 456;

These statements are clearly different; they update different employees identified by different employee numbers, and the employees receive different salary increases. To obtain the benefits of shared SQL, youcan write the application to use bind variables for the percentage salary increase and the employee numbers, such as: UPDATE emp SET salary = salary * (1 + :v_incr)

WHERE empno = :v_empno;

UPDATE emp SET salary = salary * (1 + :v_incr)

WHERE empno = :v_empno;

These statements are recognized as identical and would therefore be shared. The application would submit different values for the two variables :v_incr and :v_empno, a percentage increase of 0.1 for employee 123 and 0.15 for employee 456. Oracle substitutes these actual values for the variables in the SQL. The substitution occurs during the phase of processing known as the
bind phase
, which follows the
parse phase
and
optimize phase
. For more details, see the relevant Oracle guide for your development language.

Oracle Database 10
g
and more recent versions include tuning tools that can easily spot this type of potential application optimization.

Database Resource Manager

Oracle8
i
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
214

|

Chapter 9: Oracle and Transaction Processing

DBA to ensure that certain types of users receive sufficient machine resources to meet performance requirements.

For example, you can allocate 80 percent of the CPU resources to order-entry users, with the remaining 20 percent 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 percent allocation. In other words, the order-entry users will get up to 80 percent of CPU time, as needed, while the users asking for reports will get at least 20 percent 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.

Oracle9
i
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.

Oracle9
i
also added to the Database Resource Manager 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 Oracle9
i
, 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 toward short OLTP operations to another group that would be more appropriate for batch operations.

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

Real Application Clusters

Arguably, the biggest advance in Oracle9
i
was a feature called Real Application Clusters. Real Application Clusters (RAC) 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 applications—

applications in which data could be partitioned in logical ways and applications that primarily supported read activity. The reason why use of OPS was mostly limited to data warehousing implementations was the phenomenon known as
pinging
.

Oracle Features for OLTP

|

215

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), as shown in Figure 9-6.

Node 1

Node 2

Node 3

Node 4

Disk 1

Disk 2

Figure 9-6. RAC architecture

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 a page can contain more than a single row. If one machine in a cluster wanted to modify a row in a page that was already being modified by another machine, that page had to be flushed to the database file on the shared disk—a 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 it—to 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. RAC

fully supports 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 either being used by another machine or simply residing in the cache of another machine, the block is directly shipped to the requesting machine, usually over a very high-speed interconnect.

216

|

Chapter 9: Oracle and Transaction Processing

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 most all applications, without any modifications. With that said, for OLTP

applications deployed to RAC (where there are frequent modifications to indexes within a small set of leaf blocks), reverse key indexes might be used to distribute inserts across leaf keys in the index and eliminate possible performance issues for

this special situation (see Chapter 4 for an explanation of reverse key indexes).

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.

As of Oracle Database 10
g
, the model implemented with RAC has been extended beyond clusters to grid computing. Oracle now 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. In Oracle 10
g
Release 2, Oracle made it possible to monitor the different nodes in a cluster and to issue advisories to ensure better load balancing across the nodes.

High Availability

From an operational perspective, OLTP systems represent a company’s electronic central nervous system, so the databases that support these systems must be highly available. Oracle has a number of features that contribute to high availability:
Standby database

Oracle can provide database redundancy by maintaining a copy of the primary database on another machine, usually at another site. Redo logs from the primary server are shipped to the standby server and applied there to duplicate the production activity. Oracle8
i
introduced the automated shipping of redo logs to the standby site and the ability to open the standby database for read-only access for reporting.

Oracle9
i
Release 2 introduced the concept of
logical standby
. With a logical standby database the changes are propagated with SQL statements, rather than redo logs, which allow the logical standby database to be used for other database operations.

Transparent Application Failover (TAF)

TAF is a programming interface that automatically connects a user session to another Oracle instance should the primary instance fail. Any queries that were in process are resumed from the point of the last row fetched for the result set.

High Availability

|

217

Oracle Streams/Advanced Queuing (AQ)

AQ in Oracle Streams provides a method for asynchronous, or deferred, intersystem communication, allowing systems to operate more independently. Avoiding direct system dependencies can help to avoid “cascading” failures, allowing interconnected systems to continue to operate even if one system fails. For example, Streams can enable change data capture among Oracle databases and can be used with non-Oracle databases by leveraging gateways. These capabilities are

described in more detail in the following section and in Chapter 13.

Oracle Streams Replication

You can use Oracle’s built-in replication functionality to provide data redundancy.

Changes made by transactions are replicated synchronously or asynchronously to other databases. If the primary database fails, the data is available from the other databases. As of Oracle9
i
Release 2, log-based replication is included as part of