13
Designing Databases for Parallel Server

This chapter prescribes a general methodology for designing systems optimized for Oracle Parallel Server (OPS).

• Overview

• Case Study: From Initial Database Design to OPS

• Analyze Access to Tables

• Analyze Transaction Volume by Users

• Partition Users and Data

• Partition Indexes

• Implement Hashed or Fine Grain Locking

• Implement and Tune Your Design

Overview

This chapter provides techniques for designing new applications for use with OPS. You can also use these analytical techniques to evaluate existing applications and see how well suited they are for migration to a parallel server.

Note: Always remember that your goal is to minimize contention: doing so results in optimized performance.

This chapter assumes you have made an initial database design. To optimize your design for OPS, follow the methodology suggested here.

1. Develop an initial database design.

2. Analyze access to tables.

3. Analyze transaction volume.

4. Decide how to partition users and data.

5. Decide how to partition indexes, if necessary.

6. Choose hashed or fine grain locking.

7. Implement and tune your design.

Case Study: From Initial Database Design to OPS

A case study is used is this chapter to demonstrate analytical techniques in practice. Although your applications will differ, this example helps you to understand the process.

• "Eddie Bean" Catalog Sales

• Tables

• Users

• Application Profile

"Eddie Bean" Catalog Sales

The case study concerns the "Eddie Bean" catalog sales company, which has many order entry clerks processing telephone orders for various products. Shipping clerks fill orders and accounts receivable clerks handle billing. Accounts payable clerks handle orders for supplies and services the company requires internally. Sales managers and financial analysts run reports on the data. This company's financial application has three business processes operating on a single database:

• Order entry

• Accounts payable

• Accounts receivable

Tables

Tables from the Eddie Bean database include:

Table 13-1 "Eddie Bean" Sample Tables

Table	Contents
ORDER_HEADER	Order number, customer name and address.
ORDER_ITEMS	Products ordered, quantity, and price.
ORGANIZATIONS	Names, addresses, phone numbers of customers and suppliers.
ACCOUNTS_PAYABLE	Tracks the company's internal purchase orders and payments for supplies and services.
BUDGET	Balance sheet of the company's expenses and income.
FORECASTS	Projects future sales and records current performance.

Users

Various application users access the database to perform different functions:

• Order entry clerks

• Accounts payable clerks

• Accounts receivable clerks

• Shipping clerks

• Sales manager

• Financial analyst

Application Profile

Operation of the Eddie Bean application is fairly consistent throughout the day: order entry, order processing, and shipping are performed all day. These functions are not for example, segregated into separate one-hour time slots.

About 500 orders are entered per day. Each order header is updated about 4 times during its lifetime. So we expect about 4 times as many updates as inserts. There are many selects, because many employees are querying order headers: people doing sales work, financial work, shipping, tracing the status of orders, and so on.

There are on average 4 items per order. Order items are never updated: an item may be deleted and another item entered.

The ORDER_HEADER table has four indexes. Each of the other tables has a primary key index only.

Budget and Forecast activity has a much lower volume than the order tables. They are read frequently, but modified infrequently. Forecasts are updated more often than Budget, and are deleted once they go into actuals.

The vast bulk of the deletes are performed as a nightly batch job. This maintenance activity does not, therefore, need to be included in the analysis of normal functioning of the application.

Analyze Access to Tables

Begin by analyzing the existing (or expected) access patterns for tables in your database. Then decide how to partition the tables and group them according to access pattern.

• Table Access Analysis Worksheet

• Case Study: Table Access Analysis

Table Access Analysis Worksheet

List all your high-activity database tables in a worksheet like the one shown in Table 13-2:

Table 13-2 Table Access Analysis Worksheet

Table Name	Daily Access Volume
	Read Access		Write Access
	Select		Insert		Update		Delete
	Operations	I/Os	Operations	I/Os	Operations	I/Os	Operations	I/Os

To complete this worksheet, estimate the volume of each type of operations. Then calculate the number of reads and writes (I/Os) the operations entail.

Estimating Volume of Operations

For each type of operation to be performed on a table, enter a value reflecting the normal volume you would expect in the course of a day.

Note: The emphasis throughout this analysis is on relative values--gross figures describing the normal use of an application. Even if an application does not yet exist, you can project the types of users and estimate relative levels of activity. Maintenance activity on the tables is not generally relevant to this analysis.

Calculating I/Os per Operation

For each value in the Operations column, calculate the number of I/Os that will be generated using a worst-case scenario.

The SELECT operation involves read access, and the INSERT, UPDATE and DELETE operations involve both read and write access. These operations access not only data blocks, but also any related index blocks.

Note: The number of I/Os generated per operation changes by table depending on the access path of the table, and the table's size. It also changes depending on the number of indexes a table has. A small index, for example, may have only a single index branch block.

For example, Figure 13-1 illustrates read and write access to data in a large table in which two levels of the index are not in the buffer cache and only a high level index is cached in the SGA.

Figure 13-1 Number of I/So per SELECT or INSERT Operation

In this example, assuming that you are accessing data by way of the primary key, a SELECT entails three I/Os:

1. One I/O to read the first lower level index block.

2. One I/O to read the second lower level index block.

3. One I/O to read the data block.

   Note: If all of the root and branch blocks are in the SGA, a SELECT may entail only two I/Os: read leaf index block, read data block.

An INSERT or DELETE statement entails at least five I/Os:

1. One I/O to read the data block.

2. One I/O to write the data block.

3. Three I/Os per index: 2 to read the index entries and 1 to write the index.

One UPDATE in this example entails seven I/Os:

1. One I/O to read the first lower level index block.

2. One I/O to read the second lower level index block.

3. One I/O to read the data block.

4. One I/O to write the data block.

5. One I/O to read the first lower level index block again.

6. One I/O to read the second lower level index block again.

7. One I/O to write the index block.

   Note: An INSERT or DELETE affects all indexes, but an UPDATE sometimes may affect only one index. Check the number of changed index keys.

I/Os per Operation for Sample Tables

In the case study, the number of I/Os per operation differs from table to table because the number of indexes differs from table to table.

Table 13-3 shows how many I/Os are generated by each type of operation on the ORDER_HEADER table. It assumes that the ORDER_HEADER table has four indexes.

Table 13-3 Number of I/Os per Operation: Sample ORDER_HEADER Table

Operation	SELECT	INSERT	UPDATE	DELETE
Type of Access	read	read/write	read/write	read/write
Number of I/Os	3	14	7	14

Note: You must adjust these figures depending upon the actual number of indexes and access path for each table in your database.

Table 13-4 shows how many I/Os generated per operation for each of the other tables in the case study, assuming each of them has a primary key index only.

Table 13-4 Number of I/Os per Operation: Other Sample Tables

Operation	SELECT	INSERT	UPDATE	DELETE
Type of Access	read	read/write	read/write	read/write
Number of I/Os	3	5	7	5

For the purposes of this analysis, you can disregard the fact that changes made to data also generate rollback segments, entailing additional I/Os. These I/Os are instance-based. Therefore, they should not cause problems with your OPS application.

See Also: Oracle8i Concepts for more information about indexes.

Case Study: Table Access Analysis

Table 13-5 shows rough figures reflecting normal use of the application in the case study.

Table 13-5 Case Study: Table Access Analysis Worksheet

Table Name	Daily Access Volume
	Read Access		Write Access
	Select		Insert		Update		Delete
	Operations	I/Os	Operations	I/Os	Operations	I/Os	Operations	I/Os
ORDER_HEADER	20,000	60,000	500	7,000	2,000	14,000	1,000	14,000
ORDER_ITEM	60,000	180,000	2,000	10,000	0	0	4,030	20,150
ORGANIZATIONS	40,000	120,000	10	50	100	700	0	0
BUDGET	300	900	1	5	2	14	0	0
FORECASTS	500	1,500	1	5	10	70	2	10
ACCOUNTS_PAYABLE	230	690	50	250	20	140	0	0

The following conclusions can be drawn from this table:

• Only the ORDER_HEADER and ORDER_ITEM tables have significant levels of write access.

• ORGANIZATIONS, by contrast, is predominantly a read-only table. While a certain number of INSERT, UPDATE, and DELETE operations will maintain it, its normal use is SELECT-only.

Analyze Transaction Volume by Users

Begin by analyzing the existing (or expected) access patterns for tables in your database. Then decide how to partition the tables and group them according to access pattern.

• Transaction Volume Analysis Worksheet

• Case Study: Transaction Volume Analysis

Transaction Volume Analysis Worksheet

For each table with a high volume of write access, analyze the transaction volume per day for each type of user.

Note: For read-only tables, you do not need to analyze transaction volume by user type.

Use worksheets like the one in Table 13-6:

Table 13-6 Transaction Volume Analysis Worksheet

Table Name:
Type of User	No.Users	Daily Transaction Volume
		Read Access		Write Access
		Select		Insert		Update		Delete
		Operations	I/Os	Operations	I/Os	Operations	I/Os	Operations	I/Os

Begin by estimating the volume of transactions by each type of user and then calculate the number of I/Os entailed.

Case Study: Transaction Volume Analysis

The following tables show transaction volume analysis of the three tables in the case study that have a high level of write access: ORDER_HEADER, ORDER_ITEMS, and ACCOUNTS_PAYABLE.

ORDER_HEADER Table

Table 13-7 shows rough estimates for values in the ORDER_HEADER table in the case study.

Table 13-7 Case Study: Transaction Volume Analysis: ORDER_HEADER Table

Table Name: ORDER_HEADER
Type of User	No. Users	Daily Transaction Volume
		Read Access		Write Access
		Select		Insert		Update		Delete
		Operations	I/Os	Operations	I/Os	Operations	I/Os	Operations	I/Os
Order entry clerk	25	5,000	15,000	500	7,000	0	0	0	0
Accounts payable clerk	5	0	0	0	0	0	0	0	0
Accounts receivable clerk	5	6,000	18,000	0	0	1,000	7,000	0	0
Shipping clerk	4	4,000	12,000	0	0	1,000	7,000	0	0
Sales manager	2	3,000	9,000	0	0	0	0	0	0
Financial analyst	2	2,000	6,000	0	0	0	0	0	0

The following conclusions can be drawn from this table:

• Order entry clerks perform all inserts on this table.

• Accounts receivable and shipping clerks perform all updates.

• Sales managers and financial analysts only perform select operations.

• Accounts payable clerks never use the table.

Deletes are performed as a maintenance operation, so you do not need to consider them in this analysis. Furthermore, the application developers realize that sales managers normally access data for the current month, whereas financial analysts access mostly historical data.

ORDER_ITEMS Table

Table 13-8 shows rough estimates for values in the ORDER_ITEMS table in the case study.

Table 13-8 Case Study: Transaction Volume Analysis: ORDER_ITEMS Table

Table Name: ORDER_ITEMS
Type of User	No. Users	Daily Transaction Volume
		Read Access		Write Access
		Select		Insert		Update		Delete
		Operations	I/Os	Operations	I/Os	Operations	I/Os	Operations	I/Os
Order entry clerk	25	15,000	45,000	2,000	10,000	0	0	20	100
Accounts payable clerk	5	0	0	0	0	0	0	0	0
Accounts receivable clerk	5	18,000	54,000	0	0	0	0	10	50
Shipping clerk	4	12,000	36,000	0	0	0	0	0	0
Sales manager	2	9,000	27,000	0	0	0	0	0	0
Financial analyst	2	6,000	18,000	0	0	0	0	0	0

The following conclusions can be drawn from this table:

• Order entry clerks perform all inserts on this table.

• Updates are rarely performed

• Accounts receivable clerks, shipping clerks, sales managers and financial analysts perform a heavy volume of select operations on the table.

• Accounts payable clerks never use the table.

The ORDER_HEADER table has more writes than ORDER_ITEMS because the order header tends to require more changes of status, such as address changes, than the list of available products. The ORDER_ITEM table is seldom updated because new items are listed as journal entries.

ACCOUNTS_PAYABLE Table

Table 13-9 shows rough figures for the ACCOUNTS_PAYABLE table in the case study. Although this table does not have a particularly high level of write access, we have analyzed it because it contains the main operation that the accounts payable clerks perform.

Table 13-9 Case Study: Transaction Volume Analysis: ACCOUNTS_PAYABLE Table

Table Name: ACCOUNTS_PAYABLE
Type of User	No. Users	Daily Transaction Volume
		Read Access		Write Access
		Select		Insert		Update		Delete
		Operations	I/Os	Operations	I/Os	Operations	I/Os	Operations	I/Os
Order entry clerk	25	0	0	0	0	0	0	0	0
Accounts payable clerk	5	200	600	50	250	20	140	0	0
Accounts receivable clerk	5	0	0	0	0	0	0	0	0
Shipping clerk	4	0	0	0	0	0	0	0	0
Sales manager	2	0	0	0	0	0	0	0	0
Financial analyst	2	30	90	0	0	0	0	0	0

The following conclusions can be drawn from this table:

• Accounts payable clerks send about 50 purchase orders per day to suppliers. These clerks are the only users who change the data in this table.

• Financial analysts occasionally study the information.

Deletes are performed as a maintenance operation, so you do not need to consider them in this analysis.

Partition Users and Data

Your goal is to partition applications across instances. This can involve separating types of users across instances and partitioning data that needs to be written only by certain types of users. This minimizes the amount of contention on your system. This section covers:

• Case Study: Initial Partitioning Plan

• Case Study: Further Partitioning Plans

Case Study: Initial Partitioning Plan

In the case study, for example, the large number of order entry clerks doing heavy insert activity on the ORDER_HEADER and ORDER_ITEM tables should not be separated across machines. You should concentrate these users on one node along with the two tables they use most. A good starting point, then, would be to set aside one node for the OE clerks, and one node for all other users as illustrated in Figure 13-2.

Figure 13-2 Case Study: Partitioning Users and Data

This system is probably well balanced across nodes. The database intensive reporting done by financial analysts takes a good deal of system resources, whereas the transactions run by the order entry clerks are relatively simple.

The load balancing by manipulating the number of users across the system is typically useful, but not always critical. Load balancing has a lower priority for tuning than reducing contention.

Case Study: Further Partitioning Plans

In the case study it is also clear that accounts payable data is written exclusively by accounts payable clerks. You can thus effectively partition this data the set of users onto a separate instance as shown in Figure 13-3.

Figure 13-3 Case Study: Partitioning Users and Data: Design Option 1

When all users needing write access to a certain part of the data are concentrated on one node, the PCM locks all reside on that node. In this way, lock ownership is not switching back and forth between instances.

Based on this analysis, you primarily have two design options.

Design Option 1

You can set up your as shown above with all order entry clerks on one instance to minimize contention for exclusive PCM locks on the table. This allows sales managers and financial analysts to get up-to-the-minute information. Since they do want data that is predominantly historical, there should not be too much contention for current records.

Design Option 2

Alternatively, you could implement a separate temporary table for ORDER_ITEM/ ORDER_HEADER. This table is only for recording new order information. Overnight, you could incorporate changes into the main table against which all queries are performed. This solution would work well if it is not vitally important that financial analysis have current data. This is probably true only if they are primarily interested in looking at historical data. This would not be appropriate if the financial analysts needed up-to-the-minute data.

Figure 13-4 Case Study: Partitioning Users and Data: Design Option 2

Partition Indexes

You need to consider index partitioning if multiple nodes in your system are inserting into the same index. In this situation, you must ensure that different instances insert into different points within the index.

Note: This problem is avoided in the Eddie Bean case study because application and data usage are partitioned.

See Also: "Creating Free Lists for Indexes" for tips on using free lists, free list groups, and sequence numbers to avoid contention on indexes. For more information about indexes as a point of contention, please see "Locating Lock Contention within Applications" . Also refer to Oracle8i Concepts for tips on how to physically partition a table and an instance to avoid the use of free list groups.

Implement Hashed or Fine Grain Locking

For many applications, the DBA needs to decide whether to use hashed or fine grain locking for particular database files.

You should design for the worst case scenario that would use hashed locking. Then, in the design or monitoring phases, if you discover a situation where you have too many locks, or if you suspect false pings, you should try fine grain locking.

Begin with an analysis at the database level. You can use a worksheet like the one shown in Table 13-10:

Table 13-10 Worksheet: Database Analysis for Hashed or Fine Grain Locking

Block Class	Relevant Parameter(s)	Use Fine Grain or Hashed Locking?

Next, list the files and database objects in a worksheet like the one shown in Table 13-11. Decide which locking mode to use for each file.

Table 13-11 Worksheet: When to Use Hashed or Fine Grain Locking

Filename	Objects Contained	Use Fine Grain or Hashed Locking?

See Also: "Applying Fine Grain and Hashed Locking to Different Files" .

Implement and Tune Your Design

Up to this point, you conducted an analysis using estimated figures. To finalize your design you must now either prototype the application or actually implement it and get it running. By observing the actual system, you can tune it further.

To do this, try the following techniques:

• Identify blocks that are being pinged and determine where contention exists.

• Consider moving users from one instance to another to reduce the amount of pinging and false pinging.

• If you detect a high level of false pinging, consider increasing the granularity of the locks by placing more locks on each file.

• If there is pinging on inserts, adjust the free lists or use multiple sequence number generators so that inserts occur in different parts of the index.

  See Also: Chapter 19, "Tuning to Optimize Performance" in Oracle8i Tuning.

---