Changes in some init.ora parameter meanings in 10g

SHARED_POOL_SIZE
In previous releases, the amount of shared pool memory that was allocated was equal to the value of the SHARED_POOL_SIZE initialization parameter plus the amount of internal SGA overhead computed during instance startup. Starting with Oracle Database 10g release 10.1, the value of SHARED_POOL_SIZE must now also accommodate this shared pool overhead.

SESSION_CACHED_CURSORS
In previous Oracle Database releases, the number of SQL cursors cached by PL/SQL was determined by the OPEN_CURSORS initialization parameter. Starting with Oracle Database 10g release 10.1, the number of cached cursors is determined by the SESSION_CACHED_CURSORS initialization parameter.

LOG_ARCHIVE_FORMAT
Starting with Oracle Database 10g release 10.1, if the COMPATIBLE initialization parameter is set to 10.0.0 or higher, then archive log file names must contain each of the elements %s (sequence), %t (thread), and %r (resetlogs ID) to ensure that all archive log file names are unique. If the LOG_ARCHIVE_FORMAT initialization parameter is set in the parameter file, then make sure the parameter value contains the %s, %t, and %r elements.

PGA_AGGREGATE_TARGET
Starting with Oracle Database 10g release 10.1, Automatic PGA Memory Management is now enabled by default (unless PGA_AGGREGATE_TARGET is explicitly set to 0 or WORKAREA_SIZE_POLICY is explicitly set to MANUAL). PGA_AGGREGATE_TARGET defaults to 20% of the size of the SGA, unless explicitly set. Oracle recommends tuning the value of PGA_AGGREGATE_TARGET after upgrading.
Until 9iR2, PGA_AGGREGATE_TARGET parameter controls the sizing of workareas for all dedicated server connections, but it has no effect on shared server (aka MTS) connections and the *_AREA_SIZE parameters will take precedence in this case. In 10g, PGA_AGGREGATE_TARGET controls workareas allocated by both dedicated and shared connections.

QUERY_REWRITE_ENABLED
The default value of the initialization parameter QUERY_REWRITE_ENABLED has changed. By default it is TRUE in 10.1 and above. Prior to 10.1 the default is FALSE.

REMOTE_LOGIN_PASSWORDFILE
There are multiple modes in which a remote_login_passwordfile can be set to. The different modes are SHARED, EXCLUSIVE and NONE. A SHARED password file can be used by multiple databases running on the same server, or multiple instances of a Real Application Clusters (RAC) database. A SHARED password file cannot be modified which means that one cannot add users to a SHARED password file.
Any attempt to do so or to change the password of SYS or other users with the SYSDBA or SYSOPER privileges generates an error.
In 9.2 the default value of REMOTE_LOGIN_PASSWORDFILE is EXCLUSIVE , the same is set to SHARED by default in 10g.

FILESYSTEMIO_OPTIONS
This parameter was an under score parameter _FILESYSTEMIO_OPTIONS Prior to 9.2. Starting 9.2 it is not an under score parameter. The parameter is FILESYSTEMIO_OPTIONS. The value may be any of the following:
- asynch - This allows asynchronous IO to be used where supported by the OS.
- directIO - This allows directIO to be used where supported by the OS. Direct IO bypasses any Unix buffer cache. As of 10.2 most platforms will try to use "directio" option for NFS mounted disks (and will also check NFS attributes are sensible).
- setall - This enables both ASYNC and DIRECT IO.
- none - This disables ASYNC IO and DIRECT IO so that Oracle uses normal synchronous writes, without any DIRECT IO options.

ON Solaris and 10.2 the default is asynch.

This parameter is very important for I/O Performance. The value of FILESYSTEMIO_OPTIONS is set depending upon the design of underlying storage and OS. If the underlying storage or filesystem is designed for asynchronous IO then the value needs to be set asynch. If the underlying storage or filesystem is designed for directio then the value needs to set to "directIO".

PARALLEL_ADAPTIVE_MULTI_USER
In 9i default value of parallel_adaptive_multi_user is derived from parallel_automatic_tuning which defaults to false. If parallel_automatic_tuning is true Oracle will set the value of parallel_adaptive_multi_user parameter to true. If parallel_automatic_tuning is false, parallel_adaptive_multi_user will be false.
In 10g default value of parallel_adaptive_multi_user is true and parallel_automatic_tuning has been deprecated in 10g.


PARALLEL_MAX_SERVERS
The Default value of PARALLEL_MAX_SERVERS has changed in 10g. 10g default is derived using the following formula:
CPU_COUNT x PARALLEL_THREADS_PER_CPU x (2 if PGA_AGGREGATE_TARGET > 0; otherwise 1) x 5
In 9.2 If PARALLEL_AUTOMATIC_TUNING is false, the default value of PARALLEL_MAX_SERVERS is 5. If PARALLEL_AUTOMATIC_TUNING is TRUE, the default value of PARALLEL_MAX_SERVERS is CPU x 10.

SKIP_UNUSABLE_INDEXES
The default value of SKIP_UNUSABLE_INDEXES is TRUE IN 10g.
SKIP_UNUSABLE_INDEXES enables or disables the use and reporting of tables with unusable indexes or index partitions. In earlier releases prior to 10.1, SKIP_UNUSABLE_INDEXES was a session parameter only. In Oracle Database 10g release 10.1 and later, it is now an initialization parameter and defaults to true. The true setting disables error reporting of indexes and index partitions marked UNUSABLE. This setting allows all operations (inserts, deletes, updates, and selects) on tables with unusable indexes or index partitions.

Source: Oracle Database Upgrade advisor