Parameter
dbs/ora/close_stmt_after_exec
Short text
Close SQL statement after execution
Parameter Description
This parameter lets you force SQL statements to close immediately after they are executed for Oracle and force the release of all the resources allocated in the context of the statement.
For performance reasons, this normally does not happen. In the DB interface, a statement cache is implemented in which a certain number of SQL statements for the database are held open. The advantage of this cache is that when the same SQL statements are executed more than once, certain DB calls and thus “server roundtrips” can be saved.
Certain Oracle functions, such as that for closing a database link, can only be executed successfully when all SQL statements have been closed for the database. In such an instance, you can force the system to close immediately after the statement is executed by setting this parameter.
The parameter can have the following values:
• 0 ==> Statements are re-used (default)
• 1 ==> All Native SQL statements are closed immediately after they are executed
• 2 ==> All Open and Native SQL statements are closed after they are executed
Normally, this parameter should be left with its default value for performance reasons. In some cases, it may be useful to set this parameter to 1 when you use database links via the Native SQL interface in order to close database links again after they are used. The parameter value 2 is useful as a workaround only when certain errors occur.
Application Area
Database
Default Value
0
Operating System Restrictions
None
Database System Restrictions
Oracle
Are other parameters affected or dependent?
None
Values allowed
{ 0, 1, 2 }
