CURSOR_SPACE_FOR_TIME

 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

Oracle initializatoin parameters

CURSOR_SHARING

Property	Description
Parameter type	String
Syntax	CURSOR_SHARING = { SIMILAR | EXACT | FORCE }
Default value	EXACT
Modifiable	ALTER SESSION, ALTER SYSTEM
Basic	No

CURSOR_SHARING determines what kind of SQL statements can share the same cursors.

Values:

· FORCE

Forces statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect the meaning of the statement.

· SIMILAR

Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.

· EXACT

Only allows statements with identical text to share the same cursor.

Note:

1. Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications, or applications that use stored outlines.

2. This parameter was introduced in Oracle 8.1.6. In Oracle8i the allowed parameter values are only EXACT or FORCE. SIMILAR is introduced as a value in Oracle9i.

3. Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications and in applications using stored outlines.

4. Setting CURSOR_SHARING to FORCE causes an increase in the maximum lengths (as returned by DESCRIBE) of any selected expressions that contain literals (in a SELECT statement). However, the actual length of the data returned will not change.

5. 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_SHARING’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
cursor_sharing	EXACT	TRUE	TRUE	IMMEDIATE	TRUE	FALSE	cursor sharing mode

Oracle initializatoin parameters

CREATE_STORED_OUTLINES

Property	Description
Parameter type	String
Syntax	CREATE_STORED_OUTLINES = { true | false | category_name } [NOOVERRIDE]
Default value	There is no default value.
Modifiable	ALTER SESSION, ALTER SYSTEM
Basic	No

CREATE_STORED_OUTLINES determines whether Oracle automatically creates and stores an outline for each query submitted during the session.

Values:

· true

Enables automatic outline creation for subsequent queries in the same session. These outlines receive a unique system-generated name and are stored in the DEFAULT category. If a particular query already has an outline defined for it in the DEFAULT category, then that outline will remain and a new outline will not be created.

· false

Disables automatic outline creation during the session. This is the default.

· category_name

Enables the same behavior as true except that any outline created during the session is stored in the category_name category.

· NOOVERRIDE

NOOVERRIDE specifies that this system setting will not override the setting for any session in which this parameter was explicitly set. If you do not specify NOOVERRIDE, then this setting takes effect in all sessions.

Note:

1. This parameter was introduced in 10g.

2. 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) = ‘CREATE_STORED_OUTLINES’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
create_stored_outlines		TRUE	TRUE	IMMEDIATE	TRUE	FALSE	create stored outlines for DML statements

Oracle initializatoin parameters

CREATE_BITMAP_AREA_SIZE

Property	Description
Parameter type	Integer
Default value	8388608 (8 MB)
Modifiable	No
Range of values	Operating system-dependent
Basic	No

Note:

1. Oracle does not recommend using the CREATE_BITMAP_AREA_SIZE parameter unless the instance is configured with the shared server option. Oracle recommends that you enable automatic sizing of SQL working areas by setting PGA_AGGREGATE_TARGET instead. CREATE_BITMAP_AREA_SIZE is retained for backward compatibility.

CREATE_BITMAP_AREA_SIZE is relevant only for systems containing bitmap indexes. It specifies the amount of memory (in bytes) allocated for bitmap creation. The default value is 8 MB. A larger value may speed up index creation.

Cardinality is the number of unique values in a column in relation to the number of rows in the table. If cardinality is very small, you can set a small value for this parameter. For example, if cardinality is only 2, then the value can be on the order of kilobytes rather than megabytes. As a general rule, the higher the cardinality, the more memory is needed for optimal performance.

2. Related: BITMAP_MERGE_AREA_SIZE

This parameter is not dynamically alterable at the session level.

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) = ‘CREATE_BITMAP_AREA_SIZE’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
create_bitmap_area_size	8388608	TRUE	FALSE	FALSE	FALSE	FALSE	size of create bitmap buffer for bitmap index

Oracle initializatoin parameters

CPU_COUNT

Property	Description
Parameter type	Integer
Default value	Set automatically by Oracle
Modifiable	ALTER SYSTEM Oracle strongly recommends that you do not alter the value of this parameter. If it is necessary to do so, please refer to your operating system-specific documentation for further information.
Range of values	0 to unlimited
Basic	No

Caution:

On most platforms, Oracle automatically sets the value of CPU_COUNT to the number of CPUs available to your Oracle instance. Do not change the value of CPU_COUNT.

CPU_COUNT specifies the number of CPUs available to Oracle. On single-CPU computers, the value of CPU_COUNT is 1.

Note:

1. Related: LOG_SIMULTANEOUS_COPIES, SPIN_COUNT

This parameter typically is used in the calculation of the default value of other parameters in various releases, and to decide if 'latch spinning' is appropriate. Eg: It is used to derive a value for LOG_SIMULTANEOUS_COPIES.

On most platforms Oracle automatically sets the value of CPU_COUNT to the number of CPUs available to your Oracle instance. Do not change the value of CPU_COUNT unless specifically advised to do so.

If there is heavy contention for redo latches, change the value of LOG_SIMULTANEOUS_COPIES to twice the number of CPUs you have. Do not change the value of CPU_COUNT.

See view X$KVII for information on the number of CPU's Oracle thinks you are using.

1. 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) = ‘CPU_COUNT’;

NAME	VALUE	IS DEFAULT	ISSES_ MODIFIABLE	ISSYS_ MODIFIABLE	ISINSTANCE_ MODIFIABLE	IS DEPRECATED	DESCRIPTION
cpu_count	16	TRUE	FALSE	IMMEDIATE	TRUE	FALSE	number of CPUs for this instance

Oracle initializatoin parameters