Base de Donn

1
Fonctionnement et Tuning de la SGA sous Oracle

• TOUAMI Khaled

2
Plan

• Structure d une base de données Oracle
• Architecture technique dune base Oracle
• System Global Area (SGA)
• Fonctionnement
• Optimisation
• Statpack
• TOAD

3
Structure dune base Oracle
4
Définitions Base de Données(database) Ense
mble de fichiers de données (data files), des
fichiers de contrôle(control files) et des
fichiers journaux de transactions (redo logs),
formant la structure permanente d un serveur
oracle. Instance Elle comprend la System
Global Area (SGA), les processus d arrière-plan
associés et les structures correspondantes de
mémoire partagée. Elle est transitoire est crée à
chaque démarrage d instance.
5

• Processus
• DBWR( Database Writer)
• Transfert les blocs de données modifiées du data
  buffer dans les fichiers disque de la base de
  données.
• Le paramètre d initialisation
  DB_WRITERS_PROCESSES permet de démarrer
  plusieurs processus DBWR, afin d augmenter le
  taux d écriture sur disque.
• LGWR(Log Writer)
• Écrit les données modifiées depuis la zone
  mémoire redo-log buffer dans les fichiers
  redo-log.
• CKPT(Checkpoint)
• Signe à des intervalles réguliers, le moment
  d écriture des données modifiées dans la SGA
  dans les fichiers de la base de données.
• Il modifié lensemble des fichiers qui composent
  la base de données, pour que le numéro d ordre
  du plus récent checkpoint soit inscrit en en-tête
  de fichier.
• SMON(System Monitor)
• Surveille la base de données lors de son
  démarrage, puis au cours de son fonctionnement.
• PMON(Processus Monitor)
• Nettoie les transactions défaillantes, comme
  celle d un poste client arrêter brutalement
  durant une transaction (zonez allouées libérées,
  les verrous posés sont supprimés, les ressources
  affectées sont annulées).
• Vues Vbgprocess

6
(No Transcript)
7
System Global Area (SGA)

• Définition C est une zone mémoire qui regroupe
  un ensemble de structures de mémoire partagées
  qui contiennent les données, et les informations
  de contrôle le plus souvent utilisées d une
  instance oracle.
• Elle comprend
• Shared Pool Area
• Data buffer Cache
• Redo-log Buffer
• Volume initial de la SGA La SGA doit
  représenter au moins 2 de la taille totale de la
  base de données (physique).
• Elle est repartie comme suit
• 50 Cache de données (database buffer cache)
• 40 Shared Pool
• 10 Redo Log Buffers
• Espace libre dans la SGA Pour connaître
  l espace libre dans la SGA, on interroge la vue
  VSGASTAT
• select from vsgastat where name 'free
  memory'

8
Shared Pool Area

• Définition C est une partie de la SGA dans
  laquelle les instructions SQL, les procédures
  stockées et les informations spécifiques du
  dictionnaire sont enregistrées en mémoire.
• Elle est gérée au moyen d un algorithme LRU
  (Least Recently Used).
• Elle comprend
• Library cache il contient le code SQL des
  instructions et les plans d exécutions associés
  , les blocs PL/SQL et des classes Java.
• Dictionary cache contient des méta données
  issues du dictionnaire de données décrivant la
  structure et la sécurité de tous les objets
  inclus dans les instructions SQL récemment
  utilisées.
• Paramètres
• SHARED_POOL_SIZE Définit la taille du pool
  partagé, en octets.
• SHARED_POOL_RESERVED_SIZE Réserve une part du
  pool partagé pour des objets de grande taille (
  package, procédure, fonction).
• Vues de performance dynamique VRowcache,
  VLibrabyCache, VSqlArea

9
Analyse du code SQL
10

• PARSE
• Vérification de la syntaxe de l instruction
  SQL.
• Réalisation de la résolution d objets et les
  contrôles de sécurité pour l exécution du code.
• Construction de larbre danalyse
• Développement du plan d exécution pour
  linstruction SQL.
• EXECUTE
• Application du plan d exécution.
• Lecture des blocs de données du fichier en
  mémoire.
• Manipulation des données en mémoire (Insert,
  Update, Delete).
• FETCH (select)
• Renvoie des lignes au processus utilisateur.

11

• Qu est ce qui l optimisation
• C est un ensemble d actions de modifications
  ciblés au niveau des composants de la base de
  données pour atteindre des objectifs de
  performance, c est à dire augmenter le débit et
  réduire les temps de réponse.
• Finalité Satisfaction des utilisateurs.
• Méthodologie d optimisation
• 1. Définition des objectifs d optimisation
  raisonnable.
• 2. Mesurez et documentez les performances
  actuelles.
• 3. Identifier les goulets d étranglement actuels
  sur Oracle
• 4. Identifier les goulets d étranglement actuels
  du système d exploitation.
• 5. Optimiser les composants concernés
  (Application, Instance , Base de données, E/S,
  OS).
• 6. Application des procédures de contrôle des
  changements,
• 7. Mesurez et documenter les performances
  actuelles,
• 8. Répétez les étapes 3 à 7 jusqu à ce que les
  objectifs soient atteintes.

12
Optimisation de la Shared Pool Area

• Les principaux problèmes liés aux performances du
  pool partagé
• Utilisation intense des ressources CPU, causé
  par des analyses excessives.
• Erreur ORA-4031 manque de place mémoire.
• Mesures de performance
• a)- library cache
• La vue VLIBRARYCACHE contient lensemble des
  informations relatives aux activités de la
  library cache depuis le dernier démarrage de
  l instance.

13

14
Get Désigne le nombre de requêtes pour un
ou plusieurs éléments du cache de bibliothèque.
Gethit Le nombre de fois qu un objet à été
trouvé en mémoire. Gethitratio C est le
rapport entre le Gethits et le Gets. Pins le
nombre d exécutions d un élément donné. Pinhits
le nombre de fois qu un élément a été exécuté
en mémoire. Pinhitratio C est le rapport
entre le Pinhits et le Pins. Reloads le nombre
de manqués ( nombre de demandes infructueuses
ayant nécessité un rechargement en cache.
15
(No Transcript)
16
Library Cache hit-ratio Select SUM
(pins-reloads)/ SUM (pins) "Library cache hit
ratio" FROM VLIBRARYCACHE Library cache hit
ratio ---------------------------- ,997253872 (
gt au seuil de 85) Recommandation Si le
library cache hit ratio lt 85, augmentez la
valeur du paramètre SHARED_POOL_SIZE Nombre
d analyse réalisées Select STATISTIC, NAME ,
VALUE from vsysstat where name like 'parse
count' STATISTIC NAME
VALUE ----------
----------------------------------
---------------- 152 parse
count (total) 960796 153
parse count (hard)
98815
17
Reload ratio C est le pourcentage
d exécutions qui ont nécessité une nouvelle
analyse. Select sum(pins) "EXECUTIONS" ,
sum(RELOADS) "MISSES", sum(RELOADS)/sum(pins)
"RELOAD RATIO" from vlibrarycache EXECUTIONS
MISSES RELOAD RATIO -----------------
- ---------------- -----------------------
14069958 38637
,274606363 Select sum(pins) "EXECUTIONS" ,
sum(RELOADS) "MISSES", sum(RELOADS)/sum(pins)
"RELOAD RATIO" from vlibrarycache where
namespace 'TRIGGER' EXECUTIONS MISSES
RELOAD RATIO --------------------------------
------------------------- 337
87 ,258160237 Si la valeur
du reload-ratio gt 1, On augmente la valeur du
paramètre SHARED_POOL_SIZE.
18
Identification des objets de grande taille
Select name, type, loads, executions from
vdb_object_cache where type in ('PACKAGE',
'PACKAGE BODY','FUNCTION','PROCEDURE') and owner
like 'AGNES  NAME TYPE
LOADS EXECUTIONS
SHARABLE_M ---------------------------------------
--------------------------------------------------
--------------------------------------------- NOMB
RECLIENT PROCEDURE 3
9 8070 COMMANDE_EXISTE
FUNCTION 2 17
13333 AFFICHER PROCEDURE
3 61 9218 MAJOR
PROCEDURE 6 7
8335 Remarque Lorsqu une
procédure incluse dans un package est appelée,
c est l ensemble du package qui chargé en
mémoire et analysé.
19
(No Transcript)
20
Dictionary cache hits-ratios
21
Calcul du dictionary cache hit-ratio Select
(sum(gets-getmisses))/sum(gets)100 "Dictionary
Cache Hit ratio" from VRowcache Dictionary -
--------- 94,0434197 Une valeur du dictionary
cache hit-ratio gt 85 est satisfaisante. Le
Keeping Pour améliorer les performance on fixe
des objets de grande taille dans la SPA. SQLgt
exec dbms_shared_pool.keep(nom de l objet à
fixer ) Quels sont les objets fixés Select
Owner, Name, Type, Sharable_mem, Kept from
VDB_OBJECT_CACHE where Type in
(FUNCTION ,PACKAGE,PACKAGE
BODY , PROCEDURE) Order by Owner, Name
22
DATABASE BUFFER CACHE

• 1)-Fonctionnement
• Les données sont écrites en mémoire par blocs
  (si elles ne s y trouvent pas déjà) avant de
  pouvoir être manipulées en lecture ou en
  écriture.
• La quantité de mémoire disponible pour
  enregistrer ces blocs est limitée, de telle sorte
  que des blocs doivent être remplacés par des
  blocs plus récents en suivant un mécanisme de
  gestion appelée algorithme LRU.
• Un bloc est dit  dirty si son contenu a
  changé. Oracle n autorise pas de nouvelle
  données à prendre cette place, tant que ce
  contenu na pas été écrit sur disque.
• Une fois le bloc écrit sur le disque, il devient
  disponible pour être réutilisé, il est dit
   free .
• Exemple
• 2)-Paramètres de configuration
• DB_BLOCK_SIZE (Taille d un bloc en octets)
• DB_BLOCK_BUFFER (Nombre de blocs qui peuvent
  être enregistrés)
• - La taille d un bloc détermine la taille de
  chacun des buffers.
• - La taille totale du cache de données
  DB_BLOCK_BUFFER DB_BLOCK_SIZ .

23
ANALYSE DE DATABASE BUFFER CACHE a)-Le cache
hit-ratio C est le rapport entre le nombre de
fois où un bloc est demandé et le nombre de fois
où le cache de données d oracle a été capable de
renvoyer la valeur par une lecture logique plutôt
que par une lecture physique. CHR100(1-(physic
al reads / (consistent gets db block gets -
physical reads))) Select 1- (phy.value / (
cons.value db.value - phy.value)) from
vsysstat phy, vsysstat cons, vsysstat
db where phy.name 'physical reads' and
cons.name 'consistent gets' and db.name 'db
block gets  b)-Quels objets utilisent la plus
grande part du cache de données Select o.owner,
o.object_type, o.object_name, count(b.objd) from
vbh, dba_objects o where b.objdo.objects_id grou
p by o.owner, o.object_type, o.object_name having
count(b.objd) gt (select to_number(value0.5) from
vparameter
where namedb_block_buffers)
24
c)-Les évènements dattente Pour déterminer les
évènements d attentes qui affectent le cache de
données, on interroge la vue VSESSION_WAIT. Sele
ct SW.Sid, S.Username, Substr(SW.Event,1,35),
SW.Wait_Time from VSESSION S, VSESSION_WAIT
SW where SW.Event not like 'SQlNet' and
SW.SidS.Sid Order by SW.Wait_Time, SW.Event Les
évènements d attente courants relatifs au cache
de données Buffer busy waits
Indique une attente d un buffer dans le cache de
la base de données. Free buffer waits
Manque de buffers libres dans le cache de
données Db file sequential read Signale des
attentes associées à une lecture excessif
d index. Db file scattered read Signale
des attentes associées à un balayage complet
d une table. Sources dinformations VWAITSTAT,
VSYSTEM_EVENT, VSESSION_WAIT, STATPACK REPORT
25
Redo Log Buffer

• 1)-Fonctionnement
• Un processus utilisateur lance une instruction
  DML.
• Oracle affecte un identificateur de transaction
  à cette opération.
• Le processus serveur associé au processus
  utilisateur transfère en mémoire les données
  nécessaires, puis verrouille les lignes
  concernées qui doivent subir des manipulations.
• Le processus serveur écrit dans le redo log
  buffer l image des lignes avant les
  modifications (before image).
• Le processus serveur mis à jour les lignes de
  données.
• Le processus serveur écrit dans le redo log
  buffer l image qui suit la transaction (after
  image).
• Les données de redo log buffer sont transcrit
  sur disque lorsque survient l un des évènements
  suivants
• Chaque fois qu une période de trois secondes
  s est écoulée.
• Lors d un commit.
• Lorsque l expression MIN(1MB, LOG_BUFFER/3) est
  vérifiée.
• Au moment des Checkpoints,
• Lorsqu il est déclenché par le processus DBWR

26

• B)-Paramètres de configuration
• LOG_BUFFER (Définit la taille en
  octets)
• C)- Les évènements d attentes liés au redo log
  buffer
• Log buffer space Indique un problème potentiel
  du LGWR.
• Log file parallel write Signale une attente
  liée à l écriture de journaux sur le disque.
• Log file sync Signale des attentes liées à un
  vidage du journal lors de la validation (commit)
  par un utilisateur.

27
ANALYSE DE REDO LOG BUFFER
a)-Redo log hit-ratio Select name, value
from VSYSSTAT where name in ('redo buffer
allocation retries','redo entries')
NAME
VALUE ------------------------------------
-------- ---------------- redo entries
2674558 redo buffer
allocation retries 83 redo
buffer allocation retries indique le nombre
d attente avant décrire dans le redo log
buffer. Select
100(a.value/b.value) "redo buffer retries ratio"
from vsysstat a, vsysstat b
where a.name'redo buffer allocation retries'
and b.name'redo entries'
redo buffer retries ratio
-----------------------------
0,003102836
28
Attente due à la non disponibilité du fichier
redo log Select name, value from VSYSSTAT
where name'redo log space requests' NAME

VALUE --------------------------------------
---------------------------- redo log space
requests 64
29
STATPACK

• Un outil de diagnostic.
• Il enregistre un grand nombre de données
  relatives aux performances.
• Calcul les ratios de performance.
• Il enregistre dans un schéma permettant une
  utilisation ultérieure
• Possibilité de faire une comparaison avec les
  données d exécutions antérieures.
• Installation
• Lancer le script ORACLE_HOME/rdbms/admin/spcrea
  te.sql (création du schéma statpack et de user
  PERFSTAT)
• Exécution
• SQLgt connect perfstat/perfstet
• SQLgt execute statspack.snap
• Analyse des résultat
• SQlgt_at_spreport

30
STATSPACK report for DB Name DB Id
Instance Inst Num Release OPS Host
---------- ----------- ---------- --------
---------- ---- ---------- Test
204079298 ORCL 1 8.1.5.0.0 NO
azuro
Snap Length Start
Id End Id Start Time End
Time (Minutes) -------- --------
-------------------- --------------------
----------- 1 2 25-Sep-00
003621 25-Sep-00 003810 1.82
Cache Sizes


db_block_buffers 8192

db_block_size 2048
log_buffer
163840
shared_pool_size 15728640


Load Profile



Per Second Per Transaction
---------------
--------------- Redo
size 4,893.39 533,380.00
Logical reads
37.88 4,129.00
Block changes 22.23
2,423.00 Physical reads
4.26 464.00
Physical writes 7.16
780.00 User
calls 0.02 2.00
Parses
2.04 222.00
Hard parses 0.06
7.00 Sorts
0.35 38.00
Instance Efficiency Percentages (Target 100)


Buffer Nowait Ratio 100.00

Buffer Hit Ratio 88.76
Library Hit Ratio
94.08
Redo NoWait Ratio 99.69
In-memory
Sort Ratio 94.74
Soft Parse Ratio
96.85
Latch Hit Ratio
100.00
31
TOAD(Outil d administration)
32
(No Transcript)
33
BIBLIOGRAPHIE

• Oracle 8i DBA LONELY.K., THERIAULT.M Oracle
  Press.
• Optimisation des performances sous Oracle - GAJA
  KRISHNA, VAIDYANATHA, KIRTIKUMAR Oracle Press.
• Oracle 9i sous Linux - BRIARD.G Eyrolles
• Oracle OCP Oracle9i database FundamentalsI-II
  Exam Guides.
• C.Shallahammer,  Direct Contention
  Identification Using Oracle s Session wait
  Tables 
• C.Millsap,  Oracle Performance problem
  Diagnossis 
• www.hotsos.com
• www.oraperf.com
• technet.oracle.com/deploy/performance/
• www.orapub.com

34
Questions ?