STATISTIQUES : extended statistics 11g

Statistiques étendues

Oracle peut désormais en 11g, recueillir de nouveaux types de statistiques, rendant la qualité de l'estimation de sélectivité bien meilleure dans les cas concernés.

Statistiques multi-colonnes

Lorsque dans la clause where d'une requête, figurent plusieurs prédicats mono-colonnes d'une même table, la corrélation entre ces colonne peut avoir des conséquences sur la sélectivité de ce groupe de colonnes.
Il est par conséquent très utile de pouvoir définir des groupes de colonnes sur lesquels on déterminera des statistiques de répartition de données afin de rendre plus optimal le choix de l'optimiseur dans ces cas.

Creating a Column Group

DECLARE
  cg_name varchar2(30);
BEGIN
  cg_name := dbms_stats.create_extended_stats(null,'customers', 
             '(cust_state_province,country_id)');
END;
/

Getting a Column Group

select sys.dbms_stats.show_extended_stats_name('sh','customers',
       '(cust_state_province,country_id)') col_group_name
from dual;

The output is similar to the following:

COL_GROUP_NAME
----------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM

Dropping a Column Group

exec dbms_stats.drop_extended_stats('sh','customers',

'(cust_state_province,country_id)');

Monitoring Column Groups

Use the dictionary table user_stat_extensions to obtain information about MultiColumn statistics:

Select extension_name, extension
from user_stat_extensions
where table_name='CUSTOMERS';

EXTENSION_NAME                EXTENSION
-----------------------------------------------------------------
SYS_STU#S#WF25Z#QAHIHE#MOFFMM_("CUST_STATE_PROVINCE","COUNTRY_ID")

Use the following query to find the number of distinct values and find whether a histogram has been created for a column group:

select e.extension col_group, t.num_distinct, t.histogram
  2  from user_stat_extensions e, user_tab_col_statistics t
  3  where e.extension_name=t.column_name
  4  and e.table_name=t.table_name
  5  and t.table_name='CUSTOMERS';

COL_GROUP                             NUM_DISTINCT      HISTOGRAM
-----------------------------------------------------------------
("COUNTRY_ID","CUST_STATE_PROVINCE")  145               FREQUENCY

Statistiques d'expressions

Lorsque une fonction est appliquée à une colonne dans la clause "where" (fonction(col1)=constant), l'optimiseur n'a aucun moyen de savoir comment cette fonction affecte la sélectivité de la colonne.
En recueillant les statistiques de cette expression, l'optimiseur obtient une valeur de sélectivité bien plus juste.
 

Creating Expression Statistics 

select
dbms_stats.create_extended_stats(null,'customers',

'(lower(cust_state_province))')
from dual;

Monitoring Expression Statistics

Select extension_name, extension
from user_stat_extensions
where table_name='CUSTOMERS';

EXTENSION_NAME                    EXTENSION
------------------------------------------------------------------
SYS_STUBPHJSBRKOIK9O2YV3W8HOUE    (LOWER("CUST_STATE_PROVINCE"))

Dropping Expression Statistics

exec dbms_stats.drop_extended_stats(null,'customers',

'(lower(country_id))'); 

Il est possible de calculer directement les statistiques sur une extension (groupe ou fonction) de colonne(s) sans avoir à créer cette extension explicitement, celle-ci l'est implicitement et demeure dans la liste des extension.
 

Gathering Extended Statistics 
 
exec dbms_stats.gather_table_stats('sh','customers',method_opt =>
'for all columns size skewonly
for columns (cust_state_province,country_id) size skewonly');

et
 

exec dbms_stats.gather_table_stats('sh','customers', method_opt =>
'for all columns size skewonly
 for columns (lower(cust_state_province)) size skewonly');


Nb : skewonly indique à Oracle de ne calculer des histogrammes que pour les colonnes qui ont une répartition très inégale.