Discussion :

[Greybird]

Bonjour,

Je travaille sur Oracle 10.2.0.4.

Je rencontre aujourd'hui un souci qui me laisse perplexe, et je me tourne vers vous en quête d'une explication : une certaine requête non paramétrisée est extrèmement plus rapide que la même requête paramétrisée à laquelle on aurait passé les mêmes valeurs en paramètres. (1 seconde vs 1 minute)

Ci dessous les requêtes et leur plan d'exécution.

Code Requête paramétrisée :

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

1
2
3
4
5
6
7
 
SELECT   *
    FROM TTABLE
   WHERE 0 = 0
     AND dateSaisie >= :dateSaisie
     AND type = :type
ORDER BY idt DESC;

Code Plan d'exécution :

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

1
2
3
4
5
6
7
 
Operation                                Object Name     Rows     Bytes     Cost
 
SELECT STATEMENT Optimizer Mode=CHOOSE                    458               5611
  SORT ORDER BY                                           458       631 K   5611
    TABLE ACCESS BY INDEX ROWID     BDD$OWNER.TTABLE      458       631 K   5092
      INDEX RANGE SCAN     BDD$OWNER.ITTABLE3             8 K                 27

Code Requête non paramétrisée :

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

1
2
3
4
5
6
7
 
SELECT   *
    FROM TTABLE
   WHERE 0 = 0
     AND dateSaisie >= TO_DATE('03/09/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS')
     AND type = 'TYPE'
ORDER BY idt DESC;

Code Plan d'exécution :

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

1
2
3
4
5
6
7
 
Operation                                Object Name     Rows     Bytes     Cost
 
SELECT STATEMENT Optimizer Mode=CHOOSE                      1                  5
  SORT ORDER BY                                             1       1 K        5
    TABLE ACCESS BY INDEX ROWID     BDD$OWNER.TTABLE        1       1 K        4
      INDEX RANGE SCAN     BDD$OWNER.ITTABLE3               1                  3

S'agissant finalement du même plan, comment les coûts peuvent-il être aussi différents (moindre optimisation dans le cas de la requête paramétrisée du fait d'une moindre information ?) ?
Et surtout, pourquoi une telle différence de temps d'exécution au final ? Avez-vous des pistes pour résoudre ce souci ?

Merci d'avance,

Arnaud

[pachot]

Bonjour,
Sur le coût estimé, c'est normal puisque avec variable il estime que le prédicat correspond à 8000 entrées d'index alors qu'en conaissant la valeur (probablement la date qui est récente) c'est beaucoup moins.
Par contre le coût réel devrait être le même.
Peux-tu lancer la requête avec autotrace ou tkprof pour voir le nombre de blocs lus ? Le temps peut dépendre de ce qui est en cache.
Cordialement,
Franck.

[Greybird]

Bonjour et merci de ta réponse.

Notre DBA a fini par trouver le souci. Nous appelions ces requêtes depuis du code .NET, et nous bindions un objet de type .NET DateTime dans un paramètre de type DbType.DateTime.

Or DbType.DateTime correspond au type Oracle TimeStamp. Il fallait utiliser DbType.Date qui correspond au type Oracle Date.

Cette simple erreur causait le problème de performance indiqué.

Merci !

Arnaud

[mnitu]

Franchement je ne crois pas trop à votre solution. Déjà parce qu’un DBA qui fait tourner une base Oracle 10g en mode CHOOSE ce n’est pas un bon signe.

Je pense plutôt que vous avez être victime d’un phénomène de type introspection des valeurs des variables de liaison, « peek variable binding ». Une investigation plus approfondie pourrait vous éviter des mauvaises surprises dans l’avenir.

[Greybird]

Les paramètres passés avec ces paramètres ont permis de corriger la performance, suite à analyse par un de nos dba.
Je ne vois pas de raison de douter de son diagnostic en l'état.

En revanche, merci d'avoir porté ce phénomène de "peek variable binding", je vais étudier le cas et voir si il nous faut creuser plus loin.

Quand au fait que la base tourne en mode CHOOSE, je leur fait confiance sur la pertinence de ce choix, et d'ailleurs, je ne suis pas qualifié pour le critiquer. S'agissant d'une base critique énorme centralisant des traitements énormes et vieille de plus de 10 ans, avec des critères de disponibilité extrèmes, j'imagine que les changements de versions d'Oracle n'ont pas justifié la prise de risque amenée par un changement de configuration (mais là encore n'étant pas spécialiste, et mon problème de performance étant résolu).

[mnitu]

Bonjour,

Je vais essayer en quelques mots de vous donner « ma raison ».
Votre requête paramétrée existe déjà dans le shared pool. Le plan d’exécution qu’elle utilise a été déterminé par la valeur passé dans la variable de liaison (binding variable ou paramètre) au moment du "hard parsing" de la requête. A ce moment un curseur parent et un curseur fils à été crée et stockés dans le shared pool.

Quand vous avez changé le type de la variable de liaison en utilisant le DbType.Date le curseur parent est resté identique mais le nouveau type de la variable de liaison fait que le curseur fils ne peut plus être utilisé et donc un nouveau plan a été recherché en utilisant cette fois la nouvelle valeur de la variable de liaison. Ce qui à « résolu » en apparence votre problème de performance.

Jusqu’au là, dans votre cas, ces ne sont que des hypothèses. Mais, pour avancer je pense que vous pouvez faire un test assez simple. L’idée est de ré-exécuter 2 à 3 fois votre requête avec un variable de liaison de type DateTime (celle qui vous posée des soucis) et la valeur de votre exemple mais, en prenant soin de s ‘assurer que le hard parsing a lieu. Pour cella divers méthodes peuvent être employées comme par exemple la modification de la requête par l’ajout d’un alias de la table. Si votre DBA a raison le temps d’exécution de la requête doit reste désastreux : c’est le type de la variable qui pose problème, non ? Si non ou pourrait en discuter.

Bon courage!