Discussion :

[Cofondinde]

Bonjour à tous,

Une application que je dois aider à optimiser produit énormément de requêtes sans bind variables.

Du coup la Shared_Pool s'en trouve toute malmenée je pense, au vu des retours de la requête ci-dessous :

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
SELECT   SUBSTR (owner, 1, 10) || '.' || SUBSTR (name, 1, 35) "Object Name",
              ' Type: '
           || SUBSTR (TYPE, 1, 12)
           || ' size: '
           || sharable_mem
           || ' execs: '
           || executions
           || ' loads: '
           || loads
           || ' Kept: '
           || kept
    FROM   v$db_object_cache
   WHERE   TYPE IN ('TRIGGER', 'PROCEDURE', 'PACKAGE BODY', 'PACKAGE')
           AND executions > 0
ORDER BY   executions DESC, loads DESC, sharable_mem DESC
 
V500.A_PACKAGE                     Type: PACKAGE BODY size: 32531 execs: 2092365741 loads: 338 Kept: NO
SYS.STANDARD                                    Type: PACKAGE BODY size: 29548 execs: 345901737 loads: 1 Kept: NO
SYS.DBMS_SESSION                                Type: PACKAGE BODY size: 12560 execs: 122780504 loads: 1 Kept: NO
V500.A_PROCEDRE                       Type: PROCEDURE size: 4193 execs: 110808565 loads: 1191 Kept: NO
V500.A_TRIGGER                          Type: TRIGGER size: 6238 execs: 24575548 loads: 449 Kept: NO

Pour évaluer l'impact (j'espère très positif) de pinner certains objets dans la shared_pool pourriez-vous m'indiquer comment déterminer le temps qu'Oracle prend pour le charger dans la Shared_Pool ?

Dans le cas du premier package, exécuté 2092365741 fois et chargé 338 fois, quel serait le gain de performance ?

D'avance merci de vos lumières !

[mnitu]

Presque rien! Votre problème n'est pas le chargement des packages mais le parsing de requêtes qui n'utilise pas des variables des binding. Donc si vous voulez optimiser attaquez-vous à ce problème.

[Cofondinde]

Merci de votre réponse, tanpis pour cette piste.

Malheureusement un travail dans ce sens n'est pas à l'ordre du jour car code ancien, fragile et pas de ressources pour s'en occuper.
J'ai donc plusiurs pistes :
1/ Je constate que sur l'instance :

CURSOR_SHARING = EXACT

alors que beacuoup de requetes emises sont similaires (traitements en masse) et different juste avec le litteral utilisé.

Recommenderiez-vous de setter cette valeur à 'SIMILAR' ou 'FORCE' ? Pourrait-il y avoir un impact négatif?

2 / D'autre part, la ASM n'est pas utilisé et la shared_Pool est sizé à 2GB.

Les valeurs dans $SHARED_POOL_ADVICE sont enormes :

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
 
SELECT   SHARED_POOL_SIZE_FOR_ESTIMATE,
         SHARED_POOL_SIZE_FACTOR,
         ESTD_LC_LOAD_TIME
  FROM   v$shared_pool_advice;
 
SHARED_POOL_SIZE_FOR_ESTIMATE SHARED_POOL_SIZE_FACTOR ESTD_LC_LOAD_TIME
----------------------------- ----------------------- -----------------
                         1424                   ,6953          10119508
                         1632                   ,7969           9170676
                         1840                   ,8984           8873752
                         2048                       1           8685396
                         2256                  1,1016           8549153
                         2464                  1,2031           8443345
                         2672                  1,3047           8357544
                         2880                  1,4063           8284962
                         3088                  1,5078           8221435
                         3296                  1,6094           8165243
                         3504                  1,7109           8114661
                         3712                  1,8125           8068654
                         3920                  1,9141           8026417
                         4128                  2,0156           7987178

Je ne suis pas DBA mais il me semble que ce sont les temps estimés pour parser une requete non ? Faudrait-il passer à 4GB de Shared_Pool ?

3/ La valeur de CURSOR_SHARING est 50.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
SQL> select name, value from V$SYSSTAT
  2  where name in ('session cursor cache hits','parse count (total)');
 
NAME                                                                  VALUE
---------------------------------------------------------------- ----------
session cursor cache hits                                        2493402807
parse count (total)                                              4472666437

Dois-je comprendre que sur les requetes à traiter, 1 sur 2 (environ) necessite un hard parse ?

merci d'avance de vos réponses

[mnitu]

1) En changeant CURSOR_SHARING ça peut aider un peu. Personnellement j'ai rencontré des erreurs dans des pro*c lié à ce changement. Vous devez tester.
2) et 3) Utilisez statspack pour analyser l'activité de la base.

Mais quelle est le bout de votre optimisation ?

Pour les problèmes de parsing ce qui compte en fait c'est le nombre des utilisateurs qui envoient simultanément les requêtes. Si ce nombre est élevé alors vous avez un problème de parsing qu'il faut résoudre. Sinon vous avez un problème de parsing c'est bien dommage mais très probablement vous pouvez vivre avec.

[Cofondinde]

Le travail actuel consiste en un travail d'analyse dans un premier temps : recuperation des requetes couteuses, stockage de leur temsp de traitements puis tests de leurs plans d'execution (de manière autonome, avant les traitements, pour detecter les pb avant que le client reste planté x heures devant un ecran..)

Mes questions etaient des pistes car je constate que les traitements en masse sont ceux qui nous posent le plus de problèmes. IL s'agit de traiter plusieurs milliers de lignes, c'est fait dans des boucles et les requetes sans bind variables sont donc envoyés plusieurs millers de fois avec une valeur differente a chaque fois ...

J'ai testé le CURSOR_SHARING sur un de ces traitements => Amélioration de 25% du temsp de traitement global, pas mal. D'autant plus qu'on peut le setter juste pour la session courante et ne pas retester toute l'appli. Qu'en pensez-vous.

2 et 3/ Je vais regarder ce statspack, mais mes connaissances sont limitées

[mnitu]

L'optimisation commence avant le développement (je sais c'est dans les rêves).
Si vous avez des traitements ciblés que vous voulez optimiser alors exécutez ces traitements en activant la trace sql étendue. Ensuite utilisez un outil de type profiler (tvd$XTAT pour exemple) pour comprendre où le temps du traitement passe. En bonus, vous aller savoir qu'elle partie du traitement permettra de gagner un maximum et donc estimer le coût de l'intervention.
Mais, si vous avez de traitement qui envoie de requêtes en boucle au lieu d'exécuter une requête qui traite l'ensemble des données, la seule optimisation possible c'est de réécrire ces traitements. Ça arrive souvent avec les "témoins de la programmation objet" et autres javastan qui ont une pauvre compréhension de la nature des traitements ensembliste.