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.

vendredi 13 avril 2012

ORA-02266 lors d'un TRUNCATE TABLE

Si lors d'un TRUNCATE d'une table vous obtenez une erreur ORA-02266 c'est que la clé primaire de cette table est référencée par une autre table (ou plusieurs) via une clé étrangère. Cela arrive souvent et c'est ce qui m'est arrivé ce matin:
SQL> truncate table mdw_trace;
truncate table mdw_trace
               *
ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

La solution est simple et consiste à retrouver les tables enfants de la table que je veux truncater puis à désactiver les clés étrangères correspondantes.
Pour ce faire j'ai dans ma boite à scripts le script suivant que j'ai appelé find_child_table.sql:
-- find child tables when ORA-02292: integrity constraint
select constraint_name,table_name from user_constraints where r_constraint_name in(
select constraint_name from user_constraints where table_name = upper('&table_name')) ;

Selon la valeur de la variable TABLE_NAME cette requête retourne le nom de la table enfant ainsi que le nom de la Foreign key:
SQL> @find_child_table
SQL> -- find child tables when ORA-02292: integrity constraint
SQL> select constraint_name,table_name from user_constraints where r_constraint_name in(
  2  select constraint_name from user_constraints where table_name = upper('&table_name')) ;
Enter value for table_name: MDW_TRACE
old   2: select constraint_name from user_constraints where table_name = upper('&table_name'))
new   2: select constraint_name from user_constraints where table_name = upper('MDW_TRACE'))

CONSTRAINT_NAME                TABLE_NAME
------------------------------ ------------------------------
MDW_TRACE_PARAMETER_FK_TRACE   MDW_TRACE_PARAMETER

1 row selected.

Dans mon cas la table référençant ma table à tuncater se nomme MDW_TRACE_PARAMETER et la clé étrangère MDW_TRACE_PARAMETER_FK_TRACE. Je peux donc maintenant désactiver cette contrainte pour effectuer mon TRUNCATE:
SQL> alter table MDW_TRACE_PARAMETER disable constraint MDW_TRACE_PARAMETER_FK_TRACE;

Table altered.

SQL> truncate table mdw_trace;

Table truncated.

J'ai pu truncater ma table.
Il ne me reste enfin plus qu'à réactiver la contrainte:
SQL> alter table MDW_TRACE_PARAMETER enable constraint MDW_TRACE_PARAMETER_FK_TRACE;

Table altered.