Discussion :

[javaweb44]

voici une expérience qui permet de mettre en évidence mon problème :
a)
la table table1 contient 1 index sur la clé primaire id et 1 index sur le champ ch1
on exécute la requête suivante :
select * from table1 where id like ? and ch1 like ?
st.setString(1,"4256")
st.setString(2,"45281226600024")
-> cette requête est très rapide (moins de 1 seconde)
le plan d'exécution de cette requête passe par l'index sur la clé primaire id


ensuite on exécute une 2ème requête:
select * from table1 where id like ? and ch1 like ?
st.setString(1,"%")
st.setString(2,"45281226600024")
-> cette requête est très lente (+ de 1 minute)
explication : le plan d'exécution de cette requête passe toujours par l'index sur la clé primaire id et donc il parcourt toute la table
pour trouver le bon ch1.

b)
on poursuit l'expérience soit sur une autre base identique soit on attend le lendemain pour
être sûr que le cache oracle a supprimé tous les statements

select * from table1 where id like ? and ch1 like ?
st.setString(1,"%")
st.setString(2,"45281226600024")
-> cette requête est très rapide (moins de 1 seconde)
le plan d'exécution de cette requête passe par l'index sur le champ ch1
on vient donc de mettre en évidence que le plan d'exécution se calcul en fonction des paramètres que l'on passe
lors de la première exécution de la requête.

c)
jusqu'à présent tout est claire
maintenant la suite de l'expérience n'a pour moi pas d'explication satisfaisante :

je change de base (base identique) ou j'attends le lendemain pour
être sûr que le cache oracle a supprimé tous les statements

select * from table1 where id like ? and ch1 like ?
st.setString(1,"%")
st.setString(2,"45281226600024%")
-> cette requête est très lente (+ de 1 minute)
après vérification, le plan d'exécution utilise la clé primaire id et donc
oracle doit parcourir toute la table pour trouver le bon ch1.

conclusion:
la différence de comportement entre l'expérience b et c
montre que lorsqu'un champ se recherche via un like avec %, alors systématiquement oracle ne passe pas par l'index de ce champ


d)
dernière expérience pour montrer que le comportement des statements et des preparedstatements est différent

je change de base (base identique) ou j'attends le lendemain pour
être sûr que le cache oracle a supprimé tous les statements

select * from table1 where id like "%" and ch1 like "45281226600024%"
-> cette requête est rapide (moins de 1 seconde)
après vérification le plan d'exécution montre que l'index utilisé est celui sur le champ ch1

conclusion:
on voit bien qu'avec un statement oracle choisi le plan d'exécution adéquat.


Question :
pourquoi oracle ignore le champ ch1 dans le choix de l'index dès lors que l'on utilise % pour ce champ?
je en vois pas l'intérêt d'ignorer ce champ quand on utilise un %.
et pourquoi le calcul de plan d'exécution est différent entre un statement et un preparedstatement ?
ce comportement est très embêtant car il conduit à dégrader énormément les performances pour ce genre de requête.

[Waldar]

Puisque vous êtes en langage de développement, pourquoi ne faites-vous pas quelque chose du genre :

Code :

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

1
2
if 1 = '%' then select * from table1 where ch1 like ?
           else select * from table1 where id like ? and ch1 like ?

[javaweb44]

je ne cherche pas une solution de contournement (il en existe plusieurs) , je cherche à comprendre pourquoi le fonctionnement des preparedstatements avec un like % conduisent à des problèmes de performance.

[mnitu]

Dans ce type de problème il est vital de mentionner la version d'Oracle. D'après ce que vous dites je suppose qu'elle doit être >= 9i.

D'abord Java n'est pour rien dans cette affaire et donc je préfère de parler en jargon SQL: requête qu'utilise des variables de liaison (PreparedStatement) et requête où les valeurs sont passées en dur
(Statement)

Voilà un exemple pour chacune
PreparedStatement

Code :

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

1
2
 
select * from emp where empno = :b1

Statement

Code :

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

1
2
 
select * from emp where empno = 7934

Pour vos tests vous n'avez besoin que du sqlplus et vous n'avez pas besoin de changer de base ou d'attendre il suffit de «*tagger*» les requêtes (il y en a des autres méthodes bien sûr)

Code :

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

1
2
 
select /* mni req1 */ * from emp where empno = ...

Les cases a et b) ne sont que la manifestation d'un mécanisme disponible à partir de la version 9i et qui s'appelle l'introspection des valeurs des variables de liaison (bind variable peeking). Cela signifie que lors du première parsing de la requête (le parsing hard) Oracle «*regarde*» le contenu de la variable de liaison et élabore un plan d'exécution comme si la valeur avait été passé en dur!

Quand il analyse le cas a) le meilleur plan d'exécution est l'index sur le id. Ensuite après avoir changé la valeur de la variable de liaison la requête s'exécute avec le même plan d'exécution mais pour ce cas c'est un mauvais plan! Dans le cas b) il y a hard parsing mais la valeur actuelle utilisée par le mécanisme d'introspection détermine le choix d'un autre plan.

Vos conclusions pour c) et d) ne sont pas correctes, parfois Oracle peut utiliser un index dans ce cas (like '9999%'). Il y peut y avoir plusieurs explication à la question pour quoi Oracle n'utilise pas l'index: soit parce qu'il n'est pas certain que le résultat est correcte soit parce qu'il est obligé de faire une coercition, cad appliquer une fonction de conversion de type, soit parce qu'il considère que ce n'est pas le meilleur chemin d'accès. Sans un jeux d'essai c'est un peu compliqué de trouver l'explication.

A propos, quelle est le type de données pour les zone id et ch1 ? Comment les statistiques sont calculées ? Utilisez vous les statistiques système ? Quelles sont les valeurs des paramètres de l'optimiseur ?

[SheikYerbouti]

Pourquoi l'index n'est pas utilisé avec LIKE '%' ? mais tout simplement parce que vous demandez toutes les valeurs possible de cette colonne et donc un balayage complet de la table.

[javaweb44]

pour répondre à SheikYerbouti :
ma requête n'est pas LIKE '%' mais LIKE '45281226600024%'
sachant que 45281226600024 correspond à une valeur d'un champ en base.
le % ne sert à rien c'est à dire qu'il ne permet pas de renvoyer des lignes supplémentaires.
le % est juste présent pour montrer que la présence de ce % change le choix du plan d'exécution.

pour répondre à mnitu :
d'abord merci , car je viens d'apprendre que je pouvais introduire le même mécanisme qu'avec des preparedstatements via sqldevelopper et que l'on pouvait tagger les requêtes.

pour répondre à tes questions :
-les champs sont du type varchar2 (donc je pense pas que le % introduise un besoin de conversion)
-les statistques sont lancés tous les soirs mais personne n'a pu me renseigner plus à ce sujet
-personne non plus ne peut me renseigner sur le paramétrage de l'optimiseur



j'ai refais des tests ce matin:
select /* mni req1 */ * from table1 where id like "%" and ch1 like "45281226600024%"
je mets un tag nouveau à chaque fois pour m'assurer que le plan d'exécution est bien recalculé.
mais ce matin (ô surprise) cette requête est rapide (moins de 1 seconde).
alors que cette même requête (dans les mêmes conditions) était lente hier.

donc en effet ma conclusion n'est pas exact car oracle n'ignore pas systématiquement (dans le cas preparedstatement) un champ qui utilise like xx% pour calculer le bon plan d'exécution.

néanmoins oracle fait parfois le mauvais choix de plan d'exécution alors que si on exécute la requête via un simple statement le plan d'exécution est systématiquement le bon.

pour reformuler et être sûr d'être claire:
select /* mni req1 */ * from table1 where id like '%' and ch1 like '45281226600024%'
-> toujours le bon plan d'exécution

select /* mni req2 */ * from table1 where id like :a1 and ch1 like :a2
avec a1 = % et a2= 45281226600024%
-> parfois le mauvais plan d'exécution
je me place évidemment dans le cas où cette requête est lancée pour la première fois.


je n'arrive plus à reproduire le problème de lenteur aujourd'hui alors qu'hier je l'avais et ça fait déjà plusieurs mois que c'est la même chose, parfois lent parfois rapide.

ma conclusion:
soit mes tests sont faux , c'est à dire que je crois que c'est la première fois que j'exécute la requête et que ce n'est pas le cas et que donc le plan d'exécution a été fixé par le premier appel à la requête et donc ne pas convenir aux appels suivants.

soit en effet oracle ne constitue pas le même plan d'exécution s'il passe par un prepared ou par un simple statement mais que le problème n'est pas systématique.

autre phénomène intéressant :
si je lance ma requête sur mon pc via sqldevelopper puis que j'éteinds sqldevelopper et que je relance ma requête je vois que c'est le même preparedstatement qui est utilisé les 2 fois.

par contre si je lance cette même requête depuis soit un sqldevelopper d'un autre pc ou depuis une classe java de mon PC, c'est un autre preparedstatement qui est utilisé.
conclusion : oracle conserve en cache le preparedstatement pour un couple du style (adresseIP,processus) et donc si c'est un autre programme qui réalise la même requête elle n'est pas récupérée depuis le cache.
Un expert peut il me confirmer et me préciser cette conclusion ?

[pifor]

Pourquoi l'index n'est pas utilisé avec LIKE '%' ? mais tout simplement parce que vous demandez toutes les valeurs possible de cette colonne et donc un balayage complet de la table.

Dans certains cas LIKE '%' ou LIKE 'XXX%' peut utiliser un index:

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
65
66
67
68
69
 
SQL>
SQL> drop table t;
 
Table supprimée.
 
SQL>
SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
 
SQL>
SQL> create table t as select * from all_objects;
 
Table créée.
 
SQL> create index t_idx on t(object_name);
 
Index créé.
 
SQL> exec dbms_stats.gather_table_stats( user, 'T' );
 
Procédure PL/SQL terminée avec succès.
 
SQL> set autotrace traceonly explain
SQL> select object_name from t where object_name like '%';
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 3163761342
 
------------------------------------------------------------------------------
| Id  | Operation            | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |       | 12255 |   215K|    16   (7)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T_IDX | 12255 |   215K|    16   (7)| 00:00:01 |
------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("OBJECT_NAME" LIKE '%')
 
SQL> select object_name from t where object_name like 'DBMS%';
 
Plan d'exécution
----------------------------------------------------------
Plan hash value: 2946670127
 
--------------------------------------------------------------------------
| Id  | Operation        | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT |       |     1 |    18 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_IDX |     1 |    18 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("OBJECT_NAME" LIKE 'DBMS%')
       filter("OBJECT_NAME" LIKE 'DBMS%')
 
SQL>