En 11g, Oracle peut calculer des statistiques sur des colonnes virtuelles, mais à quoi ça sert ?
1. Schéma d’exemple
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);
begin
for i in 1..100000 loop
insert into gark(id1, id2, id3)
values (i, i, i);
end loop;
commit;
end;
/
create unique index gark_idx on gark(id1, id3);
begin
dbms_stats.gather_table_stats(
user,
'GARK',
cascade=>true,
estimate_percent=>100,
method_opt=>'FOR ALL COLUMNS SIZE 254',
no_invalidate=> false);
end;
/
2. Un plan simple et sans problème…
Nous allons exécutez une requête très simple et visualiser son plan
set autotrace traceonly
select count(*)
from gark a
where a.id1-a.id2=0
and a.id1-a.id3=0
and a.id2-a.id3=0;
Voici le résultat… sans problème :
PLAN_TABLE_OUTPUT
---------------------------------------------------
SQL_ID dv30ghpd8hbaw, child number 0
-------------------------------------
select count(*) from gark where id1-id2=0
Plan hash value: 2364300905
-------------------------------------------
| Id | Operation | Name | Rows |
-------------------------------------------
| 0 | SELECT STATEMENT | | |
| 1 | SORT AGGREGATE | | 1 |
|* 2 | TABLE ACCESS FULL| GARK | 1 |
-------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."ID1"-"A"."ID2"=0
AND "A"."ID1"-"A"."ID3"=0
AND "A"."ID2"-"A"."ID3"=0)
Quoique si vous regardez de plus près, l’algorithme de l’optimiseur part du principe que le résultat de ID2-ID1 est bien distribué et donc que la probabilité du résultat est dispersée. Le probleme dans ce cas, c’est que c’est toujours la même valeur.
3. Quand le plan devient sous optimal
Cette petite erreur peut commencer à couter cher si cette requête fait partie d’une jointure comme dans l’exemple ci-dessous :
set autotrace traceonly
select /* no_virtstat */ count(*)
from gark a, gark b
where a.id1-a.id2=0
and a.id1-a.id3=0
and a.id2-a.id3=0
and b.id1=a.id1;
set autotrace off
Execution Plan
----------------------------------------------------------
Plan hash value: 3137705415
----------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 108 |
| 1 | SORT AGGREGATE | | 1 | 20 | |
| 2 | NESTED LOOPS | | 1 | 20 | 108 |
|* 3 | TABLE ACCESS FULL| GARK | 1 | 15 | 107 |
|* 4 | INDEX RANGE SCAN | GARK_IDX | 1 | 5 | 1 |
----------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("A"."ID1"-"A"."ID2"=0 AND "A"."ID1"-"A"."ID3"=0
AND "A"."ID2"-"A"."ID3"=0)
4 - access("B"."ID1"="A"."ID1")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2987 consistent gets
0 physical reads
0 redo size
418 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
4. Utiliser le dynamic sampling
Un moyen radical pour se sortir de ce problème dans les versions antérieures est d’utiliser le dynamic sampling. Comme vous pouvez le voir ci-dessous l’optimiseur conçoit beaucoup mieux le nombre de lignes ramenée par la requête (100 mille aux étapes 3, 4, et 5) et le nombre de consistent gets est réduit par presque 5 par rapport au plan précédent :
set autotrace traceonly
select /*+ dynamic_sampling(10) */ count(*)
from gark a, gark b
where a.id1-a.id2=0
and a.id1-a.id3=0
and a.id2-a.id3=0
and b.id1=a.id1;
set autotrace off
Execution Plan
----------------------------------------------------------
Plan hash value: 4209132370
---------------------------------------------------------
| Id | Operation | Name | Rows |Cost |
---------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 386 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | HASH JOIN | | 100K| 386 |
| 3 | INDEX FAST FULL SCAN| GARK_IDX | 100K| 64 |
|* 4 | TABLE ACCESS FULL | GARK | 100K| 107 |
---------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."ID1"="A"."ID1")
4 - filter("A"."ID1"-"A"."ID2"=0 AND "A"."ID1"-"A"."ID3"=0 AND
"A"."ID2"-"A"."ID3"=0)
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
545 consistent gets
0 physical reads
0 redo size
418 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
Les 2 problèmes dans ce cas, c’est que jamais l’optimiseur décidera seul d’utiliser le dynamic sampling quand les statistiques sont collectées et que le dynamic sampling a un coup important sur le parsing puisque les étapes de la requêtes sont exécutées afin d’obtenir le plan.
5. Calculer des statistiques sur des colonnes virtuelles
En 11g, Oracle est capable de calculer des statistiques sur des colonnes virtuelles. Pour créer ce type de statistiques, vous utiliserez la fonction create_extended_stats du package DBMS_STATS comme ci-dessous :
var column_group1 char(30)
var column_group2 char(30)
var column_group3 char(30)
begin
:column_group1 :=
dbms_stats.create_extended_stats(
user,
'GARK',
'(ID1-ID2)');
:column_group2 :=
dbms_stats.create_extended_stats(
user,
'GARK',
'(ID1-ID3)');
:column_group3 :=
dbms_stats.create_extended_stats(
user,
'GARK',
'(ID2-ID3)');
end;
/
print column_group1
print column_group2
print column_group3
COLUMN_GROUP1
--------------------------------
SYS_STUBPN_#AUOJIUWCKE86KKLY#J
COLUMN_GROUP2
--------------------------------
SYS_STU7_TGZLYFBV5M8PRBF26B400
COLUMN_GROUP3
--------------------------------
SYS_STUAOJAWM0ZQHWVFMDEKG0Z6QZ
Attention, vous ne pouvez pas prendre ce type de statistiques sur le schéma SYS. Une fois le groupe défini, vous pouvez collecter les statistiques avec la commande ci-dessous :
exec dbms_stats.gather_t able_stats( -
null, -
'GARK', -
method_opt => 'for all columns size 254');
6. Afficher les statistiques sur des colonnes virtuelles :
La commande ci-dessous affiche les statistiques sur des colonnes virtuelles et multi-colonnes :
col COL_GROUP format a20
select e. extension_name,
e.extension col_group,
t.num_distinct,
t.histogram
from user_stat_extensions e, user_tab_col_statistics t
where e.extension_name = t.column_name
and t.table_name='GARK';
EXTENSION_NAME COL_GROUP N HISTOGRAM
------------------------------ ------------- - ---------
SYS_STUBPN_#AUOJIUWCKE86KKLY#J ("ID1"-"ID2") 1 FREQUENCY
SYS_STU7_TGZLYFBV5M8PRBF26B400 ("ID1"-"ID3") 1 FREQUENCY
SYS_STUAOJAWM0ZQHWVFMDEKG0Z6QZ ("ID2"-"ID3") 1 FREQUENCY
La distribution de ces données peut etre accede depuis DBA_HISTOGRAMS comme la requete ci-dessous le montre la requête ci-dessous :
select distinct column_name
from dba_histograms
where table_name='GARK'
and owner=user
order by 1;
COLUMN_NAME
------------------------------
ID1
ID2
ID3
SYS_STU7_TGZLYFBV5M8PRBF26B400
SYS_STUAOJAWM0ZQHWVFMDEKG0Z6QZ
SYS_STUBPN_#AUOJIUWCKE86KKLY#J
7. Un plan simple et sans problème (#2)
Une fois les statistiques collectées, le plan devient aussi efficace que le plan parse avec le dynamic sampling comme vous pouvez le voir (L’impact sur le parsing en moins) :
set autotrace traceonly
select /* virtstats */ count(*)
from gark a, gark b
where a.id1-a.id2=0
and a.id1-a.id3=0
and a.id2-a.id3=0
and b.id1=a.id1;
set autotrace off
set autotrace off
Execution Plan
----------------------------------------------------------
Plan hash value: 4209132370
----------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
----------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 460 |
| 1 | SORT AGGREGATE | | 1 | |
|* 2 | HASH JOIN | | 99973 | 460 |
| 3 | INDEX FAST FULL SCAN| GARK_IDX | 100K| 64 |
|* 4 | TABLE ACCESS FULL | GARK | 99973 | 110 |
----------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("B"."ID1"="A"."ID1")
4 - filter("A"."SYS_STUBPN_#AUOJIUWCKE86KKLY#J"=0 AND
"A"."SYS_STU7_TGZLYFBV5M8PRBF26B400"=0 AND
"A"."SYS_STUAOJAWM0ZQHWVFMDEKG0Z6QZ"=0)
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
545 consistent gets
0 physical reads
0 redo size
418 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
8. Collecte automatique des statistiques multi-colonnes
Au moins sur les statistiques multi-colonnes (Je vous laisse le soin de tester pour les colonnes virtuelles), les histogrammes sont collectées automatiquement quand vous mettez la clause method_opt=>’FOR ALL COLUMNS SIZE AUTO’;. Pour se faire, le moteur capture les colonnes utilisées dans les clauses where. Voila le second inconvénient du dynamic sampling levé, non ?
9. Supprimer l’exemple :
Pour supprimer l’exemple, voici quelques commandes utiles :
exec dbms_xplan.
exec drop_extended_stats(-
user, -
'GARK',-
'(ID1-ID3)');
exec drop_extended_stats(-
user, -
'GARK',-
'(ID1-ID2)');
exec drop_extended_stats(-
user, -
'GARK',-
'(ID2-ID3)');
drop index gark_idx;
drop table gark;
10. Conclusion
La bonne et la mauvaise nouvelle de la 11g, c’est qu’il y a de fortes probabilités que certains plans soient amené à changer à court terme après la migration de votre application sur Oracle 11g. La bonne nouvelle : les performances des ordres SQL devraient s’améliorées. La mauvaises : il peut arriver que ce ne soit pas le cas !
-GarK!
