[WEBINARđŸ’»] NIS 2 en action : StratĂ©gies et solutions pour une cyber-rĂ©silience renforcĂ©e ! Le replay est disponible dĂšs maintenant🎙!

✖︎
Digora blog

Comment expliquer les caprices de l'optimiseur Oracle et y faire face - partie 2

28/08/2012
Données

AprÚs avoir posé les bases dans le premier article de cette série, abordons les différentes méthodes permettant de "dompter" l'optimiseur Oracle...

1. Vue d'ensemble

Conscient que son optimiseur est quelquefois capricieux, Oracle fournit plusieurs mĂ©thodes pour contrĂŽler son  fonctionnement. A la dĂ©charge d'Oracle, il faut reconnaĂźtre que la complexitĂ© des requĂȘtes (rĂ©digĂ©es par les dĂ©veloppeurs d'applications ou gĂ©nĂ©rĂ©es par les applications) est souvent Ă©norme ! On peut se demander parfois si les inventeurs du langage SQL avaient imaginĂ© jusqu'Ă  quelle complexitĂ© le SQL allait ĂȘtre utilisĂ©... Certaines requĂȘtes font plusieurs milliers de lignes... Voici quelques mĂ©thodes proposĂ©es par Oracle pour influencer l'optimiseur sans modifier (ou presque) le code SQL :

  • les Hints
  • les Stored Outlines
  • le SQL Profile
  • Le SQL Plan Management

2. Comment influencer l'optimiseur ?

2.1 Les Hints

Oracle permet d'insĂ©rer dans l'ordre SQL des indications (Hints) en tant que commentaire permettant d'influencer l'optimiseur sur le choix du plan d'exĂ©cution. MĂȘme si il faut Ă©viter au maximum cette mĂ©thode, elle peut rendre de grands services. Une modification dans le code SQL est nĂ©anmoins nĂ©cessaire. Il existe un grand nombre de Hints diffĂ©rents. Exemple de Hint (en rouge) :

     select /*+ index(emp_alias ix_emp) */ ... from scott.emp emp_alias

2.2 Les Stored Outlines

Les Stored Outlines sont disponibles avec Oracle 8i EE, 9i EE, 10g SE et EE et  obsolĂštes  depuis Oracle 11g. Les Stored Outlines permettent de forcer le plan via des Hints, sans modifier la requĂȘte. Pour crĂ©er un Stored Outline, il suffit de modifier le contexte de la session pour obtenir le bon plan d'exĂ©cution, de spĂ©cifier Ă  Oracle de crĂ©er l'Outline et de lancer la requĂȘte. Pour que les Outlines soient utilisĂ©es, il faut mettre en place un paramĂštre au niveau de la session ou du systĂšme. Attention, il n'est pas possible de positionner ce paramĂštre dans l'init.ora ou le spfile; il faut le repositionner Ă  chaque dĂ©marrage d'instance. InconvĂ©nient du Stored Outline : on ne peut pas profiter d’évolutions de l'optimiseur ou du schĂ©ma permettant une requĂȘte plus rapide (un nouvel index par exemple).

2.3 Le SQL Profile

La fonctionnalitĂ© SQL Profile est disponible Ă  partir de la version Oracle 10g. Elle nĂ©cessite l'Ă©dition Enterprise et les options Diagnostick Pack et Tuning Pack. Le SQL Profile, contrairement Ă  un Outline, n'est en rĂšgle gĂ©nĂ©rale pas directif (nous verrons l'exception plus loin). Il est peut ĂȘtre crĂ©Ă© via le SQL Tuning Advisor. Cette mĂ©thode rajoute des indications d'ajustements qui seront utilisĂ©es par l'optimiseur Oracle. Il peut Ă©galement ĂȘtre utilisĂ© (et c'est un de ses gros avantages) en mode "force match" ce qui lui permet de s'appliquer mĂȘme pour des requĂȘtes dont les valeurs des constantes sont diffĂ©rentes. Voici un inconvĂ©nient de l'utilisation du SQL Profile : on ne maĂźtrise pas  les Hints ajoutĂ©s par Oracle, et, comme les Hints sont des ajustements , il se peut qu'avec l'Ă©volution des donnĂ©es, ceux-ci deviennent caduques et que la requĂȘte repasse par un mauvais plan d'exĂ©cution.

Compare Explain Plans

 2.4 Le SQL Plan Management

La fonctionnalitĂ© SQL Plan Management  est disponible Ă  partir de la version Oracle 11g. Elle nĂ©cessite l'Ă©dition Enterprise. Les options Diagnostick Pack et Tuning Pack sont nĂ©cessaires pour une utilisation automatisĂ©e via des Tuning Sets. Le SQL Plan Management est une Ă©volution des Stored Outlines : il permet de forcer un plan via des Hints. Mais contrairement aux Stored Outlines, il est possible de valider plusieurs plans diffĂ©rents pour une mĂȘme requĂȘte et ainsi de faire Ă©voluer les plans disponibles. L'optimiseur choisira au moment de l'exĂ©cution un de ces diffĂ©rents plans prĂ©-validĂ©s. Le SQL Plan Management ne dispose pas d’un mode « force match ».

SQL management base

3. Exemple d'utilisation avancée

DerniĂšrement, suite Ă  une migration d'Oracle 10g Ă  Oracle 11g chez un client de Digora, plusieurs  requĂȘtes sont devenues trĂšs lentes et trĂšs consommatrices en ressources, entraĂźnant la saturation du serveur. Le code de l'application n'Ă©tait pas modifiable de maniĂšre rapide. Les requĂȘtes n'utilisant pas de variables (les valeurs des champs Ă©taient en dur), le SQL Plan Management et les Stored Outlines n'Ă©taient donc pas utilisables. Un SQL Profile a Ă©tĂ© crĂ©Ă©, mais certains Hints n'Ă©taient pas satisfaisants comparativement Ă  la situation prĂ©cĂ©dente : utilisation du parallĂ©lisme de maniĂšre forcĂ©e et utilisation d'une valeur de Dynamic Sampling Ă©levĂ©e. La requĂȘte utilisait plus de ressources et restait plus lente qu'en Oracle 10g. Une investigation poussĂ©e a permis de trouver une solution plus satisfaisante. Elle utilise un script disponible sur le site du support Oracle : "coe_xfr_sql_profile.sql". Grace Ă  ce script, il est possible de gĂ©nĂ©rer manuellement des SQL Profiles utilisant les mĂȘmes Hints que les Stored Outlines ou le SQL Plan Management. AprĂšs quelques tests, nous avons donc mis en place les SQL profiles gĂ©nĂ©rĂ©s manuellement et avons ainsi rĂ©solu  les problĂšmes de performances sur ces requĂȘtes. Dans la partie 3 de cette sĂ©rie d'articles, DIGORA vous apportera d'autres prĂ©cisions sur l'optimiseur Oracle. Vous souhaitez en savoir plus ? Contactez-nous ici