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

Configuration Recommendations

Regular Oracle Database Maintenance

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:

Important!

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: