Sizing The Oracle Context Cache

The ORACLE context cache must be correctly sized to get the best performance from your TDS application. The size is specified by the CSIZE parameter which was introduced in Section 4.

Optimizing the value of CSIZE for a given TDS application involves counting the number of distinct database-id/database-name/user-id combinations which are used in all TPRs of the TDS application. This number is named DBUSRID.

The optimal value of CSIZE is equal to:

CSIZE = DBUSRID * Maximum number of simultaneous active commitment units

Of course, calculating the number of simultaneous active commitment units is not very easy. Thus, to set the CSIZE, we defined several algorithms according to the type of transaction (simple or complex). Various cases are dealt with in this section.

EXAMPLE 1:

If your application contains the following TPRs:

• TPR1 connecting to database ORAT with user-id SCOTT and database-id DB1

• TPR2 connecting to database ORAT with user-id SCOTT and database id DB1

• TPR3 connecting to database ORAT with user-id SYSTEM and database id DB1 then the number of distinct database-id/database-name/user-id combinations is 2. The combinations are:

DB1/ORAT/SCOTT DB1/ORAT/SYSTEM q

EXAMPLE 2:

If your application contains the following TPRs:

• TPRA connecting to database ORAT with user-id SCOTT and database id DB1.

• TPRB connecting to database TDSO with user-id SCOTT and database id DB1.

• TPRC connecting to database TDSO with user-id SYSTEM and database id DB1.

• TPRD connecting to database TDSO with user-id SYSTEM and database id DB1.

• TPRE connecting to database TDSO with user-id SYSTEM and database id DB2.

In this case, the number of distinct database-id/database-name/user-id combinations is 4.

The combinations are: DB1/ORAT/SCOTT DB1/TDSO/SCOTT DB1/TDSO/SYSTEM DB2/TDSO/SYSTEM q

5.1.1 Sizing Simple Transactions

Here, we define a simple transaction as one where each conversation with a user's terminal is preceded by a commitment. This is a very common occurrence. It prevents a user from holding a resource for longer than necessary.

To calculate an optimal value for CSIZE in this case, we need only DBUSRID and the simultaneity level of the TDS application (SIMU).

Use the following algorithm:

Algorithm:

CSIZE = DBUSRID * SIMU

EXAMPLE: Simple Transaction

A TDS application contains three different transactions T1, T2, and T3. These all connect to ORACLE and commit ORACLE data within a single TPR without any terminal conversation.

They all connect to ORACLE using a specific user-id; the simultaneity level of the TDS application is 5. So in this case we have:

DBUSRID = 3 SIMU = 5 Therefore:

CSIZE = 3 * 5 (which makes 15)

Notice that in this case the number of transactions has no impact on CSIZE. q

5.1.2 Sizing Complex Transactions

A complex transaction can be defined simply as one where a commit is not executed before each terminal conversation.

Since a commit has not been executed, the ORACLE context remains busy during the whole terminal conversation. This may take a rather long time, and this context cannot be re-used by another user.

Definition:

We define the think-time of terminal users as THINK.

Think-time is the total time elapsed in seconds between the time the CONNECT

statement is executed and the time the next COMMIT statement is executed for the same database.

Definition:

We define the time interval which elapses between two executions of the same transaction by the same user as INTER.

Definition:

We define the number of terminal users as USERS.

Algorithm:

To calculate an optimal value for CSIZE in this case, we need to know the average think-time for each user. Use the following algorithm:

CSIZE = (DBUSRID * USERS * THINK) / (INTER + THINK)

EXAMPLE: Complex Transaction

A TDS application contains a single TDS transaction T1. T1 comprises two screens containing fields which are filled by the terminal user.

The following takes place: the user enters the TDS transaction name T1. This triggers the execution of the first TPR TPR1 which connects to the ORACLE database, makes some updates, and sends the first screen to the user's terminal. The user thinks for 30 seconds, fills in the form and presses ENTER.

This triggers the execution of a second TPR TPR2 which checks against data in the ORACLE database and sends back a second screen to the user's terminal. The user verifies the data, fills a validation field which takes 15 seconds, and presses

ENTER again.

This in turn triggers the execution of a third TPR TPR3 which makes final updates to the ORACLE database and commits.

The terminal user might then talk to a customer for 5 minutes before entering the transaction name T1 again for the next operation.

The number of terminals connected to the application is 100. Depending on the data entered on the first screen, the TPR that connects to ORACLE may use 5 different ORACLE user-id's.

The example gives us the following information: THINK = 45 seconds (30 + 15)

INTER = 300 seconds (5 minutes) USERS = 100

DBUSRID = 5 Therefore:

CSIZE = (5 * 100 * 45) / (300 + 45) (making 66) q

5.1.3 Sizing Combined Simple and Complex Transactions

For a TDS application containing both simple and complex transactions, calculate the optimal value of CSIZE separately for each type of transaction. Effectively, this means for each type of commitment unit, since a TDS transaction may contain several commitment units.

First compute the optimal value for commitment units where no terminal conversation takes place between the connection to ORACLE and the next commitment. Use the method described above in Sizing Simple Transactions. Then, compute the optimal value for commitment units where a terminal conversation does take place between the connection to ORACLE and the next commitment. Use the method described above in Sizing Complex Transactions. Add the two results.

The values of DBUSRID used in the two methods may be different because distinct commitment units can connect to ORACLE under distinct user-id's.

Algorithm:

The final formula is:

CSIZE = (DBUSRID1*SIMU) + (DBUSRID2*USERS*THINK)/(INTER+THINK)

The methods described here should provide a good estimate of the optimal CSIZE value for a given situation. As with all optimization techniques, however, you may have to experiment a little to tune a particular application finely.

Look at the "Nb CTX Ent Reassign" statistic in the JOR of your TDS application. Try to keep its value as low as possible to get the best performance. Ideally, its value should be equal to 0.