Recommandations pour Oracle

Si vous êtes administrateur de base de données, vous souhaitez que les performances de votre base de données soient optimales. Cette rubrique propose des recommandations de base pour les installations avec bases de données Oracle.

Cette rubrique contient les sujets suivants :

Recommandations générales

Recommandations relatives à la configuration

Maintenance régulière de la base de données Oracle

Statistiques

Les statistiques d'accès des tables de base de données doivent être régulièrement actualisées. Cela se fait avec un job de mise à jour quotidienne (GATHER_STATS_JOB) qui est automatiquement planifié comme faisant partie de l'installation de la base de données, sauf si vous désélectionnez cette option. Vous pouvez librement sélectionner l'heure de la mise à jour quotidienne. Elle doit se faire de préférence lorsque le système AE subit de fortes charges, afin que vous puissiez obtenir des valeurs représentatives. En l'absence de mise à jour automatique contrôlée par Oracle, vous pouvez planifier une mise à jour dans Automation Engine.

Exemples

Instruction SQL pour mettre à jour les statistiques de la base de données UC4 (1 ligne) :

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

Vous pouvez également utiliser une instruction pour générer des statistiques mieux adaptées :

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

Afin de vérifier l'emplacement et le moment des dernières statistiques, accédez à la vue USER_TABLES :

sqlplus>select TABLE_NAME,NUM_ROWS,LAST_ANALYZED from USER_TABLES;

Exception

Il n'est pas nécessaire de créer des statistiques d'accès pour les tables UC_TEMP* tables (UC_TEMP, UC_TEMP1, UC_TEMP2, UC_TEMP3,). Vous devez donc supprimer toutes les statistiques éventuellement disponibles pour ces tables, et verrouiller les tables, afin de permettre la création de nouvelles statistiques.

Instructions SQL pour la création automatique de statistiques :

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

Il peut arriver que le nombre d'entrées des queues de message AE (tables MQ*) augmente considérablement (plusieurs 10000 d'entrées) en raison d'un dysfonctionnement, et que ces tables soient très fragmentées. Cela peut avoir un impact négatif sur les performances du système AE car ces tables sont les plus utilisées. Pour résoudre ce problème de fragmentation, il existe deux options, en ligne et hors ligne.

Liste des tables MQ

Les tables MQ sont des tables dont le nom comporte le préfixe "MQ". Le nombre de tables MQ dépend de la configuration du système AE, et en particulier le nombre de tables "MQnCP*", qui peut varier.

Exemple : l'instruction SQL suivante extrait une liste de tables MQ.

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

Réorganisation hors ligne de toutes les tables MQ

Cela nécessite un arrêt du système AE. Arrêtez le système AE, tel que décrit dans Démarrer et arrêter des processus serveur.

Lancez le système AE avec un démarrage à froid, "COLD" (définissez StartMode=COLD dans le fichier ucsrv.ini ou via le menu contextuel de la boîte de dialogue Service Manager).

Le démarrage à froid du système nettoie les queues de messages AE, ce qui implique, en principe, qu'elles seront tronquées. Pour une troncation manuelle, vous pouvez utiliser les instructions SQL suivantes, pendant que le système AE est arrêté.

Important ! Effectuez une sauvegarde avant de commencer à modifier la base de données.

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;

Réorganisation en ligne

Le système AE reste en ligne, dans ce cas.

Il est recommandé de confier la réorganisation en ligne des tables à un administrateur de base de données (DBA), afin de s'assurer que la base de données (tables MQ*) reste disponible pour AE pendant le processus de réorganisation. Cela permet d'éviter les situations de blocage, ou de les détecter et de les résoudre immédiatement.

Vous trouverez un exemple de meilleure pratique pour une procédure exécutant ce type de réorganisation avec le schéma de la BDD (UC_REORG dans le dossier Procedure de votre base de données Oracle).

Vérifier la fragmentation des tables MQ

L'instruction suivante génère des instructions de vérification pour chaque table MQ. Notez que vous devez l'exécuter en tant que propriétaire de schéma :

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

Vous pouvez prendre le résultat et l'exécuter pour voir la fragmentation actuelle. Selon votre environnement, un nombre de blocs supérieur à 64 peut donner lieu à une réorganisation. Au delà de 64, vous pouvez exécuter une réorganisation et surveiller la fragmentation en situation de charges normale et élevée. Si ce nombre augmente, vous pouvez l'utiliser comme base pour votre système.

Exemple

Demande de résultat :

select get_block_count('MQ1CP001') from dual;

Si le nombre obtenu est 1000, cette table est candidate pour une réorganisation. Exécutez alors la procédure suivante :

call uc_reorg('MQ1CP001');

Adapter les paramètres de base de données

Taux de réussite du cache tampon

En principe, le taux de réussite du cache tampon doit être supérieur à 98%. Le script suivant est un exemple de calcul de la valeur actuelle.

Calculer le taux de réussite du cache tampon :

-- 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;

/

Taux de réussite mémoire

En principe, le taux de réussite mémoire de la zone de tri doit être supérieur à 99%. Le script suivant est un exemple de calcul de la valeur actuelle.

-- 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;

/

Taille du pool partagé

Sur le nombre total de tous les accès, le pourcentage d'accès infructueux au cache de la bibliothèque de pool partagé doit généralement être largement inférieur à 1%. Le script suivant est un exemple de calcul de la valeur actuelle.

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

Vous pouvez également utiliser l'optimiseur de mémoire Memory Advisor pour cela. PGA_AGREGATE_TARGET doit être utilisé.

Voir aussi :