Utilisation de table temporaire [Résolu]

[spdev666]

Bonjour,

Je suis sur Oracle 10g.
J'ai une requête qui peut prendre en entré un très grand nombre d'identifiants (plus de 1 000). Pour contourner le problème, j'utilise une table temporaire : j'insère les identifiants, puis je fais une jointure sur cette table pour ne sélectionner que les enregistrements que je souhaite. La table est ensuite vidée.

Cela fonctionne bien, sauf qu'après un certain temps, le temps d'exécution de la requête se augmente considérablement (multiplié par 2 ou 3), mais je ne trouve pas la raison.
Si je supprime puis recrée la table temporaire, tout redevient normal.

Ma table temporaire est définie ainsi :

Code :

1
2
3
4
5
6
7
8
9
 
CREATE global TEMPORARY TABLE TABLETMP
(
  TMP_ID   NUMBER(10) NOT NULL,
  TMP_IDSELECTION NUMBER(10) NOT NULL
)
ON commit DELETE rows;
ALTER TABLE TABLETMP
  ADD constraint PK_TABLETMP PRIMARY KEY (TMP_ID, TMP_IDSELECTION);

TMP_ID est un identifiant unique par session
TMP_IDSELECTION contient la valeur que je veux selectionner

A l'utilisation, cela donne :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
INSERT INTO TABLETMP (TMP_ID, TMP_IDSELECTION)
VALUES(1, 1);
INSERT INTO TABLETMP (TMP_ID, TMP_IDSELECTION)
VALUES(1, 2);
WITH TABLE1 AS (SELECT 1 MonId, 'Label1' MonLabel FROM Dual Union ALL
SELECT 1 MonId, 'Label1' MonLabel FROM Dual Union ALL
SELECT 2 MonId, 'Label2' MonLabel FROM Dual Union ALL
SELECT 3 MonId, 'Label3' MonLabel FROM Dual Union ALL
SELECT 4 MonId, 'Label4' MonLabel FROM Dual)
SELECT T1.*
  FROM TABLE1 T1
  JOIN TABLETMP TMP ON TMP.TMP_IDSELECTION T1.MONID
 WHERE TMP.TMP_ID = 1;
 
MonId  MonLabel
---------------
1      Label1
2      Label2

Si je simule ma table temporaire avec un WITH, j'obtiens les performances normal.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
WITH TABLETMP AS (SELECT 1 TMP_IDSELECTION FROM Dual Union ALL
SELECT 2 TMP_IDSELECTION FROM Dual)
     TABLE1 AS (SELECT 1 MonId, 'Label1' MonLabel FROM Dual Union ALL
SELECT 1 MonId, 'Label1' MonLabel FROM Dual Union ALL
SELECT 2 MonId, 'Label2' MonLabel FROM Dual Union ALL
SELECT 3 MonId, 'Label3' MonLabel FROM Dual Union ALL
SELECT 4 MonId, 'Label4' MonLabel FROM Dual)
SELECT T1.*
  FROM TABLE1 T1
  JOIN TABLETMP TMP ON TMP.TMP_IDSELECTION T1.MONID;
 
MonId  MonLabel
---------------
1      Label1
2      Label2

Quelqu'un a t il une explication sur la dégradation des performances si j'utilise la table temporaire ?

[mnitu]

Ajoutez le hint dynamic sampling à 5 pour la requête utilisant la table temporaire.

[spdev666]

Merci pour la réponse.

J'ai essayé, mais cela ne change rien au temps d'execution

[mnitu]

Dans ce cas faite une trace SQL étendu de votre traitement pour voir clairement ce qui se passe.

[pachot]

Bonjour,

après un certain temps, le temps d'exécution de la requête se augmente considérablement

Est-ce que la table est vidée (commit) entre les exécutions ?
Sinon, c'est probablement l'index sur la pk qui grossit et est plus long à mettre à jour (plus profond, moins de blocks en cache,...)
Cordialement,
Franck.

[spdev666]

mnitu:
J extrais la trace SQL, mais la requete est relativement complexe, donc le plan d'execution aussi...

pachot:
Comme indiqué dans la definition de la table temporaire, (ON commit DELETE rows) elle est vidée à chaque utilisation

merci de vos réponses

[pachot]

Oui, en fait je voulais savoir s'il y avait un commit entre chaque exécution

[spdev666]

Autant pour moi...

Oui, la table est vidée à chaque fois.

[mnitu]

Envoyé par spdev666

...
J extrais la trace SQL, mais la requete est relativement complexe, donc le plan d'execution aussi...

Postez-le.
Entre nous votre exemple utilise une requête très simple

[spdev666]

Voici une partie représentative de la requete que je traite avec son plan d'execution

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
SELECT E.VILLE, E.ENTITE_ID, H.RUBRIQUE
  FROM TABLE_MOIS M, TABLE_HISTO H, TABLE_ENTITEHISTO EH, TABLE_ETATENTITE EE, TABLE_ENTITE E, TABLETMP T
 WHERE RH.HISTO_ID = H.HISTO_ID
   AND RH.ENTITE_ID = E.ENTITE_ID
   AND EH.ENTITE_ID = E.ENTITE_ID
   AND M.DATE_DEBUT BETWEEN EE.DEBUT AND EE.FIN
   AND H.RUBRIQUE IN (1,2,3,4,5,6,7)
   AND M.MOIS_ID = 12345
   AND H.ANNEE <= M.ANNEE
   AND T.TMP_IDSELECTION = E.ENTITE_ID
   AND T.TMP_ID = &Id
 GROUP BY E.VILLE, E.ENTITE_ID, H.RUBRIQUE

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                |     1 |    97 |    76   (2)|       |       |
|   1 |  HASH GROUP BY                      |                                |     1 |    97 |    76   (2)|       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE_HISTO                    |     1 |    14 |    21   (0)| ROWID | ROWID |
|   3 |    NESTED LOOPS                     |                                |     1 |    97 |    75   (0)|       |       |
|   4 |     NESTED LOOPS                    |                                |     1 |    83 |    54   (0)|       |       |
|   5 |      NESTED LOOPS                   |                                |     1 |    73 |    53   (0)|       |       |
|   6 |       NESTED LOOPS                  |                                |     1 |    60 |    52   (0)|       |       |
|   7 |        NESTED LOOPS                 |                                |     1 |    38 |     1   (0)|       |       |
|   8 |         TABLE ACCESS BY INDEX ROWID | TABLE_MOIS                     |     1 |    12 |     1   (0)|       |       |
|   9 |          INDEX UNIQUE SCAN          | PK_TABLE_MOIS                  |     1 |       |     0   (0)|       |       |
|  10 |         INDEX RANGE SCAN            | PK_TABLETMP                    |     1 |    26 |     0   (0)|       |       |
|  11 |        TABLE ACCESS FULL            | TABLE_ETATENTITE               |    50 |  1100 |    51   (0)|       |       |
|  12 |       INDEX RANGE SCAN              | PK_TABLE_ENTITEHISTO           |     1 |    13 |     1   (0)|       |       |
|  13 |      TABLE ACCESS BY INDEX ROWID    | TABLE_ENTITE                   |     1 |    10 |     1   (0)|       |       |
|  14 |       INDEX UNIQUE SCAN             | PK_TABLE_ENTITE                |     1 |       |     0   (0)|       |       |
|  15 |     INDEX RANGE SCAN                | IDXFK_TABLEHISTO_HISTOID       |    18 |       |     2   (0)|       |       |
---------------------------------------------------------------------------------------------------------------------------

Et voici le plan d execution de la version avec With:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                           | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                                |     4 |   300 |   590   (1)|       |       |
|   1 |  HASH GROUP BY                      |                                |     4 |   300 |   590   (1)|       |       |
|   2 |   TABLE ACCESS BY GLOBAL INDEX ROWID| TABLE_HISTO                    |     1 |    14 |    21   (0)| ROWID | ROWID |
|   3 |    NESTED LOOPS                     |                                |     4 |   300 |   589   (1)|       |       |
|   4 |     HASH JOIN                       |                                |     8 |   488 |   421   (1)|       |       |
|   5 |      VIEW                           |                                |   127 |   508 |   254   (0)|       |       |
|   6 |       UNION-ALL                     |                                |       |       |            |       |       |
|   7 |        FAST DUAL                    |                                |     1 |       |     2   (0)|       |       |
               ...
| 133 |        FAST DUAL                    |                                |     1 |       |     2   (0)|       |       |
| 134 |      HASH JOIN                      |                                |   748 | 42636 |   166   (1)|       |       |
| 135 |       NESTED LOOPS                  |                                |   748 | 35156 |   102   (0)|       |       |
| 136 |        NESTED LOOPS                 |                                |    50 |  1700 |    52   (0)|       |       |
| 137 |         TABLE ACCESS BY INDEX ROWID | TABLE_MOIS                     |     1 |    12 |     1   (0)|       |       |
| 138 |          INDEX UNIQUE SCAN          | PK_TABLE_MOIS                  |     1 |       |     0   (0)|       |       |
| 139 |         TABLE ACCESS FULL           | TABLE_ETATENTITE               |    50 |  1100 |    51   (0)|       |       |
| 140 |        INDEX RANGE SCAN             | PK_TABLE_ENTITEHISTO           |    15 |   195 |     1   (0)|       |       |
| 141 |       TABLE ACCESS FULL             | TABLE_ENTITE                   | 11628 |   113K|    63   (0)|       |       |
| 142 |     INDEX RANGE SCAN                | IDXFK_TABLEHISTO_HISTOID       |    18 |       |     2   (0)|       |       |
---------------------------------------------------------------------------------------------------------------------------

[mnitu]

C’est compliqué avec les informations que vous fournissez de tirer les bonnes conclusions. Néanmoins vos plans d’exécution montrent des différences dans l’estimation des cardinalités des étapes du plan ce qui est le signe de mauvais plans d’exécution.

L’utilisation des tables temporaires qui n’ont pas des statistiques pose ce type de problème surtout quand elle entre en jointure avec des autres tables. Dans ces cases vous pouvez utiliser soit le hint dynamic sampling soit un autre non documenté cardinality ou opt_estimate.

[spdev666]

J'ai essayé les différents hints sans succès.

Le problème semble être résolu en supprimant les statistiques sur la table temporaire puis en verrouillant la génération des statistiques dessus.

Merci pour vos réponses