Hp COMPAQ PROLIANT 2000, COMPAQ PROLIANT 5000 Tuning Enterprise Workflow Applications
Publication Date
June 1998
Compaq
Database Technology
and Solutions Center
CONTENTS
NOTICE............................... 2
INTRODUCTION................... 3
WORKFLOW APPLICATION
ENVIRONMENT.................... 4
VIEWSTAR APPLICATION
ARCHITECTURE ..................6
Workflow Services ................7
Data Services.......................7
File Services ........................ 7
Storage Devices................... 7
Enterprise Server..................7
Oracle Configuration............. 7
TEST APPROACH.................8
TEST RESULTS ................. 11
Phase 1 Tests .................... 11
Phase 1 Test Result............ 11
Analysis of Phase 1 Test
Results.............................. 14
Phase 2 Tests .................... 15
Phase 2 Test Result............ 15
Analysis of Phase 2 Test
Results.............................. 18
RECOMMENDATIONS......... 19
Workflow Application Tuning 19
SQL Select and Commit
Statements ........................ 19
User Connect and
Disconnect......................... 19
Queuing Process ................ 20
Use Of Stored Procedures ... 20
CONCLUSIONS .................. 21
WHITE PAPER
Database Application Performance and Characterization Series
Tuning Enterprise Workflow Applications
on ProLiant Servers
Customer Application Case Study with Oracle/ViewStar on
Microsoft Windows NT
Compaq’s Database Technology and Solutions Center (DTSC) has commissioned the
Application Performance and Characterization White Paper Series to investigate
techniques and practices designed to improve the performance of enterprise applications
running on Compaq ProLiant Servers with Oracle RDBMS on Microsoft Windows NT.
This case study jointly performed by Compaq and Oracle explores the performance
characteristics of a real world application on Compaq ProLiant servers. The testing was
performed at the Compaq/Oracle International Competency Center (ICC), located within
the Compaq DTSC in San Bruno, California. The Compaq/Oracle ICC was established to
address Oracle database related issues such as solution development and validation,
advanced technology, customer case studies and proof of concept projects.
The study involved evaluating and optimizing an enterprise class application based on
Mosaix’s1 ViewStar workflow application framework. This White Paper details the
techniques and practices used to improve the performance of the customer’s workflow
application running Oracle’s Enterprise Server on Windows NT. The results of the tests
performed indicate that:
• Identifying and correcting poorly tuned transactions significantly improved application
responsiveness.
• Tuning the ViewStar Application components within the production environment reduced the
application transaction throughput time.
• Modifying the RAID configuration of the server’s disk sub-system improved the I/O
throughput and overall application performance.
• Partitioning the data and index files on the physical disks, improved the overall database
performance
ECG096/0698
1 Formerly ViewStar Corporation
1
WHITE PAPER (cont.)
NOTICE
The information in this publication is subject to change without notice.
COMPAQ COMPUTER CORPORATION SHALL NOT BE LIABLE FOR
TECHNICAL OR EDITORIAL ERRORS OR OMISSIONS CONTAINED
HEREIN, NOR FOR INCIDENTAL OR CONSEQUENTIAL DAMAGES
RESULTING FROM THE FURNISHING, PERFORMANCE, OR USE OF
THIS MATERIAL.
This publication does not constitute an endorsement of the product or products that were tested.
The configuration or configurations tested or described may or may not be the only available
solution. This test is not a determination of product quality or correctness, nor does it ensure
compliance with any federal, state or local requirements. Compaq does not warrant products other
than its own strictly as stated in Compaq product warranties.
Product names mentioned herein may be trademarks and/or registered trademarks of their
respective companies.
Compaq, Compaq Insight Manager, ProLiant, SmartStart, NetFlex, registered United States Patent
and Trademark Office.
Netelligent is a trademark and/or service mark of Compaq Computer Corporation.
Other product names mentioned herein may be trademarks and/or registered trademarks of their
respective companies.
©1998 Compaq Computer Corporation
Printed in the U.S.A.
Microsoft, Windows, Windows NT, Windows NT Advanced Server, SQL Server for Windows NT
are trademarks and/or registered trademarks of Microsoft Corporation.
ORACLE CORPORATION
Oracle Consulting Services
Compaq Products Division
-------
COMPAQ COMPUTER CORPORATION
Enterprise Solutions Division
Database Technology and Solutions Center
Compaq/Oracle Enterprise Solutions Lab
All rights reserved. Printed in the USA
ECG096/0698
2
WHITE PAPER (cont.)
INTRODUCTION
The workflow application was a project developed and managed by the customer’s internal IT
Division. The customer’s workflow application consists of components from a number of system
and software vendors:
• Oracle database software
• Compaq ProLiant servers
• ViewStar workflow application software
• Microsoft Windows NT operating system
• Microsoft Visual Basic software
In order to analyze the performance characteristics of the customer’s application, the application
software and sample database files were installed at the Compaq/Oracle International Competency
Center (ICC). Test results of the customer’s production system revealed that the application
response time was much higher than expected. This prompted further study of the customer’s
environment to identify possible performance bottlenecks.
To establish a performance baseline for the customer’s application, the test environment was
configured exactly the same as the customer’s production system. To accurately simulate the
customer’s production environment, a log routine was run on one of the process agents at the
customer site. The routine was run for a period of 24 hours and a copy of all the SQL transactions
processed by that agent was recorded and saved to an ASCII file. The SQL transaction log file was
used as a sample of workflow application.
After the baseline testing was completed, a series of system upgrades, application transaction
changes and database modifications were made to determine the impact on the application’s
performance and throughput.
The tests began with 30 users and increased to over 150 end users over time. The underlying
hardware was then upgraded to include a second P166 MHz processor with an additional 256MB
of memory to accommodate the additional users.
ECG096/0698
3
WHITE PAPER (cont.)
ViewStar Application
Oracle Database
Scanning, Optical and Fax
WORKFLOW APPLICATION ENVIRONMENT
Figure 1 illustrates the workflow application hardware environment. The ViewStar Application
server and Oracle database server were configured to simulate the customer’s production
environment. The systems used for Scanning and Fax were not configured and only one client and
ViewStar process machine were used during the testing.
Figure 1 – Workflow Application Environment
ECG096/0698
4
WHITE PAPER (cont.)
The systems used during the testing consisted of the following hardware components. Test
environment consisted of a ProLiant 2500 which served as the Client system, a ProLiant 4500
which served as the ViewStar Application Server and Backup Domain Controller, ProLiant 5000
which served as the Oracle database server, and ProLiant 2000 that served as the database
performance monitor and Primary Domain Controller.
SYSTEM CPU SYSTEM
MEMORY
ProLiant 2500 1 200MHz
Pentium Pro
Processor
Table 1 – Client System Configuration
1GB System Memory 10 base-T
SYSTEM CPU SYSTEM
MEMORY
ProLiant 4500 1 100MHz
Pentium
Processor
Table 2 – Backup Domain Controller Server Configuration
256MB System
Memory
SYSTEM CPU SYSTEM
MEMORY
ProLiant 5000 2 200MHz
Pentium Pro
Processor
Table 3 – Oracle Database Server Configuration
512GB System
Memory
1 Compaq Smart 2/P
Array Controllers
NETWORK SOFTWARE
Windows NT
Network Card
Server 4.0 w/SP3
NETWORK SOFTWARE
10 base-T
Network Card
Windows NT
Server 4.0 w/SP3
NETWORK SOFTWARE
10 base-T
Network Card
Windows NT
Server 4.0 w/SP3
SYSTEM CPU SYSTEM
NETWORK SOFTWARE
MEMORY
ProLiant 2000 1 100MHz
Pentium
Processor
Table 4 – Server Configuration for Oracle Performance Monitor
ECG096/0698
128MB System
Memory
10 base-T
Network Card
Windows NT
Server 4.0 w/SP3
5
WHITE PAPER (cont.)
VIEWSTAR APPLICATION ARCHITECTURE
The ViewStar workflow software combines business rules and procedures with document
information to manage the flow of work throughout the enterprise. The ViewStar system is an
open, network solution that automates and manages business processes.
Workflow is the automated flow of work, documents and related information through the business
process. Workflow comprises aggregating and consolidating information from diverse sources and
routing it to the people who need it to accomplish their work.
ViewStar’s development environment can be used to define the work content, business rules,
workflow maps, and user roles and activities. A library of predefined business functions and
reusable tasks can be employed to create workflow maps that represent the business process.
The customer implemented the ViewStar workflow application to manage the documents
generated as part of the customer’s forms processing system. All forms submitted by applicants are
scanned into binary files and stored into a virtual folder. Information on each applicant is assigned
a virtual folder, which contains pertinent data submitted by the applicant. All letters, mortgage
forms, and insurance forms to be processed are scanned into binary formats and stored within the
workflow repository. All other information required to be linked with individual binary files to
each other and customers are stored and updated within the Oracle (7.3.3) database. Alteration to
any of the documents are also archived and stored within the database. Figure 2 illustrates a
typical Forms Processing Workflow Architecture.
The workflow application architecture as illustrated in Figure 2, is designed to support a number of
systems working cooperatively to serve the needs of the enterprise. This application provides a set
of services, and delivers comprehensive functionality and benefits for systems deployment.
Figure 2 – Typical Workflow Application Architecture
ECG096/0698
6
WHITE PAPER (cont.)
Workflow Services
Workflow services provide configurable and programmable automated task processing. It executes
either actively generated tasks during the workflow, such as printing, or pre-scheduled tasks to be
run periodically at pre-determined times or at specific events.
Data Services
Information associated with work documents such as location is managed by ViewStar’s data
services. The information can be contained in the workflow system or in the document repository.
File Services
The file services provide online storage and object data. All objects or unstructured data associated
with documents, folders and work packets involved in workflow applications are stored and
managed by ViewStar’s file services.
Storage Devices
The storage devices provide archived document storage on optical devices. These services control
the writing of files to optical jukeboxes and platters for long-term archival storage.
Enterprise Server
The enterprise server provides enterprise-wide workflow, including document and data
distribution. Wide-area, multi-site workflows are linked by Microsoft MAPI messaging and
replication services using ViewStar’s enterprise server. The enterprise server moves centralized
resources to the database and enables the distribution of work geographically for local processing
with local data.
Only one ViewStar process agent was configured in our test, this was done to simplify the test
environment. In addition, the process of collecting the data generated from the test and performing
data analysis was simplified.
A log routine was initiated against one of the process agents set up at the customer site. The
contents of all the activities carried out by the agent were spooled to an ASCII log file. The routine
was run for a period of 24 hours and a copy of all the SQL transactions processed by that agent
was recorded and saved to the file. This SQL transaction log file was used as a sample of the
customer’s application workload.
Oracle Configuration
A ProLiant 5000 running Microsoft Windows NT and Oracle RDBMS v7.3.3 was used as the
database server. Oracle Enterprise Manager Performance Pack was also installed on the main PDC
(Windows NT Primary Domain Controller) machine. The database monitoring tools in the
Performance Pack were used to monitor the Oracle database during system and the application
load.
ECG096/0698
7
Loading...