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 :
À ce stade on se retrouve avec deux tables et des histogrammes balancés sur les colonne ID1 et ID2 de ces deux tables.
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');
Voila ma requête :
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.
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 | ---------------------------------------------------------------------------
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 :
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.
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;
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
Partager