Discussion :

[olivier]]

Bonjour.

J'ai une base 10G sur 2003Server R2.
Dans ma base, j'avais une table contenant 26 millions de lignes.
A travers une application, dont je n'ai pas les sources, j'ai effectué une purge sur cette table.
Elle est bien vide mais quand je tape

Code :

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

 select* from card_move_history;

j'obtiens

Code :

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

1
2
3
aucune ligne sÚlectionnÚe
 
EcoulÚ : 00 :00 :20.89

J'ai poussé un peu plus loin et voila ce que j'obtiens

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
SQL> select * from table (dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------
 
-----------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |    26M|  1641M| 57535 |
|   1 |  TABLE ACCESS FULL| CARD_MOVE_HISTORY |    26M|  1641M| 57535 |
-----------------------------------------------------------------------
 
Note
-----
   - 'PLAN_TABLE' is old version
 
11 ligne(s) sÚlectionnÚe(s).
 
EcoulÚ : 00 :00 :00.06

Pourquoi considère-t-il qu'il y a 26M de lignes?

[zaza576]

Hello,

Serait-ce des données d'indexation ou bien un système de cache de tes tuples supprimés ???

[olivier]]

ce sont des données, pas des indexes (si j'ai bien compris la question).

Qu'entendez-vous pas système de cache?

[mnitu]

----------------------------------------------------------------------------------
Pourquoi considère-t-il qu'il y a 26M de lignes?

Parce que les statistiques de la table ne sont pas à jour.

[zaza576]

Est-ce que les données ont été indexées ou mise en cache dans le SGBD ?
Si oui, est-ce qu'un simple purge supprime à la fois les données de la base, les indexes pointant sur ces données et la mise en cache des données (vulgairement, le cache sert à optimiser les requêtes en conservant "en mémoire" les données les plus souvent utilisées lors des requêtes, ainsi cela évite au SGBD d'avoir à fouiller à nouveau dans la foultitude de tuples pour obtenir ces données régulièrement demandé).

[McM]

Les stats ne sont pas à jour, donc l'optimiseur considère qu'il y a toujours 26M de lignes.

[olivier]]

zaza576 : apparemment, il n'y a aucun index sur la table si j'en crois ALL_INDEXES.

mnitu et McM : Donc je ne verrai l'effet de ma suppression que demain? (apparemment il y a une analyse stats tous les soirs).
Je connais un peu les stats mais je pensais qu'en accès simple (sans croisement) l'impact de la suppression serait immédiat...

[olivier]]

Merci beaucoup.

[zaza576]

zaza576 : apparemment, il n'y a aucun index sur la table si j'en crois ALL_INDEXES.

mnitu et McM : Donc je ne verrai l'effet de ma suppression que demain? (apparemment il y a une analyse stats tous les soirs).
Je connais un peu les stats mais je pensais qu'en accès simple (sans croisement) l'impact de la suppression serait immédiat...

As-tu accès à l'exécution des jobs sur les stats ? Tu pourrais le forcer à le faire maintenant pour mettre à jour des actions sur la base et faire disparaître ces 26 Mo.

[Pomalaix]

Il y a fort à parier que la table ait été vidée par des DELETE, avec la conséquence classique que le marqueur "high water mark" n'est pas mis à niveau.
Lors d'un parcours complet d'une table (le fameux TABLE ACCESS FULL de votre plan d'exécution), tous les blocs de la table sont lus jusqu'au marqueur, car pour Oracle, ils sont susceptibles de contenir des données.

C'est facile à voir.
Dans SQL*Plus, faites un SET AUTOTRACE ON, et réexécutez votre SELECT.

Dans la partie "statistiques", vous verrez le nombre de blocs lus (db block gets et consistent gets).

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
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |   207 |   300   (0)| 00:00:04 |
|   1 |  TABLE ACCESS FULL| DEMO |     1 |   207 |   300   (0)| 00:00:04 |
--------------------------------------------------------------------------

Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
       1079  consistent gets
          0  physical reads
          0  redo size
       1351  bytes sent via SQL*Net to client
        508  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Le fait que les statistiques ne soient pas à jour n'est pas ici ce qui cause le problème de performances.
Celui-ci, même avec des statistiques à jour, persistera tant que le marqueur n'aura pas été redescendu.
Pour cela, vous pouvez, par exemple, utiliser la commande TRUNCATE.

[olivier]]

Désolé zaza576, je n'ai pas vu ta question avant ce matin.
Mais les stats n'ont rien changé et pomalaix a raison

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
24
25
26
27
28
29
30
31
SQL> select * from card_move_history;

aucune ligne sÚlectionnÚe

EcoulÚ : 00 :00 :20.67

Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 425611253

---------------------------------------------------------------------------------------
| Id  | Operation         | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                   |     1 |   122 | 56702   (1)| 00:11:21 |
|   1 |  TABLE ACCESS FULL| CARD_MOVE_HISTORY |     1 |   122 | 56702   (1)| 00:11:21 |
---------------------------------------------------------------------------------------


Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
     257695  consistent gets
     255171  physical reads
          0  redo size
        869  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Question subsidiaire :
J'ai constaté ce problème en test. Mais en prod la tables est a 26 M et je vais devoir réduire le nombre sans tout supprimer. Je n'aurai donc aucun moyen de ramener le HWM à une valeur "décente" puisqu'il faut passer par un truncate.
Me trompe-je?

[Pomalaix]

... je vais devoir réduire le nombre sans tout supprimer. Je n'aurai donc aucun moyen de ramener le HWM à une valeur "décente" puisqu'il faut passer par un truncate.

TRUNCATE est une des opérations qui réajustent le HWM, pas la seule.

Dans votre cas, ALTER TABLE matable SHRINK SPACE est la solution pertinente.
Voir par exemple : http://blog.developpez.com/pachot/shrink_table/ et n'hésitez pas à cliquer sur sa démo.

Une telle réorganisation se justifie si vous supprimez une proportion notable des données, et que vous n'allez pas, dans l'immédiat, réinsérer autant de données.

[McM]

Suivant les versions (je ne connaissais pas le Shrink Space, merci Pomalaix), on peut aussi faire un

Code :

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

alter table move tablespace <même tablespace>

Le truncate peut ne pas être utilisé tout le temps en cas de contrainte référentielle.

[olivier]]

Génial, ça fonctionne.
Lien hyper instructif mais pfiouuu... je vais devoir m'y reprendre à plusieurs fois pour bien comprendre toutes les explications de la démo.

Merci.

[mnitu]

...
Mais les stats n'ont rien changé et pomalaix a raison
...

Il y des statistique (concernant la table, les indexes, etc) et des statistiques (d'exécution de la requête)!
Ceux de la table on bien changés: regardez la valeur Rows actuelle 1 et comparez la avec la valeur de votre première intervention 26M.
En ce qu'il concerne les statistiques d'exécution vous avez maintenant les informations vous permettant de comprendre pourquoi elle restent encore assez importantes.

[Waldar]

On peut aussi faire un

Code :

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

alter table <matable> move tablespace <même tablespace>

À ce moment-là autant écrire :

Code :

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

alter table <matable> move;

[olivier]]

Effectivement mnitu, je n'avais pas vu ce changement.

Pour autant le temps de réponse restait à 20 secondes. C'est ce qui me faisait dire que les stats n'avaient rien changé.

[mnitu]

Sur la table ayant 26M lignes combien estimez-vous qu'il resteront suite à la purge ?

[olivier]]

A priori, il devrait en rester un peu moins d'un tiers.
Il faut en supprimer un peu plus de 19 M soit 72% environ.

[mnitu]

A priori, il devrait en rester un peu moins d'un tiers.
Il faut en supprimer un peu plus de 19 M soit 72% environ.

Dans ce cas il sera mieux de créer une nouvelle table (create ... as Select ...) qui contiendra que ce qui reste sans les souci de fragmentations et High Water Marck impliqués par le delete, de détruire l'ancienne table (drop table) et de renommer la nouvelle en table d'origine. Et bien sur s'occuper de tous les autres choses dépendantes:constraintes, indexes, triggers, etc.