Oracle Parallel Parameter changes

Document ID : KB000051026
Last Modified Date : 14/02/2018
Show Technical Document Details

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.
File Attachments:
TEC535005.zip