Recommendations for Oracle

This topic provides basic maintenance recommendations to help you achieve top performance when maintaining installations with Oracle databases.

This topic provides information on the following:

• General Recommendation
• Configuration Recommendations
• Regular Oracle Database Maintenance
• Hardware/Infrastructure
• Database

General Recommendation

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

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 variable , set REPORT_BLKSIZE=8000.

• In UC_SYSTEM_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).

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

• 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 not to 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

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

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.

  For example, the following SQL statement can be used to get a list of them:

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

  Offline reorganization of all MQ tables

  This requires an AE system downtime.

  1. Shut down the AE system as described here: Starting and Ending Server Processes
  2. Start the AE system with a COLD start (set StartMode=COLD in the file ucsrv.ini or via 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.

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

  Check 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 for a result query

  select get_block_count('MQ1CP001') from dual;

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

  call uc_reorg('MQ1CP001');



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

• Setting Up The Database - Oracle
• Oracle Parameters
• Automation Engine Database Maintenance