Discussion :

[DevNico]

Bonjour,

J'utilise cette requête pour lister les tables occupant beaucoup plus d'espace que ce qu'elles devraient par rapport aux données qu'elles contiennent :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
select 
    tab.owner, 
    tab.table_name, 
    tab.avg_row_len, 
    tab.num_rows, 
    round(((tab.blocks*tbs.block_size/1024/1024))) "TOTAL_SIZE (MB)", 
    round((tab.num_rows*tab.avg_row_len/1024/1024)) "ACTUAL_SIZE (MB)", 
    round(((tab.blocks*tbs.block_size/1024/1024)-(tab.num_rows*tab.avg_row_len/1024/1024))) "FRAGMENTED_SPACE (MB)", 
    decode(tab.blocks*tbs.block_size,0, null, round(((tab.blocks*tbs.block_size)-(tab.num_rows*tab.avg_row_len))/(tab.blocks*tbs.block_size)*100)) "%_VIDE" 
FROM 
    dba_tables tab, 
    dba_tablespaces tbs 
WHERE 
    tab.tablespace_name=tbs.tablespace_name

Et dans mes résultats, j'ai quelques tables qui occupent plusieurs dizaines de Go alors qu'elles ne contiennent que quelques centaines de Mo.
Et notamment celle-ci :

Code :

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

1
2
OWNER	TABLE_NAME	AVG_ROW_LEN	NUM_ROWS	TOTAL_SIZE (MB)	ACTUAL_SIZE (MB)	FRAGMENTED_SPACE (MB)	%_VIDE
DWH	TABLE1		66		42		53400		0			53400			100

Comment est-ce possible qu'une table ne contenant que quelques dizaines de lignes arrive à occuper 53Go ?
Sachant qu'elle a toujours à peu près le même nombre de lignes, elle est mise à jour en delete/insert très régulièrement.
J'avais compris qu'il était possible de perdre de l'espace libre à cause des suppressions qui créaient des 'trous' entre les données qui étaient perdus. Mais dans ce cas, j'ai du mal à comprendre qu'il ne réutilise pas l'espace disponible ?

Ou est-ce que je me suis loupé quelque part dans la requête ?

Par avance merci,
Nicolas

[Ikebukuro]

Salut,

C'est le comportement normal d'Oracle, dans certains cas l'espace libéré n'est pas réutilisé : par exemple tu fais des DELETEs et plus aucun INSERT!
Un cas plus vicieux est que tu fais des DELETEs, plein d'INSERTs après MAIS si tu as utilisé le hint APPEND, Oracle fait ce qu'on appelle du DIRECT PATH WRITE et il insère dans ce cas après le HWM : résultat, Oracle ne réutilise pas l'espace libre sous le HWM et ta table grossit sans arrêt.

J'avais fait un article ici qui rejoins ce que tu constates : http://dbaoraclesql.canalblog.com/ar.../34974508.html

A priori il faudrait que tu fasses un SHRINK sur ta table (pas un MOVE) pour récupérer l'espace.
Je te conseille aussi de regarder le résultat de l'exécution du SEGMENT ADVISOR qui a lieu toutes les nuits, ou bien de le lancer toi même, tu devrais retrouver ce que tu constates.

[DevNico]

Salut,
Et merci pour ta réponse.

J'étais déjà tombé sur ton article il y a quelques temps, il est très bien fait : j'avais tout compris.

Mais j'avais en tête que l'espace vide était réutilisé si c'était possible, d'où mon questionnement. C'est peut-être effectivement dû au DIRECT PATH WRITE.
J'utilise l'ETL Powercenter d'Informatica pour l'alimentation, il faut que je regarde s'il utilise ce mode.

Je ne suis pas DBA sur le projet, du coup ce n'est pas à moi de faire les schrinks, mais il faut que je comprenne d'où vient cette fragmentation de l'espace libre pour voir si je peux réduire sa génération via les traitements.

On a un autre cas d'une table qu'on alimente après un truncate et qui occupe 10 fois plus d'espace que nécessaire après les inserts.
Le mode DIRECT PATH WRITE pourrait expliquer ça également ?

Merci encore
Nicolas

[Ikebukuro]

On a un autre cas d'une table qu'on alimente après un truncate et qui occupe 10 fois plus d'espace que nécessaire après les inserts.
Le mode DIRECT PATH WRITE pourrait expliquer ça également ?

Ah oui, complètement; voilà un autre lien très intéressant pour ton problème. Il disent quels sont les avantages et inconvénients du hint APPEND et du DIRECT PATH WRITE (appelé aussi DIRECT PATH INSERT) : https://oracle-base.com/articles/misc/append-hint

[DevNico]

Super, merci beaucoup pour ton aide.
Et je crois que j'ai trouvé précisément le soucis : https://kb.informatica.com/solution/1/Pages/10332.aspx

Dans Powercenter, on peut gérer le nombre de lignes traitées entre chaque commit.
Comme il utilise un extend minimum à chaque commit à cause du Direct Path Write, si l'intervalle de commit est trop petit et que l'extend est trop grand... C'est la cata.

Encore merci pour ton aide efficace.
Nicolas

[DevNico]

Bonjour,

Je me permets de revenir sur cette discussion afin d'avoir une petite précision :

Je constate sur l'alimentation d'une de nos tables :
- après un truncate
- en mode Direct Path Insert
- avec des commits toutes les 10 000 lignes
- et un next extend sur la table à 100Mo

que le volume occupé après alimentation est égal au nombre de commits réalisés multiplié par la taille des next extends.
(4,6 millions de lignes => 460 commits => 46Go occupés)
Ce qui voudrait dire qu'il positionne le HWM à la fin de chaque extend après chaque commit. Je pensais qu'il le positionnerait à la fin du dernier block occupé par les données ?

Est-ce que j'ai loupé quelque chose ?

Par avance merci,
Nicolas

[Ikebukuro]

Ce qui voudrait dire qu'il positionne le HWM à la fin de chaque extend après chaque commit. Je pensais qu'il le positionnerait à la fin du dernier block occupé par les données ?

Heu, pour moi c'est à peu près la même chose.
L'extent est formé de N blocs : si Oracle alloue un extent de 10 blocs MAIS qu'il insère les données SEULEMENT dans le premier bloc car il y en a peu, je pense (à confirmer par d'autres experts) que le HWM est positionné après le dernier bloc de cet extent et non pas après le premier bloc. De toute façon que tes blocs soit remplis de données ou non, pour Oracle c'est "pareil" car la taille dans DBA_SEGMENTS tiendra compte des 10 blocs et pas que du premier.

Mais en quoi cela te pose un pb?

Petite anecdote : il existe aussi un LHWM (Low High Water Mark)

[DevNico]

Mais en quoi cela te pose un pb?

En fait, on ne reproduit pas ce constat dans tous nos environnements.
Avec l'hypothèse que le HWM est à la fin du dernier block contenant des données, on ne devrait pas avoir autant de volume (10 000 lignes génèrent environ 4Mo de données => 4*460= 1 840Mo, ce qu'on constate bien dans un autre environnement).
Mais dans l'environnement dont je parle jusqu'à présent, c'est le résultat que j'ai affiché dans le post avant : comme s'il prenait un extend complet à chaque commit, au lieu de juste les blocs occupés.

C'est juste désagréable quand on n'arrive pas à rapprocher la théorie et la pratique.
On a bien la solution (réduire la taille des extends, augmenter l'intervalle des commits, voir l'opportunité de ne plus passer par le mode Direct Path Write), mais il faudrait que j'arrive à comprendre ce qui se passe...

Nicolas

[Ikebukuro]

C'est pas hyper clair : tu crois que sur une de tes bases, pour chaque COMMIT, Oracle utilise l'ensemble des blocs d'un Extent et, pour les INSERTs suivants, il n'utilise pas les blocs inoccupés de l'extent précédent mais il en utilise un nouveau, donc il y aurait une perte d'espace disque, on est d'accord?

Si tu as un comportement différent entre les deux bases, c'est peut-être que ta table est définie différemment.
Que donnent, sur les deux bases, la commande :

Code :

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

1
2
SQL> set long 1000000
SQL> SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner) from dual?

[DevNico]

C'est pas hyper clair : tu crois que sur une de tes bases, pour chaque COMMIT, Oracle utilise l'ensemble des blocs d'un Extent et, pour les INSERTs suivants, il n'utilise pas les blocs inoccupés de l'extent précédent mais il en utilise un nouveau, donc il y aurait une perte d'espace disque, on est d'accord?

Yes, c'est bien ça.

Si tu as un comportement différent entre les deux bases, c'est peut-être que ta table est définie différemment.
Que donnent, sur les deux bases, la commande :

Code :

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

1
2
SQL> set long 1000000
SQL> SELECT DBMS_METADATA.get_ddl ('TABLE', table_name, owner) from dual?

J'ai exactement la même chose à une différence près :

Code :

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

1
2
3
4
5
6
7
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 104857600 NEXT 104857600 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "TBS_DATA_DWH" "

Le PCTUSED est à 40 sur l'environnement qui a l'air de prendre les extends complets.
Il est à 0 sur l'environnement qui optimise l'espace.

De ce que j'ai compris ce paramètre influe au niveau de l'utilisation des blocs, et pas des extends. Ca n'expliquerait donc pas ce comportement ?

Nicolas

[Ikebukuro]

BINGO!
C'est exactement ce à quoi je pensais.
Ce paramètre te dit quel pourcentage d'espace doit rester libre dans un bloc pour des Inserts ou Updates futurs.
S'il vaut 0 : Oracle va remplir les blocs au max, à 100 (moins la partie pour le data block header), s'il vaut 40, Oracle ne remplit ces blocs qu'à 60% de leur capacité.

Quand tu dis "De ce que j'ai compris ce paramètre influe au niveau de l'utilisation des blocs, et pas des extends. Ca n'expliquerait donc pas ce comportement ?" visiblement tu n'as pas compris qu'un extent c'est juste un groupe de blocs contigus, donc ce qui s'applique aux blocs s'applique forcément à l'extent puisque c'est la même chose.

Comme solution à ton pb : fais un alter table pour changer le pctused à 0 et on verra pour les nouveaux Inserts comment réagit ta table.
Si tu as une fenêtre de maintenance, tu peux aussi faire un shrink de la table pour récupérer l'espace disque perdu.

[13thFloor]

Hello,
ce n'est pas plutôt pctfree ?

[Ikebukuro]

Oh pinaise, tu as raison... je parlais du pctfree et j'ai confondu avec le pctused