Application partitioning is the process of separating database applications in such a way that each OPS node accesses a disjoint set of tables. Figure 11.2 shows two applications that are neatly partitioned between two nodes. Instance 1 runs the sales application, which has its own set of tables. Instance 2 runs the accounts application, which uses a different set of tables.

Figure 11-2. A neatly partitioned application

Tables used by multiple applications are referred to as overlap tables. There are no overlap tables in Figure 11.2. Each application is completely independent of the other. Because of this, the two instances will never contend for the same database block, and true pings will never occur.

Eliminating overlap tables from applications that are run from different OPS nodes is not sufficient to reduce or eliminate the occurrence of false pings. To reduce false pings, you must reduce the amount of PCM lock contention that occurs. A god way to do this is to place the tables and indexes used by each application in a separate tablespace and allocate separate PCM locks for the datafiles in each of those tablespaces. You want to ensure that the PCM locks protecting the data for one application are not also used to protect data used by another application. If one PCM lock covers data accessed from two different instances, you run the risk of false pings as a result of contention for that lock.

Application partitioning must be performed when you first design the database and the applications that access it. If you don’t partition applications properly when you design them, your tuning of PCM lock allocation at later stages won’t be enough to reduce true pings. Tuning cannot substitute for proper application partitioning.

The process of application partitioning can be broken down into the following steps:

As an example, let’s consider three applications developed by the ABC Sales Company. One application is used by Product Sales clerks, another is used by Inventory clerks, and the third is used by Human Resource (HR) clerks. The Product Sales clerks of the ABC Sales Company use the Product Sales application to enter customer information into the database and to record sales transactions. Inventory clerks use the Inventory application to update the stock status and record the receipt of products from vendors. The HR clerks use the Human Resource application and are responsible for handling employee information, benefits, timesheets, and payroll. The company is planning to use a two-node OPS database to run these applications, and it’s your job to set up the database and applications to run as efficiently as possible. With that in mind, you can work through each of the four steps in the application partitioning process.

Identify overlap application tables

After identifying the applications, your next step is to identify the tables that will be accessed by each of those applications. Table 11.2 shows each of the tables accessed by the Product Sales, Inventory, and Human Resource applications, along with the type of table access (R=read, W=write, RW=read/write) by each application. Since this is a hypothetical example, we’ve shown only a few tables from each application. Using this information, you next will identify the tables that are used by two or more applications.

Table 11-2. Database Tables Used by the Product Sales, Inventory, and Human Resource Applications

Tables	Product Sales	Inventory	Human Resource
CUSTOMERS	RW
SALES	RW
SALES_DETAIL	RW
PRODUCTS	R	RW
STOCK	RW	RW
VENDORS		RW
RECEIPT		RW
EMPLOYEE			RW
BENEFITS			RW
APPRAISALS			RW

In our example, the PRODUCTS and STOCK tables are overlap tables. They are accessed by both the Product Sales application and the Inventory application. Figure 11.3 shows the overlap tables between Product Sales and Inventory applications.

Figure 11-3. Overlap tables

With data /partitioning , also referred to as horizontal partitioning , data objects—for example, tables—are partitioned, and each OPS node accesses a set of partitions distinct from those accessed by the other nodes. In order to implement this partitioning scheme, you must do the following:

For example, you might partition the data of a table based on a REGION column. If valid regions for our Product Sales application are East and West, then transactions from East can be routed to node 1, and transactions from West can be routed to node 2. Even though the nodes are accessing the same table, they will be accessing different partitions of that table, eliminating any contention for the same database block. You can perform data partitioning using the Oracle8 partitioning features.

Oracle8 partitioned tables

If you specify a partitioning key at the time you create a table in Oracle8, you will be able to automatically partition a table so that data in the table is stored in multiple partitions. Each partition then can be stored in its own tablespace. This feature is commonly used in data warehouse applications to manage large tables. For example, the following CREATE TABLE statement creates the SALES table with two partitions using the region_code column as the partitioning key:

CREATE TABLE sales
      (
       sale_no               NUMBER(9) NOT NULL,
       region_code           NUMBER(1) NOT NULL,
       cust_id               NUMBER(6) NOT NULL,
       sales_rep_id          NUMBER(6) NOT NULL,
       sale_date             DATE      NOT NULL,
       CONSTRAINT pk_sales   PRIMARY KEY (sale_no)
)
       PARTITION BY RANGE (region_code)
       (
           PARTITION s_east VALUES LESS THAN (2) TABLESPACE s_east,
           PARTITION s_west VALUES LESS THAN (3) TABLESPACE s_west
       );

Figure 11.4 shows the resulting partitioned SALES table with East and West partitions based on region code.

Figure 11-4. The sales table partitioned by region

Data-dependent routing

Routing a transaction to an appropriate node based on the value of the data in a table requires extra application logic. Using a three-tier architecture is very helpful in implementing data-dependent routing (i.e., routing based on the value of data). In a three-tier architecture, the middle layer is responsible for routing transactions to the appropriate backend server and can be configured to route database transactions to specific nodes based on the data value of a specific column. The front-end application need not know which OPS instance is being used for any given transaction. This approach makes the design of your frontend applications independent of database location logic. You might use a product like the Tuxedo TP monitor to implement this type of data-dependent routing, as shown in Figure 11.5.

Figure 11-5. Data-dependent routing in a three-tier architecture

With transaction partitioning , individual transactions are routed to the most appropriate node based on the tables accessed by the transaction. This is different from data partitioning. Data partitioning is based on the value of a specific partition key, whereas transaction partitioning is based on the tables involved in a transaction. For example, an order entry transaction may be routed to a node that is primarily responsible for the subset of data required by order entry transactions. Stock status transactions may be routed to a different node, which is responsible for the subset of data required by those transactions. When application partitioning or data partitioning is not possible, you might choose to use transaction partitioning. Transaction partitioning is more complex. This type of partitioning, with its precise routing of transactions to appropriate nodes, is possible only in a three-tier architecture.

Transaction partitioning provides some advantages over other partitioning methods. If the data access characteristics of an application change, then individual transactions can be routed to the appropriate OPS node by changing the configuration in the middle layer. If new OPS nodes are added, then also changing the configuration in the middle layer can reroute transactions to the most appropriate node.

One way to do transaction partitioning is to route transactions to different OPS nodes during different hours of operation. For example, in a two-node OPS setup, let’s assume that transactions T1 and T2 access the common data set D1. Let’s also assume that transactions T3 and T4 access data sets D3 and D4, respectively. During the day, when the frequency of transactions T1 and T2 is high, these transactions are routed to one specific node in order to minimize contention. Transactions T3 and T4 are processed through a completely different node. At night, when the volume of T1 and T2 transactions is significantly less, and the volume of T3 and T4 transactions is quite high, T3 and T4 can be routed to different nodes. Running high-frequency transactions T3 and T4 through different nodes during the night achieves load balancing but does not cause any conflicts because these transactions access different data sets. If required, transactions T1 and T2 may be processed through different nodes at night without any problem because of the low frequency of these transactions at that time. This time-based partitioning helps to balance the overall load and also minimize concurrent access to the same data objects from different nodes.