cursors, soft parse, hard parse -> static sql

Version imprimable

03/05/2010, 17h10
temoanatini

cursors, soft parse, hard parse -> static sql

Bonjour,
je sais que le titre peut faire peur et on va se demander s'il ne va pas couvrir 1 000 hectares de connaissance...

Ne vous inquiétez pas, je voudrais juste poser une question sur les "static sql".

Il me semble qu'au niveau perfs, c'est ce qu'il y a de meilleur (même pas de soft parse (si, non ?)).

Je voulais savoir s'il y a d'autre moyen que d'utiliser du PL/SQL pour "arriver" à ne faire "qu' " exécuter et ne pas parser un select par exemple.
03/05/2010, 23h27
pachot

Oh oui, bien sûr.
En java par exemple, tu fais un PreparedStatement, avec des variables, puis tu l'exécute autant de fois que tu veux avec des nouvelles valeurs à chaque fois. Et il sera parsé qu'une seule fois.

Dans tous les langages, c'est possible, heureusement !
04/05/2010, 13h28
temoanatini

ok mais

lorsque tu dis qu'il ne sera parsé qu'une seule fois, c'est justement là que j'ai cette petite incertitude...

Il ne sera HARD-PARSÉ qu'une seule fois, c'est bien ça ?
Par contre, avec des valeurs différentes de binding, on aura bien à chaque fois un SOFT PARSE, non ?
04/05/2010, 15h30
pachot

prepareStatement() est l'appel qui parse la requête, et qui retourne un curseur.
Cela signifie que le curseur a été lu (parsé) syntaxiquement, puis sémantiquement, et ensuite optimisé, pour établir le plan d'exécution.

Ce parse peut être 'hard' s'il y a tout à faire, ou 'soft' si après la première analyse syntaxique et semantique il apparait comme identique à un SQL déjà optimisé. Ou même pas soft si après analyse syntaxique il est trouvé dans le cache des curseurs de session. Dans tous les cas, c'est un 'parse call'. Qui va être plus ou moins lourd (en latch et en cpu) suivant qu'il est 'hard', 'soft' ou 'session cursor cache hit'

Alors, chaque appel à execute n'a plus rien à parser: le plan d'exécution déjà établi est appliqué avec les valeurs des variables. Exactement comme un programme déjà compilé.

Citation:

Par contre, avec des valeurs différentes de binding, on aura bien à chaque fois un SOFT PARSE, non ?

Donc non. Pas de parse call du tout. Donc pas de parse ... sauf si entre temps le curseur a été invalidé (pour faire de la place dans le cache ou parce qu'il y a eu du DDL qui remet en cause le plan d'exécution). A ce moment là, pas de parse call (donc pas de 'parse count (total)' incrémenté) mais il y aura un hard parse quand même. Le nom de ces statistiques sont trompeuses. C'est expliqué ici (traduction d'un article de Jonathan Lewis).

Franck.
21/06/2011, 14h19
temoanatini

merci pachot

j'avais oublié de fermer ce thread
21/06/2011, 15h18
Mohamed.Houri

Citation:

Envoyé par temoanatini

Bonjour,
je sais que le titre peut faire peur et on va se demander s'il ne va pas couvrir 1 000 hectares de connaissance...

Je voulais savoir s'il y a d'autre moyen que d'utiliser du PL/SQL pour "arriver" à ne faire "qu' " exécuter et ne pas parser un select par exemple.

Il existe quand même une possibilité de faire beaucoup de parse et de remplir la shared pool (library cache) même en n'utilisant que du static PL/SQL.

La question est : savez vous comment :D?

Bien respectueusement

Mohamed Houri
22/06/2011, 09h20
mnitu

Citation:

Envoyé par Mohamed.Houri

Il existe quand même une possibilité de faire beaucoup de parse et de remplir la shared pool (library cache) même en n'utilisant que du static PL/SQL.

La question est : savez vous comment :D?

...

Pour réutiliser un curseur il est nécessaire que le texte de la requête et l’environnement d’optimisation soit identiques.
22/06/2011, 09h55
Mohamed.Houri
Citation:

Envoyé par mnitu

Pour réutiliser un curseur il est nécessaire que le texte de la requête et l’environnement d’optimisation soit identiques.

Bonjour,

Correct; mais ce n'est pas ceci que je visais.
La situation est telle que nous n'avons aucun 'stand alone' SQL; nous ne faisons qu'utiliser des procédures PL/SQL stockées qui ne contiennent pas du SQL dynamique mais n'empêche que le select suivant
Code:

1 2 3 4 select sql_text, executions from v$sql where executions <= 2;
retourne beaucoup de records. Il existe trop d'instructions dans le shared pool qui ne sont pas re-executées (qui ne sont pas partagées ''shared'').

Le but de cette question est uniquement de transmettre le message suivant:
il ne suffit pas d'utiliser du PL/SQL statique pour être sûr que les bind variables soient correctement utilisées. Il faut aussi être sûr qu'une autre chose
soit bien utilisée. C'est tout simple mais c'est très important.

Bien à vous

Mohamed Houri
22/06/2011, 13h55
Ahmed AANGOUR

Citation:

Envoyé par pachot

Ou même pas soft si après analyse syntaxique il est trouvé dans le cache des curseurs de session. Dans tous les cas, c'est un 'parse call'. Qui va être plus ou moins lourd (en latch et en cpu) suivant qu'il est 'hard', 'soft' ou 'session cursor cache hit'

Tom Kyte appelle les 'session cursor cache hit' des "softer soft parse".
J'aime bien ce terme...
A noter que pour qu'un curseur soit placé dans le cache des curseurs fermés, il doit avoir été exécuté 3 fois. Le but étant d'éviter de mettre en cache des curseurs qui ne seront exécutés qu'une seule fois.
Le nombre de curseur "cachables" dans la UGA est défini par le paramètre d'instance SESSION_CACHED_CURSORS

Ahmed
22/06/2011, 15h27
mnitu

Citation:

Envoyé par Ahmed AANGOUR

...
A noter que pour qu'un curseur soit placé dans le cache des curseurs fermés, il doit avoir été exécuté 3 fois. Le but étant d'éviter de mettre en cache des curseurs qui ne seront exécutés qu'une seule fois.
...

Je m'imagine que vous allez nous fournir une petite démonstration de ce comportement, n'est pas vrai ?

Christian Antognini l'explique très bien dans son livre au chapitre 8.

Mais je viens de faire un petit test basique pour illustrer mon propos:

Dans Session 1:

Code:

1
2
3
4
5
6
 
SQL> select sid from v$mystat where rownum=1;
 
       SID
----------
       136

Depuis Session 2 je regarde les stats existantes pour cette session au niveau de 'session cursor cache hits', ' session cursor cache count' et 'parse count (total)':

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SQL> SELECT sn.name, ss.value
  2   FROM v$statname sn, v$sesstat ss
  3   WHERE sn.statistic# = ss.statistic#
  4   AND sn.name IN ('session cursor cache hits',
  5   'session cursor cache count',
  6   'parse count (total)')
  7   AND ss.sid =136;
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      29

Le nombre de curseurs dans le cache de ma session est de 18 et aucun parse call n'a pour l'instant profité du cache des curseurs (cf. 'session cursor cache hits' ).

J'exécute une simple requête dans ma 1ère session:

Code:

1
2
3
4
5
6
 
SQL> select count(1) from t1;
 
  COUNT(1)
----------
       100

Je regarde les stats:

Code:

1
2
3
4
5
6
7
8
 
SQL> /
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      31

Seuls le nombre de 'parse call' a bougé. J'ai toujours les même nombre de curseur dans le cache.

J'exécute une 2è fois ma requête:

Code:

1
2
3
4
5
6
 
SQL> select count(1) from t1;
 
  COUNT(1)
----------
       100

les stats:

Code:

1
2
3
4
5
6
7
8
 
SQL> /
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      32

Idem que pour la 1ère execution.
J'exécute une 3ème fois:

Code:

1
2
3
4
5
6
7
8
 
SQL> /
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 0
session cursor cache count                                               18
parse count (total)                                                      33

Idem.

j'exécute une 4ème fois la même requête:

Code:

1
2
3
4
5
6
7
8
 
SQL> /
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 1
session cursor cache count                                               24
parse count (total)                                                      34

cette fois le nombre de curseurs dans le cache a augmenté et surtout mon "session cursor cache hits" est passé à 1. Donc c'est uniquement à la 4ème exécution que le SOFT PARSE est évité au profit d'un softer soft parse.

Après la 5ème exécution:

Code:

1
2
3
4
5
6
7
8
 
SQL> /
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                                 2
session cursor cache count                                               24
parse count (total)                                                      35

le "session cursor cache hits" est passé à 2.
J'ai fait le test pour 10 exécutions et j'obtiens un "session cursor cache hits" de 7.
10-7 = 3 <=> au nombre d'exécutions nécessaires pour que le curseur soit caché dans la UGA.

22/06/2011, 16h52
Mohamed.Houri

Citation:

Envoyé par mnitu

Je m'imagine que vous allez nous fournir une petite démonstration de ce comportement, n'est pas vrai ?

Trouble shooting performance problem (Christian Antognini) Chapter 8 Parsing
Server-Side Statement Caching page 326-327-328

http://books.google.com/books?id=b3D...aching&f=false

Bien Cordialement

Mohamed Houri
22/06/2011, 18h14
mnitu

Citation:

Envoyé par Ahmed AANGOUR

...
Mais je viens de faire un petit test basique pour illustrer mon propos:
...

Merci c'était très bien.