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

[mnitu]

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
 
SQL> create or replace procedure tttt Is
  l_bidon number(15);
Begin
  Select 1111 into l_bidon from dual;
End;
/
  2    3    4    5    6
Procédure créée.
 
SQL> select sql_text
 from v$sql
where sql_text like '%1111 FROM%'
   or sql_text like '%tttt%'
/
  2    3    4    5
SQL_TEXT
--------------------------------------------------------------------------------
select sql_text  from v$sql where sql_text like '%1111 FROM%'    or sql_text lik
e '%tttt%'
 
SQL> exec tttt
 
Procédure PL/SQL terminée avec succès.
 
SQL> select sql_text
 from v$sql
where sql_text like '%1111 FROM%'
   or sql_text like '%tttt%'
/
  2    3    4    5
SQL_TEXT
--------------------------------------------------------------------------------
select sql_text  from v$sql where sql_text like '%1111 FROM%'    or sql_text lik
e '%tttt%'
 
BEGIN tttt; END;
SELECT 1111 FROM DUAL

Ca permet de constater que la compilation de la procédure ne implique nulle part la création du plan d'exécution des requêtes que elle contient. Cella se fait à l'exécution de la procédure.

[Michel SALAIS]

• Dans le cas 3 cella se fait dans le cadre du parsing du bloc PL/SQL
• ...

Non!
Le Parse de la requête sera fait lors de l'exécution et non à la compilation du bloc. Oracle n'est pas SQL Server

[pifor]

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

Il ne s'agit pas comparer à Java mais de savoir quelles méthodes Java utiliser quand on programme en Java. Ce que le document OTN détaille page 30 et confirme ce qui Vincenzo:

Cursor representation:
A cursor is associated both with the PreparedStatement and the Statement class, however, only the PreparedStatement class can be used to separate the parse from the execute.

Parse call
During the first call of the execute() or executeQuery() method, the statement (of class PreparedStatement) will be parsed. Subsequent calls to execute() or executeQuery() will not do parsing.

Binding
Placeholders in SQL statements are specified using ‘?’ and binding is done by value. Therefore, appropriate setXXX() methods must be called before each execute.

Execute call
Execution is done with the execute() or executeQuery() method.

Array processing JDBC automatically buffers rows for fetch operations. Array inserts are not supported.

Best practice
You should open the PreparedStatement object using the SQL statement, and repeat the execute() or executeQuery() method as often as needed. If the close() method is called, it must be re-opened and hence, a parse of the SQL statement will take place. Using statement caching, you can alternatively call the close() method and repeat the open() call with an identical SQL statement text. As long as the statement cache is not exhausted, any open() with a SQL statement found in the cache will actually use the parsed statement in the cache and the subsequent call to the execute() or executeQuery() method will not do a parse.

Special considerations
The Statement class should only be used for SQL statements that are very infrequently or not at all repeated. The parse and execute steps will always be done at the same time when the Statement class is used

[mnitu]

Non!
Le Parse de la requête sera fait lors de l'exécution et non à la compilation du bloc. Oracle n'est pas SQL Server

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
 
declare 
  l_bidon number;
begin
  select /* ZZZBLA */ 1 into l_bidon from blabla;
end;
 
SQL> Select sql_text, executions from v$sql
Where sql_text like '%ZZZBLA%'  2
  3  ;
 
SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
Select sql_text, executions from v$sql Where sql_text like '%ZZZBLA%'
         1
 
declare    l_bidon number; begin   select /* ZZZBLA */ 1 into l_bidon from blabl
a; end;
         0

[Michel SALAIS]

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
 
declare 
  l_bidon number;
begin
  select /* ZZZBLA */ 1 into l_bidon from blabla;
end;
 
SQL> Select sql_text, executions from v$sql
Where sql_text like '%ZZZBLA%'  2
  3  ;
 
SQL_TEXT
--------------------------------------------------------------------------------
EXECUTIONS
----------
Select sql_text, executions from v$sql Where sql_text like '%ZZZBLA%'
         1
 
declare    l_bidon number; begin   select /* ZZZBLA */ 1 into l_bidon from blabl
a; end;
         0

J'étais préssé et j'ai mal vu au départ alors tu voulais dire quoi avec ton exemple? Je reviendrai avec un exemple à tête reposée ...

[mnitu]

Bref , je vous propose le test suivant (Oracle 10gR2):

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
 
alter session set sql_trace = true;
 
declare 
  l_bidon  Number(1);
Begin
  Select /* Tag1 MNI */ 1 Into l_Bidon From blabla;
End;
/
declare 
  l_bidon  Number(1);
  l_Sql    varchar2(100);
Begin
  l_SQL := 'Select /* Tag2 MNI */ 1 Into l_Bidon From blabla';
  Execute Immediate l_Sql Into l_bidon;
End;
/
 
alter session set sql_trace = false;

On ouvre le fichier de trace et on constate:

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
 
...
Select /* Tag2 MNI */ 1 Into l_Bidon From blabl
 
Error encountered: ORA-00942
...
declare
  l_bidon  Number(1);
  l_Sql    varchar2(100);
Begin
  l_SQL := 'Select /* Tag2 MNI */ 1 Into l_Bidon From blabla';
  Execute Immediate l_Sql Into l_bidon;
End;
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           0
 
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 62  
...

Ce fichier montre bien que le parsing de la requête dynamique se fait à l'exécution du bloc PL/SQL. Par contre le bloc PL/SQL /*TAG1 MNI*/ n'est pas présent dans le fichier trace parce que il ne passe pas l'étape de compilation!

[mnitu]

Il ne s'agit pas comparer à Java ...

Il me semble qu'on est d'accord sur ce point.
Le reste n'est qu'une histoire de comment écrire correctement le code dans le langage de programmation utilisé. Les mêmes remarques sont valable pour tous les autres langages qu'il s'agit de java, pro*c, pro*cobol, VB, Ruby, etc. En fait les même règles s'applique pour tout le monde; le PL/SQL étant un peu privilégié parce que «*tightly integrated with SQL*»; ce qui n'empêche nul part sa mauvais utilisation quand les règles sont ignorés.

[fatati]

Bonjour,

J'ai une petite intervention même que ce n'est pas au même niveau que les autres collégues
j'avais l'occasion de travailler par les trois types de requêtes.
- le type le plus stanard est le 3 eme type sauf qu'on peut pas utiliser le même curseur l'un à l'intérieur de l'autre même si on change le parametre, par exemple:

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
 
 
cursor c_test(dat date)
is
select ann,per from sper where date=dat;
;
 
v_dat:= 01.01.2008;
open  c_test(v_dat);
fetch c_test into v_test;
      while codition loop
           v_dat:=v_dat +1;
       -- ici j'ai besoin d'utiliser le curseur avec d'autres parametres
          open  c_test(v_dat);
          fetch c_test into v_test; 
          ...
          close c_test;
          end loop;
      var1:=v_test.ann;
      var2:=v_test.per;
close  c_tst;
-- ce code ne va pas marcher car il y a un cheuvauchement entre 
-- l'ouverture et la fermeture du même curseur

dans ce cas et pour éviter ce problème il faut utiliser les requettes de type 1
grâce à l'utilisation de 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
 
 TYPE CURSOR_TEST IS REF CURSOR; 
  c_test CURSOR_TEST; 
  c_test2 CURSOR_TEST; 
  v_req:= 'select ann,per from sper where date:=dat';
 
v_dat:= 01.01.2008;
open c_test for  v_req using  v_dat;;
fetch c_test into v_test;
      while codition loop
           v_dat:=v_dat +1;
          open  c_test2 for v_req using  v_dat;
          fetch c_test into v_test; 
          ...
          close c_test2;
          end loop;
      var1:=v_test.ann;
      var2:=v_test.per;
close  c_tst;

-- Ce code va normalement va marcher cerrctement même que le contenu de la requete est le meme mais le porteur de l'instruction est different mais des fois on n'a pas besoin seulement d'un parametre (date, matricule, ...etc) mais en plus de ça on a besoin de recostituer dynamiquement toute la requete imaginez qu'on veut ajouter une autre condition sur ma requete, par exemple:

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
 TYPE CURSOR_TEST IS REF CURSOR; 
  c_test CURSOR_TEST; 
  c_test2 CURSOR_TEST; 
  v_req:= 'select ann,per from sper where date:=dat and := chaine';
 
v_dat:= 01.01.2008;
v_chaine:=' ann >2007 '

open c_test for  v_req using  v_dat,v_chaine;
fetch c_test into v_test;
      while codition loop
           v_dat:=v_dat +1;
          v_chaine:=' per >3 '
          
         open  c_test2 for v_req using  v_dat,v_chaine          ;
          fetch c_test2 into v_test; 
          ...
          close c_test2;
          end loop;
      var1:=v_test.ann;
      var2:=v_test.per;
close  c_tst;

-- les deux lignes rouges ne vont pas marcher car l'instruction using
--cherche des parametres et pas une concaténation des chaines, dans
-- ce cas on utilise les requetes de type 2 et on aura:

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
 
 TYPE CURSOR_TEST IS REF CURSOR; 
  c_test CURSOR_TEST; 
  c_test2 CURSOR_TEST; 
  v_req:= 'select ann,per from sper where date:=dat and := chaine';
 
v_dat:= 01.01.2008;
v_chaine:=' ann >2007 ';
v_req1:=v_req||v_chaine;
open c_test for  v_req1 ;
fetch c_test into v_test;
      while codition loop
           v_dat:=v_dat +1;
          v_chaine:=' per >3 ';
          v_req2:=v_req||v_chaine;
          open  c_test2 for v_req2 ;
          fetch c_test2 into v_test; 
          ...
          close c_test2;
          end loop;
      var1:=v_test.ann;
      var2:=v_test.per;
close  c_tst;
-- ce type est le plus dynamique mais à utiliser seulement au cas du besoin

Bonne journée

[fatati]

Bonjour,

J'ai une petite intervention même que ce n'est pas au même niveau que les autres collégues
j'avais l'occasion de travailler par les trois types de requêtes.
- le type le plus stanard est le 3 eme type sauf qu'on peut pas utiliser le même curseur l'un à l'intérieur de l'autre même si on change le parametre, par exemple:

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
 
 
cursor c_test(dat date)
is
select ann,per from sper where date=dat;
;
 
v_dat:= 01.01.2008;
open  c_test(v_dat);
fetch c_test into v_test;
      while codition loop
           v_dat:=v_dat +1;
       -- ici j'ai besoin d'utiliser le curseur avec d'autres parametres
          open  c_test(v_dat);
          fetch c_test into v_test; 
          ...
          close c_test;
          end loop;
      var1:=v_test.ann;
      var2:=v_test.per;
close  c_tst;
-- ce code ne va pas marcher car il y a un cheuvauchement entre 
-- l'ouverture et la fermeture du même curseur

dans ce cas et pour éviter ce problème il faut utiliser les requettes de type 1
grâce à l'utilisation de 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
 
 TYPE CURSOR_TEST IS REF CURSOR; 
  c_test CURSOR_TEST; 
  c_test2 CURSOR_TEST; 
  v_req:= 'select ann,per from sper where date:=dat';
 
v_dat:= 01.01.2008;
open c_test for  v_req using  v_dat;;
fetch c_test into v_test;
      while codition loop
           v_dat:=v_dat +1;
          open  c_test2 for v_req using  v_dat;
          fetch c_test into v_test; 
          ...
          close c_test2;
          end loop;
      var1:=v_test.ann;
      var2:=v_test.per;
close  c_tst;

-- Ce code va normalement va marcher cerrctement même que le contenu de la requete est le meme mais le porteur de l'instruction est different mais des fois on n'a pas besoin seulement d'un parametre (date, matricule, ...etc) mais en plus de ça on a besoin de recostituer dynamiquement toute la requete imaginez qu'on veut ajouter une autre condition sur ma requete, par exemple:

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
 TYPE CURSOR_TEST IS REF CURSOR; 
  c_test CURSOR_TEST; 
  c_test2 CURSOR_TEST; 
  v_req:= 'select ann,per from sper where date:=dat and := chaine';
 
v_dat:= 01.01.2008;
v_chaine:=' ann >2007 '

open c_test for  v_req using  v_dat,v_chaine;
fetch c_test into v_test;
      while codition loop
           v_dat:=v_dat +1;
          v_chaine:=' per >3 '
          
         open  c_test2 for v_req using  v_dat,v_chaine          ;
          fetch c_test2 into v_test; 
          ...
          close c_test2;
          end loop;
      var1:=v_test.ann;
      var2:=v_test.per;
close  c_tst;

-- les deux lignes rouges ne vont pas marcher car l'instruction using
--cherche des parametres et pas une concaténation des chaines, dans
-- ce cas on utilise les requetes de type 2 et on aura:

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
 
 TYPE CURSOR_TEST IS REF CURSOR; 
  c_test CURSOR_TEST; 
  c_test2 CURSOR_TEST; 
  v_req:= 'select ann,per from sper where date:=dat and := chaine';
 
v_dat:= 01.01.2008;
v_chaine:=' ann >2007 ';
v_req1:=v_req||v_chaine;
open c_test for  v_req1 ;
fetch c_test into v_test;
      while codition loop
           v_dat:=v_dat +1;
          v_chaine:=' per >3 ';
          v_req2:=v_req||v_chaine;
          open  c_test2 for v_req2 ;
          fetch c_test2 into v_test; 
          ...
          close c_test2;
          end loop;
      var1:=v_test.ann;
      var2:=v_test.per;
close  c_tst;
-- ce type est le plus dynamique mais à utiliser seulement au cas du besoin

Bonne journée

[jlinho2]

Merci à tous pour vos contributions. Je passe le sujet en résolu.