Oracle7 Server Reference Manual

Contents Index Home Previous Next

Specifying Values in the Parameter File

This section discusses several aspects of setting parameter values in the parameter file. The following topics are included:

Rules

The following rules govern the specification of parameters in the parameter file:

Suggestion: It is advisable to list parameters in alphabetical order in the parameter file. That makes it easier to find them and helps ensure that each parameter is specified only once. If a parameter is specified more than once, the last value encountered is the one used.

See your operating system specific Oracle documentation for more information on parameter files.

Changing Parameter Values

To change a parameter's value, edit the parameter file. The next time the instance starts, it uses the new parameter values in the updated parameter file. Note that the change does not take effect until the instance is shut down and restarted.

Displaying Current Parameter Values

To see the current settings for initialization parameters, use the following SQL command:

SHOW PARAMETERS

This displays all parameters in alphabetical order, with their current values.

Enter the following text string to see a display for all parameters having BLOCK in their name.:

SVRMGR> SHOW PARAMETERS BLOCK

If you display all the parameters, you may wish to use the SPOOL command to write the output to a file.

Uses of Parameters

Initialization parameters can be grouped by function in several different ways. For example, there are parameters that perform the following functions:

The set of variable parameters are of particular interest to database administrators because these parameters are used primarily for improving database performance.

Types of Parameters

The Oracle7 Server has the following types of initialization parameters:

Derived Parameters

Some initialization parameters are noted as derived. This means that their values are calculated from the values of other parameters. Normally, you should not alter values for derived parameters, but if you do, the value you specify overrides the calculated value.

Dynamic Parameters

Some initialization parameters can be modified using the ALTER SYSTEM or the ALTER SESSION commands while an instance is running. These dynamic initialization parameters include the following:

Note that these three parameters can only be changed at the session level using the ALTER SESSION command, not at the system level.

The syntax for dynamically altering the initialization parameters is as follows:

ALTER SESSION SET parameter_name = value
ALTER SYSTEM SET parameter_name = value

The ALTER SESSION command changes the value of the parameter until the database is shut down.

The ALTER SYSTEM command modifies the global value of the parameter and survives database shutdown and startup. The ALTER SYSTEM command does not always change the parameter value for the current session. Use the ALTER SESSION command to change the parameter value for the current session.

Global Constants Parameters with Prefix GC

Initialization parameters with the prefix GC, such as GC_DB_LOCKS, apply to systems using the Oracle7 Parallel Server. The prefix GC stands for Global Constants. The settings of these parameters determine how the Oracle7 Parallel Server coordinates multiple instances. The settings you choose have an effect on the use of certain operating system resources.

Additional Information: For more information about the Parallel Server, see the Oracle7 Parallel Server Concepts & Administration manual.

See your system release bulletins or other operating system specific Oracle documentation for platform specific information on Parallel Server parameters.

Operating System Dependent Parameters

For some initialization parameters, the valid values or ranges depend upon the host operating system. This is denoted in the default, or range, column as operating system-dependent. For example, the parameter DB_BLOCK_BUFFERS indicates the number of data buffers in main memory, and its maximum value depends on the operating system. The size of those buffers, set by DB_BLOCK_SIZE, has a system-dependent default value.

See your operating system specific Oracle documentation for more information on operating system dependent Oracle parameters and operating system parameters.

Variable Parameters

The variable initialization parameters offer the most potential for improving system performance. Some variable parameters set capacity limits but do not affect performance. For example, when the value of OPEN_CURSORS is 10, a user process attempting to open its 11th cursor receives an error. Other variable parameters affect performance but do not impose absolute limits. For example, reducing the value of DB_BLOCK_BUFFERS does not prevent work even though it may slow down performance.

Increasing the values of variable parameters may improve your system's performance, but increasing most parameters also increases the System Global Area (SGA) size. A larger SGA can improve database performance up to a point. In virtual memory operating systems, an SGA that is too large can degrade performance if it is swapped in and out of memory. Operating system parameters that control virtual memory working areas should be set with the SGA size in mind. The operating system configuration can also limit the maximum size of the SGA.

Parameters You Should Not Specify in the Parameter File

The following types of parameters may never have to be specified in the parameter file:

When Parameters Are Set Incorrectly

Some parameters have a minimum setting below which an Oracle instance will not start. For other parameters, setting the value too low or too high may cause Oracle to perform badly, but it still runs.

You may see messages indicating that a parameter value is too low or too high, or that you have reached the maximum for some resource. Frequently, you can wait a short while and retry the operation when the system is not as busy. If a message occurs repeatedly, you should shut down the instance, adjust the relevant parameter, and restart the instance.


Contents Index Home Previous Next