preparedstatement java avec like %, lenteur de la requête

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.

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

Code:

---

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

---

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.

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:

---

1 2	select * from emp where empno = :b1

---

Statement

Code:

---

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:

---

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 ?

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.

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 ?

Citation:

---

Envoyé par 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.

---

"LIKE'%'" ou "LIKE 'xxxx%'" ==> même combat...

Citation:

---

Envoyé par javaweb44

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 ?

---

Je pense qu'il s'agit d'une conclusion très improbable car si votre conclusion était vraie, le shared pool (partie de la SGA qui contient le cache des requêtes SQL et du code PL/SQL) aurait un fonctionnement très différent: comme son nom l'indique, le but du shared pool est de partager le plus possible le code SQL et PL/SQL afin d'éviter de recompiler chaque instruction soumise (hard parse). La vue V$SQL_SHARED_CURSOR en Oracle 10.2 permet de savoir pourquoi un curseur interne (=une requête SQL) n'a pas été réutilisé.

Idéalement pour avoir un scénario reproductible, il faudrait utiliser une base en mono utilisateur et ALTER SYSTEM FLUSH SHARED_POOL qui vide le shared pool.

Merci de précisez votre version Oracle.

Code:

---

1 2 3

... st.setString(1,"%") ...

---

je n'invente rien....

réponse à SheikYerbout :
il s'agit de l'étape c) qui correspond à :
select * from table1 where id like ? and ch1 like ?
st.setString(1,"%")
st.setString(2,"45281226600024%")

or cette requête est lente même quand c'est la première fois que je lance cette requête et que donc le plan d'exécution se base sur les valeurs passées à la première exécution.
le deuxième critère devrait permettre de trouver la ligne dans la base très rapidement, et c'est d'ailleurs ce qui se passe systématiquement via un statement.
or via un preparedstatement parfois cette requête est interminable.

le problème est assez complexe, il faut bien lire l'ensemble du scénario pour bien comprendre qu'elle est mon problème.

merci pour ton aide.

Citation:

---

Envoyé par 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.

---

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

Code:

---

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>

---

Je suis d'accord, mais il l'utilise pour faire un full scan, puisque de toute façon, "%" veut dire "toutes les valeurs", conduisant de facto à une lecture intégrale de la table.

pour répondre à pifor sur la gestion du cache des statements :

moi non plus je ne comprends pas bien l'intérêt de gérer un cache par programme ou IP ou je ne sais quoi.

j'ai pourtant refait mes tests plusieurs fois.
et c'est ce que je constate.

l'expérience n'est pas très difficile à reproduire si quelqu'un à envie de confirmer ou bien de prouver que je me trompe :
- oracle 10
- il faut une table assez volumineuse (plus simple pour vérifier le comportement) avec 2 champs ayant chacun 1 index
- faire une requête via sqldevelopper :
select * from table1 where id like :1 and ch1 like :2
:1 -> prendra une valeur existante en base
:2 -> prendra %
-> cette requête est rapide

- vérifier sous entreprise manager par exemple que le preparedstatement mis en cache utilise le champ id

- relancer cette même requête en inversant les valeurs en entrée :
:1 -> prendra %
:2 -> prendra une valeur existante en base
-> cette requête est lente

- copier coller la même requête sous sqldevelopper du pc de votre collègue
puis relancer cette même requête :
:1 -> prendra %
:2 -> prendra une valeur existante en base
-> cette requête est rapide
on peut vérifier que sous oracle entreprise manager, un nouveau preparedstatement est présent et l'index utilisé est le champ ch1
de plus si on retourne sur son pc et que l'on relance cette même requête elle sera toujours lente.

cette expérience montre bien que le cache des statements n'est pas partagé par tous les clients oracle.

like % fait un full scan c'est évident
mais like 123456% dans le cas où 12456 correspond exactement à la valeur dans une des lignes passe par l'index.
en tout cas c'est toujours ce que j'ai constaté quand je fais une requête en dur via sqldevelopper.

évidemment si on se content de faire like 1% oracle décidera soit de faire un full scan soit de passer par l'index.

Citation:

---

Envoyé par Vincent Rogier

"LIKE'%'" ou "LIKE 'xxxx%'" ==> même combat...

---

Non non, certainement pas !
Le INDEX RANGE SCAN présenté plus bas par Pifor dans le cas du LIKE 'xxx%' le montre bien.

Autant pour moi...

La digestion de mon flan pâtissier a quelque peu altéré mes facultés...;)

Citation:

---

Envoyé par Vincent Rogier

Autant pour moi...

La digestion de mon flan pâtissier a quelque peu altéré mes facultés...;)

---

Allez, avoue que t'as pris un double apéro à la cantine !

à la question, le cache des preparedstatement est il partagé par tous les clients oracle ?

que pensez vous de l'hypothèse suivante :

oracle a un cache par client de manière délibérée.
2 applications différentes n'attaquent pas forcément le même jeu de donnée.
par exemple, l'application de l'ERP de comptabilité va attaquer la table facture avec le champ statut = 'Payé" alors que l'application de commerce va attaquer la table facture avec le champ statut='en cours'
(pardonné moi pour mon exemple de non initié aux ERP...)

et donc comme les données attaquées par les 2 applications sont différents il est préférable que chacune des 2 applications aient leur propre preparedstatement car ils sont susceptibles d'avoir des plan d'exécutions différents.

PreparedStatement ou Statement vont finir dans un seul et unique « caché » partagé par tous les clients. Du ce point de vue Oracle ne fait pas la différence entre requête avec variable de liaison et sans variable de liaision.

Citation:

---

Envoyé par javaweb44

...
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.
...
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.
...

---

C'est ce que je pense vu la méthode employé.
Sinon la réutilisation des requêtes se fait pour des requêtes strictement identiques (c'est pour ça que l'astuce avec tagger les requêtes marche).