Performance de SQL dynamique

Version imprimable

15/11/2012, 13h10
haykelFST

Performance de SQL dynamique

Bonjour,

Est ce que le SQL dynamique permet d'ameliorer l performance? et comment?
15/11/2012, 13h43
Chtulus

Bonjour,

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

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

Cordialement,
;)
15/11/2012, 13h57
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
15/11/2012, 15h10
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:

Citation:

A requête équivalente non.
Mal codé il détruit les performances en plus d'être source d'injection SQL.
15/11/2012, 15h25
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.
15/11/2012, 16h32
haykelFST

merci mnitu,

mais j ai pas compris:

Citation:

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.

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

Code:

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>

15/11/2012, 17h38
Mohamed.Houri
Citation:

Envoyé par 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.

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
15/11/2012, 20h08
Pomalaix

Citation:

Envoyé par Mohamed.Houri

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

Bravo, il était temps !!! Ça va, pas trop de furoncles qui poussent ?
15/11/2012, 23h18
haykelFST

bravo et merci Mohamed.Houri pour ton explication

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

merci d'avance

Citation:

Envoyé par Mohamed.Houri

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:

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>

16/11/2012, 09h15
mnitu

Citation:

Envoyé par haykelFST

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

merci d'avance

Overview of SQL Injection Techniques

INJECTION SQL