Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
18 novembre 2013 1 18 /11 /novembre /2013 13:08

 

Quand on veut savoir pourquoi une procédure rame, on peut par exemple la tracer.

"alter session set events 10046 trace name context forever, level 8" est en ce sens un outil efficace, car il permet de déterminer ce qui a fait attendre l'exécution.

 

C'est donc ce que j'ai fait l'autre jour, et voilà le résultat : 

 

INSERT INTO MA_TABLE

VALUES (...)

 

call     count       cpu    elapsed       disk      query    current        rows

------- ------  -------- ---------- ---------- ---------- ----------  ----------

Parse        1      0.00       0.00          0          0          0           0

Execute  47914     60.45     573.76      93619      11588    1221462       47914

Fetch        0      0.00       0.00          0          0          0           0

------- ------  -------- ---------- ---------- ---------- ----------  ----------

total    47915     60.45     573.76      93619      11588    1221462       47914

 

Misses in library cache during parse: 1

Misses in library cache during execute: 1

Optimizer mode: ALL_ROWS

Parsing user id: 189     (recursive depth: 1)

 

Elapsed times include waiting on following events:

  Event waited on                             Times   Max. Wait  Total Waited

  ----------------------------------------   Waited  ----------  ------------

  db file sequential read                     93619        0.83        517.81

  latch free                                      1        0.00          0.00

  latch: shared pool                              1        0.00          0.00

  buffer busy waits                               1        0.00          0.00

  undo segment extension                          1        0.00          0.00

  latch: object queue header operation            2        0.00          0.00

********************************************************************************

 

On a donc attendu environ 9 minutes en lecture d'index pour insérer 47K lignes.

 

C'est bien beau tout ça, mais quel(s) index ? La répartition du temps d'attente est homogène ?

 

Quand on regarde dans le fichier trace brut, on voit plein de lignes de ce genre : 


WAIT #78: nam='db file sequential read' ela= 8907 file#=26 block#=1865617 blocks=1 obj#=1717754 tim=1352309684636336

 

Et l'obj#, quand on vérifie dans dba_objects, c'est MA_TABLE_FK_ART.

 

On va donc travailler sur cette lueur d'espoir.

 

A. Tout d'abord, extraire les waits : 

 

grep '^WAIT' mon_fichier_trace.trc > trc_dir/my_trc_file.trc

 

B. Ensuite, créer une table externe pour lire ce fichier en SQL (des compétences correctes en shell Unix m'auraient permis de parser tout ça en live)

 

CREATE DIRECTORY trc_dir AS '/home/oracle/trc_dir';

 

CREATE TABLE trc_load  

      (msg varchar2(255))

    ORGANIZATION EXTERNAL

      (TYPE ORACLE_LOADER

      DEFAULT DIRECTORY trc_dir

      ACCESS PARAMETERS

        (RECORDS DELIMITED BY NEWLINE

         FIELDS (msg char(255))

        )

      LOCATION ('my_trc_file.trc')

      );  

 

C. L'analyse de la trace

 

select object_name

  , regexp_replace(msg, '([[:print:]]*nam=)|ela[[:print:]]*') evt

  , sum(regexp_substr(regexp_substr(msg, 'ela= ([[:alnum:]]+) file'), '[[:digit:]]+')) / 1000000 totelaela 

  , count(*) cnt

from trc_load a

  join dba_objects b on regexp_substr(regexp_substr(msg, 'obj#=([[:alnum:]]+) tim'), '[[:digit:]]+') = b.object_id

group by regexp_replace(msg, '([[:print:]]*nam=)|ela[[:print:]]*'), object_name  

order by sum(regexp_substr(regexp_substr(msg, 'ela= ([[:alnum:]]+) file'), '[[:digit:]]+')) / 1000000 desc     

 

OBJECT_NAME  EVT  TOTELAELA  CNT
MA_TABLE_PK  'db file sequential read' 261.571248 45877
MA_TABLE_ART_FK  'db file sequential read' 254.222979 47453
... 'db file sequential read'    
MA_TABLE_DEV_FK  'db file sequential read' 1.097991 174
... 'db file sequential read'    
MA_TABLE_PHO_FK  'db file sequential read' 0.951472 123
MA_TABLE_DEP_FK  'db file sequential read' 0.820889 4555

 

A présent, on peut enfin comprendre et envisager des solutions : 

- Cette table consolide pour une occurence donnée, la photo du stock d'un magasin. 

L'index PK est ordonné ainsi (article, courleur, taille, photo, magasin). Ce qui veut donc dire que le clustering factor est particulièrement mauvais, puisque les insertions se font par le module qui rame, par photo sur un magasin.

=> En réordonnançant la PK, (photo, magasin, article, courleur, taille), il n'y a plus aucune attente pour cet index

- L'index FK article est extrêmement redondante, puisque le nombre de références distinctes est finalement relavitement restreinte, mais dupliquées pour chaque photo de chaque magasin.

=> La compression de l'index divise sa taille part 3, réduisant également le nombre de blocs lus et mis à jour.

Partager cet article

Published by Pacman - dans SGBD
commenter cet article

commentaires

Xavier 23/11/2013 09:56

Bon article, comme les autres d'ailleurs ;).
L'approche est intéressante et me donne un nouvel angle d'analyse pour les problèmes de perfs que je rencontre de temps en temps.

Par contre, j'ai un peu de mal à suivre sur les conclusions.
- Pourquoi réordonner la PK est gagnant?
- Pour les indexes de FK, il me semble que les indexes stockent la référence du ROWID de la table. Du coup il y a autant d'occurrences que d'enregistrements dans la table. Pourquoi la compression
est-elle efficace?

Et en conclusion de ton exemple, en faisant les modifications que tu as identifiées, quel gain as-tu obtenu?

En tout cas, merci à toi pour ton partage!

Pacman 25/11/2013 09:53



Merci Xavier pour les encouragements :)


 


Pour la PK, le raisonnement est le suivant : imagine que tu aies un seul mode d'alimentation de la table, avec un certain ordre d'insertion des lignes. 


Si cet ordre d'insertion correspond à l'ordre des colonnes de l'index, tu vas mettre à jour un nomber minimal de bloc d'index.


 


Exemple concret, suppose que tu saisis une commande contenant 100 articles.


Si ton index est (article, commande), tu vas probablement mettre à jour 100 blocs d'index de la PK, parce que les références articles n'ont d'une part pas de raison d'être adjacents pour cette
commande, et parce que l'article peut apparaître dans une tripotée de commandes.


Maintenant, si ton index est (commande, article), il n'y aura pas encore d'entrée d'index pour cette commande, et tes 100 clefs insérées vont être regroupées dans le même bloc d'index.


Tu réduis donc drastiquement ton nombre de bloc lus.


 


Pour la FK, tu as bien raison sur le nombre de rowid à stocker... par contre, l'index stocke également la clef, qui elle peut être compressée.


Si tu as 1 champ dans l'index, et admettons que le type de donnée soit de la même taille que le rowid, et qu'il y a un nombre de valeurs distinctes négligeable (genre une dizaine) : si tu
compresses le champ, il ne te reste plus que le rowid dans l'index, et la taille de cet index est en gros divisé par deux.


(Et plus le nombre de champs avec peu de valeurs distinctes est élevé dans l'index, et le nombre de champs avec beaucoup de valeurs distinctes est faible, plus le gain à la compression est
important !)


 


 


Les gains obtenus sont vraiment significatifs. l'insertion en elle même prenait 10 minute, elle est passée ensutie en dessous de la minute. C'est difficile de dire exactement, car le batch au
global faisait plein d'autre choses, et je suis globalement passé d'un temps d'exécution qui était entre 10 minutes et 1 heure (!) à du 2 minutes... avec tous les aléas liés à l'environnement de
test bien sûr :)