Oracle7 Parallel Server Concepts and Administrator's Guide Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents		 Go to Index
Index


Go to previous file in sequence Go to next file in sequence

Designing a Database for Parallel Server

This chapter prescribes a general methodology for designing systems optimized for the Oracle Parallel Server.

Overview

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

Attention: Always bear in mind that your goal is to minimize synchronization: this will result in optimized performance.

The chapter assumes that you have made at least a first cut of your database design. To optimize your design for a parallel server, follow the methodology suggested here.

To Optimize Database Design for a Parallel Server

Case Study: From First-Cut Database Design to OPS

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

"Eddie Bean" Catalog Sales

The case study concerns the Eddie Bean catalog sales company, which has many order entry clerks who take telephone orders for various products. Shipping clerks fulfill orders, accounts receivable clerks handle billing. Accounts payable clerks handle orders for supplies and services which the company requires internally. Sales managers and financial analysts run reports on the data.

This company's financial application has three areas which operate on a single database:

Tables

Tables from the Eddie Bean database include:

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
Table 14 - 1. "Eddie Bean" Sample Tables

Users

Various types of application users access the database in order to perform different functions. Users include:

Application Profile

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

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

There are about 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, and 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 batch job at night: 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 the tables in your database. You will then decide how to partition the tables, and group them according to access pattern.

Table Access Analysis Worksheet

List all your high-activity database tables in a worksheet like this:

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
Table 14 - 2. Table Access Analysis Worksheet

To fill out this worksheet, you estimate the volume of operations of each type, and then calculate the number of reads and writes (I/Os) the operations will entail.

Estimating Volume of Operations

For each type of operation that will be performed on a table, enter a figure that reflects the normal volume you would expect in the course of a day.

Attention: The emphasis throughout this analysis is on relative values--gross figures that describe the normal use of an application. Even if an application does not yet exist, you can nonetheless project 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).

Note that 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.

Attention: 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 14 - 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 14 - 1. Number of I/Os per SELECT or INSERT Operation

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

Note: If all 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:

One UPDATE in this example entails seven I/Os:

Note: An INSERT or DELETE affects all indexes, but an UPDATE sometimes may affect only one index. Check to see how many index keys are changed.

I/Os per Operation for Sample Tables

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

The following table 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.

Operation SELECT INSERT UPDATE DELETE
Type of Access read read/write read/write read/write
Number of I/Os 3 14 7 14
Table 14 - 3. Number of I/Os per Operation: Sample ORDER_HEADER Table

Attention: Remember that you must adjust these figures depending upon the actual number of indexes and access path for each table in your database.

The following table shows how many I/Os are generated per operation for each of the other tables in the case study, assuming that each of them has a primary key index only.

Operation SELECT INSERT UPDATE DELETE
Type of Access read read/write read/write read/write
Number of I/Os 3 5 7 5
Table 14 - 4. Number of I/Os per Operation: Other Sample Tables

Note: For purposes of this analysis you can disregard the fact that any changes made to the data will also generate rollback segments, entailing additional I/Os. These I/Os are instance-based, and so should not cause problems with your parallel server application.

See Also: Oracle7 Server Concepts for more information about indexes.

Case Study: Table Access Analysis

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

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
Table 14 - 5. Case Study: Table Access Analysis Worksheet

The following conclusions can be drawn from this table:

Analyze Transaction Volume by Users

Transaction Volume AnalysisWorksheet

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

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

Use worksheets like this:

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
Table 14 - 6. Transaction Volume Analysis Worksheet

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 which have a high level of write access: ORDER_HEADER, ORDER_ITEMS, and ACCOUNTS_PAYABLE.

ORDER_HEADER Table

Table 14 - 7 shows rough figures for the ORDER_HEADER table in the case study.

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
OE clerk 25 5,000 15,000 500 7,000 0 0 0 0
AP clerk 5 0 0 0 0 0 0 0 0
AR 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
Table 14 - 7. Case Study: Transaction Volume Analysis: ORDER_HEADER Table

The following conclusions can be drawn from this table:

Deletes are performed as a maintenance operation, so they need not be considered in this analysis.

Furthermore, the application developers realize that sales managers normally access data for the current month, whereas financial analysts access historical data.

ORDER_ITEMS Table

Table 14 - 8 shows rough figures for the ORDER_ITEMS table in the case study.

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
OE clerk 25 15,000 45,000 2,000 10,000 0 0 20 100
AP clerk 5 0 0 0 0 0 0 0 0
AR 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
Table 14 - 8. Case Study: Transaction Volume Analysis: ORDER_ITEMS Table

The following conclusions can be drawn from this table:

Note that 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, instead.

ACCOUNTS_PAYABLE Table

Table 14 - 9 shows rough figures for the ACCOUNTS_PAYABLE table in the case study.

Note: 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 AP clerks perform.

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
OE clerk 25 0 0 0 0 0 0 0 0
AP clerk 5 200 600 50 250 20 140 0 0
AR 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
Table 14 - 9. Case Study: Transaction Volume Analysis: ACCOUNTS_PAYABLE Table

The following conclusions can be drawn from this table:

Deletes are performed as a maintenance operation, so they need not be considered in this analysis.

Partition Users and Data

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

Case Study: Initial Partitioning Plan

In the case study, for example, the large number of Order Entry clerks who do heavy insert activity on the ORDER_HEADER and ORDER_ITEM tables should not be split across machines. They should be concentrated on one node along with the two tables they use so intensively. A good starting point, then, would be to set aside one node for the OE clerks, and one node for all the other users, as illustrated in Figure 14 - 2.

Figure 14 - 2. Case Study: Partitioning Users and Data

This system would probably be 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 OE clerks are relatively lightweight.

This kind of load balancing of 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 the Accounts Payable data is written exclusively by AP clerks. This data and set of users can also be very effectively partitioned onto a separate instance, as shown in Figure 14 - 3.

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

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

Two design options suggest themselves, based on this analysis.

Design Option 1

You can set up the system just as shown above, with all of the order entry clerks together on one instance so as to minimize contention for exclusive PCM locks on the table. In this way sales managers and financial analysts could get up to the minute information. Since they do want data that is predominantly historical, there should still not be too much contention for current records.

Design Option 2

Alternatively, you could implement a separate ORDER_ITEM/ ORDER_HEADER temporary table purely for the taking of new orders. 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 analysts have the current day's data--if they are primarily interested in looking at historical data. (This would not be appropriate if they needed up-to-the minute data.)

Figure 14 - 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 make sure that the different instances insert into different points within the index.

(The 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.

"Pinpointing Lock Contention Within an Application" [*] regarding indexes as a point of contention.

Implement Hashed or Fine Grain Locking

If your DLM supports fine grain locking, then you can decide whether to use hashed or fine grain locking for particular database files.

On very large tables the locking mode you use will have a strong impact on performance. If one node in exclusive mode gives 100% performance with hashed locking, one node in shared mode might give 70% of that performance with fine grain locking. The second node in shared mode would also give 70% performance. With hashed locking, the more nodes are added, the more the performance degrades. Fine grain locking is thus a more scalable solution.

You should design for worst case (hashed locking). Then, in the design or monitoring phase if you come to a situation where you have too many locks, or if you suspect false pings, you should try fine grain locking.

Begin with an analysis on the database level; you can use a worksheet like the following:

Block Class Relevant Parameter(s) Use Fine Grain or Hashed Locking?
Table 14 - 10. Worksheet: Database Analysis for Hashed or Fine Grain Locking

Next, list files and database objects in a worksheet like the following, in preparation for setting GC_DB_BLOCKS. Decide which locking mode to use for each file.

Filename Objects Contained Use Fine Grain or Hashed Locking?
Table 14 - 11. Worksheet: When to Use Hashed or Fine Grain Locking

See Also: "Applying Fine Grain and Hashed Locking on Different Files" [*].

Table 9 - 4, "Initialization Parameters Which Make Block Classes Releasable," [*].

Implement and Tune Your Design

Thus far you have conducted an analysis using gross figures. To finalize your design you must now either prototype the application or implement it in practice--and get it running. By observing the system in action you can tune it further. Try the following techniques:

See Also: "Tuning the System to Optimize Performance" [*].


Go to previous file in sequence Go to next file in sequence
Prev Next		 Oracle
Copyright © 1996 Oracle Corporation.
All Rights Reserved.		 Go to Product Documentation Library
Library		 Go to books for this product
Product		 Go to Contents for this book
Contents		 Go to Index
Index