1. #1
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    483
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 483
    Points : 368
    Points
    368

    Par défaut Fonctions LENGTHB, VSIZE, DUMP et taille sur disque dur

    Bonjour,

    J'ai lu dans le livre de Thomas Kyte "Expert oracle Database Architecture" le fait que un nombre comme 1000000000 correspondait à 2 octets en "représentation interne Oracle" que l'on peut voir avec la fonction DUMP et pas à 10 comme je le pensais avec LENGTHB. Cela m'a intrigué et j'ai voulu comprendre un peu mieux ce concept en faisant des tests mais, pauvre de moi, j'y vois encore moins clair

    J'ai créé deux tables avec les valeurs 1000000000 et 1999999999 en 100 000 exemplaires.
    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
    SQL> create table TEST(id01 NUMBER);
    Table created.
     
    SQL> insert into TEST (select 1000000000 FROM DUAL CONNECT BY LEVEL < 100001);
    100000 rows created.
     
    SQL> commit;
     
    SQL> create table TEST02(ID02 NUMBER);
    Table created.
     
    SQL> insert into TEST02 (select 1999999999 FROM DUAL CONNECT BY LEVEL < 100001);
    100000 rows created.
     
    SQL> commit;
    On voit que la fonction LENGTHB renvoit 10 octets mais VSIZE et DUMP ne renvoient que 2 octets si la valeur vaut 1000000000 et 6 pour la valeur 1999999999 --> sur le disque dur, il y a quoi, 10 octets ou bien 2 ou 6?
    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
    17
    18
    19
    20
    SQL> select MAX(ID01),MIN(ID01), MIN(LENGTH(ID01)), MAX(LENGTH(ID01)), MIN(LENGTHB(ID01)), MAX(LENGTHB(ID01)), MIN(VSIZE(ID01)), MAX(VSIZE(ID01)) from TEST group by ID01;
    MAX(ID01)  MIN(ID01) MIN(LENGTH(ID01)) MAX(LENGTH(ID01)) MIN(LENGTHB(ID01)) MAX(LENGTHB(ID01)) MIN(VSIZE(ID01)) MAX(VSIZE(ID01))
    ---------- ---------- ----------------- ----------------- ------------------ ------------------ ---------------- ----------------
    1000000000 1000000000		     10 	       10		  10		     10 	       2		2
    
    SQL> select ID01, dump(ID01) from test where rownum = 1;
          ID01 DUMP(ID01)
    ---------- ------------------------------
    1000000000 Typ=2 Len=2: 197,11
    
    
    SQL> select MAX(ID02),MIN(ID02), MIN(LENGTH(ID02)), MAX(LENGTH(ID02)), MIN(LENGTHB(ID02)), MAX(LENGTHB(ID02)), MIN(VSIZE(ID02)), MAX(VSIZE(ID02)) from TEST02 group by ID02;
     MAX(ID02)  MIN(ID02) MIN(LENGTH(ID02)) MAX(LENGTH(ID02)) MIN(LENGTHB(ID02)) MAX(LENGTHB(ID02)) MIN(VSIZE(ID02)) MAX(VSIZE(ID02))
    ---------- ---------- ----------------- ----------------- ------------------ ------------------ ---------------- ----------------
    1999999999 1999999999		     10 	       10		  10		     10 	       6		6
    
    SQL> select ID02, dump(ID02) from test02 where rownum = 1	 
          ID02 DUMP(ID02)
    ---------- --------------------------------------------------
    1999999999 Typ=2 Len=6: 197,20,100,100,100,100
    Je regarde dans dba_segments et, surprise, je tombe sur 21 Ko par enregistrement, au lieu de 2 ou 6 ou 10, quelle que soit la valeur enregistrée.
    D'un côté ça me rassure de voir que chaque enregistrement a la même taille mais pourquoi 21 au lieu de 10 (chiffre renvoyé par LENGTHB)? Il y a le DATA block header, un pctfree de 10% par défaut mais cet écart est énorme, non?
    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
    17
    18
    19
    SQL> select sum(BYTES) from dba_segments where segment_name = 'TEST';
    SUM(BYTES)
    ----------
       2097152
     
    SQL> select sum(BYTES)/100000 from dba_segments where segment_name = 'TEST'; 
    SUM(BYTES)/100000
    -----------------
    	 20.97152
     
    SQL> select sum(BYTES) from dba_segments where segment_name = 'TEST02'
    SUM(BYTES)
    ----------
       2097152
     
    SQL> select sum(BYTES)/100000 from dba_segments where segment_name = 'TEST02'
    SUM(BYTES)/100000
    -----------------
    	 20.97152
    Si vous pouviez éclairer ma lanterne, par avance merci.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  2. #2
    Rédacteur

    Profil pro
    Inscrit en
    décembre 2002
    Messages
    3 199
    Détails du profil
    Informations personnelles :
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations forums :
    Inscription : décembre 2002
    Messages : 3 199
    Points : 6 329
    Points
    6 329

    Par défaut

    Il y a une erreur de logique dans vos tests.

    1) Comme LENGTHB est conçu pour s'appliquer à une chaîne de caractères, on va avoir une conversion implicite du numérique vers sa représentation en caractères avant d'en calculer la longueur.

    On ne peut pas tester LENGTHB sur un numérique, mais on peut jouer avec VSIZE :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SQL> select vsize(to_char(1000000)) from dual;
     
    VSIZE(TO_CHAR(1000000))
    -----------------------
                          7
     
    SQL> select vsize(1000000) from dual;
     
    VSIZE(1000000)
    --------------
                 2
    2) Diviser la taille du segment par le nombre de lignes est très approximatif, car en plus de l'en-tête des blocs et le PCTFREE, il y a aussi les blocs alloués au segment mais non utilisés.
    Il sera déjà nettement plus juste de calculer les statistiques sur la table et de regarder AVG_ROW_LEN (longueur moyenne d'une ligne).
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    SQL> exec dbms_stats.gather_table_stats(user, 'TEST');
     
    SQL> select avg_row_len from dba_tables where table_name='TEST' and owner=USER;
     
    AVG_ROW_LEN
    -----------
              3
    Si on veut creuser l'espace inutilisé au sein de la table :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
     
    SQL> analyze table test compute statistics;
     
    SQL> select blocks, empty_blocks, avg_space, avg_row_len 
    from dba_tables 
    where table_name='TEST' and owner=USER;
     
        BLOCKS EMPTY_BLOCKS  AVG_SPACE AVG_ROW_LEN
    ---------- ------------ ---------- -----------
           152          103        841           6
    Dans mon cas j'ai 103 blocs vides contre 152 d'utilisés, et par ailleurs j'ai 841 octets inutilisés par bloc (AVG_SPACE), ce qui correspond essentiellement au PCTFREE ici.
    Par contre je ne sais pas expliquer pourquoi la longueur moyenne des lignes est désormais, soi-disant, de 6 octets après "analyze table compute statistics", alors qu'avec dbms_stats elle n'était que de 3.
    Consultant / formateur Oracle indépendant
    Certifié OCP 12c, 11g, 10g ; sécurité 11g

  3. #3
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    5 545
    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 545
    Points : 11 363
    Points
    11 363

    Par défaut

    Comme précisé par Pomalaix vous "beneficiez" d'une coercition automatique, la fonction lengthb n'étant pas conçue pour travailler avec le type number.

    Citation Envoyé par Ikebukuro Voir le message
    ...

    On voit que la fonction LENGTHB renvoit 10 octets mais VSIZE et DUMP ne renvoient que 2 octets si la valeur vaut 1000000000 et 6 pour la valeur 1999999999 --> sur le disque dur, il y a quoi, 10 octets ou bien 2 ou 6?
    ...
    Pour voir ce que se trouve sur les disque faite un dump du block
    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
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
     
    Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
     
    SQL> create table test(a number)
      2  /
    Table created
     
    SQL> insert into test(a) values (1000000000);
    1 row inserted
     
    SQL> insert into test(a) values(1999999999);
    1 row inserted
     
    SQL> commit;
    Commit complete
     
    SQL> 
    SQL> Select file_id, relative_fno, extent_id, block_id, blocks
      2    from dba_extents
      3   where segment_name = 'TEST'
      4     And owner = 'MNI';
       FILE_ID RELATIVE_FNO  EXTENT_ID   BLOCK_ID     BLOCKS
    ---------- ------------ ---------- ---------- ----------
             4            4          0    1029224          8
     
    SQL> alter system dump datafile 4 block min 1029231 block max 1029231;
    System altered
    Puis dans le fichier dump du block
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
     
    block_row_dump:
    tab 0, row 0, @0x1f92
    tl: 6 fb: --H-FL-- lb: 0x1  cc: 1
    col  0: [ 2]  c5 0b
    tab 0, row 1, @0x1f88
    tl: 10 fb: --H-FL-- lb: 0x1  cc: 1
    col  0: [ 6]  c5 14 64 64 64 64
    end_of_block_dump

  4. #4
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    483
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 483
    Points : 368
    Points
    368

    Par défaut

    Je vous remercie pour vos réponses, je m'étais effectivement trompé avec l'utilisation de la fonction LENGTHB.
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  5. #5
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    483
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 483
    Points : 368
    Points
    368

    Par défaut

    OK, j'ai eu le temps de refaire des tests et je suis toujours aussi perplexe

    Tout d'abord, pourquoi est-ce que je me prends la tête sur ce problème? C'est que j'avais l'impression qu'entre un nombre stocké sous forme de VARCHAR2 ou de NUMBER, l'espace disque occupé pouvait varier du tout au tout. On le voit bien avec 1000000 dans une colonne VARCHAR2 (LENGTHB renvoit 7 octets) et une colonne NUMBER (VSIZE renvoit 2 octets).

    Donc si je suis chez un client et que je constate que des nombres sont stockés dans des colonnes de type CHAR ou VARCHAR2, est-il pertinent de changer le type de ces colonnes pour gagner de l'espace disque, rendre les full table scans plus rapides... (indépendamment des programmes existants à modifier)?

    La doc d'Oracle est un peu ambigüe : "VSIZE(expr) : VSIZE returns the number of bytes in the internal representation of expr." Est-ce que cette représentation interne a un impact sur le stockage ou bien aucun? J'ai fait d'autres tests, avec des champs VARCHAR2 et NUMBER, j'ai inséré 1000000 et 1999999 avec 100000 occurences et les tables occupent le même espace disque! A quoi sert alors VSIZE? A quoi ça me sert de savoir que 1000000 est stocké sur 2 octets (en représentation interne) et pas sur 7?

    Merci pour vos réponses, j'ai hâte de clore cette discussion car elle m'embrouille les esprits
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  6. #6
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    5 545
    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 545
    Points : 11 363
    Points
    11 363

    Par défaut

    Le non respecte de type des données a toujours des impacts négatifs sur le bon fonctionnement de la base: penser au statistiques, tri, validation des données, etc. Une base des données est supposé stockés des "vérités" ce qui permet via les operateurs relationnels d'en déduire des autres "vérités" et non pas être un dépotoir des données. Cela suppose entre autre que les données stockées sont valides ce qui reste toujours difficile d'assurer quand l'unique type de données connu est varchar2! Cela étant dit je pense que vous vous acharnez sur le mauvais chat quand il y en a des autres à fouetter! Je ne pense pas que vous allez trouver beaucoup des clients qui stockent le montant de leur investissement en format varchar! Et si vous trouvez un qui a stocké ses montants en format char alors un conseil: laissez le tranquille et ne perdez pas le temps avec lui; il existe certainement un dieux des "innocents" et c'est à lui de s'en occuper.

    Puis le deuxième facteur que vous devez prendre en compte c'est que l'espace chez Oracle n'est pas alloué pour chaque valeur individuel d'une colonne dans un enregistrement. Donc vous créez une table et vous insérez un seul enregistrement: bingo vous occupez N fois l'espace nécessaire sur le disc parce que Oracle manipule des blocs d'une taille prédéfini. C'est à l'intérieur de ce bloc des données que votre valeur occupé 2 ou 7 octets. Et on parle pas non plus d'espace mis à coté pour les modifications des enregistrements etc. Avez-vous rencontrez beaucoup des clients qui essaye encore de garder 2 octets de plus en stockant que les deux dernières chiffre d'un millésime ? L'espace ne coût pas grand chose aujourd'hui! Puis vous arrivez à épargner 50 octets et vous consommez 1000 via un index inutile, etc. Et encore une fois regarder plutôt l'inflation de l'espace qui se produit quand vous modifiez la valeur 1000000000 en déduisant 1: vous passez de 2 à 6 octets.

  7. #7
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    483
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 483
    Points : 368
    Points
    368

    Par défaut

    Merci pour tes conseils mnitu, je me doutais bien que je cherchais des poux dans la tête d'Oracle mais bon, quand je suis obsédé par une idée, il me faut m'en débarrasser

    OK, je vais laisser tomber MAIS je ne sais toujours pas quelle est l'utilité de VSIZE et de ce que Oracle appelle "représentation interne".
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

  8. #8
    Expert éminent sénior Avatar de mnitu
    Homme Profil pro
    Ingénieur développement logiciels
    Inscrit en
    octobre 2007
    Messages
    5 545
    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 545
    Points : 11 363
    Points
    11 363

    Par défaut

    Comme la documentation le dit vsize retourne le nombre d'octets en représentation interne.
    Peut être que l'exemple suivante pourrait nous faire avancer
    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
     
    Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 
     
    SQL> 
    SQL> select vsize(cast(1 as number(10))) vnum,
      2         vsize(cast(1 as binary_double)) vdbl,
      3         vsize(cast(1 as char(10))) vchr,
      4         vsize(cast(1 as varchar2(10))) vvchr,
      5         vsize(cast(1 as nvarchar2(10))) nnum
      6    from dual
      7  /
          VNUM       VDBL       VCHR      VVCHR       NNUM
    ---------- ---------- ---------- ---------- ----------
             2          8         10          1          2
     
    SQL>
    Comme vous pouvez constater la "même valeur" en fonction du type de données a besoin de plus ou moins d'octets pour la "représenter". Finalement cela vous le connaissez déjà : pour n'importe quel autre langage de programmation le nombre d'octets utilisé pour stocker une valeur diffère avec le type de données. C'est la même chose avec Oracle: vsize vous dit
    - si vous voulez stocker 1 comme type number je vais utiliser 2 octets
    - si vous voulez le stocker comme varchar2 1 octets
    - si c'est un double alors ça va être 10
    etc.
    Et cela signifie que 1 comme number n'est pas stocké comme 1 mais comme une autre combinaison des deux octets.

  9. #9
    Rédacteur
    Avatar de SQLpro
    Homme Profil pro
    Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Inscrit en
    mai 2002
    Messages
    17 119
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France, Var (Provence Alpes Côte d'Azur)

    Informations professionnelles :
    Activité : Expert SGBDR & SQL, spécialiste Microsoft SQL Server
    Secteur : Conseil

    Informations forums :
    Inscription : mai 2002
    Messages : 17 119
    Points : 39 735
    Points
    39 735
    Billets dans le blog
    1

    Par défaut

    Citation Envoyé par mnitu Voir le message
    Le non respect des types des données a toujours des impacts négatifs sur le bon fonctionnement de la base : penser aux statistiques, tri, validation des données, etc. Une base des données est supposée stocker des "vérités" ce qui permet via les opérateurs relationnels d'en déduire d'autres "vérités" et non pas d'être un dépotoir des données.
    Je dois dire que j'aime beaucoup et je crois que je vais re-citer cela avec permission et mention de l'auteur !!!

    (avec mes petites corrections)

    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...
    * * * * * Enseignant CNAM PACA - ISEN Toulon - CESI Aix en Provence * * * * *

  10. #10
    Membre averti
    Homme Profil pro
    DBA Oracle
    Inscrit en
    avril 2013
    Messages
    483
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : DBA Oracle

    Informations forums :
    Inscription : avril 2013
    Messages : 483
    Points : 368
    Points
    368

    Par défaut

    J'avoue que moi aussi j'aime beaucoup l'expression "dépotoir de données".
    DBA Oracle
    Rédacteur du blog : dbaoraclesql.canalblog.com

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

Discussions similaires

  1. La taille sur disque d'un fichier
    Par Zarrows dans le forum Langage
    Réponses: 11
    Dernier message: 14/06/2011, 20h40
  2. Taille sur disque de vista
    Par Ndugu dans le forum Installation
    Réponses: 6
    Dernier message: 15/04/2009, 23h26
  3. Données perdues sur disque dur esclave ?
    Par maadadi dans le forum Composants
    Réponses: 11
    Dernier message: 18/10/2005, 20h51
  4. [Espace perdue sur disque dur]
    Par Abdul dans le forum Windows XP
    Réponses: 19
    Dernier message: 26/06/2005, 14h50
  5. Taille du disque dur
    Par shaun_the_sheep dans le forum Administration système
    Réponses: 3
    Dernier message: 08/11/2004, 11h54

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