Optimiser les requêtes Siebel OPTIMIZER_INDEX_COST_ADJ = 1 et statistiques spéciales - How to optimize Siebel queries

10 avril 2021

Optimiser les requêtes Siebel OPTIMIZER_INDEX_COST_ADJ = 1 et statistiques spéciales - How to optimize Siebel queries

Introduction
Chez un client j'ai eu à optimiser des requêtes générées par l'application Siebel. A ma grande surprise, les recos Oracle (ils ont racheté Siebel) étaient incompréhensibles : mettre le paramètre OPTIMIZER_INDEX_COST_ADJ à 1 et utiliser deux scripts pour la génération des stats qui ne calculaient aucune stat pour les tables de moins de 15 lignes...

Je vais donner dans cet article mon retour d'expérience mais sachez qu'il est très difficile pour un DBA Oracle de tuner les requêtes Siebel puisque les recos vont à l'encontre des principes de base d'Oracle :
- avoir des stats à jour
- ne pas modifier le comportement du CBO (il coûte suffisamment cher pour qu'on ait pas à le tuner :-) )

Points d'attention
N/A.

Base de tests
Une base Oracle 19 multi-tenants.

Exemples

============================================================================================
Le paramètre OPTIMIZER_INDEX_COST_ADJ
============================================================================================
Le point le plus perturbant dans les recos Siebel c'est de positionner le paramètre OPTIMIZER_INDEX_COST_ADJ à 1.
Voici ce qu'en dit la doc Oracle.
"OPTIMIZER_INDEX_COST_ADJ lets you tune optimizer behavior for access path selection to be more or less index friendly—that is, to make the optimizer more or less prone to selecting an index access path over a full table scan.
Property        Description
Parameter type    Integer
Default value     100
Modifiable    ALTER SESSION, ALTER SYSTEM
Modifiable in a PDB Yes
Range of values    1 to 10000
Basic          No

The default for this parameter is 100 percent, at which the optimizer evaluates index access paths at the regular cost. Any other value makes the optimizer evaluate the access path at that percentage of the regular cost. For example, a setting of 50 makes the index access path look half as expensive as normal."

En résumé, ce paramètre permet de modifier artificiellement le calcul du coût d'utilisation d'un index par le CBO. Si cette valeur vaut 1 par exemple, le CBO estimera que l'utilisation d'un index avec un cost de 200 vaut maintenant 2 et favorisera donc son utilisation puisque ce coût de 2 est presque nul. En revanche un coût au delà de 100, de 10 000 par exemple, va favoriser les Full Table Scans car on pénalise le coût d'utilisation des index. Mais ne vous y trompez pas, lors de l'exécution de la requête, c'est le vrai coût qui s'appliquera, ce paramètre n'est pas magique!

Mais, concrètement, à quoi sert-il? Quand doit-on le modifier? Dans le cas où le CBO sélectionne un plan non optimal, on peut utiliser diverses techniques pour que ce plan soit changé : stored outlines, baselines, sql profiles, sql patch, hints etc etc. Le problème c'est quand vous n'avez pas la main sur la requête : impossible de la modifier via un hint ou bien vous n'avez même pas les droits pour créer une baselines... Une solution est alors de changer la valeur de ce paramètre pour influencer le CBO; ATTENTION à ne pas faire, si possible, un ALTER SYSTEM mais plutôt un ALTER SESSION ou un TRIGGER AFTER LOGON sinon toutes les requêtes de votre base seront impactées alors que le problème concerne un ordre SQL et un seul.

A noter un point important : sur Metalink (MOS : My Oracle Support), dans la note 2077227.2, il est dit que ce paramètre doit être mis à 1 même si, chez de rares clients, Oracle a constaté que 100 donnait de meilleurs résultats.

Mais pourquoi cette valeur de 1 pour Siebel? A cause d'un historique : sur le net j'ai lu que "when SIEBEL was tuned, this parameter was at 1." La réponse d'Oracle sur AskTom (voir plus bas) dit que, lorsque Siebel fut développé, il fut conçu et optimisé pour utiliser des indexes. C'est donc pourquoi il faut que la base Oracle où tourne Siebel favorise ces index; un des moyens est justement de positionner le paramètre OPTIMIZER_INDEX_COST_ADJ à 1. "That reflects a historical position. Siebel *used* to be written for the rule optimizer. As time went on, and the cost optimizer became mandatory, the Siebel code base stay heavily reflected its rule-based origins which has a huge bias toward indexes. Thus to avoid regression, the recommendation of that parameter kept the code leaning toward indexes. However, times have changed. There is definitive guide about what to set for Siebel for each version of the database. You can find that at MOS note 2077227.2, where OPTIMIZER_INDEX_COST_ADJ = 1 still an option, but the defaults are also strongly recommended."

============================================================================================
Problème avec ces recos
============================================================================================
En plus du paramètre ci-dessus forcé à 1, Siebel vous livrera deux scripts pour générer les stats. Ceux-ci ignorent les tables de moins de 15 enregistrements. Pas la peine de s'étendre dessus, je n'ai pas le code et puis ils relèvent de la propriété intellectuelle de Oracle.

Résultat des courses, je me retrouve avec des requêtes qui n'utilisent QUE des index, 20 000 blocs sont lus sur disque dur et 500 000 en mémoire pour finalement ne ramener qu'UNE SEULE ligne d'un SELECT, j'ai des "Estimated rows" à 1 mais des "Actual rows" à 8000... On est quand même à 4 Go de données lues (500 000 * 8 Ko) pour afficher une ligne; c'est un gâchis immonde! Pas étonnant que les clients trouvent que leur appli soit très lente.

J'ai aussi vu des requêtes avec 31 tables dans le FROM, soit 8,22e+33 combinaisons possibles, comment voulez-vous que le CBO trouve un bon plan dans le laps de temps imparti".

Bref, par rapport aux contraintes très fortes imposées sur Siebel, je me résous à lancer le SQL Tuning Advisor. Parfois il trouve un meilleur plan mais sans que ce soit bouleversant (2% d'amélioration); ah, en plus, sans rire, il me dit qu'il manque des stats sur des tables... merci les scripts Siebel :-(

============================================================================================
Mon retour d'expérience
============================================================================================
Pré-requis : LISEZ impérativement la note MOS 2077227.2 mais il vous faudra un compte entreprise pour s'y connecter.

Test 1 : avec les recos Siebel OPTIMIZER_INDEX_COST_ADJ = 1 et statistiques générées par les scripts fournis
- exécutez les requêtes lentes une par une
  - générez leurs plans d'exécutions (dbms_xplan.display_cursor) un par un et sauvegardez les au format XML

Test 2 : avec les recos Oracle classiques : OPTIMIZER_INDEX_COST_ADJ = 100 et statistiques générées avec DBMS_STATS sans passer par les scripts Siebel fournis
  - exécutez les requêtes lentes une par une
  - générez leurs plans d'exécutions (dbms_xplan.display_cursor) un par un et sauvegardez les au format XML

Observez maintenant les différences de temps d'exécution entre les résultats des test Test 1 et Test 2.

Si les requêtes de Test 2 sont maintenant plus rapides, c'est la preuve que les recos Siebel sont obsolètes (pour rappel, le RBO a été remplacé par le CBO sur Oracle 7 en 1992). En outre, si vous êtes en architecture multi-tenants, peut-être que les anciennes recos de Siebel ne sont plus pertinentes pour cette architecture.

Si les requêtes de Test 2 ne sont pas plus rapides, alors il faut utiliser les deux SQL advisors.
  - attention, pour info, si vous avez un ordre SQL avec 8 tables ou vues dans le FROM, cela génère déjà 40 000 permutations différentes... je vous laisse imaginer le temps pour traiter 15 objets; donc attention à ces ordres, il est préférable de les traiter un par un. Autre point, paramétrez un temps de traitement suffisamment long pour les advisors (cela dépend du nombre de tables et vues dans le FROM).
  - si vous devez optimiser un seul ordre SQL :
      - utilisez le SQL Tuning Advisor et regardez les recos
      - si les recos précédentes ne permettent pas d'accélérer les requêtes :
          - utilisez le SQL Access Advisor et regardez les recos

  - si vous devez optimiser plusieurs ordres SQL :
      - créez un SQL Tuning Set
      - insérez dedans ces ordres SQL
      - lancez le SQL Tuning Advisor et regardez les recos
      - si les recos précédentes ne permettent pas d'accélérer les requêtes :
          - utilisez le SQL Access Advisor et regardez les recos

A la fin des fins, quel est le résultat :
  - temps des requêtes amélioré en respectant les recos Siebel : génial, ne touchez plus à rien!
  - temps des requêtes amélioré MAIS en ne respectant pas les recos Siebel : à vous de décider avec le support Oracle ce qu'il faut faire et voir surtout si votre appli restera supportée par Oracle
  - temps des requêtes non amélioré avec les advisors :
  - utilisez le SPA (voir article ici : ) pour tester les gains apportés par une modification de votre environnement comme l'ajout de mémoire
  - si vous ne trouvez aucune amélioration via le SPA, il n'y a plus rien à faire à part migrer votre application vers un autre outil que Siebel qui est, d'après le net, un logiciel complètement dépassé

============================================================================================
Plus d'infos sur le Net
============================================================================================
Ici une discussion que j'ai initiée sur developpez.com.
https://www.developpez.net/forums/d1913483/bases-donnees/oracle/administration/siebel-optimiser-requetes-houlala-contexte-pourri/

Là une autre sur asktom.
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9541414000346488489

Sur le site de Donald Burleson http://www.dba-oracle.com/t_siebel_tuning.htm, nous lisons que certaines compagnies refusent les recos de Siebel et forcent leur appli à utiliser le CBO plutôt que le RBO et à générer les stats sans passer par les scripts fournis par Siebel. Et, miracle, leur appli va bien plus vite. Le problème est que Siebel refuse de supporter ces clients qui ne respectent plus leurs recos. Des clients rétorquent préférer avoir une appli plus rapide et économiser sur le support donc ils sont doublement gagnants.

En résumé, si en tant que DBA Oracle vous devez tuner des requêtes Siebel... bonne chance!