Oracle

• 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 variable UC_HOSTCHAR_DEFAULT, Automic recommends setting the parameter REPORT_BLKSIZE=8000. In UC_SYSTEM_SETTINGS, also set the parameter REPORT_BLKSIZE=8000 and the parameter LAST_USE=0.
  The parameter LAST_USE 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. Automic strongly recommends automatically extending the tables for a fixed size from the beginning on and not to limit the extents.

Table	Approximate size in percent
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.

Hardware/Infrastructure

• CPU: min. 4 (number of cores)
• RAM: min. 16 GB
• Gigabit network connection between the Automation Engines 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.
• Automic recommends using identical hardware for all cluster nodes.

Database

• 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 for an 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 can be scheduled in the Automation Engine with the following statement

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 the following statement, it generates more suitable statistics in certain cases.

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. For example:

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. Then 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

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

• Fragmentation
  It might happen that the number of entries in the AE message queue (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 that are used most. To solve the fragmentation problem, you stop the AE system (all CP and WP processes) and use the DB statements shown below. Afterwards, you cold start the AE system (StartMode=COLD in the file ucsrv.ini or via the context menu in the ServiceManager dialog).

  SQL statements for defragmenting the MQ* tables (offline)

  truncate table MQCP001;

  truncate table MQCP002;

  truncate table MQCP003;

  truncate table MQCP004;

  truncate table MQCP005;

  truncate table MQPWP;

  truncate table MQSRV;

  truncate table MQLS;

  truncate table MQMEM;

  truncate table MQDWP;

  truncate table MQWP;

  commit;

  You can also reorganize the tables online while the AE is running by using the following statements:

  alter table MQCP001 ENABLE ROW MOVEMENT;

  alter table MQCP001 SHRINK SPACE CASCADE;

  exec dbms_stats.gather_table_stats('UC4','MQCP001',estimate_percent=>100,

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

Alternately, you can also defragment the tables online using ‘alter table <TABLE> move’. in this case, you must rebuild all associated indexes and recreate the access statistics as shown here:

The above statements must be repeated for the tables MQCP002 to MQCPxxx.

alter table MQPWP move;

alter index PK_MQPWP rebuild online;

alter index NK_MQPWP_PRIORITY rebuild online;

exec dbms_stats.gather_table_stats('UC4','MQPWP',estimate_percent=>100,

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

The above statements must be repeated for the tables MQDWP, MQWP and MQQWP.

alter table MQMEM move;

alter index PK_MQMEM rebuild online;

exec dbms_stats.gather_table_stats('UC4','MQMEM',estimate_percent=>100,

method_opt=>'FOR ALL INDEXED COLUMNS',cascade=>TRUE

The above statements are to be repeated for the tables MQSRV to MQLS

• 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.