lundi 30 avril 2012

Fixed tables et statistics


Les fixed tables appelées aussi "X$ tables" sont des tables appartenant à SYS et qui sont censées ne pas être modifiées par les utilisateurs d'Oracle (DBA ou pas). Il faut noter ici qu'il ne s'agit pas de tables comme celles qu'on crée avec la commande SQL CREATE TABLE mais plutôt une sorte d'objet permettant d'exposer aux DBAs les structures de données codées en C situées dans la SGA.

Les vues dynamiques de performances (les fameuses V$) sont basées sur ces tables là. La preuve, lorsqu'on regarde le plan d'exécution d'un SELECT basé sur la vue V$LOCK on s'aperçoit que plusieurs tables commençant par X$ sont impliquées:

SQL> explain plan for select * from v$lock; 

Explained. 

SQL> select * from table(dbms_xplan.display); 
-------------------------------------------------------------------------------------------- 
 Id  | Operation                | Name            | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT         |                 |     1 |   131 |     1 (100)| 00:00:01 | 
|   1 |  NESTED LOOPS            |                 |     1 |   131 |     1 (100)| 00:00:01 | 
|*  2 |   HASH JOIN              |                 |     1 |    98 |     1 (100)| 00:00:01 | 
|*  3 |    FIXED TABLE FULL      | X$KSUSE         |     1 |    30 |     0   (0)| 00:00:01 | 
|   4 |    VIEW                  | GV$_LOCK        |    10 |   680 |     0   (0)| 00:00:01 | 
|   5 |     UNION-ALL            |                 |       |       |            |          | 
|*  6 |      FILTER              |                 |       |       |            |          | 
|   7 |       VIEW               | GV$_LOCK1       |     2 |   136 |     0   (0)| 00:00:01 | 
|   8 |        UNION-ALL         |                 |       |       |            |          | 
|*  9 |         FIXED TABLE FULL | X$KDNSSF        |     1 |    94 |     0   (0)| 00:00:01 | 
|* 10 |         FIXED TABLE FULL | X$KSQEQ         |     1 |    94 |     0   (0)| 00:00:01 | 
|* 11 |      FIXED TABLE FULL    | X$KTADM         |     1 |    94 |     0   (0)| 00:00:01 | 
|* 12 |      FIXED TABLE FULL    | X$KTATRFIL      |     1 |    94 |     0   (0)| 00:00:01 | 
|* 13 |      FIXED TABLE FULL    | X$KTATRFSL      |     1 |    94 |     0   (0)| 00:00:01 | 
|* 14 |      FIXED TABLE FULL    | X$KTATL         |     1 |    94 |     0   (0)| 00:00:01 | 
|* 15 |      FIXED TABLE FULL    | X$KTSTUSC       |     1 |    94 |     0   (0)| 00:00:01 | 
|* 16 |      FIXED TABLE FULL    | X$KTSTUSS       |     1 |    94 |     0   (0)| 00:00:01 | 
|* 17 |      FIXED TABLE FULL    | X$KTSTUSG       |     1 |    94 |     0   (0)| 00:00:01 | 
|* 18 |      FIXED TABLE FULL    | X$KTCXB         |     1 |    94 |     0   (0)| 00:00:01 | 
|* 19 |   FIXED TABLE FIXED INDEX| X$KSQRS (ind:1) |     1 |    33 |     0   (0)| 00:00:01 | 
--------------------------------------------------------------------------------------------

L’appel à V$LOCK implique l’accès à 11 fixed tables, et justement j’ai récemment repéré, dans une des bases sur lesquelles je travaille, une requête basée sur la vue V$LOCK et qui mettait une quarantaine de secondes à s’exécuter. Cette requête de monitoring utilisée par je ne sais quelle application était exécutée plusieurs milliers de fois par jour.

Voici la requête en question ainsi que son plan d’exécution mettant en évidence les temps réels d’exécution de chaque opération :


SQL> SELECT NVL(TRUNC(MAX(DECODE(W.REQUEST,0,0,W.CTIME))/60),0) FROM V$LOCK W WHERE W.REQUEST > 0;   

NVL(TRUNC(MAX(DECODE(W.REQUEST,0,0,W.CTIME))/60),0) 
--------------------------------------------------- 
                                                  0 

1 row selected. 

Elapsed: 00:00:48.10 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last')); 

  Plan hash value: 2399206389 
------------------------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation               | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes |  OMem |  1Mem | Used-Mem | Used-Tmp| 
------------------------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT        |            |      1 |        |      1 |00:00:47.99 |      11 |  16080 |  64350 |       |       |          |         | 
|   1 |  SORT AGGREGATE         |            |      1 |      1 |      1 |00:00:47.99 |      11 |  16080 |  64350 |       |       |          |         | 
|*  2 |   HASH JOIN             |            |      1 |      1 |      0 |00:00:47.99 |      11 |  16080 |  64350 |   675M|    18M|  202M (1)|     537K| 
|   3 |    MERGE JOIN CARTESIAN |            |      1 |    100 |     10M|00:00:06.87 |       0 |      0 |      0 |       |       |          |         | 
|*  4 |     FIXED TABLE FULL    | X$KSUSE    |      1 |      1 |   1536 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|   5 |     BUFFER SORT         |            |   1536 |    100 |     10M|00:00:03.34 |       0 |      0 |      0 |   337K|   337K|  299K (0)|         | 
|   6 |      FIXED TABLE FULL   | X$KSQRS    |      1 |    100 |   6976 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|   7 |    VIEW                 | GV$_LOCK   |      1 |     10 |      0 |00:00:00.08 |      11 |      0 |      0 |       |       |          |         | 
|   8 |     UNION-ALL           |            |      1 |        |      0 |00:00:00.08 |      11 |      0 |      0 |       |       |          |         | 
|*  9 |      FILTER             |            |      1 |        |      0 |00:00:00.03 |       0 |      0 |      0 |       |       |          |         | 
|  10 |       VIEW              | GV$_LOCK1  |      1 |      2 |      0 |00:00:00.03 |       0 |      0 |      0 |       |       |          |         | 
|  11 |        UNION-ALL        |            |      1 |        |      0 |00:00:00.03 |       0 |      0 |      0 |       |       |          |         | 
|* 12 |         FIXED TABLE FULL| X$KDNSSF   |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 13 |         FIXED TABLE FULL| X$KSQEQ    |      1 |      1 |      0 |00:00:00.02 |       0 |      0 |      0 |       |       |          |         | 
|* 14 |      FIXED TABLE FULL   | X$KTADM    |      1 |      1 |      0 |00:00:00.04 |       0 |      0 |      0 |       |       |          |         | 
|* 15 |      FIXED TABLE FULL   | X$KTATRFIL |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 16 |      FIXED TABLE FULL   | X$KTATRFSL |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 17 |      FIXED TABLE FULL   | X$KTATL    |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 18 |      FIXED TABLE FULL   | X$KTSTUSC  |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 19 |      FIXED TABLE FULL   | X$KTSTUSS  |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 20 |      FIXED TABLE FULL   | X$KTSTUSG  |      1 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 |       |       |          |         | 
|* 21 |      FIXED TABLE FULL   | X$KTCXB    |      1 |      1 |      0 |00:00:00.02 |      11 |      0 |      0 |       |       |          |         | 
------------------------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - access("SADDR"="S"."ADDR" AND TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR")) 
   4 - filter("S"."INST_ID"=USERENV('INSTANCE')) 
   9 - filter(USERENV('INSTANCE') IS NOT NULL) 
  12 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  13 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  14 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  15 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  16 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  17 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  18 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  19 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  20 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSOBFLG",1)<>0)) 
  21 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND "INST_ID"=USERENV('INSTANCE') AND BITAND("KSSPAFLG",1)<>0))

Le plan nous montre que le SELECT sur V$LOCK s’est exécutée en 48 secondes.
En regardant le plan de plus près on s’aperçoit qu’un MERGE JOIN CARTESIAN (opération 3 du plan) entre la fixed table X$KSUSE et X$KSQRS est utilisé à mauvais escient puisque la jointure retourne 10 millions de lignes (colonne A-ROWS) au lieu des 100 estimés par le CBO (colonne E-ROWS). Ce mauvais choix d’opération est dû au fait que la cardinalité sur la fixed table X$KSUSE est mal estimé. En effet, l’optimiseur estime que l’accès à cette table va retourner qu’une seule ligne au lieu de 1536. Le fait d’estimer une cardinalité de 1 explique le choix du MERGE JOIN CARTESIAN.
Je me suis rendu compte que pour cette base les statistiques sur les fixed tables et celles sur le dictionnaire de données n’avaient jamais été calculées par les DBAs de PROD chargés d’administrer cette base.

Après avoir demandé un calcul de stats sur ces tables via les procédures gather_fixed_objects_stats et gather_dictionary_stats du package DBMS_STATS, la requête s’est mise à s’exécuter instantanément.

Voici le nouveau plan après le calcul des stats sur les « fixed objects » :
Plan hash value: 2965011029 
---------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | 
---------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT         |            |      1 |        |      1 |00:00:00.08 |      10 |    |  |          | 
|   1 |  SORT AGGREGATE          |            |      1 |      1 |      1 |00:00:00.08 |      10 |    |  |          | 
|*  2 |   HASH JOIN              |            |      1 |    698 |      0 |00:00:00.08 |      10 |   816K|   816K|  203K (0)| 
|*  3 |    HASH JOIN             |            |      1 |     10 |      0 |00:00:00.08 |      10 |   845K|   845K|  190K (0)| 
|   4 |     VIEW                 | GV$_LOCK   |      1 |     10 |      0 |00:00:00.08 |      10 |    |  |          | 
|   5 |      UNION-ALL           |            |      1 |        |      0 |00:00:00.08 |      10 |    |  |          | 
|*  6 |       FILTER             |            |      1 |        |      0 |00:00:00.02 |       0 |    |  |          | 
|   7 |        VIEW              | GV$_LOCK1  |      1 |      2 |      0 |00:00:00.02 |       0 |    |  |          | 
|   8 |         UNION-ALL        |            |      1 |        |      0 |00:00:00.02 |       0 |    |  |          | 
|*  9 |          FIXED TABLE FULL| X$KDNSSF   |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 10 |          FIXED TABLE FULL| X$KSQEQ    |      1 |      1 |      0 |00:00:00.02 |       0 |    |  |          | 
|* 11 |       FIXED TABLE FULL   | X$KTADM    |      1 |      1 |      0 |00:00:00.04 |       0 |    |  |          | 
|* 12 |       FIXED TABLE FULL   | X$KTATRFIL |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 13 |       FIXED TABLE FULL   | X$KTATRFSL |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 14 |       FIXED TABLE FULL   | X$KTATL    |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 15 |       FIXED TABLE FULL   | X$KTSTUSC  |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 16 |       FIXED TABLE FULL   | X$KTSTUSS  |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 17 |       FIXED TABLE FULL   | X$KTSTUSG  |      1 |      1 |      0 |00:00:00.01 |       0 |    |  |          | 
|* 18 |       FIXED TABLE FULL   | X$KTCXB    |      1 |      1 |      0 |00:00:00.01 |      10 |    |  |          | 
|* 19 |     FIXED TABLE FULL     | X$KSUSE    |      0 |   1536 |      0 |00:00:00.01 |       0 |    |  |          | 
|  20 |    FIXED TABLE FULL      | X$KSQRS    |      0 |   6976 |      0 |00:00:00.01 |       0 |    |  |          | 
---------------------------------------------------------------------------------------------------------------------------- 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - access(TO_CHAR(USERENV('INSTANCE'))||RAWTOHEX("RADDR")=TO_CHAR("R"."INST_ID")||RAWTOHEX("R"."ADDR")) 
   3 - access("SADDR"="S"."ADDR") 
   6 - filter(USERENV('INSTANCE') IS NOT NULL) 
   9 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  10 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  11 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  12 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  13 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  14 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  15 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  16 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  17 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSOBFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  18 - filter(("KSQLKREQ">0 AND ("KSQLKMOD"<>0 OR "KSQLKREQ"<>0) AND BITAND("KSSPAFLG",1)<>0 AND 
              "INST_ID"=USERENV('INSTANCE'))) 
  19 - filter("S"."INST_ID"=USERENV('INSTANCE'))

On s’aperçoit que le MERGE JOIN CARTESIAN a disparu car la cardinalité estimée sur la fixed table X$KSUSE est désormais bien de 1536 et non pas de 1 comme précédemment.

CONCLUSION :
Lorsque vous constatez des performances catastrophiques sur des requêtes impliquant des vues dynamiques de performances ou des vues du dictionnaire de données demandez-vous s’il ne serait pas nécessaire de lancer un calcul de stats sur ces objets.
Attention, la doc Oracle précise que ces stats doivent être calculées pendant une période représentative de l’activité de la base.

Aucun commentaire:

Enregistrer un commentaire