Recommendations for Oracle

As a database administrator, you want your database to achieve top performance. This topic provides basic maintenance recommendations for installations with Oracle databases.

General Recommendations

• Reorganize the Automation Engine database (including all Clients) on a regular basis.

• Gigabit network connection between the instances of the Automation Engine and the database with as few active components as possible on the path.

• If the database's data files are stored externally, you must take the speed of the external disk subsystem as well as its high-performance connection (such as Fibre Channel) into account.

• It is recommended to use identical hardware for all cluster nodes.

• For information about installation requirements, see CDA System Requirements and Sizing.

Configuration Recommendations

• Do not limit resource consumption. Aborting transactions due to limitations specified in the database can impede processing in the Automation Engine system. Additionally, inconsistent database contents can result, which can cause subsequent errors or endless loops.

• In the UC_HOSTCHAR_DEFAULT - Host Characteristics variable, set the key REPORT_BLKSIZE=8000.

• In UC_SYSTEM_SETTINGS - Systemwide Settings, set the following:

  • REPORT_BLKSIZE=8000

  • LAST_USE=0

    This parameter affects performance in the RAC/Data Guard environment in particular, but it can also lead to improvements in the performance of stand-alone databases.

• Optimize the TCP/IP connection. In the file SQLNET.ORA, set tcp.nodelay = yes (valid for database server and client).
  When a message is longer than a logical transport block, it must be split in several blocks. The above setting has the effect that this can happen without waiting for a TCP/IP response. Oracle's performance can be increased this way.

• Activate Dead Connection Detection as Oracle needs this setting to reliably recognize that the Automation Engine has crashed. In the file SQLNET.ORA, set SQLNET.EXPIRE_TIME to a maximum of 1 minute (valid for the database server). This is the interval in which the database server checks whether existing client sessions are still alive. The database server closes non-responding sessions.

• Set the OS parameter tcp_keepalive <= 5 minutes (valid for the database client).
  This parameter is necessary to ensure that a connection failure on database-client side can be detected when the database server is no longer in the position to end a database session, either in a controlled way or using Dead Connection Detection. Existing TCP connections are checked in the preset interval and if the connection partner does not respond, the connection is automatically terminated. The associated database session ends and the Oracle client can send an error report to the AE so that a new connection can be established.

• In the file TSNAMES.ORA, set enable = broken (valid for the database client).
  This parameter must be the first one of the complete entry in tnsnames as shown below:

  UC4P =

  (DESCRIPTION =

  (ENABLE=BROKEN)

  (ADDRESS = (PROTOCOL = TCP)(HOST = dbhost)(PORT = 1521))

  (CONNECT_DATA =

  (SERVER = DEDICATED) (SERVICE_NAME = UC4P)

  )

  )

• When setting up the database, its tables need to have the appropriate sizes. The following table provides an overview of the recommended sizes in percent. The database in a productive environment should have a size of 2 - 4 GB. For very complex installations, the database size can be 10 to 20 times as big. Automatically extend the tables for a fixed size from the beginning on and do not limit the extents.

  • RT: 40% - 60%

  • AH: 20% - 30%

  • MELD: 10% - 20%

  • AJPP: 5%

  • RH: 5%

  • Other tables: < 1%

• Make sure that the indexes are reorganized on a regular basis.

Regular Oracle Database Maintenance

• Create tablespaces for the Automation Engine with ASSM (Automatic Segment Space Management).

  In addition to several performance advantages when inserting data, ASSM also allows tables to be transparently reorganized.

• Use of local disks

  If the database is not installed on an external disk subsystem, the data files of both the index and data tablespaces (UC4_INDEX, UC4_DATA) should be kept on physically separate disks in order to achieve maximum I/O throughput.

  In order to further improve the I/O, the tables E*, A*, R* and MELD, as well as the temporary AE tables (UC_TEMP*, MQ*) should be placed in their own tablespaces or data files on physically separate disks. For this purpose, you must create additional data tablespaces (such as UC4_DATA_E, UC4_DATA_A, UC4_DATA_R, UC4_DATA_M, UC4_DATA_T). The final distribution of data files, however, always depends on your requirements and the available infrastructure.

• Reorganization with database means

  You should use Oracle SEGMENT ADVISOR here. It generates a list of all tables/indexes that need to be reorganized. You can also do this manually following but this involves extensive changes in the AE database.

  Example

  Online index reorganization:

  sqlplus> alter index <index_name> rebuild online;

Statistics

The access statistics of the database tables should be updated on a regular basis. This happens with a daily update job (GATHER_STATS_JOB) that is automatically scheduled as part of the database installation, unless you explicitly deselect this option. You can freely select the time when the daily update should take place. Preferably, it takes place when there is high load on the AE system so that you will obtain representative values. If there is no automatic update controlled by Oracle, you can schedule an update in the Automation Engine.

Examples

SQL statement to update the statistics of the database UC4 (1 row):

exec dbms_stats.gather_schema_stats('UC4',options=>'GATHER AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE AUTO', cascade => TRUE);

You can also use a statement to generate better suitable statistics:

exec dbms_stats.gather_schema_stats('UC4',options=>'GATHER AUTO', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL INDEXED COLUMNS SIZE AUTO', cascade => TRUE);

To check if and when the statistics were last updated, use the USER_TABLES view:

sqlplus>select TABLE_NAME,NUM_ROWS,LAST_ANALYZED from USER_TABLES;

Exception

It is not useful to create access statistics for the UC_TEMP* tables (UC_TEMP, UC_TEMP1, UC_TEMP2, UC_TEMP3,). Therefore, you need to delete all statistics that might be available for these tables, and lock the tables so that new statistics can be created.

SQL statements for automatically creating statistics:

exec dbms_stats.delete_table_stats('uc4','UC_TEMP');

exec dbms_stats.lock_table_stats('uc4','UC_TEMP');

exec dbms_stats.delete_table_stats('uc4','UC_TEMP1');

exec dbms_stats.lock_table_stats('uc4','UC_TEMP1');

exec dbms_stats.delete_table_stats('uc4','UC_TEMP2');

exec dbms_stats.lock_table_stats('uc4','UC_TEMP2');

exec dbms_stats.delete_table_stats('uc4','UC_TEMP3'); e

exec dbms_stats.lock_table_stats('uc4','UC_TEMP3');

Fragmentation

It might happen that the number of entries in the AE message queues (MQ* tables) has highly increased (several 10000 entries) because of a malfunction and these tables may be very fragmented. This can negatively affect the performance of the AE system because these tables are used most. To solve the fragmentation problem there are two options, offline and online.

List of MQ tables

MQ tables are tables with “MQ” as table name prefix. It depends on the AE system setup how many MQ tables are present, in particular the number of “MQnCP*” tables might vary.

Example: the following SQL statement retrieves a list of MQ tables.

select TABLE_NAME from user_tables where TABLE_NAME like 'MQ%';

Offline Reorganization of all MQ Tables

This requires an AE system downtime. Shut down the AE system as described in Starting and Ending Server Processes.

Start the AE system with a COLD start: set StartMode=COLD in the file ucsrv.ini . For more information, see Automation Engine. You can also use the context menu in the Service Manager Dialog.

The cold start of the system cleans up the AE message queues, which basically means that they will be truncated. In case the truncation needs to be done manually, you can use the following SQL statements while the AE system is down.

Important! Create a backup before you start modifying the database.

SET SERVEROUTPUT ON

BEGIN

FOR c IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME LIKE 'MQ%')

LOOP

dbms_output.put_line (c.TABLE_NAME || ' Table will be truncated now ...');

EXECUTE immediate 'truncate table ' || c.TABLE_NAME ;

dbms_output.put_line (c.TABLE_NAME || ' Table was truncated.');

END LOOP;

END;

Online Reorganization

The AE system remains online in this case.

It is recommended that a DBA carries out the online reorganization of the tables in order to ensure that the database (MQ* tables) stays available for the AE during the reorganization process. In doing so, lock situations can be avoided, or be detected and resolved immediately.

A best practice example for a procedure that executes such a reorganization is delivered with the DB schema (UC_REORG in the folder Procedure in your Oracle database).

If the count you retrieve is 1000, this table is a candidate for a reorganization. Now run the following procedure:

call uc_reorg('MQ1CP001',3);

The second parameter v_method has to be a number and can have one of the following values:

• 1: STANDARD (default)

  Behavior remains unchanged

• 2: MOVE ONL

  The table is reorganized with ALTER TABLE MOVE ONLINE

• 3: DBMS_REDEF

  The table is reorganized with the DBMS_REDEF.REDEF_TABLE process

Important!

• Values 2 and 3 reorganize BASICFILE and SECUREFILE LOBs and work with versions 12c and higher

• To use value 3 the UC4 user requires the following permissions, otherwise the DBMS_REDEF calls do not work:

  grant execute on sys.dbms_redefinition to <uc4-user>;
  grant redefine any table to <uc4-user>;
  grant create table to <uc4-user>;

Checking the Fragmentation of MQ Tables

The following statement generates check statements for each MQ table. Note that you must run it as a schema owner:

select 'select get_block_count(''' || table_name || ''') from dual;' ResultToExecute from user_tables where table_name like 'MQ%';

You can take the result and execute it to see the current fragmentation. Depending on your environment, a block count above 64 might be considered to be a candidate for a reorganization process. Above 64, you can run a reorganization and monitor the fragmentation in normal and peak load situations. If the count increases, you can use it as a basis for your system.

Example

Result query:

select get_block_count('MQ1CP001') from dual;

Tuning the Database Parameters

Buffer Cache Hit Rate

Basically, the buffer cache hit rate should be more than 98%. The following script is an example for calculating the current value.

Calculating the buffer cache hit rate:

-- DB_BLOCK_BUFFERS: 'buffer cache hit rate %' should be > 98% 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 > 98%');

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;

/

Memory Hit Rate

Basically, the memory hit rate of the sort area should be greater than 99%. The following script is an example for calculating 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;

/

Shared Pool Size

Out of the total number of all accesses, the percentage of unsuccessful accesses to the library cache within the shared pool should, in general, be much smaller than 1%. The following script is an example for calculating 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

Memory Advisor

You can also use the Memory Advisor for this purpose. PGA_AGREGATE_TARGET should be used.

See also:

• Preparing the AE Database - Oracle
• Oracle Parameters
• Automation Engine Database