Configure Oracle DB Parameter Settings

After ensuring that the requirements are covered as described in "Prepare the Hardware and Infrastructure for Oracle," you need to prepare the parameter settings for your database(s) as described in the following sections:

• Define the Database Parameters for the PCO Server
• Define the Database Parameters for a PCO/PDA Client

Important Prerequisite: Make sure that both the database server and clients have the same Oracle version installed.

Define the Database Parameters for the PCO Server

After you install your Oracle server(s), check and set the following parameters and settings:

1. Verify the character set used.

   This should be one of the NLS character sets that support the fulltext indexing. See the table in the related step in "Install the Database."

   After the installation process, you can use the SQL statement to verify which setting is being used:

         select * from sys.nls_database_parameters;

   Sample output:

   PARAMETER	VALUE
   NLS_LANGUAGE	AMERICAN
   NLS_TERRITORY	AMERICA
   NLS_CURRENCY	$
   NLS_ISO_CURRENCY	AMERICA
   NLS_NUMERIC_CHARACTERS	.,
   NLS_CHARACTERSET	WE8ISO8859P1
   ...

2. Update the SQLNET.ORA file with the following parameter values:

   • TCP.NODELAY = YES (applies to the DB Server and DB client)

     Setting this parameter improves system performance because it ensures that the system does not wait for an acknowledgment of a packet before sending the next.

   • SQLNET.EXPIRE_TIME <= 5 minutes (applies to the DB Server)

     Recommended value: 1

     This parameter applies to the "Dead Connection Detection". This is the amount of time that the database server checks whether existing client sessions have reacted. The Server ends sessions that do not respond.

3. Create tablespaces

   Tablespaces for the PCO must be created using ASSM (Automatic Segment Space Management). ASSM provides improved performance for data inserts and provides transparency of table reaorganizations.

4. Reorganize with DB tools

   Automic recommends using the Oracle SEGMENT ADVISOR because it generates a list of all tables and indexes that must be reorganized. You could also reorganize your data manually, but this would require extensive manual changes in the PCO databases.

   Example of an online index reorganization:

   sqlplus> alter index <index_name> rebuild online;

5. Configure the statistics update.

   You should regularly update the access statistics of the database tables. A daily job is automatically scheduled when you install the database (GATHER_STATS_JOB) unless you deactivate this option. You can specify the time when the job should run.

   Another way to update your database statistics is to schedule the following SQL statement in AE (line 1):

   exec dbms_stats.gather_schema_stats('PCO',options=>'GATHER AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>

   'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

   Alternatively, you can use the following statement. In some cases it may generate more useful statistical results.

   exec dbms_stats.gather_schema_stats('PCO',options=>'GATHER AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt =>

   'FOR ALL INDEXED COLUMNS SIZE AUTO', cascade => TRUE);

   To verify whether and when the statistics have last been updated, you can use the SQL view "USER_TABLES", as you see in the following example:

   sqlplus>select TABLE_NAME, NUM_ROWS, LAST_ANALYZED from USER_TABLES;

6. Tune the database parameters.

   Buffer Cache Hit Rate

   The buffer cache hit rate should be higher than 99%. The following example script retrieves the current value.

   -- DB_BLOCK_BUFFERS: 'buffer cache hit rate %' should be > 99%

   execute dbms_output.put_line(chr(9));

   execute dbms_output.put_line(chr(9));

   execute dbms_output.put_line('>>>>>> DB_BLOCK_BUFFERS: "buffer cache hit rate %" should be > 99%');

   execute dbms_output.put_line(chr(9));

   declare dbg number;

   cg number;

   pr number;

   begin

   select value into dbg

   from v$sysstat

   where name = 'db block gets';

   select value into cg from v$sysstat

   where name = 'consistent gets';

   select value into pr from v$sysstat

   where name = 'physical reads';

   dbms_output.put_line('db block gets

   |'||to_char(dbg,'999G999G999G999'));

   dbms_output.put_line('consistent gets

   |'||to_char(cg,'999G999G999G999'));

   dbms_output.put_line('physical reads

   |'||to_char(pr,'999G999G999G999'));

   dbms_output.put_line('----------------------------------------

   ');

   dbms_output.put_line('buffer cache hit rate in % |'|| to_char (round(100*(1-(pr/(dbg+cg))),2),'999D00')); end;

   /

   Retrieving the buffer cache hit rate

   Memory Hit Rate

   The memory hit rate of the sort area should be higher than 99%. The following example script retrieves the current value.

   -- SORT_AREA_SIZE: memory hit rate % should be > 99%

   execute dbms_output.put_line(chr(9));

   execute dbms_output.put_line(chr(9));

   execute dbms_output.put_line('>>>>>> SORT_AREA_SIZE: "memory hit rate %" should be > 99%');

   execute dbms_output.put_line(chr(9));

   declare sm number;

   sd number;

   begin

   select value into sm

   from v$sysstat

   where name = 'sorts (memory)';

   select value into sd from v$sysstat

   where name = 'sorts (disk)';

   dbms_output.put_line('sorts (memory)

   |'||to_char(sm,'999G999G999'));

   dbms_output.put_line('sorts (disk)

   |'||to_char(sd,'999G999G999'));

    

   dbms_output.put_line('-------------------------------');

    

   dbms_output.put_line('memory hit Rate % |'|| to_char (round(100-(100*sd/sm),2),'999D00')); end;

   /

   Retrieving the memory hit rate

   Size of the Shared Pool

   The percentage of non-successful library cache accesses within the shared pool as compared to the total number of all accesses should be much lower than 1%. The following example script retrieves the current value.

   -- SHARED_POOL_SIZE: 'Misses %' should be << 1%

   execute dbms_output.put_line(chr(9));

   execute dbms_output.put_line(chr(9));

   execute dbms_output.put_line('>>>>>> SHARED_POOL_SIZE: "Misses

   %" should be << 1%');

   column 'misses %' format a10

   select sum(pins) "Executions",

   sum(reloads) "Cache Misses while Executing",

   to_char(sum(reloads)/(sum(pins)+sum(reloads))*100,'990D0000') "Misses %"

   from v$librarycache;

   Retrieving the library cache misses

   Note: Alternatively, you can use the Memory Advisor to retrieve the library cache misses. Make sure to use the PGA_AGREGATE_TARGET parameter.

Define the Database Parameters for a PCO/PDA Client

For an Oracle database for the PCO Modelling Studio, you must also do the following.

1. Verify the character set used.

   This should be one of the NLS character sets that support the fulltext indexing. See the table in the related step in "Install the Database."

   After the installation process, you can use the SQL statement to verify which setting is being used:

         select * from sys.nls_database_parameters;

   Sample output:

   PARAMETER	VALUE
   NLS_LANGUAGE	AMERICAN
   NLS_TERRITORY	AMERICA
   NLS_CURRENCY	$
   NLS_ISO_CURRENCY	AMERICA
   NLS_NUMERIC_CHARACTERS	.,
   NLS_CHARACTERSET	WE8ISO8859P1
   ...

2. Update the SQLNET.ORA file with the following parameter values:

   • TCP.NODELAY = YES

     Setting this parameter improves system performance because it ensures that the system does not wait for an acknowledgment of a packet before sending the next.

3. If you connect the Admin database to the source system database over a TNS service, then you need to do the following in the client databases:

   1. In SQLNET.ORA check whether the entry NAMES.DIRECTORY_PATH exists and make sure it contains the value TNSNAMES. A valid line would look something like this:

           NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

   2. Update the TNSNAMES.ORA file with the following parameter value:

      • ENABLE=BROKEN

        This parameter must be the first part of the TNSNAME entry, as you see in the following example:

        PCOP =
          (DESCRIPTION =
            (ENABLE=BROKEN)
            (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))
            (CONNECT_DATA =
               (SERVER = DEDICATED)
               (SERVICE_NAME = PCOP))
           )

        Example of tnsnames.ora

   3. In Windows, create an environment variable named TNS_ADMIN that points to the directory of the file TNSNAMES.ORA.

      To add an environment variable, open the Control Panel and search for "environment variable." Then add a new variable in the Environment Variables dialog box.