Base de Données avancées

Base de Données avancées
Fonctionnement et Tuning de la SGA sous Oracle
TOUAMI Khaled
1
Base de Données avancées
Plan
• Structure d ’une base de données Oracle
• Architecture technique d’une base Oracle
• System Global Area (SGA)
– Fonctionnement
– Optimisation
• Statpack
• TOAD
2
Base de Données avancées
Structure d’une base Oracle
Instance Oracle
SGA
Processes
Base de données
Oracle
3
Base de Données avancées
Définitions
Base de Données(database)
Ensemble 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.
4
Base de Données avancées
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é l’ensemble 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 : V$bgprocess
5
Base de Données avancées
SGA
Shared Pool
Processus SNPnnn
Processus Pnnn
Data Dictionnary Cache
Data Buffer Cache
Processus LCKnn
Library Cache
Redo-Log Buffer
DBWR
LGWR
SMON
PMON
CKPT
Processus dédié à un
utilisteur
Processus dédié à un
utilisateur
Démarrage/arrêt de l’instance
Listener
Oracle Net
Fichiers de
données
Fichiers RedoLog
Fichiers de
contrôle
Fichier Init.ora
Fichiers
Listener.ora
Réseau
ARCH
Programme
utilisateur
Programme
utilisateur
Archivage des
fichiers Redo-Log
Fichiers
TNSNAMES.
ORA
Architecture Technique
6
Base de Données avancées
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 V$SGASTAT
select * from v$sgastat where name = 'free memory'
Mesure de performance : la mesure de la performance revient à calculer les différents indicateurs ratios
d ’analyse correspondants aux différentes partis de la SGA.
7
Base de Données avancées
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 : V$Rowcache, V$LibrabyCache, V$SqlArea
8
Base de Données avancées
Analyse du code SQL
Parse
1
User Process
2
3
Execute
Fetch (Select)
Server Process
Requête
utilisateur
SGA
9
Base de Données avancées
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 l’arbre d’analyse
• Développement du plan d ’exécution pour l’instruction 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.
10
Base de Données avancées
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.
11
Base de Données avancées
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 V$LIBRARYCACHE contient l’ensemble des informations relatives aux activités de la library cache
depuis le dernier démarrage de l ’instance.
12
Base de Données avancées
DESC V$LIBRARYCACHE
Nom de la colonne
Null?
------------------------------ -------NAMESPACE
GETS
GETHITS
GETHITRATIO
PINS
PINHITS
PINHITRATIO
RELOADS
INVALIDATIONS
DLM_LOCK_REQUESTS
DLM_PIN_REQUESTS
DLM_PIN_RELEASES
DLM_INVALIDATION_REQUESTS
DLM_INVALIDATIONS
Type
---VARCHAR2(15)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
13
Base de Données avancées
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.
14
Base de Données avancées
select NAMESPACE , gets, gethits, gethitratio, pins, pinhits, pinhitratio,
reloads from v$librarycache
order by 1
NAMESPACE
GETS
GETHITS
GETHITRATI
PINS
PINHITS
PINHITRATI
RELOADS
--------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------BODY
94
86 ,914893617
97
83 ,855670103
4
CLUSTER
3648
3639 ,997532895
4861
4847 ,997119934
1
INDEX
180116
120054 ,666537121
180115
120054 ,666540821
0
OBJECT
0
0
1
0
0
1
0
PIPE
0
0
1
0
0
1
0
SQL AREA
951964
870446 ,914368611
13407693
13226440 ,986481418
15085
TABLE/PROCEDURE
207217
138097 ,666436634
410495
285940 ,696573649
22616
TRIGGER
143
138 ,965034965
206
120 ,582524272
63
15
Base de Données avancées
Library Cache hit-ratio
Select SUM (pins-reloads)/ SUM (pins)
FROM V$LIBRARYCACHE
"Library cache hit ratio"
Library cache hit ratio
---------------------------,997253872
( > au seuil de 85%)
Recommandation : Si le library cache hit ratio < 85%, augmentez la valeur du
paramètre SHARED_POOL_SIZE
Nombre d ’analyse réalisées
Select STATISTIC#, NAME , VALUE from v$sysstat
where name like 'parse count%'
STATISTIC# NAME
VALUE
---------- ---------------------------------- ---------------152
parse count (total)
960796
153
parse count (hard)
98815
16
Base de Données avancées
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 v$librarycache
EXECUTIONS
MISSES
RELOAD RATIO
------------------ ---------------- ----------------------14069958
38637
,274606363
Select sum(pins) "EXECUTIONS" , sum(RELOADS) "MISSES",
sum(RELOADS)/sum(pins) "RELOAD RATIO"
from v$librarycache
where namespace = 'TRIGGER'
EXECUTIONS
MISSES
RELOAD RATIO
--------------------------------------------------------337
87
,258160237
Si la valeur du reload-ratio > 1, On augmente la valeur du paramètre SHARED_POOL_SIZE.
17
Base de Données avancées
Identification des objets de grande taille
Select name, type, loads, executions from v$db_object_cache
where type in ('PACKAGE', 'PACKAGE BODY','FUNCTION','PROCEDURE')
and owner like 'AGNES ’
NAME
TYPE
LOADS
EXECUTIONS
SHARABLE_M
-------------------------------------------------------------------------------------------------------------------------------------NOMBRECLIENT
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é.
18
Base de Données avancées
B)- Dictionary Cache
desc v$rowcache
Nom de la colonne
Null?
------------------------------ -------CACHE#
TYPE
SUBORDINATE#
PARAMETER
COUNT
USAGE
FIXED
GETS
GETMISSES
SCANS
SCANMISSES
SCANCOMPLETES
MODIFICATIONS
FLUSHES
DLM_REQUESTS
DLM_CONFLICTS
DLM_RELEASES
Type
---NUMBER
VARCHAR2(11)
NUMBER
VARCHAR2(32)
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
NUMBER
PARAMETER: Nom de de l ’élément de dictionnaire de données.
GETS : Cumul des demandes d’informations sur l’élément.
GETMISSES : Cumul des demandes manqués sur l ’élément
19
Base de Données avancées
Dictionary cache hits-ratios
SQLWKS> select parameter, gets, getmisses, (gets-getmisses)/(gets) "HIT RATIO" ,
modifications from v$rowcache
where gets != 0
PARAMETER
GETS
GETMISSES
-------------------------------- ---------- ---------dc_free_extents
90595
952
dc_used_extents
2609
825
dc_segments
160997
78607
dc_tablespaces
12486
56
dc_tablespace_quotas
78
15
dc_users
157370
43
dc_rollback_segments
3091309
52
dc_objects
182943
128284
dc_constraints
26
15
dc_object_ids
115365
13330
dc_synonyms
670
120
dc_sequences
35
3
dc_usernames
142586
26
dc_database_links
2
2
dc_histogram_defs
76787
30591
dc_profiles
123
1
dc_user_grants
136588
42
HIT RATIO MODIFICATI
---------- ---------,989491694
1585
,683786892
1691
,511748666
1254
,995514977
0
,807692308
50
,999726759
2
,999983179
41
,298776122
377
,423076923
26
,88445369
108
,820895522
0
,914285714
17
,999817654
2
0
0
,601612252
9856
,991869919
0
,999692506
0
20
Base de Données avancées
Calcul du dictionary cache hit-ratio
Select (sum(gets-getmisses))/sum(gets)*100 "Dictionary Cache Hit ratio"
from V$Rowcache
Dictionary
---------94,0434197
Une valeur du dictionary cache hit-ratio > 85% est satisfaisante.
Le Keeping
Pour améliorer les performance on fixe des objets de grande taille dans
la SPA.
SQL> exec dbms_shared_pool.keep(‘nom de l ’objet à fixer ’)
Quels sont les objets fixés
Select Owner, Name, Type, Sharable_mem, Kept from V$DB_OBJECT_CACHE
where Type in (‘FUNCTION ’,’PACKAGE’,’PACKAGE BODY ’, ’PROCEDURE’)
Order by Owner, Name
21
Base de Données avancées
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 n’a 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 .
22
Base de Données avancées
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.
CHR=100*(1-(physical reads / (consistent gets + db block gets - physical reads)))
Select 1- (phy.value / ( cons.value + db.value - phy.value))
from v$sysstat phy, v$sysstat cons, v$sysstat 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 v$bh, dba_objects o
where b.objd=o.objects_id
group by o.owner, o.object_type, o.object_name
having count(b.objd) > (select to_number(value*0.5) from v$parameter
where name=‘db_block_buffers’)
23
Base de Données avancées
c)-Les évènements d’attente
Pour déterminer les évènements d ’attentes qui affectent le cache de données, on interroge la vue
V$SESSION_WAIT.
Select SW.Sid, S.Username, Substr(SW.Event,1,35), SW.Wait_Time
from V$SESSION S, V$SESSION_WAIT SW
where SW.Event not like 'SQl*Net%'
and SW.Sid=S.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 d’informations
V$WAITSTAT, V$SYSTEM_EVENT, V$SESSION_WAIT, STATPACK REPORT
24
Base de Données avancées
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
25
Base de Données avancées
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.
26
Base de Données avancées
ANALYSE DE REDO LOG BUFFER
a)-Redo log hit-ratio
Select name, value from V$SYSSTAT
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 v$sysstat a, v$sysstat b
where a.name='redo buffer allocation retries'
and b.name='redo entries'
redo buffer retries ratio
----------------------------0,003102836
27
Base de Données avancées
Attente due à la non disponibilité du fichier redo log
Select name, value from V$SYSSTAT
where name='redo log space requests'
NAME
VALUE
-----------------------------------------------------------------redo log space requests
64
28
Base de Données avancées
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/spcreate.sql (création du schéma statpack et de user PERFSTAT)
Exécution
• SQL> connect perfstat/perfstet
• SQL> execute statspack.snap
Analyse des résultat
• SQl>@spreport
29
Base de Données avancées
STATSPACK report for
DB Name
---------Test
DB Id
----------204079298
Instance
---------ORCL
Inst Num
-------1
Start Id
End Id
Start Time
-------- -------- -------------------1
2 25-Sep-00 00:36:21
Cache Sizes
~~~~~~~~~~~
db_block_buffers:
8192
db_block_size:
2048
log_buffer:
163840
shared_pool_size:
15728640
Release
---------8.1.5.0.0
OPS
---NO
End Time
-------------------25-Sep-00 00:38:10
Host
---------azuro
Snap Length
(Minutes)
----------1.82
Load Profile
~~~~~~~~~~~~
Per Second
--------------Redo size:
4,893.39
Logical reads:
37.88
Block changes:
22.23
Physical reads:
4.26
Physical writes:
7.16
User calls:
0.02
Parses:
2.04
Hard parses:
0.06
Sorts:
0.35
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
Per Transaction
--------------533,380.00
4,129.00
2,423.00
464.00
780.00
2.00
222.00
7.00
38.00
30
Base de Données avancées
TOAD
(Outil d ’administration)
31
Base de Données avancées
32
Base de Données avancées
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
33
Base de Données avancées
Questions ?
34