Discussion :

[jlinho2]

Bonjour,

j'ai une question un peu "débutant" à vous poser:

Qu'elle est la différence sous le capot d'Oracle ( de stockage des plans d'exécution des requêtes, le moment du calcul du plan d'exécution, moment du passage des paramètres, passage de paramètres entre moteurs PL/SQL et moteurs SQL etc...) entre:

1 - Utilisation d'un REF CURSOR avec passage de paramètre par USING

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
 
declare 
  2       TYPE CURSOR_CLIENTS IS REF CURSOR; 
  3       c_clients CURSOR_CLIENTS; 
  4       v_client  CLIENTS%ROWTYPE; 
  5       v_pays    CLIENTS.PAYS%TYPE := 'France'; 
  6       v_SQL     varchar2(200)  
  7                := 'SELECT * FROM CLIENTS WHERE PAYS = :a_pays'; 
  8  begin 
  9       open  c_clients FOR v_SQL  USING  v_pays; 
 10       loop 
 11            fetch c_clients into v_client; 
 12            exit when c_clients%NOTFOUND; 
 13            dbms_output.put_line( 'Client : '|| 
 14                        v_client.societe||' '|| v_client.ville); 
 15       end loop; 
 16       close c_clients; 
 17  end; 
 18  /

2 - Utilisation d'un REF CURSOR avec passage de paramètre à la main par concaténation

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
 
declare 
  2       TYPE CURSOR_CLIENTS IS REF CURSOR; 
  3       c_clients CURSOR_CLIENTS; 
  4       v_client  CLIENTS%ROWTYPE; 
  5       v_pays    CLIENTS.PAYS%TYPE := 'France'; 
  6       v_SQL     varchar2(200)  
  7                := 'SELECT * FROM CLIENTS WHERE PAYS =  '; 
  8  begin 
  9       open  c_clients FOR v_SQL || '''' || v_pays || '''' ; 
 10       loop 
 11            fetch c_clients into v_client; 
 12            exit when c_clients%NOTFOUND; 
 13            dbms_output.put_line( 'Client : '|| 
 14                        v_client.societe||' '|| v_client.ville); 
 15       end loop; 
 16       close c_clients; 
 17  end; 
 18  /

3 - Utilisation d'un curseur paramétré

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
 
declare 
  2       CURSOR c_clients (p_pays  CLIENTS.PAYS%TYPE) IS
  3           SELECT * FROM CLIENTS WHERE PAYS = p_pays;
  4
  5       v_client   CLIENTS%ROWTYPE; 
  6       v_pays    CLIENTS.PAYS%TYPE := 'France'; 
  7       
  8  begin 
  9       open  c_clients (v_pays); 
 10       loop 
 11            fetch c_clients into v_client; 
 12            exit when c_clients%NOTFOUND; 
 13            dbms_output.put_line( 'Client : '|| 
 14                        v_client.societe||' '|| v_client.ville); 
 15       end loop; 
 16       close c_clients; 
 17  end; 
 18  /

Pour vous montrer le genre de réponses que j'attends je vous confie mon intuition:
1 - plan d'exécution calculé à l'exécution du bloc , plan d'exécution est calculé une fois pour toutes et réutilisable lors des appels suivants du même bloc.

2 - interprété à l'exécution du bloc, plan d'exécution non réutilisable ou non conservé en mémoire (par le driver client Oracle ? par la base?).

3 - plan d'exécution calculé à la compilation du bloc. Plus rapide que 1 et 2 car moins de travail à l'exécution (une bonne partie du travail étant déjà faite à la compilation). Ressemble à l'appel d'une Procédure (sauf qu'elle s'appelle CURSOR qqch) appelée depuis notre bloc.

Pour ceux qui connaissent la Java. Je vois (1) comme l'appel d'un PreparedStatement, (2) comme l'appel d'un Statement (3) comme l'appel d'une procédure stockée.

Merci beaucoup de m'avoir lu et d'éclairer ma lanterne.

[pifor]

Je pense que les options 1 et 3 utilisent des variables de liaison (ou bind variables): l'option 1 explicitement et l'option 3 implicitement (mécanisme par défaut du PL/SQL lorsque le SQL n'est pas dynamique) : le plan est compilé 1 fois et utilisé plusieurs fois (s 'il référence le même schéma, avec les mêmes paramètres de sessions pour l'optimiseur etc.). L'option 2 n'utilise pas de variables de liaison donc la requête est compilée à chaque exécution (sauf si la requête a déjà été exécutée avec la même valeur passée en paramètre). Oracle tend à garder un plan d'exécution en mémoire tant qu'il a de la place car le shared pool fonctionne à la manière d'un cache.

Je ne suis pas sûr qu'il y a une grande différence de temps d'exécution entre 1 et 3: il faut le mesurer dans le bon environnement.

Je pense aussi que même dans une procédure stockée, le plan d'exécution n'est pas stocké sur disque.

LE spécialiste des bind variables Oracle c'est Tom Kyte.

Je m'appuie par défaut sur Oracle 10 car Oracle 11 a une fonctionnalité intéressante de cache de résultat de requête qui peut changer pas mal de choses.

[Vincent Rogier]

Comme Pifor le mentionne, les cas 1 et 3 sont similaires car le code est connu à la compilation et utilisent des variables.

Seul le cas 2 est différent car l'ordre SQL est compilé à la volée car dynamique.

Pour la comparaison avec Java, les cas 1 et 3 relèveraient donc du PreparedStatement et le cas 2 du Statement

[Michel SALAIS]

En plus de ce qui a été dit, la règle est :
Quand c'est possible avec SQL statique alors on ne fait pas avec SQL dynamique.

En ce qui concerne la compilation (parse) des requêtes, il y a plus de choses à dire ...

[jlinho2]

Tout d'abord merci à vous 3 pour vos réponses...
J'ai appris par exemple que 1) était également vérifié à la compilation ce dont je ne m'étais pas encore rendu compte...

En plus de ce qui a été dit, la règle est :
Quand c'est possible avec SQL statique alors on ne fait pas avec SQL dynamique.
[...]

Ca semble clair ne serait-ce que pour la lisibilité et la possibilité de vérification de la requête à la compilation et non à l'exécution. Mais ma question était plutôt de comprendre pourquoi Oracle avait - par exemple - introduit deux façons de passer des paramètres à une requête (façon des cas 1 et 3 de mon premier post). En clair, qu'elle est la différence entre 1 et 3 ?

Je laisse le post encore ouvert quelques temps si certains voudraient rajouter des explications... Je le passerai en RESOLU assez vite

[pifor]

Effectivement, il y a beaucoup à dire sur la compilation des requêtes. Le document OTN Designing applications for performance and scalability constitue un bon point de départ d'autant plus que la documentation ex-papier est assez discrète à ce sujet là. Et il y a bien sûr les livres de Tom Kyte.

[mnitu]

Il y a une confusion entre la compilation du code PL/SQL et le parsing des requêtes SQL.

• 1),2) et 3) sont trois blocs PL/SQL il n’y aucun compilation statique. Ce ne sont pas des procédures stockées. Donc les blocs PL/SQL sont compilés juste avant d’être exécutées comme les requêtes SQL. Dans les cas 1 et 2 les requêtes SQL dynamiques ne peuvent être parsés que à leur exécution. Dans le cas 3 cella se fait dans le cadre du parsing du bloc PL/SQL
• 1) et 3) utilisent des variables de liaison, 2) n’utilise pas. En général la non utilisation des variables de liaison est une erreur avec un impact important sur les performances du serveur, accompagnées souvent des problèmes de sécurité.
• Forcement 3) coûte moins cher que 1) ou 2).

Il n’y a pas de comparaison à faire avec Java