Faut-il collecter des histogrammes sur les colonnes quand vous utilisez cursor_sharing=SIMILAR ? Dans ce cas, que se passe-t-il si vous gardez la méthode de collecte par défaut des statistiques d’Oracle 11g ? Sur quoi s’appuie la méthode automatique de collecte des histogrammes sur les colonnes ? Voici quelques unes de ces questions qui sont abordées ou illustrées dans ce post… Quoique ce n’est que le sommet de l’iceberg !
Remarque :
Ces exemples ont été testés avec oracle 10.2.0.4 et 11.1.0.6 sur Linux x86 !
Partons d’un exemple simple…
Une table, deux colonnes, cursor_sharing=SIMILAR. Voila le test case :
create table demo(
col1 number,
col2 varchar2(1000)
);
begin
for i in 1..50000 loop
insert into demo(col1, col2)
values (i, rpad('A',999,'A'));
insert into demo(col1, col2)
values (1000, rpad('A',999,'A'));
end loop;
commit;
end;
/
create index demo_idx on demo (col1);
alter session set cursor_sharing=similar;
Examinons le cas #1, sans histogramme:
Avant d’exécuter les requêtes, nous allons collecter les statistiques, dans un premier cas en s’assurant qu’il n’y a pas d’histogramme (i.e. FOR ALL COLUMNS SIZE 1)
exec dbms_stats.gather_table_stats(user, -
'DEMO',method_opt=>'FOR ALL COLUMNS SIZE 1', -
cascade=> true)
col COLUMN_NAME format a6
select column_name, count(*)
from user_histograms
where table_name='DEMO'
group by column_name
order by column_name;
COLUMN COUNT(*)
------ ----------
COL1 2(*)
COL2 2
(*) Notez que s’il n’y a que 2 lignes pour une colonne dans ALL_HISTOGRAMS, cela signifie qu’il n’y a pas d’histogrammes (sauf s’il n’y a que 2 valeurs dans la colonne)
Une fois les statistiques collectées et vérifiées, vous pouvez lancer les requêtes suivantes dans SQL*Plus; n’utilisez pas un autre outils comme SQL*Developer parce que l’appel à dbms_xplan.display_cursor() référence l’ordre SQL précédent lancé dans la session et les outils « évolués » exécutent d’autres ordres SQL sans que vous ne soyez notifié(e). SQL*Plus, à moins que vous n’utilisiez des modes funky comme serveroutput on, n’a pas ce genre de comportement :
set pages 1000
set lines 180
select count(col2)
from demo
where col1=1001;
COUNT(COL2)
-----------
1
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d27tczp42j637, child number 0
-------------------------------------
select count(col2) from demo where col1=:"SYS_B_0"
Plan hash value: 1661014706
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEMO | 2 | 2 (0)|
|* 3 | INDEX RANGE SCAN | DEMO_IDX | 2 | 1 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=:SYS_B_0)
select count(col2)
from demo
where col1=1000;
COUNT(COL2)
-----------
50001
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d27tczp42j637, child number 0
-------------------------------------
select count(col2) from demo where col1=:"SYS_B_0"
Plan hash value: 1661014706
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEMO | 2 | 2 (0)|
|* 3 | INDEX RANGE SCAN | DEMO_IDX | 2 | 1 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=:SYS_B_0)
Vous remarquerez, non seulement que le SQL_ID sont les mêmes, puisque les valeurs littérales 1001 et 1000 sont substituées par une variable de type BIND générée par le système et qu’en outre (Et c’est le plus important pour cet exemple), le « CHILD CURSOR » est le même, i.e. 0. Vous pouvez valider ce dernier point en vous connectant DBA et en interrogeant la fixed view V$SQL pour le-dit SQL_ID comme ci-dessous :
select SQL_ID, CHILD_NUMBER
from v$sql
where sql_id='d27tczp42j637';
SQL_ID CHILD_NUMBER
------------- ------------
d27tczp42j637 0
Dans ce cas, les 2 ordres SQL sont considérés comme complètement identiques. Puisque les colonnes n’ont pas d’histogrammes, les plans pour les valeurs 1000 et 1001 seront forcément les mêmes. L’optimiseur en tient compte et utilise donc un seul curseur. C’est ce que la documentation décrit par cet section consacrée au cursor_sharing = SIMILAR, comme ci-dessous :
Causes statements that may differ in some literals, but are otherwise identical, to share a cursor, unless the literals affect either the meaning of the statement or the degree to which the plan is optimized.
Avant de passer au second exemple, videz le contenu de la shared pool :
alter system flush shared_pool;
select SQL_ID, CHILD_NUMBER
from v$sql
where sql_id='d27tczp42j637';
no rows selected
Examinons le cas #2, avec un histogramme:
Dans ce deuxième exemple et avant d’exécuter les requêtes, nous allons collecter les statistiques, en forçant la prise d’histogrammes sur <code>COL1 :
exec dbms_stats.gather_table_stats(user, -
'DEMO', method_opt=>'FOR COLUMNS COL1 SIZE SKEWONLY', -
cascade=> true)
col COLUMN_NAME format a6
select column_name, count(*)
from user_histograms
where table_name='DEMO'
group by column_name
order by column_name;
COLUMN COUNT(*)
------ ----------
COL1 131
COL2 2
Utilisez encore SQL*Plus et ré-exécutez les 2 ordres SQL précédents :
set pages 1000
set lines 180
select count(col2)
from demo
where col1=1001;
COUNT(COL2)
-----------
1
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d27tczp42j637, child number 0
-------------------------------------
select count(col2) from demo where col1=:"SYS_B_0"
Plan hash value: 1661014706
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEMO | 2 | 2 (0)|
|* 3 | INDEX RANGE SCAN | DEMO_IDX | 2 | 1 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=:SYS_B_0)
select count(col2)
from demo
where col1=1000;
COUNT(COL2)
-----------
50001
select * from table(dbms_xplan.display_cursor());
-------------------------------------
SQL_ID d27tczp42j637, child number 1
-------------------------------------
select count(col2) from demo where col1=:"SYS_B_0"
Plan hash value: 2180342005
--------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
--------------------------------------------------------
| 0 | SELECT STATEMENT | | | 4127 (100)|
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | TABLE ACCESS FULL| DEMO | 49213 | 4127 (1)|
--------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("COL1"=:SYS_B_0)
Dans ce cas, vous constaterez que le CHILD CURSOR diffère; la présence de l’histogramme force l’ordre SQL à un nouveau HARD PARSE. Cela semble évident puisque dans ce cas, le plan change aussi. Essayez avec d’autre valeurs et vous constaterez que le SQL donne lieu à un nouveau CHILD CURSOR pour chaque nouvelle valeur :
select count(col2)
from demo
where col1=1002;
COUNT(COL2)
-----------
1
select * from table(dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID d27tczp42j637, child number 2
-------------------------------------
select count(col2) from demo where col1=:"SYS_B_0"
Plan hash value: 1661014706
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost (%CPU)|
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | 2 (100)|
| 1 | SORT AGGREGATE | | 1 | |
| 2 | TABLE ACCESS BY INDEX ROWID| DEMO | 2 | 2 (0)|
|* 3 | INDEX RANGE SCAN | DEMO_IDX | 2 | 1 (0)|
----------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("COL1"=:SYS_B_0)
Dit autrement, la présence d’histogrammes empêche l’ordre SQL d’être ré-utilisé lorsque cursor_sharing=SIMILAR. Vous pouvez mettre en évidence cet utilisation de la SHARED POOL en interrogeant la fixed view V$SQL :
select SQL_ID, CHILD_NUMBER
from v$sql
where sql_id='d27tczp42j637';
SQL_ID CHILD_NUMBER
------------- ------------
d27tczp42j637 0
d27tczp42j637 1
d27tczp42j637 2
Autre remarque intéressante, si vous interrogez V$SQL_SHARED_CURSOR, vous ne saurez pas pourquoi les curseurs ne sont pas ré-utilisés ! (Exécutez la requête pour vous en persuader) :
select *
from v$sql_shared_cursor
where sql_id='d27tczp42j637';
Un pas de plus: 2 mots sur « FOR ALL COLUMNS SIZE AUTO »
Ce post précédent illustre le fonctionnement de cette option de collecte des statistiques. Il faut simplement modérer mon enthousiasme de l’époque à la lumière de l’impact des histogrammes sur la réutilisation des curseurs avec cursor_sharing=SIMILAR. Vous pouvez également ajouter que la table SYS.COL_USAGE$ est en fait ce qui permet à DBMS_STATS de déclencher la prise d’histogrammes.
Conclusion
cursor_sharing=EXACT est la meilleure façon de voir le monde ! C’est d’autant plus vrai que beaucoup d’encre cou
le chaque jour au sujet des histogrammes ou du bind peeking dans la « blogosphère et sur les différents canaux de discussions Oracle ». EXACT permet-il d’adresser tous les cas ? oui mais évidemment, cela suppose que les développeurs utilisent des BIND variables quand les curseurs doivent être réutilisés, qu’ils utilisent des littéraux quand il ne faut pas. Cela suppose que le monde soit parfait mais il ne l’est pas. C’est donc pour ça qu’il y a d’autres mode.
Peut-être que ce post vous aidera à appréhender un peu mieux l’impact de SIMILAR. Un rapide coup d’oeil à Metalink sur les bugs spécifiques à ce mode vous instruira également quant aux « autres risques » associés. Quant à savoir s’il faut ou non prendre des histogrammes…
Et si vous voulez encore creuser plus ?
D’abord, regardez ce post de Laurent Schneider pointant ASKTOM qui vous occupera sans doute un bon moment mais qui est passionnant et beaucoup plus creusé que ces quelques lignes !
Ensuite, enchaînez les tests sans et avec histogramme sans vider la shared pool, le résultat avec 10.2.0.4 est plus qu’intéressant (Je n’ai pas testé avec 11.1.0.6 mais je ne serais pas surpris s’il est identique). Je devine ce qu’il se passe mais je n’arrive pas à mettre en évidence les données associés. Enfin, examinez aussi la pertinence ou non de V$SQL_BIND_DATA .
Avant d’en finir…
drop table demo purge;
