Vous êtes ici

Configuration et Optimisation Oracle

Nous sommes ici dans le cas d'une base de données Oracle 9i . Certains paramètres sont gérés de manière plus autonome dans les versions supérieurs d'Oracle.

1)      Ce qu’il faut savoir

Réglage de l’optimizer d’oracle :

-          Optimizer_index_cost_adj : valeur par défaut 100, au plus la valeur est basse, au plus nous allons indiquer à oracle de privilégié l’utilisation des Index, si la valeur est élevé, oracle va plus facilement se tourner vers le FULL ACCESS, donc un scan complet de la base de donnée, et donc, engendrer un grand nombre d’IOPS

-          Optimizer_index_caching : Selon la valeur, ce paramètre précise a oracle si il a plus de chance de trouver des Index en cache ou non, une valeur de 100% va pousser oracle à aller chercher ces index en mémoire RAM, une valeur de 0% va pousser oracle a accéder à la base sans utiliser les index en RAM

-          Optimizer_mode : plusieurs valeurs possibles :

o   ALL_ROWS : l'optimiseur essaie de trouver le plan d'exécution qui a le meilleur temps de réponse global

o   FIRST_ROWS_N : l'optimiseur essaie de trouver le plan d'exécution qui permet de ramener le plus rapidement possible les premières N lignes où N prends les valeurs 1, 10, 100 ou 1000.

o   FIRST_ROWS : l'optimiseur essaie de trouver le plan d'exécution qui permet de ramener le plus rapidement possible le premier enregistrement mais, en utilisant un mixe de coût et heuristique

o   CHOOSE : Oracle va se baser sur les stats des tables et indexes pour optimiser les couts de sa requête

o   RULE : Oracle va se baser uniquement sur les règles définies

La mémoire Oracle

2 Types de mémoire, la SGA, qui est la partie mémoire système de oracle et la partie PGA, qui est une plage mémoire destinée au processus

-          sga_max_size : ce paramètre définit la valeur SGA maximale utilisable par le système, la mémoire SGA contient le pool partagé, la mémoire tampon et le cache des Redo-log. Si nous voulons ajouter plus de mémoire tampon, il faut vérifier la taille du SGA totale du moment

-          Cache de tampon : il est utile de passer par l’outil conseil d’oracle pour vérifier si la mémoire tampon est correctement dimensionnée, au mieux la mémoire tampon sera dimensionné, au moins il y aura d’accès physique

2)      Présentation de l’interface d’administration graphique d’Oracle :

Connexion à la console Oracle Enterprise Manager :

Les variables paramètres :

La partie mémoire avec les boutons conseils permettant l’optimisation :

Attention pour le bon fonctionnement des statistiques oracles, les 2 variables suivantes doivent être paramétrées :

-          DB_CACHE_ADVICE = ON

-          STATISCTICS_LEVEL = TYPICAL

Ici, nous voyons que si je veux ajouter de la mémoire tampon, je dois augmenter la taille max de mon SGA (SGA_MAX_SIZE)

Nous voyons clairement que notre base fera moins d’accès physiques avec un peu plus de mémoire tampon. Cependant nous avons un pourcentage en mémoire cache de 100%, ce qui est très bon.

Utilisation de la ligne de commande pour ces réglages.

ATTENTION, il faut savoir que la plus part des modifications ne peuvent pas être exécutées en direct, il faut les envoyer dans le SPFILE qui sera pris en compte au redémarrage de la base. Aussi bien pour le mode texte que le mode graphique.

3)      Présentation de sqlplus ( ligne de commande )

Pour modifier un paramètre en passant par sqlplus, il faut taper la commande suivante :

Alter system set ‘nom_du_param’=’la_valeur’ scope=spfile

Puis redémarrer la base oracle

Exemple : Modification du paramètre Optimizer_index_cost_adj

1)      Je déclare le nom de mon instance dans la variable oracle_sid

2)      J’exécute sqlplus

3)      Je me logue avec l’utilisateur system

4)      Je commence par voir la valeur actuelle de mon paramètre

5)      Je modifie sa valeur

4)      Ce qu’il faut faire pour optimiser sa base oracle correctement :

Dimensionnement de la mémoire Oracle SGA + PGA de manière plus avancé :

10% de la mémoire physique doit être alloué au système pour Unix, et 20 % pour Windows

Dans ce qu’il reste, donc 80 % dans le cas de Windows, 80% doivent être alloué à la mémoire SGA et 20% à la mémoire PGA.

Vérifier les bonnes performances d’une base oracle :

1)      Vérification du cache tampon

Commande:

SELECT name, value

FROM v$sysstat

WHERE name IN ('db block gets',

'consistent gets', 'physical reads');

Le résultat est :

Le ratio :

Doit être supérieur ou égal à 70%

2)      Vérification du pool partagé

o   Les requêtes partagées :

SELECT sum(pins) "Executions",

sum(reloads) "Défaut de cache",

sum(reloads) / (sum(pins) + sum(reloads))*100 "R"

FROM v$librarycache ;

 

La valeur de R doit être inférieur à 1%, si il est supérieur, il faut augmenter la taille de la mémoire partagée :

Ici c’est parfait.

o   Le cache du dictionnaire d’oracle

SELECT sum(gets) "DC Gets",

sum(getmisses) "DC cache get Misses",

sum(getmisses) / (sum(gets)+sum(getmisses))*100 "R"

FROM v$rowcache ;

R doit être inférieur ou égal à 10%, sinon, il faut augmenter la valeur de la mémoire partagée :

Encore une fois, c’est parfait.

3)      Le cache REDO LOG

C’est l’endroit ou Oracle conservent les informations en cours de modification, avant et après.

Pour contrôler qu’il est bien dimensionné :

SELECT name, value

FROM v$sysstat

WHERE name = 'redo log space requests' ;

La valeur doit être proche de 0, si ce n’est pas le cas, il faut augmenter la taille du log_buffer par palier de 5%

Ici c’est mauvais.

4)      Large_pool_size

Ce paramètre permet de soulager le cache tampon lors de grosse requête.

Dans notre cas, il est à 0.

5)      Lock_SGA

Ce paramètre permet de forcer la mémoire SGA à rester en mémoire Vive, et ne pas passer dans la mémoire virtuelle de la machine.

La mémoire PGA :

1)      Déterminer la quantité de mémoire PGA utilisée par une session :

Select ss.sid,ss.value,sn.name

FROM v$sesstat ss, v$statname sn, v$session se

WHERE ss.statistic#=sn.statistic#

and sn.name in ('session pga memory')

and se.sid=ss.sid and type != 'BACKGROUND';

Ici par exemple la session 9 utilise 875 Ko de mémoire PGA.

o   Sort_area_size, c’est la zone de tri :

Pour vérifier que sa taille est suffisante, il faut exécuter cette commande :

SELECT name, value FROM v$sysstat

WHERE name in ('sorts (memory)', 'sorts (disk)');

Si le nombre de tri sur disque est élevé et qu’il augmente, c’est qu’il faut augmenter la valeur du sort_area_size

ATTENTION : Une mémoire PGA allouée trop faible peut engendrer des fermetures de sessions et processus non prévu

Tracer une session :

-          Pour voir la requête qu’une application exécute

-          Pour voir les temps cpu, les attentes, etc….

 

1)      Déterminer les paramètres sid et serial# d’une session :

select  sid, serial#from v$session;

2)      Exécuter la trace

exec sys.dbms_system.set_bool_param_in_session(sid, serial#, 'timed_statistics', true);

exec sys.dbms_system.set_int_param_in_session(sid, serial#,'max_dump_file_size', 2147483647);

exec sys.dbms_system.set_ev(sid, serial#,10046, 8, '');

Exemple pour la session sid = 22 et serial# = 25991 :

ATTENTION, ça ne sert à rien d’activer la trace pour toute une journée, il faut l’activer de manière brève et utiliser ou passer les requêtes à votre application rapidement pour bien cerner la partie que vous voulez analyser (pas plus de 10 minutes pour une trace en moyenne)

3)      Désactiver la trace

 exec sys.dbms_system.set_ev(sid, serial#,10046, 0, '');

Exemple pour la session sid = 22 et serial# = 25991 :

4)      Trouver le fichier trace

Le fichier trace ce trouve dans le répertoire qui est définit dans la variable USER_DUMP_DEST d’oracle.

5)      Lire et comprendre le fichier trace

·         len : Longueur de l'instruction.

·         dep : Profondeur récursive du curseur.

·         uid : User id de l'utilisateur qui analyse le curseur.

·         oct : Type de commande Oracle.

·         lid : User id du privilège.

·         tim : Timestamp en microsecondes

·         hv : Identifiant du hash

·         ad : Adresse de la commande

L’instruction SQL

Les lignes XCTEND marquent la fin d'une transaction.

·         rlbk est à 1 si un rollback a été effectué, 0 dans le cas contraire.

·         rd_only               est à zero si des modifications ont étés effectuées, 1 sinon.

Les lignes PARSE, EXEC, FETCH sont celles connues : interpréter une commande, l'exécuter, récupérer une ligne d'un curseur.

·         c             temps CPU time (centièmes de seconde).

·         e             temps elapsed (centièmes de seconde).

·         p             lectures physiques.

·         cr            buffers lu en mode consistent.

·         cu           buffers lus en mode courant.

·         mis        curseurs non trouvés dans le cache.

·         r              nombre de lignes.

·         dep       profondeur de la récursivité (0 = ordre courant, 1 et plus = appel récursif).

·         og          optimizeur: 1=All_Rows, 2=First_Rows, 3=Rule, 4=Choose

·         tim         timestamp (centièmes de seconde). Base pour mesurer le temps passé entre deux actions.

Les lignes stat constituent la source pour l'explain plan interprété.

Les lignes WAIT nous informent des attentes système pour le curseur en cause:

·         Nam      L'objet attendu.

·         Ela          Temps passé pour l'opération.

www.le-gas.fr : Le guide de l'admin Systeme