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.

This page includes the following:

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 Automic Automation 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 variable, set the REPORT_BLKSIZE= parameter to 8000, see UC_HOSTCHAR_DEFAULT - Host Characteristics

  • In the UC_SYSTEM_SETTINGS variable, 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.

      For more information, see UC_SYSTEM_SETTINGS - Systemwide Settings.

  • 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 TSNAMES.ORA file, 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 table spaces 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 table spaces (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 table spaces or data files on physically separate disks. For this purpose, you must create additional data table spaces (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 UC4 database (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');

Optimizing Oracle statistics using TOM_UC4_Oracle_Histograms

You can use the TOM_UC4_Oracle_Histograms table to define when which table should be analyzed. You can select PEAK or OFF PEAK, and decide for which columns you want to generate histograms.

TOM_UC4_Oracle_Histograms (

Table_Name VARCHAR2(30) NOT NULL,

Column_name VARCHAR2(30),

Collection_Type VARCHAR2(1) --- P for PEAK, O for OFF PEAK

);

Examples

To analyze the MQPWP table in PEAK times, insert the following in TOM_UC4_Oracle_Histograms

(`MQPWP`,null,`P`);

To analyze the EH table OFF PEAK, insert

(`EH`,null,`O´);

To analyze the AH table OFF PEAK, and to generate a histogram for the AH_STATUS, insert

Commit;

When you are working with patches, you can make changes directly through the TOM_UC4 package.

Examples

To calculate an additional histogram for ANY_COLUMN and switch to PEAK:

Begin

Tom_uc4.add_histogram(´AH´,´P´,´ANY_COLUMN´);

Commit;

End;

/

To create new statistics for PEAK, use

Begin

Tom_uc4.histogram_maintanance (`P´);

End;

/

Index recommendations

To query current recommendations, use:

Select * from TABLE ( tom_UC4.create_index_reorg_script());

The output is a script that you can execute immediately with EXECUTE IMMEDIATE, or you can write it to a file, and call the file accordingly.

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. For more information about shutting down the AE system, see Starting and Stopping Server Processes.

Start the AE system with a COLD start (set StartMode=COLD in the INI file (ucsrv.ini) of the Automation Engine, see Automation Engine) 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.

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 block count you retrieve is high (above the normal value for this environment, for example, above 128 block for the MQ* table), this table is a candidate for a reorganization. Now run the following procedure:

call uc_reorg('<TableName>', <Mode>, <DetailedOutput>);

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

  • 1: STANDARD (default)

    Behavior remains unchanged (shrink space compact used)

  • 2: MOVE ONLINE (optional)

    The table is reorganized with ALTER TABLE MOVE ONLINE

  • 3: DBMS_REDEF (optional)

    The table is reorganized with the DBMS_REDEF.REDEF_TABLE process

    Values allowed: TRUE or FALSE

    If the value is set to TRUE, make sure that DBMS.OUTPUT is enabled for the session to get more detailed information

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;

-- RESTULT: 200

-- Make sure DBMS.OUTPUT is enabled on the session as parameter 3 is set to true – to run the table reorganization with ONLINE MOVE:

call uc_reorg('MQ1CP001', 2, TRUE);

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", zo_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: