ORACLE Tuning A Envoyer [PDF]

Zitiervorschau

ORACLE Performance Tuning

Pr. M.RADOUANE 2022-2023

Plan: C’est quoi SQL Tuning et pourquoi nous en avons besoin? Bien connaitre l’architecture pour bien régler les performances. SQL Tuning Basique. Plans d’execution. Chemin des tables et des indexes. Opérations de jointure. Techniques de base du réglage. Techniques d’indexation avancées. Astuces de réglage

2

Architecture d’ORACLE:

3

Architecture d’ORACLE:

4

Architecture d’ORACLE: User process: (Processus Utilisateur) Oracle est une BD multiutilisateurs. Des millions d’utilisateurs peuvent se connecter à la même BD sans aucun problème. Ces processus envoient des commandes SQL à la BD et obtiennent des données s’ils en ont besoin. Une fois ces commandes sont envoyées, le serveur prend le contrôle et envoie l’instruction au PGA (Program Global Area). PGA: est une zone mémoire dédiée à un seul utilisateur, et chaque utilisateur à ses propres PGA. Pendant que votre code interpète, vos données spécifiques à la session sont stockées ici et cette zone ne peut être lue que par son propriétaire.

N.B: Chaque instruction SQL est exécutée avec un Plan d’exécution (les moyens ou les techniques pour exécuter votre code). 5

Architecture d’ORACLE: Problème: Avant d’exécuter votre requête, le plan d’exécution du code SQL doit être créé. À ce moment là, un problème de réglage (tuning issue) commence. En effet, la même requête SQL peut être exécutée ou peut avoir été exécutée par le même utilisateur ou par des utilisateurs différents plusieurs fois. Et générer un plan d’exécution est parfois une opération coûteuse.

Solution: Une zone SQL partagée est créée (Shared SQL Area) afin de stocker les plans d’exécution pour des utilisateurs ultérieurs. Le serveur ORACLE prend le plan d’exécution stocké dans la zone partagée et le gère vers le PGA (Program Global Area) de l’utilisateur. Gain: Augmentation de la performance.

6

Architecture d’ORACLE: Fondamentalement, nous avons deux zones de mémoire importantes dans la BD ORACLE, ‘PGA’ (Program Global Area) et ‘SGA’ (System Global Area). Nous pouvons les appeler des mémoires caches. PGA & SGA: Simples zones mémoires utilisées par le serveur ORACLE pour améliorer les performances. Comment?: Si certaines sessions utilisent les mêmes données, au lieu d’écrire les données sur le disque et d’essayer de les récupérer, ils sont stockées dans une mémoire et renvoyées à partir de cet emplacement et non à partir des disques de données. Etant donné que la mémoire est beaucoup plus rapide que les disques physiques, les performances augmenterons considérablement. 7

Architecture d’ORACLE: PGA (Program Global Area): est une zone mémoire dédiée à chaque utilisateur et également privée pour chaque utilisateur. SGA (System Global Area): est une zone mémoire partagée par tous les utilisateurs. Ainsi, certaines données privées ou spécifiques à une session sont stockées dans les PGA et les données communes ou partagées sont stockées dans le SGA. Et dans ces zones de mémoires, il y a des zones de sous-mémoire (caches). Le SGA est plus gros que les PGA, près de 80% de la mémoire totale. Nous stockons donc une très grande partie de données dans les SGA.

8

Architecture d’ORACLE: Le serveur ORACLE dispose de nombreuses mémoires caches pour augmenter les performances. Shared pool: est un très grand cache comprenant des sous caches comme Library cache, Result cache, Data dictionary cache, etc. chaque cache est spécialisé à des fins différentes. Data dictionary cache: permet de stocker la définition des tables. Lorsque vous exécutez une requête de sélection, par exemple, Select * From Employe, la BD doit savoir s’il existe une table des employés et quelle colonne doit renvoyer. Lorsqu'une base de données Oracle est lancée pour la première fois, aucune donnée n'existe dans le cache du dictionnaire de données. Afin d’augmenter les performances et avoir un accès plus rapide, ces données sont copiées dans le cache du dictionnaire de données. 9

Architecture d’ORACLE: Result cache: permet de mettre en cache les résultats des requêtes. Si vous exécutez une requête, vous voyez que cela prend un certain temps pour renvoyer une valeur. Mais lorsque vous exécutez à nouveau la même requête, vous vous en rendez compte, elle vous revient immédiatement. Exécuter une requête n’est pas une tache très simple. Il a besoin de beaucoup de lectures de disque et de cycles CPU.et la lecture du disque est une opération très couteuse. N.B: Si les données de la table changent, les données ne seront pas les mêmes avec le cache de résultats. Donc, le résultat stocké est supprimé du cache de résultats et la requête est exécutée lors du prochain appel.

10

Architecture d’ORACLE: Library cache: est le conteneur et l’organisateur des zones SQL partagée (Shared SQL Area). Il gère la taille de ces zones en créant d’autres nouvelles (en supprimant la plus inutilisée) en cas d’insuffisance de la mémoire. Cette zone stocke les plans d’exécutions, les procédures, les fonctions, les packages, etc. Database buffer cache: est la zone mémoire qui permet de stocker les blocs de données pendant une courte période de temps.

Généralement, nous interrogeons ou manipulons les mêmes données. Afin de ne pas lire les mêmes données sur les disques tout le temps , la BD stocke les blocs de données dans le buffer pour effectuer la lecture plus rapidement. Cela augment les performances.

11

Architecture d’ORACLE: Redo Log Buffer: est une zone qui permet de conserver l’état initial des valeurs modifiées avec des informations spécifiques sur ces valeurs. De cette façon, les valeurs peuvent revenir facilement à leur état initial avec les données de journalisation. les fichiers de journalisation sont extrêmement utiles lorsque quelque chose d’inattendu se produit comme une défaillance du système.

12

Stockage des données: Toutes les données de la BD ORACLE sont stockées dans des blocs à la fois sur les disques et sur la mémoire. Un bloc est la plus petite unité de la BD pour stocker les données. C’est une unité logique constituée de plusieurs blocs de système d’exploitation. Un bloc peut contenir une table entière ou quelques lignes d’une table. Ou parfois, un bloc peut avoir des lignes de tables différentes lorsque plusieurs tables sont regroupées. Chaque bloc a une taille spécifique et ne peut pas être étendu directement. Un bloc est définit sur 8Ko par défaut. Un bloc peut également avoir les données d’indexe. 13

ORACLE Vs SQL Server Data File

Data File

Data File

Data File

Data File

Data File

Temporary Tablespace Groups Tablespace

Tablespace

Filegroup

Filegroup

Segment

Segment

Heap/Index

Heap/Index

Extent

Extent

Extent

Extent

Extent

Extent

Extent

Blocks

Blocks

Blocks

Blocks

Pages

Pages

Pages

ORACLE

SQL Server

14

Stockage des données: Architecture d’un bloc: Un block se compose de l’ en-tête du bloc et des lignes. Un en-tête de bloc comprend des métadonnées sur les blocs (près de 100 octets de données). Chaque ligne est stockées dans de vrais blocs de mémoire, et ces blocs ont des adresses spécifiques (ROWID).

15

Stockage des données: Le reste du bloc a les lignes et quelques espaces vides. Ces espaces sont importants. Parce que, si vous effectuez une mise à jour et vous augmentez la taille d’une ligne, il sera couteux de prendre cette ligne et de la transporter à un endroit de ce bloc ou bien à un autre bloc. ORACLE laisse un espace après chaque ligne. Donc, si la taille de cette ligne augmente, elle l’écrit simplement au même endroit. Cela améliore beaucoup les performances. PCTFREE et PCTUSE permet de spécifier la taille de l’espace libre dans un bloc. N.B: Si vous ne laissez pas un espace libre dans un bloc, chaque mise à jour changera très probablement la place de la ligne, ce qui diminuera les performances (les opérations IO augmentent). 16

PGA (Program Global Area):

• •

Stocke les informations de session pour chaque utilisateur. Stocke les variables de session (login, état de la session, etc).

•

Stocke les variables liaison des curseurs.

•

Stocke les informations d’exécution des requêtes.

•

Stocke les informations sur les curseurs.

•

Stocke les informations sur la zone de travail SQL (tri, hachage, fusion, etc).

de

17

Données d’annulation (Undo): Lorsqu’une transaction modifie des données, le serveur de base de données copie les données d’origine du disque et les stocke dans la mémoire avant de les modifier. La copie originale des données associées est appelée « données d’annulation ». Ensuite, une autre copie des données associées est également écrite dans le buffer cache et toutes les modifications sont effectuées dans ce bloc. Ainsi, les données d’annulation reste inchangées parce que: • Si vous souhaitez annuler vos modifications ou si vos modifications devaient être annulées en raison d’une défaillance du système, etc, les blocs d’annulation sont utilisées pour les annulations (Rollback). • Ils fournissent une cohérence de lecture. 18

Optimisation du système

19

Optimisation du système Etapes de l’optimisation

La réflexion sur les performances doit donc prendre place dès les premiers moments du projet, et durant toute son évolution : modélisation, choix du matériel, installation, organisation du stockage physique et logique (une bonne indexation des tables est naturellement déterminante), codage SQL, supervision du serveur… Cette réflexion est trop souvent négligée, et le besoin d’optimisation émerge en bout de chaîne, lorsque le système est en place et que les temps de réponse, soudainement ou progressivement, ne donnent plus satisfaction.

20

Sécurité Etapes de l’optimisation

Parfois, l’augmentation du volume de données ou du nombre d’utilisateurs simultanés a ralenti le temps d’exécution des requêtes de façon inacceptable. Vous vous retrouvez alors en face d’un constat, et dans l’obligation urgente de remédier au problème. Cette situation n’est pas favorable pour effectuer un travail en profondeur, et dans le bon sens, mais c’est malheureusement la situation la plus fréquente, lorsqu’une planification prenant en compte les performances n’a pas été menée depuis le début du projet.

21

Sécurité Etapes de l’optimisation

Augmenter la puissance du matériel est trop souvent une première réponse facile. Les gains de performance ne seront pas automatiquement obtenus par un matériel plus puissant, car bien souvent, le problème provient d’une mauvaise architecture de la base de données, d’un manque d’index, de requêtes peu optimales… toutes sources de problèmes que l’augmentation de la puissance du matériel ne pourra pallier que très faiblement.

22

Sécurité Optimisation?

Question: Faut-il privilégier exclusivement les performances par rapport à tout autre critère ? Réponse: Évidemment non.

Il est important de maintenir un équilibre entre:  Simplicité;  Lisibilité;  Performance.

23

SQL Tuning basique: Moments de réglage des requêtes: Le processus de réglage SQL est un processus continu.

Les requêtes doivent être réglées au moment de leurs création. Les requêtes les plus consommatrices sont fréquemment vérifiées pour augmenter les performances. Une requête peut être exécutée très lentement après un changement de la structure d’une table, si un nouvel index est créé ou supprimé, ou même si la quantité de données augmente.

24

SQL Tuning basique: Quand décidez-vous de régler une requête SQL: • Surveillez les requêtes les plus consommatrices.

La plupart du temps, les requêtes les plus consommatrices (plus que 5% des requêtes) utilisent 80% des ressources totales. Comment connaitre les requêtes les plus consommatrices? (un peu plus loin ). • Ajuster le Bad SQL (un peu plus loin ). • Plainte des utilisateurs (réclamations après des réponses très lentes de l’exécution).

25

SQL Tuning basique: Qu'est ce qui peut ralentir une requête SQL? •

Un changement structurel (modification de la structure d’une table ou la création/suppression d’un index, etc.). Cela affecte les performances de la requête.

•

Un changement dans le volume de données (implique un changement dans le plan d’exécution).

•

Une application peut changer (variables, variables de liaison, etc).

•

Les statistiques peuvent vieillir (statistiques non mises à jour).

•

Une mise à niveau de la BD (certaines requêtes peuvent ne pas fonctionner correctement).

•

Un changement de paramètres de la BD (taille PGA, taille SGA). 26

SQL Tuning basique: Bad SQL? On peut obtenir le même résultat avec de nombreuses instructions SQL différentes. Toutes peuvent renvoyer le même résultat, mais presque toutes fonctionnent avec des taux de performance différents. Certaines instructions utilisent beaucoup plus de ressources inutiles. Ce genre de requêtes est appelée « Bad SQL» (SQL peu performant). La performance des requêtes peut être affectée par:

• • • •

Temps d’analyse Opérations I/O non nécessaires Temps du CPU excessif Temps d’attente excessif 27

SQL Tuning basique: La performance des requêtes peut être affectée par: • Temps d’analyse

Lorsque nous exécutons une requête, le serveur ORACLE l’analyse. Il vérifie sa validité et réserve une zone SQL privé pour cette instruction et génère le plan d’exécution. •

Opérations I/O

Nous pouvons considérer les opérations I/O comme les opérations de lecture de données à partir des disques. Si la requête lit trop de blocs pour obtenir les données, cela signifie que la requête comporte un nombre excessif d’opérations d’I/O.

28

SQL Tuning basique: La performance des requêtes peut être affectée par: • Temps du CPU excessif

Les jointures, les tris, les calculs, etc. ces opérations sont effectuées par le processeur et la mémoire. Si ces opérations sont faites avec de mauvaises méthodes, les requêtes consommeront trop de cycles CPU. • Temps d’attente excessif Les requêtes exécutées peuvent avoir un temps de réponse très lent. Ils attendent leur tour dans le CPU, ou ils s’exécute via le réseau. Temps global = temps d’attente + temps d’exécution 29

SQL Tuning basique: Généralement Bad SQL est dû à: • Une mauvaise conception de la BD.

• Un mauvais codage des requêtes. • Un plan d’exécution inefficace.

30

SQL Tuning basique: Schéma de BD: La première étape du réglage des performances pour les développeurs consiste à concevoir efficacement le schéma de la BD. •

Sélectionner soigneusement les types des données (char, varchar2,long, etc.) et la taille des données.

•

Assurer l’intégrité des données en sélectionnant exactement le même type de données entre les clés (primaires et étragngères). Dans le cas contraire, le serveur ORACLE fait la conversion pour chaque ligne au moment de la jointure.

•

Assurer la normalisation.

•

Créer des clusters.

•

Utiliser les indexes. 31

SQL Tuning basique: Schéma de BD: La première étape du réglage des performances pour les développeurs consiste à concevoir efficacement le schéma de la BD. •

Sélectionner soigneusement les types des données (char, varchar2,long, etc.) et la taille des données.

•

Assurer l’intégrité des données en sélectionnant exactement le même type de données entre les clés (primaires et étragngères). Dans le cas contraire, le serveur ORACLE fait la conversion pour chaque ligne au moment de la jointure.

•

Assurer la normalisation.

•

Créer des clusters.

•

Utiliser les indexes. 32

Partitionnement des tables et des index Le partiotionnement est une organisation du stockage permettant de diviser les données de tables et/ou d’index sur plusieurs groupes de fichiers.

Index: Structure qui permet l’accélération de l’extraction des lignes d’une table. Il existe deux types d’index:

• Index en cluster (index clusterisé) Stock les lignes de données dans une structure triée. Chaque table ne comporte qu’un seul index clusterisé (table en cluster).

• Index non en cluster (non clusterisé)

33

Partitionnement des tables et des index Fonctionnalités avancées: • Opération destinée à des tables et des BD volumineuses. • Nécessite un DBA pour la mise en oeuvre et la gestion. Objectif:

• Dépasser les limites de stockage des disques (pour les tables de grande taille) • Améliorer les performances • Gérer la purge des données. • Gérer l’import ou l’archivage des données.

34

Partitionnement des tables et des index

Id_cmd

Id_clt

Date_cmd

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10004

9820

01-fév-18

10005

1134

03-fév-18

10006

8011

04-fév-18

10007

8756

01-mars-18

10008

2460

02-mars-18

10009

7233

13-mars-18

10010

2945

20-avr-18

10011

8105

21-avr-18

10012

2843

22-avr-18

Data Tablespace

35

Partitionnement des tables et des index Table Commandes

Id_cmd Partition 1 (Janvier)

Partition 2 (Février)

Partition 3 (Mars)

Partition 4 (Avril)

Id_clt

Date_cmd

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10004

9820

01-fév-18

10005

1134

03-fév-18

10006

8011

04-fév-18

10007

8756

01-mars-18

10008

2460

02-mars-18

10009

7233

13-mars-18

10010

2945

20-avr-18

10011

8105

21-avr-18

10012

2843

22-avr-18

Tablespace 1

Tablespace 2

Tablespace 3

Tablespace 4

36

Partitionnement des tables et des index Purge de données et switch de partition Table Commandes

Id_cmd Partition 1 (Janvier)

Partition 2 (Février)

Partition 3 (Mars)

Partition 4 (Avril)

Id_clt

Date_cmd

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10004

9820

01-fév-18

10005

1134

03-fév-18

10006

8011

04-fév-18

10007

8756

01-mars-18

10008

2460

02-mars-18

10009

7233

13-mars-18

10010

2945

20-avr-18

10011

8105

21-avr-18

10012

2843

22-avr-18

Table PurgeCommandes

Id_cmd

Id_clt

Date_cmd

Création d’une nouvelle table (même structure que Commandes)

37

Partitionnement des tables et des index Purge de données et switch de partition Table Commandes

Id_cmd

Id_clt

Date_cmd

Table PurgeCommandes

SWITCH

Partition 1 (Janvier)

Partition 2 (Février)

Partition 3 (Mars)

Partition 4 (Avril)

10004

9820

01-fév-18

10005

1134

03-fév-18

10006

8011

04-fév-18

10007

8756

01-mars-18

10008

2460

02-mars-18

10009

7233

13-mars-18

10010

2945

20-avr-18

10011

8105

21-avr-18

10012

2843

22-avr-18

Id_cmd

Id_clt

Date_cmd

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

Tablespace 1

38

Partitionnement des tables et des index Import de données Table AnciennesCommandes

Id_cmd

Id_clt

Date_cmd

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

Table Commandes

Id_cmd

SWITCH

Id_clt

Date_cmd

Partition 1 (Janvier)

Partition 2 (Février)

Partition 3 (Mars)

Partition 4 (Avril)

10004

9820

01-fév-18

10005

1134

03-fév-18

10006

8011

04-fév-18

10007

8756

01-mars-18

10008

2460

02-mars-18

10009

7233

13-mars-18

10010

2945

20-avr-18

10011

8105

21-avr-18

10012

2843

22-avr-18

Tablespace 1

39

Partitionnement des tables et des index Import de données Table AnciennesCommandes

Id_cmd

Id_clt

Date_cmd

Table Commandes

SWITCH Partition 1 (Janvier)

Partition 2 (Février)

Partition 3 (Mars)

Partition 4 (Avril)

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10001

2341

01-jan-18

Tablespace 1

40

Partitionnement des tables et des index Archivage de données Table Commandes

Id_cmd Partition 1 (Janvier)

Partition 2 (Février)

Partition 3 (Mars)

Partition 4 (Avril)

Id_clt

Date_cmd

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10004

9820

01-fév-18

10005

1134

03-fév-18

10006

8011

04-fév-18

10007

8756

01-mars-18

10008

2460

02-mars-18

10009

7233

13-mars-18

10010

2945

20-avr-18

10011

8105

21-avr-18

10012

2843

22-avr-18

Table ArchiveCommandes

Id_cmd

Id_clt

Date_cmd

Création d’une nouvelle table (même structure que Commandes)

41

Partitionnement des tables et des index Réduction du volume des sauvegardes

Table Commandes

Id_cmd Partition 1 (Janvier)

Partition 2 (Février)

Partition 3 (Mars)

Partition 4 (Avril)

Id_clt

Date_cmd

10001

2341

01-jan-18

10002

8761

11-jan-18

10003

1209

18-jan-18

10004

9820

01-fév-18

10005

1134

03-fév-18

10006

8011

04-fév-18

10007

8756

01-mars-18

10008

2460

02-mars-18

10009

7233

13-mars-18

10010

2945

20-avr-18

10011

8105

21-avr-18

10012

2843

22-avr-18

ReadOnly

ReadOnly

ReadOnly

42

Partitionnement des tables et des index Exemple: ALTER TABLE employees MODIFY PARTITION BY RANGE (hitre_date) ( PARTITION p_name1 VALUES LESS THAN (TO_DATE(’01/01/2018’,’DD/MM/YYYY’)) TABLESPACE USERS, PARTITION p_name2 VALUES LESS THAN (TO_DATE(’01/01/2019’,’DD/MM/YYYY’)) TABLESPACE USERS, PARTITION p_name3 VALUES LESS THAN (TO_DATE(’01/01/2020’,’DD/MM/YYYY’)) TABLESPACE USERS, PARTITION p_name3 VALUES LESS THAN (MAXVALUE) TABLESPACE USERS, )ONLINE UPDATE INDEXES ( IDX1_SALARY LOCAL, IDX2_EMP_ID GLOBAL PARTITION BY RANGE (employee_id) (PARTITION IP1 VALUES LESS THAN (MAXVALUE)) );

43

Processus d’exécution d’une requête SQL Lorsqu’une instruction SQL doit être exécutée, le serveur ORACLE suit certaines étapes avant l’exécution:

44

Processus d’exécution d’une requête SQL Quel est le rôle d’un Optimizer: Logiciel dédié à trouver le bon plan d’exécution le plus rapidement possible. Avec un bon plan d’exécution les requêtes peuvent s’exécuter peut-être 100 fois plus vite ou peut être plus. Cela réduit absolument le coût du matériel et permet d’avoir une satisfaction des utilisateurs.

Une requête peut être exécutée de différentes manières. Mais la performance sera très différente. Une méthode renvoie la réponse en 1 seconde, l’autre peut la renvoyer en quelques minutes ou quelques heures.

45

Processus d’exécution d’une requête SQL Fonctionnement de l’Optimizer:

Vérification des indexes et des types d’indexe

Un index est trouvé sur la colonne prod_category

Une lecture de la totalité de la table peut se faire Vérification des statistiques pour choisir un chemin

L’utilisation d’un index n’est pas un moyen efficace

46

Processus d’exécution d’une requête SQL Fonctionnement de l’Optimizer: 1- Une fois qu’une requête est analysée, elle est gérée par l’optimiseur 2- Trouver la meilleure requête qui renvoie le même résultat mais de manière plus performante

3- Vérification du coût de la requête (disque+cpu+mémoire) 4- génère plusieurs plans d’exécution et choisit le meilleur plan.

47

Processus d’exécution d’une requête SQL Coût= Coût d’exécution d’un seul bloc*nbr_bloc à lire N.B: Il s’agit d’une estimation basée sur les statistiques. Mais le coût réel ne peut être connu qu’après l’exécution de la requête. Les statistiques sont recueillies à des moments précis, mais les données réelles changent instantanément. Ainsi, l’estimateur estime le coût de la requête et renvoie le coût estimé.

48

Processus d’exécution d’une requête SQL Query Transformer:

ORACLE transforme nos requête en une plus performante. Important: Si les transformations ne sont pas meilleures que la requête initiale, il n’y aura pas de transformation.

1ère technique: "OR Expansion" • L’utilisation de l’opérateur ‘OR’ dans la clause WHERE empêche l’utilisation des indexes. - Supprimer ‘OR’ ou utiliser ‘AND’.

49

Processus d’exécution d’une requête SQL Query Transformer:

Exemple:

50

Processus d’exécution d’une requête SQL Query Transformer:

2ème technique: Requête imbriquée • L’Optimizer transforme une requête imbriquée en une requête de jointure. Exemple: Opération coûteuse

Index 51