Partitioning is the process of designing database applications in such a way that OPS instances running on different nodes access mutually exclusive sets of data. This reduces contention for the same data blocks by multiple instances. The end result is that pinging is reduced, and the OPS system will run more efficiently.
Partitioning must be done when a database application or set of database applications is designed. You have to analyze the data access requirements of each application that you are designing and assign applications to OPS nodes so that contention for data from multiple nodes is minimized. This chapter describes three common approaches to partitioning in an OPS environment.
In an OPS environment, several nodes access a shared database. If multiple database instances access the same set of data objects, these data objects end up in the buffer cache of each of those instances. The system will then need to synchronize the buffer caches in the different instances using Parallel Cache Management (PCM) locks. The use of these locks results in pinging, a process (introduced in Chapter 8) that has a high amount of overhead associated with it. The result is a decrease in overall database performance.
Chapter 8 described two types of pings: false pings and true pings. False pings result when multiple OPS instances are contending for the same PCM locks but not the same database blocks. You can reduce false pings by allocating additional PCM locks and by using an appropriate PCM lock allocation strategy.
True pings occur when multiple OPS instances contend for the same block in the database. This happens when one or more applications running on different nodes need read/write access to the same set of data blocks. Because true pings represent conflicts for a database block, allocating additional PCM locks won’t help. The only way to reduce the occurrence of true pings is to partition your database and applications in a way that reduces the need for two instances to access the same database block.
Partitioning works well for certain types of database applications and is quite difficult for others. Application partitioning may not be possible for an OLTP database in which many users use one large application with read/write access to all the tables. Using multiple OPS instances in an environment like that will result in a large number of pings due to conflicts between the instances. This situation may degrade OPS performance to the point at which it’s worse than that of a standalone database. An OPS database is not suitable for such an environment, and you should consider other options, such as an Oracle database on SMP hardware.
Partitioning makes a lot of sense for many types of departmental and DSS applications. It’s easy to partition departmental applications in which the data overlap is minimal. An example might be two applications in which the sales department and the accounts department each has its own set of tables. Since these applications have few overlap tables, each set of tables can be placed in its own tablespace, and pings will not occur. Figure 11.1 illustrates such an arrangement.
