IdentifiantMot de passe
Loading...
Mot de passe oublié ?Je m'inscris ! (gratuit)
Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

Oracle Discussion :

Stockage d'une table [11g]


Sujet :

Oracle

  1. #1
    Membre du Club
    Profil pro
    Inscrit en
    Novembre 2012
    Messages
    149
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2012
    Messages : 149
    Points : 52
    Points
    52
    Par défaut Stockage d'une table
    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 !

  2. #2
    Membre du Club
    Profil pro
    Inscrit en
    Novembre 2012
    Messages
    149
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2012
    Messages : 149
    Points : 52
    Points
    52
    Par défaut
    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

  3. #3
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  4. #4
    Membre éclairé
    Profil pro
    Inscrit en
    Février 2010
    Messages
    412
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2010
    Messages : 412
    Points : 807
    Points
    807
    Par défaut
    Citation Envoyé par SQLpro Voir le message
    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.

  5. #5
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    Bonjour,

    Citation Envoyé par tropiko Voir le message
    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.
    Franck Pachot - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  6. #6
    Membre actif Avatar de Ahmed AANGOUR
    Homme Profil pro
    DBA Oracle
    Inscrit en
    Janvier 2010
    Messages
    139
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 45
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : Janvier 2010
    Messages : 139
    Points : 271
    Points
    271
    Par défaut
    @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.)

  7. #7
    Membre du Club
    Profil pro
    Inscrit en
    Novembre 2012
    Messages
    149
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Novembre 2012
    Messages : 149
    Points : 52
    Points
    52
    Par défaut
    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

  8. #8
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    Bonjour,
    Citation Envoyé par tropiko Voir le message
    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.
    Franck Pachot - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  9. #9
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    Octobre 2007
    Messages
    5 611
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Marne (Champagne Ardenne)

    Informations professionnelles :
    Activité : Ingénieur développement logiciels
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Octobre 2007
    Messages : 5 611
    Points : 11 252
    Points
    11 252
    Par défaut
    Citation Envoyé par pachot Voir le message
    ...Pour les tables, c'est différent. ...
    A lire indexes.

  10. #10
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    Citation Envoyé par mnitu Voir le message
    A lire indexes.
    Exact. Je corrige, merci.
    Franck Pachot - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  11. #11
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    Citation Envoyé par Rams7s Voir le message
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  12. #12
    Expert éminent
    Avatar de pachot
    Homme Profil pro
    Developer Advocate YugabyteDB
    Inscrit en
    Novembre 2007
    Messages
    1 821
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Âge : 53
    Localisation : Suisse

    Informations professionnelles :
    Activité : Developer Advocate YugabyteDB
    Secteur : High Tech - Éditeur de logiciels

    Informations forums :
    Inscription : Novembre 2007
    Messages : 1 821
    Points : 6 443
    Points
    6 443
    Billets dans le blog
    1
    Par défaut
    @SQLpro

    Non,

    Citation Envoyé par SQLpro Voir le message
    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.

    Citation Envoyé par SQLpro Voir le message
    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.
    Franck Pachot - Developer Advocate Yugabyte 🚀 Base de Données distribuée, open source, compatible PostgreSQL
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

  13. #13
    Rédacteur

    Avatar de SQLpro
    Homme Profil pro
    Expert bases de données / SQL / MS SQL Server / Postgresql
    Inscrit en
    Mai 2002
    Messages
    21 736
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert bases de données / SQL / MS SQL Server / Postgresql
    Secteur : Conseil

    Informations forums :
    Inscription : Mai 2002
    Messages : 21 736
    Points : 52 447
    Points
    52 447
    Billets dans le blog
    5
    Par défaut
    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 +
    Frédéric Brouard - SQLpro - ARCHITECTE DE DONNÉES - expert SGBDR et langage SQL
    Le site sur les SGBD relationnels et le langage SQL: http://sqlpro.developpez.com/
    Blog SQL, SQL Server, SGBDR : http://blog.developpez.com/sqlpro
    Expert Microsoft SQL Server - M.V.P. (Most valuable Professional) MS Corp.
    Entreprise SQL SPOT : modélisation, conseils, audit, optimisation, formation...
    * * * * * Expertise SQL Server : http://mssqlserver.fr/ * * * * *

  14. #14
    Membre éclairé
    Profil pro
    Inscrit en
    Février 2010
    Messages
    412
    Détails du profil
    Informations personnelles :
    Localisation : France

    Informations forums :
    Inscription : Février 2010
    Messages : 412
    Points : 807
    Points
    807
    Par défaut
    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 : 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
     
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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 : Sélectionner tout - Visualiser dans une fenêtre à part
    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

+ Répondre à la discussion
Cette discussion est résolue.

Discussions similaires

  1. [MySQL] Créer une table de stockage de paramètre sans plusieurs entrées
    Par patrick58 dans le forum PHP & Base de données
    Réponses: 1
    Dernier message: 22/02/2008, 19h03
  2. Stockage dans une table
    Par Spanish_ dans le forum Requêtes et SQL.
    Réponses: 76
    Dernier message: 04/01/2008, 16h26
  3. Stockage d'OBJET OLE dans une Table Paradox ou Firebird ?
    Par QAYS dans le forum Bases de données
    Réponses: 2
    Dernier message: 06/04/2007, 13h59
  4. [Avancés pour débutante]Stockage d'une table (tableau associatif)
    Par delma dans le forum Collection et Stream
    Réponses: 12
    Dernier message: 17/11/2006, 13h16

Partager

Partager
  • Envoyer la discussion sur Viadeo
  • Envoyer la discussion sur Twitter
  • Envoyer la discussion sur Google
  • Envoyer la discussion sur Facebook
  • Envoyer la discussion sur Digg
  • Envoyer la discussion sur Delicious
  • Envoyer la discussion sur MySpace
  • Envoyer la discussion sur Yahoo