Question & Answer
Question
Are there any Oracle client specific settings that can improve the speed of processing data from Oracle source/target databases?
Answer
In many cases Oracle is configured to use the Multi Threaded Server (MTS) by default which minimizes the number of processes that are required for the running Oracle instance thus minimizing the memory requirements for the instance. However, this may impact the performance of DataStage jobs that process large amounts of data due to sharing the resources that are configured for MTS. In these cases it is possible to request a dedicated server connection which establishes a separate process on the Oracle server for each DataStage connection by altering the tnsnames.ora file on the DataStage server to include SERVER = dedicated as part of the service name definition for the Oracle SID that is accessed via DataStage:
The tnsnames.ora file is located in the following directory by default: $ORACLE_HOME/network/admin or in the location specified by the TNS_ADMIN variable.
The following is an extract from tnsnames.ora that shows the setting:
# tnsnames.ora Network Configuration File: /apps/oracle/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
orcl =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orclhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21576713