Thank you for visiting Spatial DBA - Oracle and ArcSDE.
Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.
====================================================================
Property
|
Description
|
Parameter type
|
Boolean
|
Default value
| false |
Modifiable
|
No
|
Range of values
| true | false |
Basic
|
No
|
CURSOR_SPACE_FOR_TIME lets you use more space for cursors in order to save time. It affects both the shared SQL area and the client's private SQL area.Values:
·
TRUE
Shared SQL areas are kept pinned in the shared pool. As a result, shared SQL areas are not aged out of the pool as long as an open cursor references them. Because each active cursor's SQL area is present in memory, execution is faster. However, the shared SQL areas never leave memory while they are in use. Therefore, you should set this parameter to
TRUE only when the shared pool is large enough to hold all open cursors simultaneously.
In addition, a setting of
TRUE retains the private SQL area allocated for each cursor between executions instead of discarding it after cursor execution, saving cursor allocation and initialization time
·
FALSE
Shared SQL areas can be deallocated from the library cache to make room for new SQL statements.
Note:1. If this parameter is set to
TRUE, the SERIAL_REUSE parameter is disabled.2. Related: SESSION_CACHED_CURSORS
Setting this parameter to TRUE also retains the private SQL area allocated for each cursor between executes instead of discarding it after cursor execution. If there is a "cursor leak" in the application when this is set to TRUE it can quickly lead to ORA-4031 errors or severe shared pool performance problems.
3. Query for the current value of the parameter
select name, value, isdefault, isses_modifiable, issys_modifiable,
isinstance_modifiable, isdeprecated, description
from v$parameter where upper(name) = ‘CURSOR_SPACE_FOR_TIME’;
NAME
|
VALUE
|
IS
DEFAULT
|
ISSES_
MODIFIABLE
|
ISSYS_
MODIFIABLE
|
ISINSTANCE_
MODIFIABLE
|
IS
DEPRECATED
|
DESCRIPTION
|
cursor_space_for_time
|
FALSE
|
TRUE
|
FALSE
|
FALSE
|
FALSE
|
FALSE
|
use more memory in order
to get faster execution
|
