PLSQL_CURSOR_WORKAREA_ARRAY_SZ

This attribute specifies the array size of the PL/SQL cursor work area used by TimesTen during bulk SQL operations in PL/SQL, such as FORALL.

TimesTen uses this work area to batch rows passed from the PL/SQL engine to the SQL engine. Larger batches reduce the number of context switches between PL/SQL and SQL, which can improve performance for workloads that process large PL/SQL collections or VARRAYs.

The valid values range from 1024 to 131072. The default value is 2048. If a value below 1024 is specified, TimesTen automatically uses the minimum value 1024. If a value above 131072 is specified, TimesTen automatically uses the maximum value 131072.

The effective number of rows processed in each batch depends on the number of bind variables in the SQL statement. The work area is shared across all bind variables, so statements with more bind variables process fewer rows per batch.

For example, if PLSQL_CURSOR_WORKAREA_ARRAY_SZ value is 16384:

• A FORALL INSERT with one bind variable can process up to 16384 rows per batch.
• A FORALL INSERT with five bind variables can process up to floor (16384/5) rows per batch.

Increasing this value can reduce SQL executions and improve performance for large PL/SQL bulk operations. However, larger values may increase per-connection memory usage, so choose a value appropriate for the size of the PL/SQL arrays and the available connection memory.

Required Privilege

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

Setting

Set PLSQL_CURSOR_WORKAREA_ARRAY_SZ 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	PLSQL_CURSOR_WORKAREA_ARRAY_SZ	An integer value range from 1024 to 131072. Default value is 2048.
Windows ODBC Data Source Administrator	N/A