Le fait qu’un plan SQL calculé par Oracle soit sous-optimal ne vient généralement pas d’un algorithme mathématique erroné. Le plus souvent, c’est le manque d’informations à propos de la distribution des données (les statistiques) qui ne permettent pas à l’optimiseur de trouver le meilleur plan. Au fil des versions depuis que le CBO existe, le modèle de statistiques évolue. De plus en plus d’informations à propos de la distribution ou à propos de la manière dont les données sont utilisées sont conservées ou calculées. 11g ne déroge pas à la règle puisque les statistiques peuvent désormais représenter la distribution relative de données entre plusieurs colonnes : des statistiques multi colonnes. Au fait, à quoi ça sert et comment ça marche ?
Dans la suite de ce Post, nous mettrons en évidence l’impact du manque de statistiques multi-colonnes sur le calcul de certains plans ainsi que l’utilisation de ces statistiques. Mais, assez discuté, commençons par créer un exemple.
1. Schéma d’exemple
Pour les besoins de la démonstration, il nous faut une table ainsi que des données distribuées de manière telle que ce que vous voulons démontrer soit si exacerbée que le problème devienne évident. Voici le script que vous aller utiliser à cet effet :
create table gark
(id1 number not null,
id2 number not null,
id3 number not null,
id4 number not null,
text varchar2(50));
begin
for i in 1..200 loop
for j in 1..50 loop
insert into gark(id1, id2, id3, id4, text)
values (j, j, j, j, 'Text');
end loop;
end loop;
commit;
end;
/
create index gark_idx on gark(id1, id2, id3, id4);
begin
dbms_stats.delete_table_stats(
user,
'GARK',
no_invalidate=> false);
dbms_stats.gather_table_stats(
user,
'GARK',
cascade=>true,
estimate_percent=>100,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
no_invalidate=> false);
end;
/
col column_name format a20
col histo_width format 999
select column_name,
count(endpoint_number) histo_width
from user_histograms
where table_name='GARK'
group by column_name
having count(endpoint_number) >2;
no rows selected
2. Etonnante 11g…
Nous allons exécutez une requête très simple et visualiser son plan
set autotrace traceonly;
select count(text)
from gark
where id1=1
and id2=1
and id3=1
and id4=1;
set autotrace off;
Execution Plan
----------------------------------------------------------
Plan hash value: 2364300905
--------------------------------------------------
| Id | Operation | Name | Rows | Cost |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| GARK | 200 | 11 |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID1"=1 AND "ID2"=1 AND "ID3"=1 AND "ID4"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
En comparant ce plan à celui de 10g, on pourrait en conclure que Oracle 11g est plus intelligent que son prédécesseur (Le nombre de lignes ramenées est effectivement 200 pour l’étape 2) et qu’il estime beaucoup mieux les cardinalités des étapes d’un plan. En fait, ce n’est pas le cas (Oracle 11g ne dispose pas de plus d’information qu’Oracle 10g, il n’y donc aucune raison qu’il s’en sorte mieux), c’est simplement un effet visuel, comme vous pourrez vous en rendre compte dans la section suivante ! Regardez le résultat en changeant les valeurs de notre requête :
set autotrace traceonly;
select count(text)
from gark
where id1=1
and id2=2
and id3=3
and id4=4;
set autotrace off;
Execution Plan
----------------------------------------------------------
Plan hash value: 2364300905
--------------------------------------------------
| Id | Operation | Name | Rows | Cost |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| GARK | 200 | 11 |
--------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID1"=1 AND "ID2"=2 AND "ID3"=3 AND "ID4"=4)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
38 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Il n’y a donc aucune intelligence dans le calcul de 11g, simplement l’algorithme qui calcule le nombre de lignes au niveau de la table a change. En fait, il n’y a pas d’impact sur le plan choisi comme vous le découvrirez juste après.
3. Rappel des mêmes tests en 10g
Voici les tests effectués en 10g :
select count(text)
from gark
where id1=1
and id2=1
and id3=1
and id4=1;
select * from table(dbms_xplan.display_cursor);
Execution Plan
----------------------------------------------------------
Plan hash value: 2364300905
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 8
| 1 | SORT AGGREGATE | | 1 | 17 |
|* 2 | TABLE ACCESS FULL| GARK | 1 | 17 | 8
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID1"=1 AND "ID2"=1 AND "ID3"=1 AND "ID4"=1)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
34 consistent gets
0 physical reads
0 redo size
414 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Etrange non ? L’étape 2 ne semble ramener qu’une ligne et pourtant l’optimiseur décide de faire un « full table scan » plutot que de passer par l’index… La bonne nouvelle c’est que le plan ne change pas par rapport à la 11g. Mais pourquoi ? Nous pouvons le découvrir grace à l’event 10053 ou encore plus simplement en forçant l’utilisation de l’index en 10g :
select /*+ index(gark) */ count(text)
from gark
where id1=1
and id2=1
and id3=1
and id4=1;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID f1w7qh09n7dy2, child n umber 0
-------------------------------------
select /*+ index(gark) */ count(text) from gark where id1=1 and id2=1
and id3=1 and id4=1
Plan hash value: 2577124290
-----------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
-----------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 32 |
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| GARK | 1 | 32 |
|* 3 | INDEX RANGE SCAN | GARK_IDX | 200 | 1 |
-----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID1"=1 AND "ID2"=1 AND "ID3"=1 AND "ID4"=1)
Voila, « behind the scene », l’optimiseur d’Oracle 10g faisait déjà cette estimation, simplement la valeur n’était pas remontée à l’accès à la table. Merci, les plans ne vont pas changer à cause de ça. J’ai fait ce test avec une 10.2.0.1 sous Linux, ca vaudrait sans doute la peine de tester avec une 10.2.0.3 et une 9i… Juste pour voir !
Le plan de la deuxième requête est strictement identique à la première requête comme on pouvait s’en douter :
set autotrace traceonly;
select count(text)
from gark
where id1=1
and id2=2
and id3=3
and id4=4;
set autotrace off;
Execution Plan
----------------------------------------------------------
Plan hash value: 2364300905
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 8
| 1 | SORT AGGREGATE | | 1 | 17 |
|* 2 | TABLE ACCESS FULL| GARK | 1 | 17 | 8
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID1"=1 AND "ID2"=2 AND "ID3"=3 AND "ID4"=4)
Statistics
----------------------------------------------------------
91 recursive calls
0 db block gets
54 consistent gets
0 physical reads
0 redo size
413 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed
4. Prise de statistiques
Prenons les statistiques une fois encore… Des histogrammes apparaissent :
begin
dbms_stats.gather_table_stats(
user,
'GARK',
cascade=>true,
estimate_percent=>100,
method_opt=>'FOR ALL COLUMNS SIZE AUTO',
no_invalidate=> false);
end;
/
col column_name format a20
col histo_width format 999
select column_name,
count(endpoint_number) histo_width
from user_histograms
where table_name='GARK'
group by column_name
having count(endpoint_number) >2
order by 1;
COLUMN_NAME HISTO_WIDTH
-------------------- -----------
ID1 10
ID2 10
ID3 10
ID4 10
5. Les statistiques changent; les plans aussi !
Voila les mêmes requêtes exécutées après la prise de statistiques
set autotrace traceonly;
select count(text)
from gark
where id1=1
and id2=1
and id3=1
and id4=1;
set autotrace off;
Execution Plan
----------------------------------------------------------
Plan hash value: 2577124290
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2
| 1 | SORT AGGREGATE | | 1 | 17 |
| 2 | TABLE ACCESS BY INDEX ROWID| GARK | 1 | 17 | 2
|* 3 | INDEX RANGE SCAN | GARK_IDX | 1 | | 1
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID1"=1 AND "ID2"=1 AND "ID3"=1 AND "ID4"=1)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
33 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
On remarque que les plans changent mais que (merci l’évolution de l’algorithme Nested Loop ne sont pas moins bon). En 10g, on ne peut pas en dire autant. Voici le second plan :
set autotrace traceonly;
select count(text)
from gark
where id1=1
and id2=2
and id3=3
and id4=4;
set autotrace off;
Execution Plan
----------------------------------------------------------
Plan hash value: 2577124290
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2
| 1 | SORT AGGREGATE | | 1 | 17 |
| 2 | TABLE ACCESS BY INDEX ROWID| GARK | 1 | 17 | 2
|* 3 | INDEX RANGE SCAN | GARK_IDX | 1 | | 1
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID1"=1 AND "ID2"=2 AND "ID3"=3 AND "ID4"=4)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
5. Calculer des statistiques multi-colonnes
En 11g, Oracle est capable de calculer des statistiques multi-colonnes. J’imagine qu’il y a un moyen de « monitorer » l’utilisation de plusieurs colonnes dans les clauses where. Si ce n’est pas le cas, ça sera pour la prochaine version. Pour l’instant faisons le manuellement :
var column_group char(30)
begin
:column_group :=
dbms_stats.create_extended_stats(
user,
'GARK',
'(ID1,ID2,ID3,ID4)');
end;
/
print column_group
COLUMN_GROUP
--------------------------------
SYS_STUAOVVGZAKL7CM0F7FRL1E7O1
Une fois le groupe défini, vous pouvez collecter les statistiques avec la commande ci-dessous :
exec dbms_stats.gather_table_stats( -
null, -
'GARK', -
method_opt => 'for all columns size auto');
select column_name,
count(endpoint_number) histo_width
from user_histograms
where tabl e_name='GARK'
group by column_name
having count(endpoint_number) >2
order by 1;
COLUMN_NAME HISTO_WIDTH
-------------------- -----------
ID1 50
ID2 50
ID3 50
ID4 50
Rien ne change mais exécutez la requête une fois encore et reprenez les statistiques :
select count(text)
from gark
where id1=1
and id2=2
and id3=3
and id4=4;
exec dbms_stats.gather_table_stats( -
null, -
'GARK', -
method_opt => 'for all columns size auto');
col column_name format a30
select column_name,
count(endpoint_number) histo_width
from user_histograms
where table_name='GARK'
group by column_name
having count(endpoint_number) >2
order by 1;
COLUMN_NAME HISTO_WIDTH
------------------------------ -----------
ID1 50
ID2 50
ID3 50
ID4 50
SYS_STUAOVVGZAKL7CM0F7FRL1E7O1 50
6. Nouvelles statistiques
L’optimiseur à de nouvelles statistiques… Quelle sera sa réaction ?
set autotrace traceonly;
select count(text)
from gark
where id1=1
and id2=1
and id3=1
and id4=1;
set autotrace off;
Execution Plan
----------------------------------------------------------
Plan hash value: 2364300905
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 11
| 1 | SORT AGGREGATE | | 1 | 17 |
|* 2 | TABLE ACCESS FULL| GARK | 200 | 3400 | 11
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID1"=1 AND "ID2"=1 AND "ID3"=1 AND "ID4"=1)
Statistics
----------------------------------------------------------
8 recursive calls
0 db block gets
40 consistent gets
0 physical reads
0 redo size
421 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Bien ! Son estimation est absolument exacte. Et pour l’autre plan ?
set autotrace traceonly;
select count(text)
from gark
where id1=1
and id2=2
and id3=3
and id4=4;
set autotrace off;
Execution Plan
----------------------------------------------------------
Plan hash value: 2364300905
-----------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 11
| 1 | SORT AGGREGATE | | 1 | 17 |
|* 2 | TABLE ACCESS FULL| GARK | 100 | 1700 | 11
-----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("ID1"=1 AND "ID2"=2 AND "ID3"=3 AND "ID4"=4)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
420 bytes sent via SQL*Net to client
415 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
Etrange, non ? Le plan n’est pas du tout compatible avec le nombre de consistent gets… Ne serait-ce pas encore un cas pour lequel l’explain plan me cache le vrai plan ? Recommençons en regardant le vrai plan et pas l’explain :
select count(text)
from gark
where id1=1
and id2=2
and id3=3
and id4=4;
select * from
table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------
SQL_ID 5dchpg2301brf, child number 0
-------------------------------------
select count(text) from gark where id1=1 and id2=2 and id3=3 and
id4=4
Plan hash value: 2577124290
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (
| 1 | SORT AGGREGATE | | 1 | 17 |
| 2 | TABLE ACCESS BY INDEX ROWID| GARK | 1 | 17 | 2
|* 3 | INDEX RANGE SCAN | GARK_IDX | 1 | | 1
-------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("ID1"=1 AND "ID2"=2 AND "ID3"=3 AND "ID4"=4)
C’était ça. Le plan est maintenant optimal quelques soient les valeurs utilisées… Reste à savoir si ce dernier phénomène est un bug de la beta ou le comportement attendu
9. Supprimer l’exemple :
exec dbms_stats.drop_extended_stats(-
user,-
'GARK',-
'(ID1,ID2,ID3,ID4)');
drop index gark_idx;
drop table gark;
10. Conclusion
Il n’y a pas, dans ce qui a été illustré, de raison qu’Oracle 11g change les plans de la 10g à cause d’un changement d’estimation des cardinalités. En revanche nous disposons maintenant d’un outil puissant pour modéliser des corrélations entre plusieurs colonnes d’une même table, les statistiques multi colonnes. Jusqu’à présent, seul le dynamic sampling permettait de s’en sortir honorablement dans ce genre de cas… Dans mon prochain post, je continuerai d’explorer ces nouvelles possibilités avec un cas intéressant à propos des statistiques sur des colonnes virtuelles. J’illustrerai encore une fois l’intérêt du dynamic sampling dans les versions 9i et 10g. Alors, continuez d’explorer mon blog…
-GarK!
