Discussion :

[haykelFST]

Bonjour,

Est ce que le SQL dynamique permet d'ameliorer l performance? et comment?

[Chtulus]

Bonjour,

Ceci répond-t-il a votre besoin ?

(Pensez à développer vos besoins/demandes.)

Cordialement,

[skuatamad]

A requête équivalente non.
Mal codé il détruit les performances en plus d'être source d'injection SQL.

Le SQL dynamique permet d'exécuter une requête construite dynamiquement à partir d'une chaîne (une concaténation de chaînes) de caractères comme fonctionnent les language client (php, java...)
Voici un exemple où l'approche sql dynamique permet un gain de performance comparée à l'approche sql statique :
Making a genric search sql Query

[haykelFST]

skuatamad; le document lié est trés long de façon que j ai pas pu trouvé mon besoin:

aussi, j ai pas compris ce que t'as ecris:

A requête équivalente non.
Mal codé il détruit les performances en plus d'être source d'injection SQL.

[mnitu]

En PL/SQL, avant Oracle 10g le SQL dynamique était toujours moins performant que le SQL statique. Suite aux divers optimisations apportées au PL/SQL à partir d’Oracle 10g il n’y plus une différence notable entre les requêtes dynamiques et statiques. Néanmoins utiliser du SQL dynamique à la place du SQL statique dans PL/SQL est une erreur au moins pour le fait qu’il détruit le mécanisme des dépendances.

[haykelFST]

merci mnitu,

mais j ai pas compris:

Néanmoins utiliser du SQL dynamique à la place du SQL statique dans PL/SQL est une erreur au moins pour le fait qu’il détruit le mécanisme des dépendances.

[mnitu]

Regardez cet exemple, la base n’enregistre qu’une seule fonction qui dépende de la table hr.employees!

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
39
40
41
42
43
44
45
46
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> create or replace function get_name_for_id (
  2    employee_id     In Number
  3  ) Return varchar2 Is
  4    l_name Varchar2(100);
  5  Begin
  6    Select e.first_name
  7      Into l_name
  8      From hr.employees e
  9     Where e.employee_id = get_name_for_id.employee_id;
 10     --
 11     Return l_name;
 12  End;
 13  /
 
Function created
SQL> create or replace function get_name_for_id_dyn (
  2    employee_id     In Number
  3  ) Return varchar2 Is
  4    l_name Varchar2(100);
  5    l_sql Constant Varchar2(100) := 'Select e.first_name '
  6                                    ||'From hr.employees e '
  7                                    ||'Where e.employee_id = :employee_id';
  8  Begin
  9    Execute Immediate l_sql Into l_name;
 10    Return l_name;
 11  End;
 12  /
 
Function created
SQL> Select name
  2    from all_dependencies d
  3   Where d.referenced_owner = 'HR'
  4     And d.referenced_name = 'EMPLOYEES'
  5     And d.type = 'FUNCTION'
  6  /
 
NAME
------------------------------
GET_NAME_FOR_ID
 
SQL>

[Mohamed.Houri]

En PL/SQL, avant Oracle 10g le SQL dynamique était toujours moins performant que le SQL statique. Suite aux divers optimisations apportées au PL/SQL à partir d’Oracle 10g il n’y plus une différence notable entre les requêtes dynamiques et statiques. Néanmoins utiliser du SQL dynamique à la place du SQL statique dans PL/SQL est une erreur au moins pour le fait qu’il détruit le mécanisme des dépendances.

Le SQL Statique (PL/SQL) a l'avantage d'utiliser automatiquement les "bind variables". Il est fait ainsi. Un genre "d'auto binding" est introduit dans le moteur PL/SQL statique si bien que tout ce que vous mettez comme variable, en dur ou pas, elle sera "re-bindée" (Désolé pour les termes en anglais). Vous avez donc l'énorme avantage de soulager la mémoire (library cache du shared pool) sans déployer d'effort spécifique à ce sujet. Par contre, lorsque vous utilisez du SQL Dynamique dans du PL/SQL (procédure), vous devez vous même veiller à la bonne utilisation des "bind-variables". Ce qui ôte déjà un sérieux avantage par rapport au SQL statique.

De plus, qui dit SQL dynamique dit peut-être une faille dans la sécurité à cause du SQL injection. D’une manière générale, moi je conseille de:

1. N’utiliser le SQL dynamique que lorsque le SQL statique n’est pas possible
2. Lorsque vous utilisez le SQL dynamique veillez à bien utiliser les variables de liaison (ouf j’ai fini par utiliser le terme en français)
3. Lorsque vous utilisez le SQL dynamique faites attention au SQL injection (utilisation des concaténations et absence de variables de liaison)
4. Lorsque vous utilisez le SQL dynamique pensez à tracer le texte du code SQL généré avant son utilisation. Il vous sera très facile de débugger les différentes erreurs obtenues lors de l’exécution et non lors de la compilation

cursor sharing and bind variable

[Pomalaix]

... ouf j’ai fini par utiliser le terme en français...

Bravo, il était temps !!! Ça va, pas trop de furoncles qui poussent ?

[haykelFST]

bravo et merci Mohamed.Houri pour ton explication

mais s'il est possible d'expliquer la notion de SQL injection.

merci d'avance

[mnitu]

Le SQL Statique (PL/SQL) a l'avantage d'utiliser automatiquement les "bind variables".
...

Le moteur PL/SQL implémente beaucoup des automatisations favorables au programmeur.

[Edit+]
Il suffit d’écrire correctement le code dynamique comme vous le suggérez pour retrouver la sérénité du « library cache » et de se protéger contre les injections. D’ailleurs si vous utilisez les autres langages vous n’avez pas le choix. Mais dans l’analyse des différances entre le SQL statique est dynamique je trouvé des autres aspects sont bien prioritaire. Voilà un autre exemple qui illustre pourquoi il ne faut pas abuser des SQL dynamique.

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
 
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 
Connected as mni
 
SQL> 
SQL> CREATE OR REPLACE FUNCTION get_name_for_id_dyn1 (
  2    employee_id     IN Number
  3  ) RETURN varchar2 IS
  4    l_name Varchar2(100);
  5    l_sql Constant Varchar2(100) := 'En dynamique le requête SQL n''est pas analysée jusqu''à la exécution ';
  6  Begin
  7    Execute Immediate l_sql INTO l_name;
  8    RETURN l_name;
  9  End;
 10  /
 
Function created
 
SQL> show err
No errors for FUNCTION MNI.GET_NAME_FOR_ID_DYN1
 
SQL>

[mnitu]

...
'il est possible d'expliquer la notion de SQL injection.

merci d'avance

Overview of SQL Injection Techniques

INJECTION SQL