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