Optimisation SQL et BIND variable

Bonjour,
J'utilise Oracle 9i sou w2003, avec SQL Developer d'oracle je soumets un requête avec des paramètres, et suivant que la manière de passage des paramètres mon plan d’exécutions et le temps de réponse se multiplie par 5 à 10 :

methode 1 plus performante

Code:

---

1 2	Def &param1 = xx Select * from dept where champ1= &param1

---

methode 2

Code:

---

Select * from dept where champ1= :param1

---

D’abord c’est quoi la différence entre c’est méthode?
Comment on peut avoir la même performance ? Ou au moins expliquer la différence ?
Merci de votre aide

Pour la première methode , oracle analyze le plan d'execution à chaque lancement de la requête .

Pour la deuxieme, oracle analyze le plan pour le premier lancement puis utilisera toujours le m^me plan grace à la bind variable. L'avantage est déconomiser le cout d'analize du plan donc de réduire le temps global.

Citation:

---

Envoyé par jf4db

le cout d'analize du plan

---

qu'on appelle le parsing d'où l'appellation parse hit ratio qui indique le taux de recalcul de plan d'exécution de la base ;)

Citation:

---

Envoyé par exempleinfo

J'utilise Oracle 9i sou w2003, avec SQL Developer d'oracle je soumets un requête avec des paramètres, et suivant que la manière de passage des paramètres mon plan d’exécutions et le temps de réponse se multiplie par 5 à 10 :

---

S'agit-il de la vraie requête exécutée ou seulement d'un exemple ?
Quel est l'ordre de grandeur de la durée d'exécution de la requête ?
Quel est le nombre total de lignes dans la/les table(s) concernée(s) ?
La colonne utilisée dans la clause WHERE est-elle indexée ?
Combien de lignes retourne la requête en moyenne ?
La charge de la base est-elle la même pendant vos tests (que font les autres connexion sur la base ? Que se passe-t-il d'autre sur le serveur ?)
Idéalement, il faudrait activer la trace SQL et TKPROF sur les différentes exécutions pour comparer les vrais plan d'exécutions.

A moins d'exécuter des requêtes des dizaines ou des centaines de fois par secondes, le coût de la compilation (hard parsing) ne peut pas expliquer des écarts aussi importants.

Citation:

---

Envoyé par jf4db

Pour la première methode , oracle analyze le plan d'execution à chaque lancement de la requête .

---

Sauf si la requête a déjà été exécutée avec la même valeur du paramètre.

Citation:

---

Envoyé par pifor

Sauf si la requête a déjà été exécutée avec la même valeur du paramètre.

---

ou si cursor_sharing=SIMILAR si je ne m'abuse ;)

Edit : je ne m'abuse pas visiblement :mrgreen:
http://download.oracle.com/docs/cd/B...ory.htm#i40017

Citation:

---

Usually, SQL statements that differ only in literals cannot use the same shared SQL area. For example, the following SQL statements do not resolve to the same SQL area:

SELECT count(1) FROM employees WHERE manager_id = 121;
SELECT count(1) FROM employees WHERE manager_id = 247;

The only exception to this rule is when the parameter CURSOR_SHARING has been set to SIMILAR or FORCE. Similar statements can share SQL areas when the CURSOR_SHARING parameter is set to SIMILAR or FORCE. The costs and benefits involved in using CURSOR_SHARING are explained later in this section.

---

autant pou moi !! ;)

quant aux bind variables, elles n'ont pas que des avantages.

Si tu as une requete

Code:

---

select * from t where deptno=:x;

---

et qu'il y a 99.9999% des employés dans le département 10, alors le plan-10 pour le département 20 sera foireux.

Dans Oracle 11g,

Code:

---

select * from t where deptno=:x;

---

génére plusieurs plans, un avec Full Table Scan pour le département 10 et un avec Index Scan pour le département 20.

On dit parfois que bind variable est divin en oltp et démon en dw.

A+
Laurent

Citation:

---

Envoyé par pifor

Idéalement, il faudrait activer la trace SQL et TKPROF sur les différentes exécutions pour comparer les vrais plan d'exécutions.

---

oui, mais il faut se méfier des conclusions hatives avec les bind variables... la trace sql et la clause EXPLAIN de tkprof peuvent être différentes de la réelle exécution !

http://asktom.oracle.com/pls/asktom/...55493384131730

:-)

Citation:

---

Envoyé par laurentschneider

oui, mais il faut se méfier des conclusions hatives avec les bind variables... la trace sql et la clause EXPLAIN de tkprof peuvent être différentes de la réelle exécution !

http://asktom.oracle.com/pls/asktom/...55493384131730

:-)

---

Pour EXPLAIN PLAN, c'est même officiellement documenté. Pour la trace SQL, il y a un article dans Oracle Magazine.

Citation:

---

Envoyé par pifor

S'agit-il de la vraie requête exécutée ou seulement d'un exemple ?
Quel est l'ordre de grandeur de la durée d'exécution de la requête ?
Quel est le nombre total de lignes dans la/les table(s) concernée(s) ?
La colonne utilisée dans la clause WHERE est-elle indexée ?
Combien de lignes retourne la requête en moyenne ?
La charge de la base est-elle la même pendant vos tests (que font les autres connexion sur la base ? Que se passe-t-il d'autre sur le serveur ?)
Idéalement, il faudrait activer la trace SQL et TKPROF sur les différentes exécutions pour comparer les vrais plan d'exécutions.

A moins d'exécuter des requêtes des dizaines ou des centaines de fois par secondes, le coût de la compilation (hard parsing) ne peut pas expliquer des écarts aussi importants.



Sauf si la requête a déjà été exécutée avec la même valeur du paramètre.

---

- Non, ce n'est pas la vraie requête.
- environ 3 mn dans le pire des cas.
- environ 200.
- oui les colonnes sont indexe.
- la charge de la base est la même.
....
La vraie différence c'est que si j'utilise sql developer d'oracle avec un driver JDBC sinon quand j'utilise sqlplus le temps de réponse est d'environ 30 secondes au lieu de 3 mn

30 secondes pour requêter une table de 200 lignes .... ça parait effectivement très long

Essaie de changer le paramètre dans le fichier init.ora :
db_file_multiblock_read_count=128

Le but est de parser ta table en full avec un nombre de blocs lus en parallèle de 128.
Assurre toi de bien avoir calculer les stats sur ta table. Normallement ton index ne sera pas utilisé et la réponse doit être instantanée.

Si tu veux de bonne perf évite de faire du cursor_sharing=SIMILAR prend plutôt EXACT.
Le SIMILAR ne "sert" que si tu as un code applicatif avec des bind variables.

Citation:

---

Envoyé par lmartin

Essaie de changer le paramètre dans le fichier init.ora :
db_file_multiblock_read_count=128

---

Pas d'accord en général pour changer un paramètre d'initialisation qui risque de modifier un nombre indéterminé de plans d'exécutions :aie:

Citation:

---

DB_FILE_MULTIBLOCK_READ_COUNT

This parameter specifies the number of blocks that are read in a single I/O during a full table scan or index fast full scan. The optimizer uses the value of DB_FILE_MULTIBLOCK_READ_COUNT to cost full table scans and index fast full scans. Larger values result in a cheaper cost for full table scans and can result in the optimizer choosing a full table scan over an index scan.

---

Citation:

---

Envoyé par lmartin

Le SIMILAR ne "sert" que si tu as un code applicatif avec des bind variables.

---

Non, justement c'est l'inverse ! S'il n'y a pas de bind variables, CURSOR_SHARING à SIMILAR ou FORCE va transformer des littéraux de requêtes en bind variables.

Euh ... désolé si j'insiste.
On peut avoir l'avis d'un tiers ?

Donnez-nous une "preuve" avec un scénario de test complet et la version d'Oracle pour qu'on puisse le reproduire. Pour CURSOR_SHARING, voir ce que dit Laurent Schneider sur son blog

Effectivement lorsqu'on change un paramètre d'instance on fait un certain nombre de test sur les environnements de PRé-PROD, et si on veut limiter ce genre de test on fait simplement un "alter session".

Une précision sur CURSOR_SHARING.
Effectivement on a intérêt à le positionner à SIMILAR dans le cas où il n'y a pas de bind variable dans le code.

Ce qu'on gagne c'est la hash_value du code sql mis en mémoire dans la shared_pool. On réutilise une précedente exécution pour re-employer cette hash_value; puis si les colonnes "bindé" sont reconnu par oracle comme sûr le plan d'exécution n'est pas redéfinit. On gagne sur ce second plan également, à condition que l'optimizer est raison de considérer ce plan comme sûr.

Si la valeur est FORCE, l'optimizer ne se pose pas la question et réutilise systématiquement le plan d'exécution.

Dans certain cas (DW), c'est une erreur et il est alors préférable d'affecter la valeur EXACT à cursor_sharing.

Citation:

---

Envoyé par laurentschneider

quant aux bind variables, elles n'ont pas que des avantages.

Si tu as une requete

Code:

---

select * from t where deptno=:x;

---

et qu'il y a 99.9999% des employés dans le département 10, alors le plan-10 pour le département 20 sera foireux.

Dans Oracle 11g,

Code:

---

select * from t where deptno=:x;

---

génére plusieurs plans, un avec Full Table Scan pour le département 10 et un avec Index Scan pour le département 20.

On dit parfois que bind variable est divin en oltp et démon en dw.

A+
Laurent

---

Désolé d'arriver après la bataille et de dépoussiérer un vieux sujet mais je suis confronté à un problème similaire. Je suis en 10gR2

J'ai le plan d'exécution d'une requête avec des bind variables qui est mauvais (car full scan qui serait justifié dans la majorité des cas), alors que la requête en mettant les valeurs des variables me donne un plan d'exécution correct (accès par index qui est effectivement meilleur par rapport aux valeurs des variables). Les stats sont bien à jour

Y a-t-il un moyen de remédier à celà, autre que des hints (c'est une requête générée par un progiciel donc impossible de la modifier) ?

regarde du coté des histogrammes ;)

Ca ne change rien ... :cry:
Je crois que je vais me tourner vers les plans d'exécution stockés ... :?

Citation:

---

Envoyé par scheu

J'ai le plan d'exécution d'une requête avec des bind variables qui est mauvais (car full scan qui serait justifié dans la majorité des cas), alors que la requête en mettant les valeurs des variables me donne un plan d'exécution correct (accès par index qui est effectivement meilleur par rapport aux valeurs des variables). Les stats sont bien à jour

Y a-t-il un moyen de remédier à celà, autre que des hints (c'est une requête générée par un progiciel donc impossible de la modifier) ?

---

a/ Plan stability
b/ migrer vers 11g
c/ supprimer certains histograms
d/ ?

Citation:

---

Envoyé par DAB.cz

c/ supprimer certains histograms

---

Peux-tu expliciter cette méthode ?

Citation:

---

Envoyé par scheu

Peux-tu expliciter cette méthode ?

---

Oui, à coups du blog de quelqu'un d'autre.