C H A P T E R 8 - Tuning RDBMS Applications

C H A P T E R 8

Tuning RDBMS Applications

This chapter describes how to size relational database management systems (RDBMS) applications. It contains the following topics:

Tuning an Oracle RDBMS

Tuning a Sybase RDBMS

An RDBMS uses memory much as Sun MTP does. Because most application activity involves specific execution, this memory is on the client side, which is a Sun MTP transaction server. The server side of the database requires considerably less memory.

The data segment of the transaction server contains the memory for SQL cursors and host variable items. This memory can grow depending on the number of SQL statements executed.

Tuning an Oracle RDBMS

Oracle applications allocate the SQL cursor area for each application program and keep the file name used in the allocation as a signature in working storage. This enables the application to find which cursor is open and closed for a given program. Because Sun MTP initializes working storage, it can grow significantly. To avoid this situation, use the -d oracle option to kixclt when compiling online programs that contain EXEC CICS and EXEC SQL statements. Refer to the Sun Mainframe Transaction Processing Software Reference Guide for more information about kixclt.

Oracle applications perform some SQL cursor caching for each transaction server to avoid multiple parsing. This boosts performance, but it also increases the memory of each transaction server significantly. Typically, users set a higher threshold memory to avoid frequent cycling of the transaction servers. A better solution is to use the Oracle SQL*Net V 2 product and Oracle's multithreaded server option, which enables you to reduce the threshold and core values. The multithreaded server option enables the SQL cursor memory to be moved from the client side to the server side, pooling all the SQL cursors at one central point. Although this solution requires additional server memory, significantly less memory is required on the clients.

Refer to the Oracle documentation for more information.

Tuning a Sybase RDBMS

The Sybase software has optimal logic for using SQL cursors from an application. It also uses stored procedures similar to the DB2 BIND process. However, the Sybase software does not support ANSI-style handling of COMMITS and ROLLBACKs with the stored procedures; this might cause problems with your application. You might need to use explicit BEGIN and END transactions to make it ANSI compatible.

Also, Sybase applications use page locks instead of record locks, which might affect the performance of online transactions.

The Sybase software also uses the TCP/IP protocol to communicate with clients, which could cause heavy traffic on the network. Refer to the Sybase documentation for information about setting the network parameters.