Discussion :

[exempleinfo]

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 :

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

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

methode 2

Code :

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

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

[jf4db]

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.

[orafrance]

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

[pifor]

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.

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.

[orafrance]

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
http://download.oracle.com/docs/cd/B...ory.htm#i40017

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.

[jf4db]

autant pou moi !!

[laurentschneider]

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

Si tu as une requete

Code :

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

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 :

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

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

[laurentschneider]

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

:-)

[pifor]

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.

[exempleinfo]

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

[lmartin]

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.

[pifor]

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

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.

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.