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