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

SQL Oracle Discussion :

Calcul de cardinalité


Sujet :

SQL Oracle

Vue hybride

Message précédent Message précédent   Message suivant Message suivant
  1. #1
    Nouveau membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2013
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Janvier 2013
    Messages : 6
    Par défaut Calcul de cardinalité
    Bonjour

    Je suis nouveau sur ce forum.
    Je suis DBA indépendant sur Oracle, spécialisé performance.

    J'ai un cas de test à vous soumettre sachant que je tente de le résoudre depuis assez longtemps sans succès.
    La requête est volontairement très simple.

    Je vous propose donc le cas de test suivant :
    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
    create table seba (
        id1 number,
        id2 number
    );
     
    begin
        for i in 1..300 loop
            insert into seba values ( i, i );
        end loop;
    end;
    /
     
    begin
        for i in 301..310 loop
            for a in 1..50 loop
                insert into seba values ( i, i );
            end loop;
        end loop;
    end;
    /
     
    create table sebb as select * from seba;
     
    execute dbms_stats.gather_table_stats('SYSTEM','SEBA', method_opt=>'for all columns size 254');
    execute dbms_stats.gather_table_stats('SYSTEM','SEBB', method_opt=>'for all columns size 254');
    À ce stade on se retrouve avec deux tables et des histogrammes balancés sur les colonne ID1 et ID2 de ces deux tables.

    Voila ma requête :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    select *
    from seba a, sebb b
    where a.id1=b.id1;
     
    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      | 27773 |   433K|     5  (20)| 00:00:01 |
    |*  1 |  HASH JOIN         |      | 27773 |   433K|     5  (20)| 00:00:01 |
    |   2 |   TABLE ACCESS FULL| SEBA |   800 |  6400 |     2   (0)| 00:00:01 |
    |   3 |   TABLE ACCESS FULL| SEBB |   800 |  6400 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    Ce que je ne sais pas faire c'est déterminer la part de cardinalité dans le calcul de cardinalité de la jointure pour les valeurs impopulaires.

    Je commence mon calcul.

    Les statistiques sont identiques pour SEBA et SEBB.

    Vous devriez constater que :
    • Cardinality de SEBA = 800
    • ndv id1 310
    • Nombre de valeur populaire de id1 10
    • Somme des selectivité des valeur pop de id1 167/254


    Cette requête nous donnera la sélectivité de la jointure pour les valeurs populaires :
    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    with ssq_a as (
            select  endpoint_number-nvl(lag(endpoint_number,1) over  (partition by owner, table_name, column_name order by endpoint_number ),0) bucket_count, 
                    endpoint_value 
            from dba_tab_histograms where table_name='SEBA' and column_name='ID1' order by endpoint_number asc ),
         ssq_b as (
            select  endpoint_number-nvl(lag(endpoint_number,1) over  (partition by owner, table_name, column_name order by endpoint_number ),0) bucket_count, 
                    endpoint_value 
            from dba_tab_histograms where table_name='SEBB' and column_name='ID1' order by endpoint_number asc )
    select  endpoint_value, a.bucket_count bucket_count_a, b.bucket_count bucket_count_b,  a.bucket_count/254*b.bucket_count/254 sel, 
            sum(a.bucket_count/254*b.bucket_count/254) over ( order by endpoint_value ) cumulative_sel
    from    ssq_a a inner join  ssq_b b using(endpoint_value)
    where   a.bucket_count>1 and
            b.bucket_count>1;
    Remarque : cette requête ne tient pas compte des valeurs null qui auraient pu avoir sur id1, etc.. tout est simplifié pour arriver à ma question.

    Soit sur la dernière ligne: 0.043260586521173

    Au final:

    card = 800 x 800 x 0.043260586521173 = 27686

    L'optimiseur donne 27773

    En fait je sais qu'il manque une sélectivité, c'est celle des valeurs impopulaire et c'est là que je sèche.
    Dans cet exemple la différence est négligeable mais c'est parce que les valeurs populaires ont un gros poids ce qui n'est pas le cas tout le temps.

    Par reverse:

    1/(27773/800/800-0.043260586521173) = 7422

    Il me manque donc 1/7422 à ajouter à mes 0.043260586521173 que je vais appeler DELTA.

    C'est 1/7422 que je cherche à découvrir !!!!

    En temps normal sans histogramme :

    card = card(a)xcard(b)xsel où sel=1/(max( ndv(seba,id1), ndv(sebb,id1) )

    Si je raisonne comme cela :
    • Nombre de val impopulaire 300
    • Somme des selctivité val impop 87/254
    • Sel d'un val impo = 87/254/300 = 1/876


    En premier abord j'aurais pu penser que DELTA = 1/300
    Sauf que je dois quand même déduire quelque part la sélectivité des valeurs populaires.

    Donc on pourrait imaginer réduire le volume de card(a)*card(b) aux seules valeurs impopulaires :

    card = 800 x 800 x ( 1 - 0.043260586521173 ) = 612313

    Et en se basant sur la formule sans histogramme :

    card = 612313 x 1/300 = 2041

    Je ramène ces 2041 par rapport au volume global soit sel(val impop) = 2041 / ( 800 x 800 ) = 0,0031890625 = 1/313

    J'ai beau tenter plusieurs scénarios je ne trouve pas.

    Est-ce que un mec qui gère bien perf aurait ma solution ?

    Merci d'avance

    Seb

  2. #2
    Expert confirmé 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
    Par défaut
    Bonjour,

    Et bien venu. Utilisez les balises code et /code (bouton #) pour formater votre code.

    Je me suis rappelé d'un article que je n'ai jamais suivi dans ses détails JoinOverHistogrammes.

  3. #3
    Membre chevronné
    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
    Par défaut
    Citation Envoyé par sebcb1 Voir le message
    Vous devriez constater que :
    • Cardinality de SEBA = 800
    • ndv id1 310
    • Nombre de valeur populaire de id1 10
    • Somme des selectivité des valeur pop de id1 167/254

    [...]
    Au final:

    card = 800 x 800 x 0.043260586521173 = 27686

    L'optimiseur donne 27773

    En fait je sais qu'il manque une sélectivité, c'est celle des valeurs impopulaire et c'est là que je sèche.
    27773-27686=87
    Et: 254-167=87
    Hasard ou magie ?

    Plus sérieusement, ça ne va pas aider à trouver les nombres qui manquent mais personnellement je suis pas à l'aise avec les divisions par 254,300, 800 etc. Une autre fois, je vous conseille d'opter pour des ratios en multiple de 100. Par exemple, 100 valeur pas populaire, et 400 populaires, et un histogramme de 100 (ou 10)colonnes. Des éventuels rapports sauteront plus facilement aux yeux.

    Ps: SYSTEM c'est pas forcément l'endroit rêvé pour jouer avec la BdD

  4. #4
    Nouveau membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2013
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Janvier 2013
    Messages : 6
    Par défaut
    Hazard mais bien vu
    J'ai plusieurs cas de test et sur les autres ca ne le fait pas

    Pour system c'est une base de test montée que pour ca donc osef

    Je vais regarder la note de la première réponse

    Merci en tout cas de votre participation

  5. #5
    Nouveau membre du Club
    Homme Profil pro
    Administrateur de base de données
    Inscrit en
    Janvier 2013
    Messages
    6
    Détails du profil
    Informations personnelles :
    Sexe : Homme
    Localisation : France

    Informations professionnelles :
    Activité : Administrateur de base de données

    Informations forums :
    Inscription : Janvier 2013
    Messages : 6
    Par défaut
    Donc j'ai regardé la note: http://www.adellera.it/investigation...sExplained.pdf

    Not-populars subtable (contributor 3):

    num_rows_np(T1) * num_rows_np(T2) * min (density_np (T1), density_np (T2))

    Rappel:

    Sum sel val impop = 87/254

    J'en déduis :

    num_rows_np(T1) = 800 x 87/254 =274 (pour les deux tables)
    density_np (T1) = 1 / num_distinct_np (id1) = 1/300

    card = 274 x 274 x 1/300 = 250

    Sauf que 27686 + 250 = 27936 ce qui est supérieur au chiffre du CBO (27773)

    Par contre mon calcul avec que les valeurs populaires n'est pas exactement fait de la même façon que celui de la doc mais c'est peut-être la même chose:

    Je vais donc le faire de la même manière:

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    select sum(count) from (
    with ssq_a as (
            select  endpoint_number-nvl(lag(endpoint_number,1) over  (partition by owner, table_name, column_name order by endpoint_number ),0) bucket_count, 
                    endpoint_value 
            from dba_tab_histograms where table_name='SEBA' and column_name='ID1' order by endpoint_number asc ),
         ssq_b as (
            select  endpoint_number-nvl(lag(endpoint_number,1) over  (partition by owner, table_name, column_name order by endpoint_number ),0) bucket_count, 
                    endpoint_value 
            from dba_tab_histograms where table_name='SEBB' and column_name='ID1' order by endpoint_number asc )
    select  endpoint_value, a.bucket_count bucket_count_a, b.bucket_count bucket_count_b,  a.bucket_count/254*800 *b.bucket_count/254*800 count
    from    ssq_a a INNER JOIN  ssq_b b USING(endpoint_value)
    where   a.bucket_count>1 and
            b.bucket_count>1 );
    27686 même résultat !!

    Du coup j'ai une autre idée et si je découvrais combien de lignes estime le CBO uniquement avec les valeurs populaires ????

    Cette information n'étant pas dans une trace 10053

    Je vais tricher avec ça :

    Code : Sélectionner tout - Visualiser dans une fenêtre à part
    1
    2
    3
    select *
    from seba a, sebb b
    where a.id1=b.id1 and a.id1 between 301 and 310;
    Je sais qu'entre 301 et 310 j'ai que des valeurs populaires:

    ---------------------------------------------------------------------------
    | Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
    ---------------------------------------------------------------------------
    |   0 | SELECT STATEMENT   |      | 27522 |   430K|     5  (20)| 00:00:01 |
    |*  1 |  HASH JOIN         |      | 27522 |   430K|     5  (20)| 00:00:01 |
    |*  2 |   TABLE ACCESS FULL| SEBA |   524 |  4192 |     2   (0)| 00:00:01 |
    |*  3 |   TABLE ACCESS FULL| SEBB |   524 |  4192 |     2   (0)| 00:00:01 |
    ---------------------------------------------------------------------------
    27 522 !!!!!

    Si j'ajoute les 250 précédent : 27 522 + 250 = 27 772 (CBO 27773)

    A l'arrondi près c'est bon

    Donc c'est mon calcul de départ (card val pop) qui est faux
    Cette note est pas mal elle me permet de relancer mes recherches

    Je regarde ça

Discussions similaires

  1. [TP7] Calculer sin, cos, tan, sqrt via le FPU
    Par zdra dans le forum Assembleur
    Réponses: 8
    Dernier message: 25/11/2002, 04h09
  2. Calcul des numéros de semaine d'un calendrier
    Par Invité dans le forum Algorithmes et structures de données
    Réponses: 4
    Dernier message: 06/11/2002, 21h29
  3. Réponses: 8
    Dernier message: 18/09/2002, 03h20
  4. Récupérer 10 nb différents avec un calcul aléatoire
    Par BXDSPORT dans le forum Langage
    Réponses: 3
    Dernier message: 04/08/2002, 02h35
  5. Algo de calcul de FFT
    Par djlex03 dans le forum Traitement du signal
    Réponses: 15
    Dernier message: 02/08/2002, 17h45

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