Utilisation de table temporaire

**spdev666** · 09/03/2011, 16h59

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 :

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

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 :

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
 
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 :

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
 
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** · 09/03/2011, 17h16

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

**spdev666** · 09/03/2011, 17h45

Merci pour la réponse.

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

**mnitu** · 10/03/2011, 08h57

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

**pachot** · 10/03/2011, 14h33

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** · 10/03/2011, 16h36

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** · 10/03/2011, 16h54

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

**spdev666** · 10/03/2011, 18h00

Autant pour moi...

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

**mnitu** · 10/03/2011, 20h17

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** · 11/03/2011, 15h53

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

Code :

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

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 :

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
 
---------------------------------------------------------------------------------------------------------------------------
| 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 :

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
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** · 14/03/2011, 14h22

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** · 18/03/2011, 09h05

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

Utilisation de table temporaire

SQL Oracle

Vue hybride

Discussions similaires

Partager

Partager