The PER_STMT_EXEC memory duration

A PER_STMT_EXEC memory pool is associated with the execution of each SQL statement. A statement is the entire SQL statement plus any SQL commands that the SQL statement initiates.

The SQL statement initiates as follows:

An SQL statement that the client application invokes
An SQL statement that an SPL routine invokes
An SQL statement that one of the following DataBlade API statement-execution functions executes:
- mi_exec()
- mi_exec_prepared_statement()
- mi_open_prepared_statement()

When a C UDR allocates memory with the PER_STMT_EXEC memory duration, this memory is available to all routine instances that execute in the same SQL statement. For example, suppose that the following SELECT statement invokes the a_func2() user-defined function:

SELECT a_func2(x) FROM table1 WHERE y > 7;

Suppose also that the a_func2() function calls mi_exec() to execute a SELECT that also invokes a_func2(), as follows:

mi_integer a_func2(arg)
   mi_integer arg;
{
   ...
   mi_exec(
      "select a_func2(y) from table2 where b_func(y) > 7;", ...)

The SELECT query in the call to mi_exec() is a separate SQL command from the main SELECT query. All invocations of the a_func2() function in the mi_exec() SELECT statement can share any PER_STMT_EXEC memory that this instance of a_func2() allocates. They can also share any PER_STMT_EXEC memory that the b_func() function (in the WHERE clause) allocates.

The invocations of a_func2() in the SELECT on table1 have their own PER_STMT_EXEC memory pool. They would not share it with invocations of a_func2() in the mi_exec() call.

The database server reclaims any PER_STMT_EXEC memory in the current memory context as follows:

If the SQL statement does not contain any subqueries, the statement consists of a single SQL command. The database server deallocates PER_STMT_EXEC and PER_COMMAND memory at the same time.
If the SQL statement contains one or more subqueries, the statement consists of several SQL commands, one for the main query and one for each subquery. The PER_STMT_EXEC memory remains allocated until all SQL commands and UDRs complete.

At the completion of execution of a statement, the database server does not reclaim any memory in the memory context with a duration higher than PER_STMT_EXEC. The database server reclaims any PER_STMT_EXEC memory when the SQL statement completes execution, as follows:

For a noncursor statement, the database server deallocates PER_STMT_EXEC memory as soon as the statement status is returned to the client application.
This memory is actually freed on entry to the next execution of an SQL statement. After the last (or only) execution of the SQL statement, the database server deallocates the PER_STMT_EXEC memory after sending the status of the SQL statement to the client application. If a statement completes before the status is returned, the database server schedules the memory for release but does not free it until the return value is sent to the client application.
For a cursor statement, the database server deallocates PER_STMT_EXEC memory as soon as the statement status of close cursor is returned to the client application.
This memory is actually freed on entry to the next open of the cursor. After the last (or only) open of the cursor, the database server deallocates the memory after sending the status of the closed cursor to the client application.