Discussion :

[tristan_37]

LOL
Je t'assure que des fois on voit de tout et de n'importe quoi avec les clients
Bref, en effet il s'agit bien de la taille physique et non du nombre d'enregsitrements, c'est pourquoi l'explication du extend me parait convenir. Cependant, de quoi parles-tu quand tu dis pctincrease ? Ça peut éventuellement m'intéresser aussi.

[dgi77]

PCTINCREASE est un autre paramètre éventuellement spécifié à la création de la table, qui défini un pourcentage d'augmentation d'un next extent à l'autre. Il est plutôt déconseillé de l'utiliser, parce qu'après on abouti à des tailles d'extents biscornues et on n'a pas 2 extents identiques. Par défaut, elle reste à 0, mais je n'ai encore jamais vu personne la positionner à 4000...
Tu verras toutes ces infos dans la user_tables.

Par contre, la deuxième hypothèse ne me paraît pas du tout à prendre à la légère : moi aussi j'ai eu des galères parce que le client t'écarte de la bonne piste en omettant de te parler de certaines manipulations qu'il a pu faire car il est loin d'imaginer que les 2 peuvent être reliés (donc, en toute bonne foi de sa part, mais capital à savoir pour toi).
Je pense qu'il faut que tu cherches à savoir s'ils n'ont pas fait des chargements de masse sur ces tables pour tester je ne sais quoi, puis ont deleté, mais les extents eux sont alloués désormais...

[tristan_37]

Ah merci, j'en profite également pour demander les valeurs de PCTINCREASE. Je sais que ce seront celles des nouvelles tables mais on va supposer que ces valeurs n'ont pas changé.
Les clients sont généralement de bonne foi, pas du style à vouloir nous faire des crasses. De plus les modifications (insert-delete) dans ces tables sont faits automatiquement tous les soirs, logiquement ils n'ont pas à y toucher. Il est donc sain de supposer que seules quelques simples opérations (insert-delete) ont mené à un tel résultat (augmentation soudain et significatif de la taille physique des données des tables). Bien entendu il y a eu des exceptions dans le passé, et il y en aura d'autres

[McM]

Le truc à prendre en compte, c'est que la taille des index n'a pas bougé, donc un énorme insert / delete est probablement à écarter

[dgi77]

Le truc à prendre en compte, c'est que la taille des index n'a pas bougé, donc un énorme insert / delete est probablement à écarter

C'est vrai, mais il ne faut pas non plus totalement l'écarter : tu peux supposer que l'initial extent des index était énorme... (des fois, il y en a qui font de ces trucs tellement tordus que si tu cherches la logique, tu ne trouveras jamais le problème )

[tristan_37]

Exactement. Seuls un nombre limité de modifications a provoqué un tel changement de taille. D'où, comme décrit précédemment, probablement un problème de PCTINCREASE ou bien extend.
J'attends (toujours) les infos du client et je vois ça.

[tristan_37]

Salut dgi77
J'ai pas saisi ce que tu as dit: même si l'espace initial alloué aux index était énorme, le fait de faire insert-delete massivement se voit sur la taille des index, non ? C'est seulement quand celle-ci atteint la taille max allouée qu'une réallocation se fait.
Tu m'as perdu

[dgi77]

Je veux dire que ça serait le cas où l'espace initial alloué à l'index serait énorme et loin d'être atteint.

Par exemple, si tu as une table avec 40 colonnes, que tu fais un seul index dessus avec une de ces colonnes, et que tu as mis la même valeur d'initial extent et de next extent pour la table que pour l'index, et bien tu vas en moyenne (et en supposant pour l'exemple que les colonnes soient de taille comparable) alloué un extent pour l'index tous les 40 extents pour la table.
Donc, au départ, tu vas insérer quelques lignes et avoir l'impression que table et index occupent la même place, sauf que le premier extent de la table est presque plein alors que le premier extent de l'index est quasiment vide. Tu vas continuer d'ajouter des données, voir les extents de la table s'ajouter les uns après les autres, mais pas au niveau de l'index qui continue de remplir tranquillement son premier extent, et donc tu auras l'impression que l'index ne grossit pas.

La démonstration est poussée à l'extrême pour illustrer ce type de situation, mais arrive souvent : en fait, quelle est la part des personnes qui créent des tables sous Oracle sans connaître ces paramètres de stockages ? A mon avis, largement majoritaire. Donc ils créent leurs tables sans spécifier ces paramètres, souvent même graphiquement, et là Oracle récupère par défaut comme taille d'extent celle définie au niveau du tablespace. J'imagine que ces mêmes utilisateurs ne sont pas non plus sensibilisés au fait qu'il est préférable de créer les tables et les index sur des tablespaces différents, donc ils ne vont pas non plus spécifier le tablespace, et là Oracle balance tout ça sur le tablespace par défaut du schéma, et donc ce sera le même pour les tables et les index.
Tu as donc la démonstration que quand on crée une table se soucier des paramètres de stockages, la table et son index seront sur le même tablespace et auront par défaut les mêmes tailles d'extent car héritées du même tablespace.

[tristan_37]

Ok, je vois ce que tu veux dire. Par contre un truc qui m'interpelle:
je pense qu'il faut faire la différence entre taille allouée et taille utilisée. Y a-t-il moyen de faire une requête qui renvoie les 2 ? L'extend doit j'imagine renvoyer la taille allouée, par contre la taille vraiment utilisée je ne sais pas (c'est probablement ce que m'a envoyé le client).

[dgi77]

Je ne sais pas faire, mais justement ça m'intéresse aussi si c'est possible.
L'extent donne effectivement l'espace alloué, mais pour l'espace réellement occupé à l'intérieur d'un extent ???

[tristan_37]

Bonjour

Voici la réponse que m'a faite le client:
en retour de la requête:

Code :

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

1
2
3
SELECT initial_extent, next_extent, PCT_INCREASE, blocks
FROM user_tables
WHERE table_name = '...'

il obtient:

Code :

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

1
2
3
4
TABLE_NAME INITIAL_EXTENT NEXT_EXTENT PCT_INCREASE BLOCKS
----------- -------------- ----------- ------------ ----------
BKCPTDOS 22544384 131072 0 4859
BKEVE 75235328 4194304 0 18522

Soit donc, reprenez-moi si je me trompe, une taille de 22G pour la première table. Au vu des infos logiquement (38 - 1816 pour sa taille) on serait toujours dans l'extent initial.
Et pour le pct_increase, il est positionné comme il faut à 0.

En ce qui concerne la taille (38 - 1816) celle-ci est ramenée par la requête suivante:

Code :

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

1
2
3
select segment_name,bytes from dba_segments where owner = 'BANK' and segment_name in ('BKCPTDOS','BKEVE');
select segment_name,bytes from dba_segments where owner = 'BANK' and segment_name in
(select index_name from dba_indexes where owner = 'BANK' and table_name in ('BKCPTDOS','BKEVE'));

Peut-être une conversion bite-byte qui m'échappe quelque part...

J'aurais donc encore 2 3 questions: mon client m'a fourni le nombre de blocks, mais comment l'utiliser ?
dgi77, tu sembles dire que les extent ne sont pas les mêmes pour les tables que pour les index, ça pourrait en effet être l'explication du problème. Si je comprends bien, les extent ramenés sont ceux des tables, maintenant comment obtenir les extent des index ?
Si vous avez d'autres pistes je suis preneur
Merci d'avance

[dgi77]

Bonjour,

Les colonnes initial_extent, next_extent et pct_increase de la user_tables ne te permettent pas de connaître la taille de la table, elles te donnent juste les valeurs auxquelles ces paramètres sont positionnés. Mais tu ne sais pas le nombre d'extents alloués pour la table. Pour ça, il faut passer par la user_extents, qui contient pour chaque table ou index autant de lignes que d'extents alloués.
initial_extent et next_extent sont exprimés en octets, donc l'initial extent de ta table est plutôt taillé à 22 Mo et non à 22 Go. Si tu divises tes valeurs 2 fois par 1024, tu verras que tu tombes sur des comptes à peu près juste.

Par contre, je viens de m'apercevoir que la colonne blocks de la user_tables permet de connaître l'espace occupé réellement (chose qu'on se demandait hier), définition Oracle : "The number of used blocks in the table".
Le bloc étant la plus petite unité d'allocation d'espace au sein de l'extent, ce qui permet d'avoir une idée précise de l'espace véritablement occupé.
Certains diront qu'il faut tenir compte des taux de remplissage des blocs avec les PCT_FREE et les PCT_USED, mais pour notre raisonnement global, on peut l'ignorer. D'autant qu'à partir de la version 10g, tout est géré en autonome par Oracle.
Les blocs sont en général de 8 Ko (8192 octets). Pour le vérifier, il faut faire :

Code :

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

1
2
 
select value from v$parameter where name = 'db_block_size'

Tu fais la multiplication entre le nombre de blocks de user_tables et la valeur de db_block_size pour connaître la taille occupée.

Pour l'espace alloué, tu passes donc par la user_extents en faisant :

Code :

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

1
2
 
select sum(bytes) from user_extents where segment_name = '<nom_table>'

bytes est exprimé en octets. Divise par 1024 pour passer en Ko, puis encore par 1024 pour passer en Mo, etc.
Comme je l'ai dit plus haut, la user_extents te donne une vision de chaque extent d'un segment (c'est-à-dire une table ou un index).
Si la PCT_INCREASE est à 0, tu dois pouvoir retrouver dans la colonne bytes la valeur soit de l'initial_extent soit du next_extent de la user_tables.
Sur n'importe quelle ligne de user_extents, si tu divises, bytes par blocks, tu retombes sur la taille de bloc de la base (db_block_size dans v$parameter).

Pour les index : de même qu'il existe une user_tables, il existe une user_indexes.
Au niveau des extents, c'est la user_extents qui regroupe table et index sous la notion générique de segment, un segment désignant soit une table soit un index.

[McM]

Les extents (initial et next ) sont en Octets
ex 65536 = 64 Ko

Code :

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

1
2
3
SELECT VALUE
FROM v$parameter
WHERE NAME = 'db_block_size'

Te donne le nb d'octets par blocks

Il faut que tu te bases sur le nb de blocks pour avoir la taille de ta table (parce que si ça se trouve, tu as un extent initial et 15 extents suivants)

donc 4859 * 8192 Ko (valeur standard d'une taille de block) = 38 Mo pour la première table (pas 22 Go)

22544384 octets = 21.5 Mo


Edit : Grillé sur le fil

[tristan_37]

Merci messieurs pour ces explications, j'y vois beaucoup plus clair
Pour récupérer la taille mon client a fait une requête sur dba_segments.
Toi dgi77 tu dis qu'on peut voir la taille de chaque segment par user_extents. Ce qui est beaucoup plus intéressant car on peut voir la place occupée par chaque extent. J'imagine que la taille (bytes) qui se trouve dans dba_segments est le résultat de ta requête (SELECT sum(bytes) FROM user_extents WHERE segment_name = '<nom_table>') additionné avec la même chose que pour les index de la même table. D'ailleurs, y a-t-il autre chose (à part données et index) qui peuvent prendre de la place et seraient donc à prendre en compte pour obtenir la taille de la table ?
Donc en demandant le résultat de la requête:

Code :

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

SELECT bytes FROM user_extents WHERE segment_name = '<nom_table>'

ainsi que pour tous les index associés (et éventuellement d'autres éléments ?), j'aurais une idée précise de ce qui prend de la place dans ces tables.
Je voudrais pas dire une connerie donc j'attends votre confirmation

[dgi77]

J'imagine que la taille (bytes) qui se trouve dans dba_segments est le résultat de ta requête (SELECT sum(bytes) FROM user_extents WHERE segment_name = '<nom_table>') additionné avec la même chose que pour les index de la même table.

Oui, il semble que ce soit le cas, mais sans les index : bytes de dba_segments correspond à SUM(bytes) de dba_extents, objet par objet, c'est-à-dire table par table ou index par index.
Il n'existe pas de colonne bytes (dans ces vues du moins) donnant la taille cumulée d'une tables et de ses index.
Et tout cela donne un aperçu de l'espace alloué.
Pour l'espace occupé, c'est avec blocks dans user_tables.

Non, il n'y a rien d'autre à prendre en compte pour l'estimation de la volumétrie... sauf les vues matérialisées si vous en avez.

[tristan_37]

Okey dokey.
J'ai donc demandé le résultat de la requête sur user_extents, ça donnera plus d'info.
En espérant bien sûr que le client a conservé un backup quelque part.
Si ce n'est pas le cas et étant donné que le problème arrive une fois tous les 6 mois, je me permettrais de cliquer sur 'résolu'.
Merci en tout cas à chacun d'entre vous pour vos réponses, j'aurais appris pas mal de notions dans l'affaire.

Bonne journée