PL/SQL First Connection Attributes

Use these attributes to determine the virtual address and size of the shared memory segment required by PL/SQL and specify the maximum number of cursors that can be open in a session at one time.

The PL/SQL first connection attributes are described in detail next.

PLSQL_MEMORY_ADDRESS

Use of PL/SQL requires a shared memory segment. This attribute determines the virtual address at which this shared memory segment is loaded into each process that uses the TimesTen direct drivers.

This shared memory contains recently-executed PL/SQL code, shared package state, and metadata associated with the operation of PL/SQL. This shared memory segment is separate from the one containing the TimesTen database.

The memory address at which this shared memory segment is loaded must be identical in each process using TimesTen. You must specify the value as a hexadecimal address.

If you do not specify a value for PLSQL_MEMORY_ADDRESS, TimesTen uses a platform-dependent default value.

The default values for each platform are designed to:

1. Maximize the amount of virtual space for your TimesTen database and for your applications.

2. Minimize the fragmentation of the virtual address space.

3. Avoid conflicts with other uses of virtual address space.

The platform specific default memory addresses are:

Operating system	Address
Linux x86-64	0000005000000000
AIX	06ffffff00000000
Windows	000000005b8c0000
HP-UX	0

Some things to consider when setting this attribute are:

• If applications simultaneously connect to multiple TimesTen databases in direct mode, then each database must use a different value for PLSQL_MEMORY_ADDRESS.

• The value of this attribute is stored persistently by TimesTen. The persistent attribute value is specified in situations when the database is loaded automatically by TimesTen. For example, the database is automatically loaded if RamPolicy for the database is set to 1.

• If the PL/SQL shared memory cannot be mapped at the appropriate address, TimesTen returns an error and the connection to the database fails.

• The memory segment size is determined by the value of PLSQL_MEMORY_SIZE.

Required Privilege

Only the instance administrator can change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PLSQL_MEMORY_ADDRESS as follows:

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems odbc.ini file in TimesTen Classic or in the database definition (.dbdef) file in TimesTen Scaleout	PLSQL_MEMORY_ADDRESS	A hexidecimal value that indicates the memory address for PL/SQL process.
Windows ODBC Data Source Administrator	Not applicable

PLSQL_MEMORY_SIZE

Use of PL/SQL requires a shared memory segment. This attribute determines the size in megabytes of the shared memory segment used by PL/SQL. All connections share this memory segment.

This shared memory contains recently-executed PL/SQL code, the shared package state, and metadata associated with the operation of PL/SQL. This shared memory segment is separate from the one containing the TimesTen database.

Some things to consider when setting this attribute are:

• The value of this attribute is stored persistently by TimesTen. The persistent attribute value is specified in situations when the database is loaded automatically by TimesTen. For example, the database is automatically loaded if RamPolicy for the database is set to 1.

• For most PL/SQL users, the default memory size should be an adequate amount of memory. For databases that make extensive use of PL/SQL, specify a larger memory size. If the memory space is exhausted, ORA-4031 errors may occur during PL/SQL execution.

• The address of the memory segment is determined by the value of PLSQL_MEMORY_ADDRESS.

• There is both a fixed and per connection overhead allocated from the PL/SQL segment, even if you do not use PL/SQL. The minimum fixed memory allocated is approximately 1500 KB. Additionally, approximately 40 KB of memory is allocated per connection. Thus, you can compute an estimated minimum memory setting needed as 1500 KB plus (number_of_connections * 40). If the application uses PL/SQL, we recommend that you allocate twice the estimated minimum required memory for this segment. If the application does not use PL/SQL, you can allocate less than twice the estimated minimum required memory.

Required Privilege

Only the instance administrator can change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PLSQL_MEMORY_SIZE as follows:

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems odbc.ini file in TimesTen Classic or in the database definition (.dbdef) file in TimesTen Scaleout	PLSQL_MEMORY_SIZE	Specify a positive integer greater than 2 representing the size in MB of the shared memory segment in megabytes. The default size is 128 MB.
Windows ODBC Data Source Administrator	Not applicable

PLSQL_OPEN_CURSORS

This attribute specifies the maximum number of PL/SQL cursors that can be open in a session at one time.

Use this to prevent a session from opening an excessive number of cursors. Default is 50 PL/SQL cursors.

Updating the value of this attribute takes effect on the next connection, not the current connection. The value also can be set at a database level via the ttDBConfig built-in procedure, providing a default for future connections.

If you decrease the value and the number of open cursors currently exceeds or equals the new setting, no new cursors can be opened until the total number of open cursors is less than the new setting (i.e., some of the currently open cursors have to close).

A value of 0 indicates no PL/SQL cursors can be open. (But if there are cached PL/SQL cursors that contain any PL/SQL code, they could still be executed.)

Note:

This attribute has the same functionality as OPEN_CURSORS in Oracle Database.

Required Privilege

No privilege is required to change the value of this attribute.

Usage in TimesTen Scaleout and TimesTen Classic

This attribute is supported in both TimesTen Classic and TimesTen Scaleout.

Setting

Set PLSQL_OPEN_CURSORS as follows:

Where to set the attribute	How the attribute is represented	Setting
C or Java programs or UNIX and Linux systems odbc.ini file in TimesTen Classic or in the database definition (.dbdef) file in TimesTen Scaleout	PLSQL_OPEN_CURSORS	A positive integer from 0 to 65535 representing the number of cursors that can be open in one session at one time. The default value is 50.
Windows ODBC Data Source Administrator	N/A