Remerciez-le!

Remerciez @Admin pour avoir partagé cet document gratuitement, de la manière la plus simple, en partageant sur les réseaux sociaux.

ORACLE Performance Tuning Pr. M.RADOUANE 2022-2023 Plan: 2 C’est quoi SQL Tunin

ORACLE Performance Tuning Pr. M.RADOUANE 2022-2023 Plan: 2 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 Architecture d’ORACLE: 3 Architecture d’ORACLE: 4 Architecture d’ORACLE: 5 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). Architecture d’ORACLE: 6 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. Architecture d’ORACLE: 7 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. Architecture d’ORACLE: 8 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. Architecture d’ORACLE: 9 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. Architecture d’ORACLE: 10 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. Architecture d’ORACLE: 11 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. Architecture d’ORACLE: 12 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. Stockage des données: 13 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. ORACLE Vs SQL Server 14 ORACLE SQL Server Data File Data File Data File Data File Data File Data File Temporary Tablespace Groups Tablespace Tablespace Segment Segment Extent Extent Extent Extent Extent Extent Extent Blocks Blocks Blocks Blocks Heap/Index Heap/Index Filegroup Filegroup Pages Pages Pages Stockage des données: 15 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). Stockage des données: 16 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). PGA (Program Global Area): 17 • Stocke les informations de session pour chaque utilisateur. • Stocke les variables de session (login, état de la session, etc). • Stocke les variables de liaison des curseurs. • Stocke les informations d’exécution des requêtes. • Stocke les informations sur les curseurs. • Stocke les informations sur les curseurs. • Stocke les informations sur la zone de travail SQL (tri, hachage, fusion, etc). Données d’annulation (Undo): 18 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. 19 Optimisation du système 20 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 uploads/Ingenierie_Lourd/ oracle-tuning-a-envoyer 1 .pdf