Discussion :

[sebcb1]

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

[mnitu]

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.

[Rams7s]

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

[sebcb1]

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

[sebcb1]

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