Introduction to Oracle Parallel Server

Oracle's parallel server option (PSO) allows multiple instances, each operating a separate node, to share a single database. Each instance has its own set of shared memory segment (SGA), background processes, online redo logs, and rollback segments. The data file and control files are shared by both instances. Online redo logs and rollback segments acquired by an instance at the startup are written to by that instance only, however, read access is available to other instances as well.

Parallel server is designed to allow an instance to be brought down, either voluntarily or involuntarily, without affecting the transactions running on other instances. No committed data in the failed instance will be lost. There will, however, be a small amount of degradation in response time during recovery if blocks in the failed instance are being accessed by other instances.

The ability to spread instances across different machines provides you with scalability. Many hardware vendors offer machines that have upper limits on both memory and numbers of processors. With parallel server, though, if you have a machine with a 2-gigabyte upper limit on memory and your memory requirements grow beyond the 2 gigabytes, you can split your users across two machines. Each machine will have its own instance, but all users will share the same database. 

If you are running parallel server on two different machines and you lose one of the machines, the product allows your key users to log on to the machine that is still running. Parallel server also tells the node that is still running to recover the instance that has crashed. To allow these functions, the disks that contain your database must have physical cables attached to both machines. 

Many sites have both online transaction-processing (OLTP) users and batch users. Mixing the two types of users in one instance often has a devastating effect on response times. To address this problem, most sites set standards dictating that batch jobs that run for more than n minutes (usually 5) cannot run during prime production hours. However, this is not a satisfactory scenario at many sites that need to run management reports requiring timely information. With parallel server, System Global Areas (SGAs) can be specifically tuned to support OLTP users in the instance on one machine and batch users in the instance on the other machine. 

Thus Oracle Parallel Server offers improved scalability, availability and higher throughput.

You can have disks hard-cabled to both machines and also use the second machine for other purposes, such as reporting from another database. If the machine that runs the main database (usually a substantially larger machine) goes down, an instance can be started on the second machine to bring the database up. Your users can log on to the second machine without the need to run parallel server. This configuration is normally known as the cluster fail-oversolution and is available both on Unix as well as NT platforms. There are many variations of this implementation. Many hardware vendors are offering this solution. Cluster fail-over solution is available from Oracle in the commercial name of Oracle Failsafe Option. Presently it's available on NT and HP/Unix. Oracle Failsafe Option from Oracle is free.

Parallel server is an extra-cost Oracle option. When Oracle Parallel Server was first introduced inn version 6.3, in addition to buying parallel server option, you also needed to get teh distributed lock manager from your hardware vendor. However, with version 8.0, Oracle has integrated the lock manager in its kernel code. Oracle Parallel Server incurs some overhead compared to the Oracle without parallel option. However, Oracle is optimizing its parallel server code with every new release and this performance overhead is coming down. 

Given the possible performance problems, the reasons for choosing parallel server are:

• High Availability
• Cost Savings
• Recoverability
• Reduced administration of a single database. 

Parallel server is also the engine that most observers believe will support the emerging technology of massively parallel systems, which promises brilliant performance and excellent scalability. Purchasing many small machines is considerably cheaper than purchasing fewer large machines. In addition, an application designed to run using parallel server can provide very acceptable response times (assuming that your design is suitable for parallel server and you have tuned your instances and locking correctly). Reductions in hardware costs can provide your organization with huge cost savings. 

 Parallel server will run very well on certain types of applications and very poorly on others. Parallel server is best suited to the following types of applications:

• Query-intensive applications, such as decision support systems, in which data is usually loaded nightly and heavy query activity by lots of users takes place during the day
• Applications that need to run both batch and OLTP users, each accessing different sets of tables and indexes
• Applications that are CPU-intensive, rather than I/O-intensive
• Applications that update different tables, in which users of each application are able to use a different instance
• Applications that update the same tables but at different times
• Applications in which users access tables on a location basis; for example, account A users have no reason to see account B users' data, and vice versa

In general, parallel server is unsuited to the following:

• Applications that perform many inserts, updates, and deletes on tables, clusters, and indexes, in which it is difficult to split user access to different tables, clusters, and indexes
• I/O-intensive applications in which particular tables, indexes, and other database objects are hit heavily by all users

Benefits of Parallel Server

Parallel server allows for high availability. In a 24X7 shop the automatic fail over capability for some versions mean even with a full system failure, the database will not become unavailable. Also, in a parallel server environment adding more nodes and parallel instances with few changes to the database structure increases the number of users served. Under Oracle parallel server, complex applications can be partitioned among several nodes thus enhancing performance. One thing to note is that partitioning applications is complex and may not be possible if you are porting from standard to parallel server without significant redesign. 

Problems with Parallel Server

The major problems that occur under parallel server involve two types of lock contention, PING and FALSE PING. Using parallel cache management combined with distributed lock management Oracle assigns database file blocks to locks based on how the DBA sets certain initialization parameters (shown below). Usually when a database is designed to operate under parallel server, tables are distributed to multiple tablespaces and datafiles and the granularity of the locking is set for each table. The granularity depends on type of access, for example, where data is accessed one record at a time you would desire as fine a grain locking as possible. In applications where data is read in chunks then courser grain locking is required. If an application mixes these two types of access (OLTP fine grain transactions during the day, batch load course grain locking at night) then the setting of the initialization parameters for the PCM system is difficult and at best a compromise is reached. 

A PING occurs in either a course or fine grain locking situation where a data block required by one instance is already locked by another. The block must be released and re-acquired. A FALSE PING happens usually under course grain locking when an instance requires a block that is held under a course PCM lock but isn't being used. This results in the required release and relock of that block.

Minimizing PINGS and FALSE PINGS (sometimes know as FALSE COLLISIONS) will be the primary job of a shared instance DBA. One thing to remember is that if the application cannot be partitioned, you will not be able to eliminate PINGS.

PCM Initialization Parameters

Parallel Server Architecture

Parallel server runs under two main configurations: a loosely coupled architecture and a massively parallel architecture. A third type of configuration, a tightly coupled architecture, can run parallel server as a node. Some platforms allow you to have multiple parallel instances on a single node; with others, you must have the second and subsequent instances located on other machines that are part of a loosely coupled or massively parallel configuration. There are potential performance bottlenecks associated with each type of configuration. We recommend if you are running parallel server at your site, that you learn as much as possible about the different types of hardware configurations, so you can purchase the most suitable hardware to support your parallel server applications.