Discussion :

[StringBuilder]

Bonjour,

Savez-vous où trouver une liste de "petites optimisations" et règles afin d'améliorer les performances d'une requête "de façon universelle", c'est à dire indépendamment de son plan d'exécution.

J'entends par là par exemple :
- champ1 = substr(champ2, x, y) plus ou moins rapide que champ2 like '%' || champ1 || '%'
- champ1 = substr(champ2, 0, x) plus ou moins rapide que champ2 like champ1 || '%'
- champ1 = substr(champ2, x, EOF) plus ou moins rapide que champ2 like '%' || champ1
- champ1 || champ2 plus ou moins rapide que concat(champ1, champ2)
- pour un index (champ1, champ2, champ3), "champ1 = x and champ2 = y and champ3 = constante" plus ou moins rapide que "champ1 = x and champ2 = y" quand on sait que champ3 est toujours égal à "constante" ?
- "t1 inner join t2 on t2.champ1 = t1.champ1 and t2.champ2 = t1.champ2" plus ou moins rapide que "t1 inner join t2 on t2.champ1 = t1.champ1 and t2.champ2 = constante" quand on sait que t2.champ2 sera toujours égal à "constante"

Etc.

[lola06]

Salut,

Le seul tuto que je connaisse est celui de SQLPro. Je le trouve assez bien même s'il ne va pas autant dans le détail de ce que tu voudrais avoir...

[StringBuilder]

Le souci, c'est que SQLPro est plutôt orienté SQL Server.

Et entre SQL Server et Oracle, il y a quelques abérations au niveau des optimisations (quand l'un préfère certaines choses, l'autre préfère parfois le contraire).

Et là mon problème est justement qu'en désespoir de cause, je tente le tout pour le tout, pour essayer de faire des "micro-optimisations", espérant que rapporté aux centaines de millions de lignes traitées, ça change quelque chose...

Par exemple, la sous-requête sur un IN est plus rapide sous Oracle que le EXISTS (ceci dit, il me semble que c'est le cas aussi pour SQL Server).

Après des tests fais il y a quelques années, c'est même 2 ou 3 plus plus rapide d'utiliser un IN qu'un EXISTS.

A moins que ça ait encore changé, ce qui ne m'étonnerait pas plus que ça :/

[Scriuiw]

Attention au IN et au EXISTS qui ne te donneront pas nécessairement les mêmes résultats

[StringBuilder]

En tout cas, y'a un truc avec Oracle que je ne comprends pas...

... et je suis à deux doigts de dire des grossièretés à l'encontre de ce "SGBD".

Depuis des années, tout le monde s'égosille (moi le premier) quand il voit une requête du type :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select t1.champ1, t2.champ2
from t1, t2
where t2.t1_id = t1.id;

Et moi, systématiquement, je converti :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select t1.champ1, t2.champ2
from t1
inner join t2 on t2.t1_id = t1.id;

Arguments :
- C'est la syntaxe standard
- Vu qu'on stipule explicitement les jointures, le SGBD est capable d'optimiser au mieux les jointures

C'est parfaitement vrai avec SQL Server.

Mais franchement, et c'est là que ça me gonfle vraiment, c'est absolument FAUX pour Oracle.

Pire :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select t1.champ1, t2.champ2
from t1, t2
where t2.t1_id = t1.id;

Peut mettre le double de temps à s'exécuter que :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select t1.champ1, t2.champ2
from t2, t1
where t2.t1_id = t1.id;

=> Notamment si t1 contient moins de lignes que t2, ou que les clauses de filtre permettent de filtrer plus fortement t1 que t2.

Il est alors très facile de tester divers ordres pour optimiser les requêtes, et ça marche.

Tandis que :

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select t1.champ1, t2.champ2
from t1
inner join t2 on t2.t1_id = t1.id;

et

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select t1.champ1, t2.champ2
from t2
inner join t1 on t2.t1_id = t1.id;

Ça merder à plein tuble, il ne sait plus utiliser les index (je viens de tester).

Du coup, là ça fait plusieurs mois que je suis en clientèle.
Je suis vendu par ma boîte comme CP sur un ERP, mais moi j'ai bien insisté sur ma bonne maîtrise du SQL (et même si je me sens souvent petit pandawa sur ce forum, je pense largement mieux maîtriser SQL qu'une majorité de développeurs, alors que je ne suis qu'un chef de projet, donc pas censé pisser du code toute la journée).
Depuis que je suis ici, je modifie systématiquement les requêtes sur lesquelles je travaille, afin d'utiliser la syntaxe normalisée, et résultat, tout est à chaque fois plus lent. Je passe pour un incompétent fini, le client n'a plus confiance en moi, et franchement, ça me gonfle.

Oracle, t'as vraiment de la chance que tu sois leader du marché, et que ta réputation suffise à vendre des licences. Parce que si c'était les développeurs qui choisissait, tout le monde se tournerais vers MySQL : au moins c'est de la merde, mais tout le monde le sait, et au moins, c'est de la merde qui va vite et qui évolue, pas une bouse infâme qui se complaît dans sa médiocrité (voilà c'est dit).

[pachot]

Bonjour,

La grosse erreur, à mon avis, c'est de comparer le temps d'exécution.
La même exécution peut avoir des temps différents: données en cache, changement du plan d'exécution, etc.

Pour voir ce qui est plus optimal il faut:
1. regarder le plan d'exécution
2. mesurer les statistiques basiques avec autotrace par exemple: consistent gets principalement.

Les règles que tu cherche n'existent pas, en tout cas pas de manière générale. Ca dépendra beaucoup du SGBD, de sa version, du contexte (volume de données), etc.

Sur les exemples que tu donnes,
- il n'y a aucune raison pour que l'ordre des tables dans le 'from' change quelque chose - sauf utilisation de hints particulers.
- il n'y a aucune raison, sauf bug éventuel d'une version, pour que la syntaxed de jointure ANSI soit différente de la syntaxe historique d'Oracle
- il n'y a aucune raison pour que IN et EXISTS de donne pas la même chose en terme de performance, du mois sur les versions pas trop anciennes.

Tout ça ne devrait pas changer le plan d'exécution choisi. Le SQL est un langage déclaratif: tu définit le résultat que tu veux, et pas comment aller chercher les données. C'est l'optimiseur qui fera ce travail. Et il va tout remettre dans l'ordre qu'il choisit.

Il y a quelques cas que l'optimiseur arrive mieux à traiter que d'autres. Par exemple il peut utiliser un index pour un LIKE 'xxx%' alors qu'il n'y pensera pas si tu fais SUBSTR(...,1,...) dans les version actuelles.

Depuis que je suis ici, je modifie systématiquement les requêtes sur lesquelles je travaille, afin d'utiliser la syntaxe normalisée, et résultat, tout est à chaque fois plus lent. Je passe pour un incompétent fini, le client n'a plus confiance en moi, et franchement, ça me gonfle.

Quelle version ? Quel OPTIMIZER_MODE ?

Si l'appli utilise l'OPTIMIZER_MODE=RULE (ce qui ne devrait plus être le cas depuis plusieurs années) alors l'ordre des tables peut avoir un impact. Et le IN/EXIST aussi. Ce que je disais plus haut concerne l'optimiseur statistique (CBO). Donc si ta base est en mode RULE, lorque tu utilise une syntaxe ANSI, alors il passera automatiquement en CBO. Parce que RULE est obsolete et toutes les fonctionnalités qui sont arrivées ensuite (comme la syntaxe ANSI) ne fonctionne pas avec. C'est peut-être une idée si ton problème est si général. En RULE ça marchait bien et en CBO ce n'est plus le cas parce que les statistiques sur les tables sont mauvaises.

Si c'est le cas, il faudra penser à abandonner RULE une fois que les statistiques sont calculées correctement. Et alors, meilleures performances et syntaxe ANSI, et support (parce que RULE plus supporté).

Mais la bonne démarche pour comprendre le problème: comparer les plans d'exécutions.

Si c'est le mêmes, alors c'est que les mesures du temps de réponse de sont pas bonne et ça n'a rien à voir avec tes modifications.

S'ils sont différents, alors il faut investiguer. N'hésite pas à les poster sur ce forum.

Oracle essaie de plus en plus d'optimiser lui même les requêtes. L'avantage: arriver à de bonnes performances sans trop se poser de questions. Inconvénient: grosse instabilité du résultat - donc impossible de juste regarder le temps de réponse d'une exécution. Il faut soit regarder les temps de réponse sur plusieurs exécutions réelles, soit regarder le plan d'exécution et les statistiques de base (blocs lus, volume manipulé).

Le mode 'RULE' dont je parlais était beaucoup plus stable, mais limité en terme de performance. Comme les SGBD que tu as l'air de préférer Mais Oracle a dû évoluer pour traiter des bases de plusieurs teraoctets, avec des milliers de transactions par seconde. Le CBO est mieux à condition que les statistiques des tables soient à jour.

Cordialement,
Franck.

[mnitu]

...
Depuis que je suis ici, je modifie systématiquement les requêtes sur lesquelles je travaille, afin d'utiliser la syntaxe normalisée, et résultat, tout est à chaque fois plus lent. Je passe pour un incompétent fini, le client n'a plus confiance en moi, et franchement, ça me gonfle.
...

Vous perdez votre temps et celui du client aussi. D’une manière évidente vous ne savez pas optimiser les requêtes pour Oracle et le pire est que vous ne voulez pas du tout apprendre une démarche correcte. Vous cherchez toujours la solution magique comme au temps du moyen âge on cherchait la pierre philosophale.

[mnitu]

...
Le mode 'RULE' dont je parlais était beaucoup plus stable, mais limité en terme de performance. ...

Frank il avait plutôt l'air. Parfois pour optimiser une requête il suffisait de supprimer un index et le récréer à l'identique par la suite. Juré!

[pacmann]

Salut,

Du coup, là ça fait plusieurs mois que je suis en clientèle.
Je suis vendu par ma boîte comme CP sur un ERP, mais moi j'ai bien insisté sur ma bonne maîtrise du SQL (et même si je me sens souvent petit pandawa sur ce forum, je pense largement mieux maîtriser SQL qu'une majorité de développeurs, alors que je ne suis qu'un chef de projet, donc pas censé pisser du code toute la journée).
Depuis que je suis ici, je modifie systématiquement les requêtes sur lesquelles je travaille, afin d'utiliser la syntaxe normalisée, et résultat, tout est à chaque fois plus lent. Je passe pour un incompétent fini, le client n'a plus confiance en moi, et franchement, ça me gonfle.

Oracle, t'as vraiment de la chance que tu sois leader du marché, et que ta réputation suffise à vendre des licences. Parce que si c'était les développeurs qui choisissait, tout le monde se tournerais vers MySQL : au moins c'est de la merde, mais tout le monde le sait, et au moins, c'est de la merde qui va vite et qui évolue, pas une bouse infâme qui se complaît dans sa médiocrité (voilà c'est dit).

Sincèrement, si j'ai bien compris ta situation, c'est ton arrogance qui te mène à ta perte.
Tu connais un peu / bien le SQL, et du coup tu prends l'initiative de t'imposer en tant que référent, et visiblement tu oublies de te dégager de la responsabilité sur les performances.

Coder, optimiser, c'est un vrai boulot.

On me demande régulièrement de l'assistance sur du SQL alors que je ne suis que MOA. Bien que comme toi je connaisse un peu / bien le SQL et Oracle, jamais il ne me viendrais à l'idée de promettre quoi que ce soit sur ce domaine où je ne suis pas expert (qui n'est plus mon métier depuis longtemps).

Ma réponse standard : "T'es tu mis en contact avec le DBA ? Pour ma part, je peux regarder un peu pour voir si quelque chose me saute aux yeux... mais je ne peux rien te garantir"

Parce que j'ai peur ? Non... juste parce que j'ai conscience que c'est un vrai métier, et que ça ne s'improvise pas.

PS : Lola, ne prends pas le tuto de SQLPro au sérieux, en tous cas pas pour Oracle. Il n'y a que des règles magiques de transformation, alors que comme on le voit tous les jours, la réalité de l'optimiseur est tout sauf du tour de passe-passe

[StringBuilder]

Il n'est pas question d'arrogance ici.

Moi je pars d'un constat simple :
- Il y a un DBA qui travaille en intérim 1/2 journée par semaine
- Il est sur un site différent du mien, ne gère pas que ma base, et je n'ai jamais eu l'autorisation de faire appel à lui pour quoi que ce soit
- Les statistiques sont recaculées le dimanche, alors que certaines tables subissent des millions de modifications dès le recacul des stats effectuées
- Le client ne veut rien savoir quand je lui dis que c'est pas une façon de gérer un serveur Oracle (évidement, je ne lui dis pas comme ça)

Du coup moi je suis comme un con avec des stats pourries, et un Oracle qui se comporte n'importe comment (il préfère parfois utiliser un index dans lequel je ne filtre qu'un seul champ situé en 4° position, alors que l'ensemble de mes filtres portent sur l'index unique de la table). Je n'ai rien d'un DBA, que ce soit niveau compétences, mais aussi niveau matériel : pour activer le mode trace, encore faut-il disposer d'un compte ayant les droits pour le faire...

Bref, des requêtes existent. Le besoin évolue, je tente d'y apporter des modification, et là, sur la requête dont je parle dans l'autre topic, on est passé de 30 minutes (ce qui est déjà, au vue de la volumétrie, complètement absurde) à 2 heures 40. L'explication est simple et rationnelle, le nombre de lignes lues est démultiplié, mais c'est pas moi qui fait la demande. Et vu que je n'ai pas droit de créer d'autres éléments permettant d'améliorer les choses (par exemple, un trigger pour stocker dans un champ le résultat du max() dont j'ai besoin), je suis coincé.

Et pour toutes ces raisons, c'est bien une solution "miracle" que je recherche, car si j'avais les outils pour ça fait longtemps que j'aurais tenté de modifier ne serait-ce que la périodicité de recalcul des statistiques.

Quand je vois que sur certaines tables, sur la base de dev, donc sans aucune charge, le serveur met parfois 15 minutes à me retourner un count(*) sans filtre sur une table, je me pose des questions.

[pacmann]

D'accord, je comprends... sur le message précédent t'avais l'air de dire "c'est parce qu'Oracle pue", mais c'est bien parce que ton client refuse de s'en servir correctement
(...Et désolé pour l'arrogance, hein !)

A exposer à ta société, leur expliquer que sans les moyens adéquats tu ne peux rien faire.

Parce que les micro optimisations ne te rendront pas des perfs correctes.
(Genre si ta requête est 100 fois trop lente parce qu'elle lit 100 trop de blocs, optimiser disons même de 10% le traitement de la donnée lue ne te rendra pas heureux...)

[StringBuilder]

Ils doivent changer de version de l'ERP.

Qui dit changement de version dit :
- Nouveau serveur plus puissant
- Nouveau Oracle plus récent (on passe d'une 10 R1 à une 11 R2 logiquement). Et surtout, nouveau paramétrage d'Oracle (effectivement, il est actuellement en mode RULE sur préconisation de l'éditeur, et ça change il me semble dans cette version)
- Réorganisation ce certaines tables, et mise en place d'archives dans une base auxiliaire (actuellement, on se palluche des millions de lignes de 2005 dont personne n'a rien à faire, mais qu'on doit garder pour raison légales)

J'espère qu'avec ces changements, on arrivera à quelque chose... C'est loin d'être gagné si y'a toujours pas de DBA qui s'occupe du serveur !

Mais d'ici là, de l'eau va couler sous les ponts, et tous les jours bibi va devoir expliquer pourquoi le traitement qu'il a touché est passé de 30 minutes à 2h40

[pachot]

effectivement, il est actuellement en mode RULE sur préconisation de l'éditeur

Ah. J'avais bien deviné. Ton éditeur livre l'appli pour qu'elle tourne en mode RULE.
Les statistiques sont calculées n'importe comment, mais pas de soucis: RULE ne les utilise pas. Donc tout ça fonctionne moyennement, comme fonctionnaient les applis sur les vieilles versions d'Oracle.
Parce que RULE c'est ce qui existait avant la 8i. Ils l'ont laissé pour raison de compatibilité mais toutes les nouvelles fonctionnalités ajoutées après la 8i ne fonctionnent pas en RULE.
Elles n'ont été imlémentèes que dans l'optimiseur statistique, le CBO, le seul qui est supporté par Oracle dans ta version.

Et toi tu modifie les requêtes, avec plein de bonnes intentions. Tu utilise la syntaxe ANSI parce que tu trouve ça beaucoup plus lisible (je trouve aussi ).

Mais Oracle a intégré la syntaxe ANSI qu'après la 8i. Et cela ne fonctionne pas en RULE. RULE 'est plus supporté, il n'évolue plus. Et que se passe-t-il alors ? Il pourraient simplement t'envoyer une erreur 'cannot use ANSI in RULE mode'. Mais ils ont fait un autre choix: dès que tu utilise une fonctionnalité qui n'existe pas en RULE, l'optimiseur passe en CBO.

Et c'est ton cas: tu parles du coût du plan d'exécution. Mais il n'y a pas de coût en RULE. C'est le CBO (Cost Based optimizer) qui calcule le coût.

Et tu as des plans d'exécutions catastrophiques, parce qu'avec des statistiques pourries, les estimations sont fausses et les plan d'exécution choisis sont mauvais. CBO est un optimiseur statistique, il a besoin des statistiques.

Tes solutions:
- ne rien changer qui feraient passer en CBO. Vérifie le plan d'exécution. Si tu as un cost, tu n'es plus en RULE
- prendre le risque de passer en CBO contrairement aux préconisations de l'éditeur, parce que tu trouves ringard d'être encore en RULE en 10g (et je trouve aussi ) mais alors, essaie d'avoir des statistiques correctes. Mieux vaut ne pas avoir de statistiques que d'en avoir des mauvaises car sans statistiques, le CBO va faire du dynamic sampling au moment du parse pour se faire une idée.

il préfère parfois utiliser un index dans lequel je ne filtre qu'un seul champ situé en 4° position, alors que l'ensemble de mes filtres portent sur l'index unique de la table

Ça paraît tellement gros. Il serait intéressant de comparer ce plan d'exécution avec celui où on forcerait l'utilisation de l'index unique avec un hint. Il est bien utilisable cet index ?

Le besoin évolue, je tente d'y apporter des modification

bibi va devoir expliquer pourquoi le traitement qu'il a touché est passé de 30 minutes à 2h40

Je sais c'est très difficile à faire comprendre et il faut souvent argumenter longtemps. Mais si on touche à quelques chose de bancal, ça tombe. Et c'est celui qui y a touché qui est responsable.

Il faut être clair: soit geler les évolutions sur ce logiciel, soit budgéter une grosse activité de stabilisation.
Imagine que tu sois architecte. Je te demandede rajouter un étage à la tour de Pise. Tu acceptes ?

Bon courage,
Franck.

[mnitu]

...
Si l'appli utilise l'OPTIMIZER_MODE=RULE (ce qui ne devrait plus être le cas depuis plusieurs années) alors l'ordre des tables peut avoir un impact. Et le IN/EXIST aussi. Ce que je disais plus haut concerne l'optimiseur statistique (CBO). Donc si ta base est en mode RULE, lorque tu utilise une syntaxe ANSI, alors il passera automatiquement en CBO.
...

Franck est-vous certain de ce que vous affirmez ici ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
 
mni@DIANA> select count(*)
  2  from hr.employees a, hr.departments b
  3  Where a.department_id = b.department_id
  4  /
 
  COUNT(*)
----------
       106
 
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 1756381138
 
----------------------------------------
| Id  | Operation          | Name      |
----------------------------------------
|   0 | SELECT STATEMENT   |           |
|   1 |  SORT AGGREGATE    |           |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |
----------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A"."DEPARTMENT_ID" IS NOT NULL)
 
Note
-----
   - rule based optimizer used (consider using cbo)
...
mni@DIANA> select count(*)
  2  from hr.employees a
  3       Join
  4       hr.departments b
  5  on a.department_id = b.department_id
  6  /
 
  COUNT(*)
----------
       106
 
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 1756381138
 
----------------------------------------
| Id  | Operation          | Name      |
----------------------------------------
|   0 | SELECT STATEMENT   |           |
|   1 |  SORT AGGREGATE    |           |
|*  2 |   TABLE ACCESS FULL| EMPLOYEES |
----------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("A"."DEPARTMENT_ID" IS NOT NULL)
 
Note
-----
   - rule based optimizer used (consider using cbo)

[pachot]

Bonjour mnitu,

Franck est-vous certain de ce que vous affirmez ici ?

Bonne idée d'avoir testé . Mon affirmation effectivement était trop générale. Désolé.

C'est en fait les syntaxes ANSI pour les jointures externes seulement qui on ce problème.
Les limitations de RULE sont clairement documentée par la Product Manager du CBO: https://blogs.oracle.com/optimizer/e..._the_rule_hint

Merci d'avoir rectifié.

Ce qui est sûr, c'est que StringBuilder est passé en CBO à certains moments, puisque dans son autre post post il a un coût dans le plan d'exécution.
Dans ce post-ci, comme il change de plan d'exécution en changeant l'ordre des colonnes dans le from, il était surement en RULE.

Donc mon conseil à StringBuilder:
- toucher le moins possible aux requêtes en attendant de passer en CBO. Et si tu dois le faire, compare bien les plans d'exécution avant/après.
- ne pas juger Oracle tant que tu utilise une techno qui date de plus de 20 ans. L'optimiseur statistique a été introduit en 1992 pour replacer RULE dans les années qui suivent.

Cordialement,
Franck.

[mnitu]

Le problème du StringBuilder est que lui a deux méthodes d'optimisation pas très appréciées :

• c'est la recherche des règles d'or (silver bullets)
• c'est ce qu'on appelle KILL THEM WITH IRONS (tuez-le avec de la ferraille: nouveau serveur, version d'Oracle, etc...)

Si il reprenne ses autres interventions il va trouver quelques, bons conseils de ce qu'il convient de faire dans son cas. Et je ne veux pas me prononcer sur le fait de ne pas s'apercevoir pour quelle optimiseur il «optimise» (et j'ai de doutes ...).