Tuesday, November 25, 2008

PARALLEL_IO_CAP_ENABLED

Property

Description

Parameter type

Boolean

Default value

false

Modifiable

ALTER SESSION, ALTER SYSTEM

Range of values

true | false

Basic

No

Real Application Clusters

Multiple instances can have different values.

PARALLEL_IO_CAP_ENABLED specifies whether or not Oracle caps the default degree of parallelism to no greater than what the I/O system can support. This new value is calculated based on the results of the resource manager's I/O calibration package.

If PARALLEL_IO_CAP_ENABLED is set to true and the database I/O capacity has been calibrated, then Oracle will reduce the default degree of parallelism if the I/O capacity cannot support the available number of CPUs. To calibrate the I/O capacity, use the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure to measure the system's I/O capacity. Oracle then computes the degree of parallelism so as not to exceed the system's I/O capacity.

Note:

1. This parameter was introduced in 11g.

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

NAME

VALUE

IS

DEFAULT

ISSES_

MODIFIABLE

ISSYS_

MODIFIABLE

ISINSTANCE_

MODIFIABLE

IS

DEPRECATED

DESCRIPTION

parallel_io_cap_enabled

FALSE

TRUE

TRUE

IMMEDIATE

TRUE

FALSE

enable capping DOP

by IO bandwidth

Oracle initializatoin parameters

PARALLEL_INSTANCE_GROUP

Property

Description

Parameter type

String

Syntax

PARALLEL_INSTANCE_GROUP = service_name | group_name

Default value

There is no default value; parallel execution is enabled across all currently active instances.

Modifiable

ALTER SESSION, ALTER SYSTEM

Range of values

Any service name or any group name specified in the INSTANCE_GROUPS parameter of any active instance

Real Application Clusters

Multiple instances can have different values.

PARALLEL_INSTANCE_GROUP is a Real Application Clusters parameter that you can specify in parallel mode only. Used in conjunction with services or with the INSTANCE_GROUPS parameter, it lets you restrict parallel query operations to a limited number of instances.

This parameter identifies the parallel instance group Oracle will use for spawning parallel execution processes. If used in conjunction with services, then parallel operations will spawn parallel execution processes only on instances defined in the service. If used in conjunction with INSTANCE_GROUPS, then parallel operations will spawn parallel execution processes only on instances that specify a matching group in their INSTANCE_GROUPS parameter.

If the value assigned to PARALLEL_INSTANCE_GROUP is the name of a service or group that does not exist, then the operation runs serially. No parallelism is used.

Note:

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

NAME

VALUE

IS

DEFAULT

ISSES_

MODIFIABLE

ISSYS_

MODIFIABLE

ISINSTANCE_

MODIFIABLE

IS

DEPRECATED

DESCRIPTION

parallel_instance_group

TRUE

TRUE

IMMEDIATE

TRUE

FALSE

instance group to use

for all parallel operations

Oracle initializatoin parameters

PARALLEL_MAX_SERVERS

 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
Integer
Default value
Derived from the values of CPU_COUNT, PARALLEL_THREADS_PER_CPU, and PGA_AGGREGATE_TARGET
Modifiable
ALTER SYSTEM
Range of values
0 to 3600
Real Application Clusters
Multiple instances can have different values.
Note:
1. This parameter applies to parallel execution in exclusive mode as well as in a Real Application Clusters environment.
PARALLEL_MAX_SERVERS specifies the maximum number of parallel execution processes and parallel recovery processes for an instance. As demand increases, Oracle Database increases the number of processes from the number created at instance startup up to this value.
If you set this parameter too low, then some queries may not have a parallel execution process available to them during query processing. If you set it too high, then memory resource shortages may occur during peak periods, which can degrade performance.
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) = ‘PARALLEL_MAX_SERVERS’;
NAME
VALUE
IS
DEFAULT
ISSES_
MODIFIABLE
ISSYS_
MODIFIABLE
ISINSTANCE_
MODIFIABLE
IS
DEPRECATED
DESCRIPTION
parallel_max_servers
80
TRUE
FALSE
IMMEDIATE
TRUE
FALSE
maximum parallel query
servers per instance

Oracle initializatoin parameters

PARALLEL_EXECUTION_MESSAGE_SIZE

Thank you for visiting Spatial DBA - Oracle and ArcSDE.

I have stopped updating the blog.

Please visit Oracle DBA Tips (http://www.oracledbatips.com) for more Oracle DBA Tips.


Property
Description
Parameter type
Integer
Default value
Operating system-dependent
Modifiable
No
Range of values
2148 to 65535 (64 KB - 1)
Real Application Clusters
Multiple instances must have the same value.
PARALLEL_EXECUTION_MESSAGE_SIZE specifies the size of messages for parallel execution (formerly referred to as parallel query, PDML, Parallel Recovery, replication).
On most platforms, the default value is 2148bytes if PARALLEL_AUTOMATIC_TUNING is set to false, and 4096 bytes if PARALLEL_AUTOMATIC_TUNING is set to true. The default value is adequate for most applications. Larger values require a larger shared pool. Larger values result in better performance at the cost of higher memory use. For this reason, replication gets no benefit from increasing the size.
Note:
1. When PARALLEL_AUTOMATIC_TUNING is set to TRUE, message buffers are allocated out of the large pool. In this case, the default is generally higher. Note that the PARALLEL_AUTOMATIC_TUNING parameter has been deprecated.
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) = ‘PARALLEL_EXECUTION_MESSAGE_SIZE’;
NAME
VALUE
IS
DEFAULT
ISSES_
MODIFIABLE
ISSYS_
MODIFIABLE
ISINSTANCE_
MODIFIABLE
IS
DEPRECATED
DESCRIPTION
parallel_execution_message_size
2152
TRUE
FALSE
FALSE
FALSE
FALSE
message buffer size
for parallel execution

Oracle initializatoin parameters

PARALLEL_AUTOMATIC_TUNING

Property

Description

Parameter type

Boolean

Default value

false

Modifiable

No

Range of values

true | false

Note:

1. The PARALLEL_AUTOMATIC_TUNING parameter is deprecated. It is retained for backward compatibility only.

When PARALLEL_AUTOMATIC_TUNING is set to true, Oracle determines the default values for parameters that control parallel execution. In addition to setting this parameter, you must specify the PARALLEL clause for the target tables in the system. Oracle then tunes all subsequent parallel operations automatically.

If you used parallel execution in a previous release and are now enabling PARALLEL_AUTOMATIC_TUNING, then you should reduce the amount of memory allocated from the shared pool to account for the decreased demand on that pool. This memory will now be allocated from the large pool, and will be computed automatically if LARGE_POOL_SIZE is not specified.

As part of the automatic tuning, Oracle will enable the PARALLEL_ADAPTIVE_MULTI_USER parameter. You can override any of the system-provided defaults if desired.

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

NAME

VALUE

IS

DEFAULT

ISSES_

MODIFIABLE

ISSYS_

MODIFIABLE

ISINSTANCE_

MODIFIABLE

IS

DEPRECATED

DESCRIPTION

parallel_automatic_tuning

FALSE

TRUE

FALSE

FALSE

FALSE

TRUE

enable intelligent defaults

for parallel execution parameters

Oracle initializatoin parameters