NCR Australia Pty Ltd
Chris Tham
NCR Australia Pty Ltd
This paper attempts to outline some issues involved in performance tuning and monitoring applications utilising a relational database system running under UNIX. It gives an overview of performance monitoring and tuning strategies to employ in order to optimise the performance of relational database applications. A section on suitable benchmarks for estimating relational database application performance is also included. Finally, the paper concludes with a discussion of the results and findings obtained by conducting a series of performance tuning case studies. The applications in the case studies are based on the TPC-A and TPC-B Benchmarks. These exercises were conducted on two commercially available relational database systems (Oracle RDBMS and Informix On-line). using various tuning parameters for both the UNIX kernel and the specific database product and under different disk configurations (single disk slice, Volume Manager, different RAID levels etc.) The results are interesting as a relative measure of performance but should not be judged as absolute measures of performance.
24 September 1993.
First of all, define a system as a combination of computer hardware and operating software designed and configured to support the execution of a suite of applications. The applications are run by or on behalf of the users of the system.
A given system is comprised of a number of finite resources (CPU, memory, disk, IPC, STREAMS, etc.). Applications have specific resource requirements. If the system cannot give an application all the resources it requires (because of under-configuration - hardware or software - or because resources are already allocated to other applications), the application may not run or may run slowly. If many applications demand a particular resource and the system cannot fulfil all demands, the resource becomes a bottleneck.
Ideally, a system would be configured so that no resource would ever become a bottleneck no matter what combination of applications are currently being executed. However, in real life this is not often possible, either because of cost considerations or due to other constraints, such as internal operating system limits or because applications have the policy of demanding as much resources as they can acquire.
Performance tuning is the art of providing the right quantity of resources in a system to allow applications to run at the highest possible speed and at the lowest possible price. This can range from increasing the number of resources in a system (by buying additional hardware) to altering the system's configuration so that existing resources are utilised in a more optimal manner.
Traditionally, performance tuning consists of following any or all of the following steps:
The last step, modifying the application, is not usually performed, because many users buy applications 'off the shelf' and the users are not in a position to be able to modify them.
Many of today's business and commercial applications utilise relational database technology to store business information. This adds an additional factor into the performance tuning process, since a relational database product is a 'half-way house' between the application and the underlying operating system.
An application built using relational database technology often communicates to a relational database engine using a client-server model. The application interacts with the user and contains business logic, whereas the relational database engine actually stores and retrieves the data to and from the system. To the application, the relational database engine may appear as an extension of services or resources provided by the operating system. However, to the system, the relational database engine appear as another consumer of resources, sometimes in competition with the applications themselves.
Tuning the performance of relational database applications on a system revolves around a complex triangle consisting of the applications, the relational database engine and the underlying system.
The first step towards performance tuning is monitoring existing or anticipated performance. Besides tuning, performance monitoring may be performed for a number of other reasons:
Performance monitoring can be done at several levels:
When we monitor the entire system, we typically monitor system resources like CPU, memory, disk, I/O controllers etc. When we monitor packages, we also monitor system resources, but we want our figures to be specific to the package, i.e. we are not interested in system resources consumed by other packages or applications running on the system. Lastly, when we monitor specific applications, we are interested in the specific resources consumed by the application as well as, say, the execution profile of the application to determine how efficiently it is utilising system resources.
In any case, we definitely do not want to monitor resources consumed by the monitoring process, unless the monitoring process is considered part of the 'normal' application mix when the system is in 'production'. Tools used for performance monitoring (this includes the performance monitoring package, activity logging and accounting functions, monitoring I/O) may consume significant resources that may skew or otherwise distort the results obtained.
First of all, we need to identify all potential resources in the system. This may include obvious hardware resources such as CPU, memory, etc., but also include operating system configuration parameters such as those for System V IPC, Berkeley sockets, Streams, pseudo-devices, the file system (disk space, inodes, file descriptors), kernel data structure sizes, buffering, and locking. If we are monitoring the relational database engine, we also need to recognise any configuration parameters or limits imposed by the engine itself (such as database block buffering, locking, etc.).
Next, we need to identify ways to measure each resource. This amounts to choosing a performance monitoring tool (either built into the system or package or an external analysis tool). Many resources can be difficult if not impossible to measure.
Lastly, we need to define the environment in which the monitoring is to be conducted. This includes not just the configuration of the system that is to be used but also the application type and mix. Ideally of course, we would monitor the actual production system running the quantity and type of applications for a duration that can be characterised as being 'typical' for the organisation.
When the above scenario is not possible (because the system is not yet in production, the applications have yet to be written, or simply because the monitoring process is considered too disruptive) we would need to assess the impact of any differences to the results that we are likely to achieve. For example, we may be monitoring the application mix on a system that is different from the actual production system, because the production system is not available, or we may not yet have purchased a suitable system and are evaluating between proposed alternatives. We need to consider how the system being monitored relates to the actual production system, and how the results obtained can be adapted or scaled to the production system.
Once we have obtained a set of results of each resource that we are monitoring, we need to check the scalability of each resource when we increase either the amount of the resource that is available or the number of applications that require the specific resource. For example, if one program needs X kB of memory in order to run without paging, two copies of the program running concurrently may only need 1.5 × X kB (due to the shared program text). Also, if CPU capacity is a bottleneck, increasing the number of processors in a multi-processing system may not deliver twice the processing power (particular if there is only one instance of one application running on the system).
Basic monitoring tools available under UNIX (such as truss, ps, sar, netstat, strconf etc.) are usually adequate for superficial monitoring. When monitoring a large system supporting many packages and applications, we would probably need more specialised tools.
The relational database product often provides its own set of monitoring tools that can be used to monitor the database engine plus resources consumed by the engine. In addition, some tools can monitor the connections into the engine from various applications. Examples are tbstat under Informix, ipm under Ingres, and the SQL*DBA monitor command under Oracle. Another approach is to gather performance monitoring information into the data dictionary. In this way, SQL queries can be performed to analyse statistical information automatically collected by the database engine. Some relational database vendors take both approaches, e.g., provide a monitoring tool as well as collect information in the data dictionary.
Another approach is to purchase a performance monitoring product. Many network and systems management products (for example, NCR StarSENTRY) provide analysis and monitoring facilities that can be utilised. An example of a performance monitoring tool specifically designed to monitor system activity is NCR's OSA Performance Monitor, which is an optional component of the Open System Administrator (OSA). The base OSA package is bundled together with NCR's UNIX SVR4 MP-RAS (in the Client/Server Foundation Operating Environment).
The following are brief descriptions of the performance monitoring tools that have been used in the case studies discussed in this paper. The intention is not to present the features and benefits of each product but to give sufficient background to allow the reader to understand how the product was used in the case studies.
OSA Performance Monitor is a utility and user interface that gathers and presents real-time performance statistics and metrics for specific system resources as well as general system resources and activities. Performance Monitor gathers the performance information at the time it is requested and displays the information in a graphical format (using the X11/Motif graphical user interface) such as a line graph or an EKG graph.
Volume Manager Visual Administrator is a graphical systems administration tool (based on the Motif graphical user interface) intended for use with Volume Manager (refer to section 4.2 for a description of Volume Manager).
Volume Manager itself provides two types of performance information: I/O statistics and I/O traces.
The Analyse menu selection within Visual Administrator is used to monitor sub-disk activity and device utilisation. This information originates from the performance statistics recorded by Volume Manager.
A Remote Terminal Emulator (RTE) is a combination of computer hardware and software that work together to emulate one or more users typing on terminals connected to a System Under Test (SUT). From the point of view of the SUT, every RTE user is a "real" user typing at a terminal. The RTE runs on a driver machine which is electrically connected to the SUT. Hence, the RTE software generates no disk, memory or CPU overhead on the SUT.
Most UNIX system RTEs emulate users at asynchronous character terminals connected to the SUT using the RS-232 protocol. There is usually no restriction on the SUT other than it must be able to support RS-232 terminal connections. Enhanced RTEs support users over a network login protocol (e.g. telnet or rlogin) or users using graphical user interfaces (e.g. X servers supporting X clients on the SUT).
An RTE simulates users at their terminals or workstations running 'workloads' on the System Under Test (SUT). RTE users enter data into the SUT at a specified typing rate and think time (time between operations). Most RTEs have a programming language that allows a user session to be defined within the RTE. Creating a workload is done by writing a program in the RTE language (called a 'script') or by using a tool to generate an RTE script from a captured session.
An RTE is an excellent performance monitoring tool as it generates realistic system wide workloads by driving applications as real users would and calculates the response time(s) of the system whilst users are performing normal everyday tasks. This makes the RTE useful for measurement, analysis and tuning of application and system software, for functional, regression, and capacity testing, for service level monitoring, and as an apparatus for creating a sustained work load. Although it is possible to do all of the above without an RTE, doing so requires real users running applications from terminals with some external timing mechanism.
NCR have used the Quartz RTE, originally developed in the early 1980s by AT&T Bell Laboratories and now commercially available from AT&T/NCR, for performance analysis, TPC-A, and TPC-C benchmarking, system sizing and capacity planning, application and system software testing, quality control sampling of live systems, and live customer demonstrations.
A good RTE will have the following features:
An ideal RTE generates no load on the SUT. This is usually achieved by running the RTE on a different system altogether and using serial lines or network logins to simulate users on the SUT. If the SUT is also used to run the RTE software, then pseudo terminals can be used to simulate users.
It should be fairly simple to write an RTE using the public domain expect package. The Tcl language within expect can be used to simulate users typing on a terminal. However, capturing and replaying workloads can be fairly tedious, as an expect script must be written that monitors for output text and then sends in a simulated user response. This is particularly tricky for interactive screen applications, as many software applications often perform bizarre cursor movement optimisations (or, as the case may be, dis-optimisations) that can fool expect. A good RTE often automates the generation of workload scripts from a sample user interaction capture. RTEs also contain code to build a virtual screen image from terminal escape sequences in order to handle weird cursor motion sequences.
Once the performance analysis have been completed using the available performance monitoring tools, we need to identify the resources that appear to be bottlenecks. Performance tuning consists of modifying the system environment to eliminate these bottlenecks or to reduce their impact on the system performance. This can be done in a number of ways, ranging from adding hardware components to the system to altering the system configuration, to altering the application itself.
Aside from increasing a kernel parameter to overcome a resource shortage, tuning the UNIX SVR4 kernel without a source licence is primarily striking an appropriate balance of memory usage between the file system, the process system, and the networking layer. Memory that is allocated for shared memory or programs is not available for file I/O or network buffering and vice versa. If applications do not receive enough physical memory (because, for example, it is used for file buffering), swapping and paging will reach excessive levels and the system can be said to be thrashing.
The BUFHWM and NBUF parameters control the allocation of
buffer headers for file system buffering. SEGMAPSZ can be used to control the
proportion of physical memory used for paging and for file buffering. The System V IPC
parameters (for shared memory, semaphores and messages) are also important as many
relational database products use the IPC mechanisms. The LOTSFREE, DESFREE,
and MINFREE parameters control paging. The STRTHRESH and *_NDEV
parameters control Streams and TCP/IP memory allocation.
This section is intended to be a "no frills" guide to tuning relational database applications because it is not specific to a particular database vendor's RDBMS implementation but is "generic" across most relational databases.
Tuning a relational database application occurs at many different levels. If we are allowed to tune the application itself, tuning can be achieved using the following approaches:
Often, however, tuning the application is not a feasible option and we are limited to optimising the data storage and tuning the relational database engine.
Before we can determine the optimal approach to storing the application data on the data, we need to estimate the demographics of the data. This includes estimating size of tables plus frequency of change, and also typical initial and change values. The following are some hints for optimising data placement:
SELECT * FROM CUSTOMER) will sometimes perform
slower if an index is present and used (need to find a way to turn of use of indexing for
that statement, or even DROPping the index temporarily) This is very specific to the tuning parameters provided by the relational database implementation so it is impossible to generalise the approach in this paper. The case studies in this paper can serve as a guide and indication of the parameters that can be tuned for certain database products as well as the approach taken.
Because relational database applications often impose a high load on the disk sub-system, it is worthwhile considering the different options available for physically storing the data in the database.
As an example, although all relational databases allow the storage of data on the UNIX file-system, some products allow the option to store some or all information directly into a UNIX raw or block device, corresponding to a slice on the physical disk. Doing so often yields a 10-15% improvement in performance as the UNIX file-system overhead is skipped (and many symmetric multi-processing versions of UNIX have annoying bottlenecks in the file-system code when more than one CPU attempts to perform I/O on the same file-system). However, UNIX file buffering is not normally performed on direct device I/O, and the relational database engine has to compensate by using its own block buffering strategies.
The following section discusses two alternatives that can potentially increase I/O throughput for relational database applications. The impact of using these alternatives will be covered in Case Studies B and C later on in this paper.
A study by the University of California at Berkeley coined the term RAID to describe an I/O architecture that can safeguard information and improve performance. RAID stands for Redundant Array of Inexpensive Disks. Current generation, small to medium capacity disks (originally designed for small systems or personal computers) are used to build RAID arrays.
The original RAID approach was to use these small disks to build a large logical disk that is cheaper as well as offering better performance and reliability than a single large and expensive disk (SLED). With the increase in hard disk capacities and drop in hard disk prices, RAID technology is nowadays more commonly utilised to provide higher reliability and availability as well as increased performance.
The Berkeley study originally defined five RAID classes or modes of operation (RAID levels 1 to 5). Subsequently two new levels were defined (RAID 0 and RAID 6). Commercially available RAID arrays (such as the NCR 6292 and 6298 Disk Arrays) usually support more than one RAID level (but each controller only supports one type of RAID level at a given time) using a software configuration utility. For the purposes of this paper, we are interested in using RAID technology to increase performance, not to achieve high availability per se.
Tuning a RAID array involves consideration of the following factors:
Volume Manager is an NCR UNIX SVR4 MP-RAS add-on package (bundled with the Client/Server Foundation) that provides software disk management. It creates a virtual disk sub-system transparent to the user by building virtual (logical) disk volumes on top of UNIX's physical devices. Once the virtual volume is created, a normal UNIX file system, or the raw device used by a database system, accesses the logical disk volume rather than the physical disk partitions. Virtual volumes appear as character and block devices to the UNIX user, hence it is possible to create and mount a file-system on a virtual volume.
The most obvious use of Volume Manager is to bundle together several small physical disks and presenting the storage space as one big logical disk. It is also possible to configure Volume Manager to achieve software based disk mirroring by storing duplicate copies of data on multiple drives (RAID 1) and striping (similar to RAID 0). Mirroring increases data availability and reliability, and allows parallel reads. Striping improves the data transfer rate.
Benchmarking is an art and there are many subtleties and nuances which do not immediately come to mind. The following two sections are quoted almost verbatim from the NCR 3450 and NCR 3550 TPC Benchmark Full Disclosure Reports and were written by R. J. Hanson, NCR Performance and Tools Unit, Naperville, Illinois, USA.
A benchmark is a representation of some real workload or application. This representation is used either because it is smaller and easier to transport from one system to another than an actual application, or because it seems similar to an application which has not been implemented yet. Running the benchmark on various systems then gives the user an idea of how those systems might perform with the actual application, and the user can then make more informed decisions.
A good benchmark must have several properties:
The preceding list is not conclusive; there may be other attributes which various audiences demand from their benchmarks. However, these are the major considerations.
Since the benchmarks can be configured and implemented in different ways, the reader should keep a few things in mind when comparing results. The following points should be helpful when comparing benchmark results between systems:
The Transaction Processing Performance Council was founded in August 1988 to establish standard benchmarks for measuring database computing performance. Most of the members of the TPC are computer hardware and software vendors. NCR has been a member of the Council since the early stages. Prior to the TPC benchmarks, several non-standard database benchmarks existed. The most familiar were the Debit/Credit and TP1 benchmarks. Because there was no official standard, these tests were often modified in various ways to suit individual architectures. The TPC sought to end this confusion and has succeeded by publishing official specifications for two benchmarks known as TPC-A and TPC-B. The work of the Transaction Processing Council has been well received and major vendors including NCR are contributing to this work. The Transaction Processing Council has recently released a new TPC-C benchmark (which has a more realistic description of a typical OLTP environment) and is in the process of drafting the specifications of a TPC-D benchmark (which is currently in draft and is an attempt to measure the performance of decision support systems).
The TPC-A specification, which replaces the former Debit/Credit test, was released in November 1989. TPC-A requires a comprehensive setup which includes an external transaction generator and appropriate networking hardware. The rules for scaling the size of the database in accordance with the number of transactions per second (TPS) to be demonstrated further add to its complexity. Also, the TPC, unlike SPEC, does not provide an actual benchmark tape, but rather a specification as to how the benchmark must be implemented. The vendor or system evaluator is free to implement the benchmark using whatever method, provided that it conforms to the standard specification. Upon completion, an audit by an external consultant is usually required, along with a full disclosure report of how the benchmark was executed.
TPC-A is a remote terminal emulation (RTE) benchmark which performs on-line transaction processing (OLTP) operations. The benchmark is a representation of a banking application containing teller terminals, each of which is called a "teller", "terminal" or "user". The model is a set of teller terminals at a bank and the bank has multiple branches. There is only one type of transaction at this bank - an account update which is either a deposit or a withdrawal.
The transaction involves updating three items in the bank's database:
and creation of
so that bank auditors can review transactions.
TPC-A requires:
The number of branches controls the database size and must be scaled to the number of TPS to be demonstrated according to the rule: one branch per demonstrated TPS.
The RTE submits transactions from each emulated terminal with a mean arrival time of 10 seconds. A random number generator is used to determine the arrival times. More precisely, these arrival times have an exponential distribution (the TPC officially uses the unusual terminology "negative exponential" for this distribution).
This distribution is characterised by having a wide range of values so that some times may be close to 0, some as big as 20 and a few close to 30 but the average over a large sample would be close to 10. This same distribution (with a different, much larger mean, of course) is used to model product reliability with the mean in this case referred to as MTBF. TPC-A delivers two metrics as an end result: the performance in tpsA-local or tpsA-wide, and the price/performance ratio in $/tpsA. The bigger the tpsA number, the better. The smaller the $/tpsA, the better. Wide refers to a wide-area network communication configuration conforming to rules stated in the specification. Local is for other system configurations, typically using direct connect (e.g. local RS-232) or Local Area Network (LAN).
TPC-B is a batch-oriented database stress test which performs multi-threaded SQL submit operations. The benchmark uses the same conventions as TPC-A but without the on-line transaction processing (OLTP) aspects of the benchmark.
As in TPC-A, the size of the database is scaled to the size of the system being tested. That is, the same scaling rules apply: 10 tellers, 100,000 accounts per branch, and one branch for each tpsB to be demonstrated.
A driver program spawns a number of background batch programs which then submit transactions which range over the entire database; there is no teller or "user" assignment per program as in TPC-A.
TPC-B delivers two metrics as the end result: The performance in tpsB-simplex or tpsB-duplex and the price/performance ratio $/tpsB. As before, the bigger the tpsB the better, the smaller the $/tpsB the better.
Simplex refers to a system configuration where the transactions are submitted on the same system(s) they are served and duplex means that the database back-end resides on a different system and network connectivity is used for the interface (i.e. client-server).
TPC-B replaces what was formerly known as TP1. It is similar to TPC-A, but does not require the external networking setup.
TPC-C is also an on-line transaction processing (OLTP) benchmark but addresses more complicated business scenarios and produce a tpmC rating - transactions per minute. TPC-C is different and more complex than TPC-A because of its multiple transaction types, more complex database, and overall execution structure.
As an OLTP system benchmark, TPC-C simulates a complete computing environment where a population of terminal operators execute transactions against a database. The benchmark is centred around the principal activities (transactions) of an Order-Entry environment. These transactions include entering and delivering orders, recording payments, checking the status of orders, and monitoring the level of stock at the warehouses. However, it should be stressed that it is not the intent of TPC-C to specify how to best implement an Order-Entry system. While the benchmark portrays the activity of a wholesale supplier, TPC-C is not limited to the activity of any particular business segment, but, rather, represents any industry that must manage, sell, or distribute a product or service.
The tpmC metric is the number of New-Order transactions executed per minute. Given the required mix and the wide range of complexity and types among the transactions, this metric more closely simulates a complete business activity, not just one or two types of transactions. For this reason, the tpmC metric is considered to be a measure of business throughput.
The RTE is also used to measure the response time of each transaction and to simulate keying times and think times. The keying time represents the time spent entering data at the terminal and the think time represents the time spent, by the operator, to read the result of the transaction at the terminal before requesting another transaction. Each transaction has a minimum keying time and a minimum think time. In addition, the response time of each transaction must be below a required threshold. These thresholds have been defined to give predominance to New-Order as the performance limiting transaction.
In moving toward modelling more realistic environments, TPC-C reduces the number of artificial limitations commonly found in other benchmarks. For example, to promote the use of fully-functional terminals of workstations and screen management software, TPC-C requires all terminal inputs and displays to be usable by real-life operators. To that end, all screens must be formatted using labelled input and output fields, as specified, and must provide all the common screen manipulation features, including moving forward or backward through the input fields and entering numbers in right justified fields. In another area, physical database design techniques that can be used to improve the performance of a real-life application, such as partitioning or replication of data, are allowed in TPC-C. The use of database records by the transactions has been carefully defined to preclude test sponsors from gaining unrealistic advantages from any of these techniques.
These benchmarks are less relevant for relational database applications but a short description of the benchmarks have been included here for completeness.
This is a set of 18 tests developed by Neal Nelson and Associates in Chicago, and has been used for a number of years as an "industry standard" multi-user benchmark. The benchmark was originally developed in 1986, but a major revision to the benchmark in 1992 has 30 tests expanding and improving on the original 18.
This was developed by NCR and uses simulated workloads to provide information on the performance capability of the CPU, Disk and terminal sub-systems. It also provides information on the overall system capability for various mixes of disk and terminal I/O.
AIM Technology provides a full range of benchmarking suites for measuring multi-user, workstation, and sub-system performance. The AIM Performance Report (APR) are based on the Multi-user AIM Suite III Benchmark and the single threaded Suite II Benchmark. The later version called APR II provides four overall measures of various system characteristics:
SPEC is an acronym for the Standard Performance Evaluation Corporation. SPEC is an organisation aimed at establishing benchmarking standards for the evaluation of high-performance computing systems.
The SPEC Benchmark Suite Release 1 is the most well-known SPEC suite and is targeted at the technical workstation market and features ten CPU intensive tests drawn from real world applications including CASE, EDA, MCAE, and other scientific and engineering areas. Six of these ten benchmarks are floating-point programs written in FORTRAN, and all ten of the benchmarks are single tasking programs performing little disk I/O, hence have limited applicability to multi-processor systems. All the SPEC tests have a common measurement unit; namely elapsed time. A reference unit is the elapsed time of the VAX 11/780. Three measures (the SPECint, SPECfp and SPECmark) are the geometric means of the relative scores of the tests with respect to the reference units.
SPEC Release 1.2b is an update to SPEC Release 1.0 that is more suited for multi-processing systems. The SPECthruput figure is the key metric of this benchmark, and is generated by running two copies of each Release 1.0 benchmark on each CPU within a given machine, and then calculating a relative average figure.
The SPEC SDM Release 1.0 (System Development Multi-tasking) consists of two benchmarks. 057.sdet is a version of AT&T's Gaede benchmark, and 061.kenbus1 is a derivative of the MUSBUS benchmark. Both benchmarks represents a UNIX/C software development and research environment, and are multi-tasking tests which perform UNIX commands involving significant amounts of disk I/O. The performance metric is "throughput", defined as the total amount of work done in a given time. Each copy of the benchmark workload is known as a "script" and the total workload on the system is gradually increased by increasing the number of concurrent scripts. The throughput of the system is measured by a plot of scripts/hour versus the number of concurrently executing scripts. The peak throughput achieved is also reported.
This is a synthetic benchmark used to characterise scalar computational performance. It simulates high level language patterns and provides a measure of CPU and floating point efficiency. The unit of measurement is "Whetstones per second" and higher results indicate better performance.
This is another synthetic benchmark based on statistical data about the actual usage of programming features in a programming language. It provides information on compiler efficiency and the integer execution capability. The name of the benchmark is a pun on the Whetstone benchmark and the unit of measurement, "Dhrystones per second" is interpreted similarly to the Whetstone per second.
This case study was an attempt to understand the specific system requirements for Oracle SQL*Forms applications running on the NCR System 3000 platform with the Oracle RDBMS Engine. The objective was to allow NCR to be able to configure, propose, benchmark, and tune systems for customers who run applications based on the Oracle RDBMS. The case study was done several years ago.
The NCR 3445 is a floor-standing, single box system containing an Intel i486 processor running at 33 MHz. It has a proprietary local bus interface to memory and SCSI controllers, but has Micro-Channel Architecture (MCA) slots for other I/O adaptors. In many ways, it is a precursor to today's 'Power User' PCs and will deliver a similar level of performance. The case study was conducted using Oracle 6.0.31.0.2 and an earlier release of NCR UNIX SVR4 (Multi-user Operating Environment Release 1.00.00. The current release is 2.02).
The database used in the case study is very similar to the TPC-A and TPC-B benchmark descriptions and is based on a scenario of a bank with a number of branches, each with a number of tellers. The bank has a large number of customers, each with one account. All updates to the database are logged in a history file.
The database size is as follows:
| Table | No. of rows | Size (MB) |
| ACCOUNT | 1,000,000 | 100.000 |
| TELLER | 100 | 0.010 |
| BRANCH | 10 | 0.001 |
| HISTORY | 4000 | 0.200 |
The application was written in SQL*Forms and processes two different types of transactions on the database: a query on existing data, and an update to existing data. The workload on the system consists of 80% queries and 20% updates. A query joins over the ACCOUNT and BRANCH tables given an account number. An update writes to all four tables given the account, branch, teller numbers, and an amount.
An RTE was used to measure response times for each transaction. A transaction begins when the last input character was sent and ends when the last screen update is received. Varying numbers of users were simulated over RS-232 lines in several RTE runs, ranging from 5 to 32. Each user is sending one transaction every 5 seconds. Sometimes, a particular RTE run was repeated several times in order to determine the variability of the response times. sar, the UNIX System Activity Reporter, was used to monitor each RTE run. Oracle statistics were also extracted for analysis.
Each RTE run lasted approximately 27 minutes, consisting of
Each session involved 6 RTE runs, with the following number of users simulated (and hence the system load in terms of number of transactions per second):
| RTE Run | # of Users | Trans/Sec |
| 1 | 5 | 1 |
| 2 | 10 | 2 |
| 3 | 15 | 3 |
| 4 | 20 | 4 |
| 5 | 25 | 5 |
| 6 | 32 | 6.4 |
The system was configured with 16 MB of memory and one 670 MB disk, set up with a root file-system, a 100 MB database raw slice and two 10 MB raw slices for the Oracle redo log files. The Oracle database was set up with a block size of 4096 and two rollback segments. All tables and indexes were created to fit into only one extent to ensure that data are stored on contiguous blocks as well as eliminating recursive calls created by dynamic allocation. 500 data block buffers were allocated in the SGA (equivalent to approximately 2 MB of shared memory).
Beyond 20 users, disk activity started increasing exponentially and it was not possible to complete the 32 user run since the system consistently had a response time g