[11g] Stockage d'une table [Résolu]

[tropiko]

Comment peut-on vérifier et valider le stockage d'une table ?

1/ Structure :

PCTFREE ...quelle valeur ?
PCTUSED ...quelle valeur ?
Autre ...?


2/ Stockage des données

Défragmentation ?
L'organisation des blocks de la table ? trous ?

Autrement dit qu'elles sont les Checks à passer pour vérifier de la "bonne santée" d'une table et de ses données.

merci pour votre aide précieuse !

[tropiko]

Salut ,

Cette semaine un collègue a plombé les perfs en positionnant PCTFREE = 0 dans une table principale !!!
Morale : Faire attention à ces paramètres de table / index , voir éviter de les modifier ...

merci

[SQLpro]

Le principe de PCTFREE (ou FILLFACTOR dans d'autres SGBDR) est de réserver de l'espace afin d'éviter la fragmentation.
Donc le bon réglage consiste à trouver la valeur de ce paramètre de telle sorte que au moment de la phase de maintenance des index, vous soyez à la limite d'un début de fragmentation.

Par exemple si votre table "grossit" de 1% par heure et que vous avez mis en place une maintenance d'index journalière, alors un PCTFREE de 25% parait suffisant (personnellement je prendrais un peu de marge donc 30%)

En général le PCTFREE évolue en fonction de la vieillesse de la BD. En effet, au début de sa vie la BD nécessite un fort PCTFREE, mais après quelques années d'exploitation, peut être baissé de manière très sensible.
Explication : le taux de mise à jour est relativement constant, alors que le volume augmente.
Exemple :

• table des factures, volume journalier des factures : 5 pages.
• Avec 50 pages j'ai besoin d'un PCTFREE d'au moins 10%. Lorsque la table atteint 1000 pages je n'ai pas besoin d'un PCTFREE de plus de 0,5 %...

Mais tout dépend de comment sont organisées les données.
Par exemple ceci est valable si la dispersion des nouvelles données est uniforme. Si par exemple il s'agit d'un index sur une clef auto-incrémentée, alors le PCTFREE peut être de 0, de même pour un index contenant un horodatage.

Lisez le chapitre consacré aux index de la dernière édition de mon bouquin sur SQL.

A +

[Rams7s]

Envoyé par SQLpro

Exemple :

• table des factures, volume journalier des factures : 5 pages.
• Avec 50 pages j'ai besoin d'un PCTFREE d'au moins 10%. Lorsque la table atteint 1000 pages je n'ai pas besoin d'un PCTFREE de plus de 0,5 %...

Comme c'est dans le forum Oracle, je pense que vous vous trompez.

http://docs.oracle.com/cd/E11882_01/...l.htm#autoId13

Moi ce que je comprends, c'est que le PCTFREE s'applique à chaque bloc de donnée, et non pas à la table.

Percentage of Free Space in Data Blocks

The PCTFREE storage parameter is essential to how the database manages free space. This SQL parameter sets the minimum percentage of a data block reserved as free space for updates to existing rows. Thus, PCTFREE is important for preventing row migration and avoiding wasted space.

For example, assume that you create a table that will require only occasional updates, most of which will not increase the size of the existing data. You specify the PCTFREE parameter within a CREATE TABLE statement as follows:
CREATE TABLE test_table (n NUMBER) PCTFREE 20;

Figure 12-9 shows how a PCTFREE setting of 20 affects space management. The database adds rows to the block over time, causing the row data to grow upwards toward the block header, which is itself expanding downward toward the row data. The PCTFREE setting ensures that at least 20% of the data block is free. For example, the database prevents an INSERT statement from filling the block so that the row data and header occupy a combined 90% of the total block space, leaving only 10% free.

Pourcentage d'espace libre dans les blocs de données

Le paramètre de stockage PCTFREE est essentiel dans la gestion de l'espace libre par le SGBDR. Ce paramètre SQL indique le pourcentage minimum d'espace libre dans un block de donnée afin de pouvoir mettre à jour les lignes existantes. Ainsi, le PCTFREE est important pour prévenir les migrations de lignes et éviter de gâcher de l'espace.

Par exemple, admettons que l'on créé une table qui ne subira que quelques UPDATES de manière occasionnels, et dont la majorité n'augmentera pas la taille des données déjà sauvegardées. Alors, on peut mettre le paramètre PCTFREE dans le CREATE TABLE comme suit:
CREATE TABLE tmp (N NUMBER) PCTFREE 20;

L'illustration 12-9 montre comment un PCTFREE de 20 impacte la gestion de l'espace. Le RDBMS ajoute des lignes au fur et à mesure, ce qui augmente le volume de donnée le poussant vers l'en-tête header du bloc, qui lui même s'agrandit vers les données. Le réglage PCTFREE assure qu'au moins 20% du bloc de donnée est libre. Par exemple, le RDBMS va empêcher un INSERT de remplir le bloc de telles manières que suite à cet INSERT les données plus l'en-tête occuperait 90% de l'espace total, ne laissant que 10% de libre.

[pachot]

Bonjour,

Envoyé par tropiko

Autrement dit qu'elles sont les Checks à passer pour vérifier de la "bonne santée" d'une table et de ses données.

Aucun de manière statique.
Car des paramétrages bons dans un cas d'utilisation sont mauvais dans un autre.

Par exemple, PCTFREE=0 sur les tables est très bon sur des tables qui n'ont
- pas d'updates qui font grossir les lignes
- et qui n'ont pas de modifications concurrentes
Et par contre très mauvais lorsque une ligne va grossir et qu'on voudra y accéder via index: elle va migrer et on aura besoin de lire un bloc de plus lorsqu'on va y accéder via index.

Tout dépends comment les données sont accédées, sont modifiées, etc.

Donc le seule check possible (à mon avis) c'est de vérifier que les temps de réponse correspondent aux spécifications. Et lorsque ce n'est pas le cas, tuning (plan d'exécution, statistiques Statspack/AWR, etc) pour voir d'où vient le problème.

SQLpro, désolé mais il y a une grosse confusion dans votre réponse.
Sur les tables PCTFREE détermine le taux de remplissage des blocs par des inserts. Pour les index, il détermine le taux de remplissage uniquement lors de la création (ou du rebuild) de l'index, pour éviter trop de splits de blocs lors des premières modifications.

Cordialement,
Franck.

[Ahmed AANGOUR]

@Frederic
Il est dangereux de vouloir appliquer les concepts SQL server à Oracle.
Ils ont des choses en commun mais sur beaucoup de choses ils sont très différents (authetification,optimiseur,locks,transactions,journaux,stockage,sécurité etc.)

[tropiko]

Merci Franck ,
J'ai bien compris l'utilité de PCTFREE.
Est-ce la même logique pour PCTUSED et INITRANS ?
Faut-il vérifier ces 2 paramètres aussi ?
merci

[pachot]

Bonjour,

Envoyé par tropiko

Est-ce la même logique pour PCTUSED et INITRANS ?

PCTUSED correspond à la même logique pour une table: quand on dépasse PCTFREE, on ne fait plus d'insert pour garder de la place en cas d'update. Mais si les lignes diminuent (update ou delete) on se remet à accepter les insert si on descend au dessous de PCTUSED.
Sauf qu'en Autmatic Segment Space Management, PCTUSED est ignoré, la gestion se fait automatiquement en fonction du remplissage des blocs.

INITTRANS est un peu lié. on a parlé de réserver de la place pour les update qui font grossir les lignes. Il faut aussi avoir de la place pour les informations de transaction (ITL). INITRANS permet de réserver de la place pour un certain nombre de transaction par bloc. Au delà, c'est en fonction de l'espace disponible (réservé par PCTFREE) et s'il n'y a plus de place pour une nouvelle entrée de transaction, elle attendra (enq: TX - allocate ITL entry)

Pour les tablesindex, c'est différent. Il est toujours possible de trouver de la place en faisant un 'block split'. On utilise juste PCTFREE pour éviter qu'il n'y ait trop de block splits d'un coup (donc mauvaise perf) quand on vient de créer/rebuilder un index.

En ASSM, PCTUSED est ignoré, INITRANS et probablement ok à sa valeur par défaut. On utilise PCTFREE pour garder de la place aussi bien pour agrandir les lignes que pour les ITL de transactions concurrentes.

Cordialement,
Franck.

[mnitu]

Envoyé par pachot

...Pour les tables, c'est différent. ...

A lire indexes.

[pachot]

Envoyé par mnitu

A lire indexes.

Exact. Je corrige, merci.

[SQLpro]

Envoyé par Rams7s

Comme c'est dans le forum Oracle, je pense que vous vous trompez.

http://docs.oracle.com/cd/E11882_01/...l.htm#autoId13

Moi ce que je comprends, c'est que le PCTFREE s'applique à chaque bloc de donnée, et non pas à la table.

Je n'ai jamais dit que cela s'appliquait à la table. Relisez moi. Comme dans SQL Server, le FILLFACTOR ou son inverse (PCTFREE) s'applique à la page ce que Oracle appelle bloc...

A +

[pachot]

@SQLpro

Non,

Envoyé par SQLpro

En général le PCTFREE évolue en fonction de la vieillesse de la BD

Le PCTFREE gouverne le remplissage par les INSERT. Pour prévoir la taille que prendront les enregistrement au bout de toutes leurs modifications. Ça ne change pas avec le vieillissement.

Envoyé par SQLpro

table des factures, volume journalier des factures : 5 pages. Avec 50 pages j'ai besoin d'un PCTFREE d'au moins 10%.

Non. les nouvelles pages/blocs sont allouées au fur et à mesure des besoins. PCTFREE concerne l'espace dans les anciens blocs. C'est pour ça que Rams7s avait l'impression que vous parliez de la table, vu qu'il s'agissait de nouveaux blocs.

Cordialement,
Franck.

[SQLpro]

Vous m'avez mal compris. Lorsque je dit que le PCTFree évolue en fonction de la vieillesse de la BD, je veut dire que compte tenu que le volume de transaction est souvent constant dans le temps, mais que le volume des données augmente il est sage de prévoir un PCTFREE imoprtant à la naissance et de plus en plus petit au fur et à mesure du vieillissement de la base.

A +

[Rams7s]

Malheureusement, soit vous n'avez pas compris, soit c'est très mal dit.

Pour suivre votre exemple de nombre de transactions constantes:
Si tous les jours j'insère des blocks de données, et que je sais que je vais faire des updates sur ces champs, je vais mettre le PCTFREE qui va bien, de manière à ce qu'oracle alloue le nombre final de blocks de données requis dès la création. Si la BdD s'amuse a allouer des blocks pendant les updates, ben ça va augmenter les temps des réponses des updates pour rien. Et ceci, ça ne dépend en rien de l'ancienneté de la base, mais seulement des opérations qu'on va faire sur les blocks nouvellement créés.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
CREATE TABLE tmp (n number(32), n2 number(32), n3 number(32), n4 number(32), n5 number(32)) pctfree 20;
INSERT INTO tmp (n) SELECT to_number(lpad(level,25,1)) FROM dual connect BY level<1e4;
CREATE TABLE results AS SELECT '00' AS step, sum(blocks) AS number_blocks FROM user_segments WHERE segment_name='TMP';
UPDATE tmp SET (N2,n3)=(SELECT to_number(lpad(1,31,1)),to_number(lpad(2,31,1)) FROM dual);
INSERT INTO results SELECT '01',sum(blocks) FROM user_segments WHERE segment_name='TMP';
 
UPDATE tmp SET (n4,n5)=(SELECT to_number(lpad(3,31,1)),to_number(lpad(4,31,1)) FROM dual);
INSERT INTO results SELECT '02',sum(blocks) FROM user_segments WHERE segment_name='TMP';
 
SELECT * FROM results;
STEP	NUMBER_BLOCKS
-----    ------------
00	40
01	256
02	256

Maintenant, comme on est malin, on sait que lors des insertions on ne remplit que N, puis que en deux temps on va progressivement remplir toutes les colonnes.
Par rapport à l'espace total requis par tuple, au début on utilise seulement 20%. Donc on va mettre un PCTFREE de 80% pour avoir le bon nombre de blocks dès le début.

Code :

1
2
3
4
5
6
 
STEP	NUMBER_BLOCKS
-----    ------------
00	256
01	256
02	256

De la même façon, le premier update augmente de 2/3 l'espace consommé par chaque ligne dans le block. (On passe de un NUMBER(32) à trois NUMBER(32) Donc, si on met 70%, le premier update ne nécessitera pas la création de blocks supplémentaires. Bien évidemment, pour ajouter le reste des données il faudra ajouter des blocks, les premiers étant déjà plein.

Code :

1
2
3
4
5
6
7
8
9
 
CREATE TABLE tmp (n number(32), n2 number(32), n3 number(32), n4 number(32), n5 number(32)) pctfree 70;
 
SELECT * FROM results;
STEP	NUMBER_BLOCKS
-----    ----------------
00	96
01	96
02	384

Alors que si on met une valeur inférieur, par exemple 60%, le premier update va créer de nouveaux blocks:

Code :

1
2
3
4
5
6
7
8
CREATE TABLE tmp (n number(32), n2 number(32), n3 number(32), n4 number(32), n5 number(32)) pctfree 60;
 
SELECT * FROM results;
STEP	NUMBER_BLOCKS
-----    ----------------
00	72
01	112
02	384