The default Oracle parameters can be changed for high performance issues. The different parameters will be presented by using the SQL command “show parameter” with different parameter names like “processes” in this example:
SQL> show Parameter processes
NAME TYPE VALUE
------------------------------------ ----------- ----------------
aq_tm_processes integer 0
db_writer_processes integer 1
gcs_server_processes integer 0
global_txn_processes integer 1
job_queue_processes integer 10
log_archive_max_processes integer 4
processes integer 180
>
Parameter which controls the number of shared servers
shared_servers: Shared server is enabled by setting the SHARED_SERVERS initialization parameter to a value greater than 0. On Unix systems, dedicated server connections are used. For the ImageMaster 9 Integration Service set a value of zero. On Windows, the value has to be set greater than 1, e.g. 2, which means that shared server processes in the listener.ora file are defined. The number of the value indicates the number of shared processes.
SQL> alter system set shared_servers=0 scope=spfile;
Parameter which controls the number of server processes
processes: This parameter specifies the maximum number of OS user processes which can be connected to the Oracle instance. Its value must consider the background processes and the login process which start the instance. Be sure to add extra six processes for the background processes.
SQL> alter system set processes=600 scope=spfile;
Parameter which controls the number of sessions
sessions: This parameter specifies the maximum number of sessions. It is recommended setting this parameter to be above specified number for processes multiplied by 1.5 (and to be at least above specified number for processes + 50):
SQL> alter system set sessions=900 scope=spfile;
Parameters which control the optimizer behavior
optimizer_index_caching (default 0): This parameter controls the costing of an index probe in conjunction with a nested loop. The range of values from 0 to 100 for optimizer_index_caching indicates the percentage of index blocks in the buffer cache, which modifies the optimizer assumptions about index caching for nested loops and in-list iterators. A value of 100 infers that 100% of the index blocks are likely to be found in the buffer cache and the optimizer adjusts the cost of an index probe or nested loop correspondingly. Pay attention when adapting this parameter because execution plans may change in favor of index caching. 90 is a good value for many instances of ImageMaster 9.
SQL> alter system set optimizer_index_caching=90 scope=spfile;
optimizer_index_cost_adj (default 100): This parameter can be used to adjust the cost of index probes. The range is 1 to 10000. The default value is 100, which means that indices are evaluated as an access path based on the normal costing model. A value of 50 implies that the cost of an index access path is 50 percent of the normal cost. 50 is a good value for many instances of ImageMaster 9.
SQL> alter system set optimizer_index_cost_adj=50 scope=both;
In some environments an issue has been reported with the Oracle optimizer. See chapter Oracle optimizer issue for Windows based installations for further details.