Oracle Parallel Parameter changes

Products

• CA eHealth

Issue/Introduction

Description:

Internal testing has determined that changes to certain Oracle parameter settings will benefit users of the eHealth product. These changes will be productized in version 6.3 of eHealth. Customers running all patch levels of versions 6.1 and 6.2 of the product, which embeds Oracle 10.2, should make these changes. They do not apply to earlier versions of eHealth, which embedded Oracle 9i. These changes are meant to improve the performance of the product.

Solution:

1. Login to the system as the eHealth administrator account ($NH_USER). Download the attached file setParams.zip and copy it to an empty directory on the system. Open a command prompt and change to the directory where you downloaded the zip file. Extract the file with the following command.
   
   $NH_HOME/bin/sys/nhiUnZip setParams.zip
   
   This will extract one file, setParams.sh. On Unix servers only, add execute permission to the file with the following command.
   
   chmod 755 setParams.sh
   
   
2. Execute the setParams.sh file with the following command.
   
   ./setParams.sh
   
   If any errors are reported, contact CA eHealth Technical Support (see end the NOTES section below for the fix to one common error). Otherwise continue and edit the Oracle parameter file.
   
   
3. Edit the Oracle parameter file.
   
   For this step you need to know the value of the ORACLE_SID and $NH_ORACLE_HOME environment variables and the Oracle parallel_max_servers parameter setting. To find these values, follow these steps.
   
   
   1. On Unix, open a command prompt as the $NH_USER. On Windows, log in as the eHealth administrator account
      
      
   2. To find the value of ORACLE_SID on your system, from the command prompt type:
      
      env | grep SID
      
      To find the value of $NH_ORACLE_HOME on your system, from the command prompt type:
      
      env | grep NH_ORACLE_HOME
      
      To find the value of parallel_max_servers on your system, from the command prompt type:
      
      $NH_HOME/bin/sys/nhisql "show parameter parallel_max_servers"
      
      
4. Use the values discovered in step 3 to identify the Oracle parameter include file init{SID}.ora.usr file by substituting the value returned for $NH_ORACLE_HOME and SID
   
   On Windows this file is $NH_ORACLE_HOME/database/init{SID}.ora.usr
   On Unix this file is in $NH_ORACLE_HOME/dbs/init{SID}.ora.usr
   
   For example, if the command returned EHEALTH for SID and E:/oracle/oracle10.2 for $NH_ORACLE_HOME, on Windows the init{SID}.ora.usr file would be E:/oracle/oracle10.2/database/initEHEALTH.ora.usr
   
   Make a backup of the init{SID}.ora.usr file before editing the file. The file is empty by default. Add the following three entries. If the file is not empty, add these lines to the end of the file.
   
   For the parallel_max_servers entry, use the value returned by the nhisql command in step 3. In this case we are using 32, but the value will vary between 4 and 32 depending on the number of CPU's in your system.
   
   parallel_max_servers=32
   parallel_execution_message_size=16384
   optimizer_dynamic_sampling=6
   
   Save the init{SID}.ora.usr file.
   
   
5. To enable these changes, the database must be restarted. This will require restarting eHealth, Report Center (if installed), and the database with the commands:
   
   nhServer stop
   nhReportCenter stop
   nhStopDb immediate
   nhStartDb
   nhReportCenter start
   nhServer start

NOTES ON A COMMON ERROR

We have noted that some customers have experienced an Oracle error when running the setParams.sh script. Soon after starting the script, and error will raise stating that it cannot find the spFile{SID}.ora file, even though it is loaded. If you encounter this error, please follow the steps below to resolve.

1. Stop eHealth
   
   nhServer stop
   
   
2. start sqlplus as the $NH_USER. For example,
   
   sqlplus ehealth/ehealth
   
   
3. Stop the database
   
   shutdown immediate
   
   
4. Start the database
   
   startup
   
   
5. Exit from sqlplus
   
   exit
   
   
6. Return to step 2 in the process above to run the setParams.sh script and continue.

Environment

Release: LHBASC99000-6.2-eHealth-Live Health Upgrade Elements
Component: