Statistiques d'index et de table

Tables volatiles

Les tables suivantes changent de manière significative au cours de la journée et ne sont pas concernées par la collecte automatique de statistiques :

• YFS_TASK_Q
• YFS_TASK
• YFS_EXPORT
• YFS_IMPORT

Par exemple, la table YFS_TASK_Q contient des tâches qui se trouvent à différents stades du traitement. La taille de cette table varie tout au long de la journée. Pendant la nuit, lorsque le traitement des commandes est terminé, cette table contient seulement quelques enregistrements en cours. Si la collecte automatique de statistiques est effectuée pendant la période de maintenance, il résultera de ces statistiques, de manière erronée, qu'il s'agit d'une petite table.

Il est recommandé d'effectuer l'une des deux actions suivantes pour ces tables :

• Supprimez les statistiques pour ces tables, puis verrouillez les statistiques.
• Collectez manuellement les statistiques pendant la journée, lorsque la table est volumineuse, puis verrouillez les statistiques.

Si vous choisissez la première option, Oracle suppose, en l'absence de statistiques, que la table est volumineuse. Les commandes pour la première option sont les suivantes :

   exec dbms_stats.delete_table_stats(<propriétaire_schéma>,'YFS_TASK_Q')
   
   exec dbms_stats.lock_table_stats(<propriétaire_schéma>,'YFS_TASK_Q')

Les commandes pour la deuxième option sont les suivantes :

   exec dbms_stats.gather_table_stats (ownname => 'YANTRA', -
   tabname=>'YFS_TASK_Q', -
   estimate_percent => dbms_stats.auto_sample_size)
   
   exec dbms_stats.lock_table_stats(<propriétaire_schéma>,'YFS_TASK_Q')

Colonnes avec défaut d'alignement et histogrammes

Dans le cadre de la génération de statistiques, Oracle génères des histogrammes pour les colonnes avec défaut d'alignement.

Les colonnes avec défaut d'alignement sont des colonnes dans lesquelles les valeurs ne sont pas réparties de manière uniforme. Par exemple, la colonne enterprise_key de la table YFS_ORDER_HEADER peut être constituée de quelques valeurs dont une qui peut être plus répandue. En revanche, les colonnes telles que order_no sont réparties de manière plus uniforme.

Lorsqu'il s'agit de statistiques de base telles que le nombre de lignes et le nombre de valeurs de colonne distinctes, Oracle a tendance à choisir une analyse complète des tables lorsqu'une requête est émise, telle que la requête ci-dessous, émise pour les colonnes à fort défaut d'alignement et/ou à faible cardinalité :

   select * 
   from yfs_order_header
   where derived_from_order_header_key = '2011012412213801928344';
   

De telles requêtes peuvent se traduire par des analyses de table, même si les colonnes sont indexées. L'exemple ci-dessus provient d'un cas réel (voir ci-dessous).

Du point de vue de l'optimiseur, les requêtes pour ces colonnes renvoient soit un petit nombre de résultats, soit un très grand nombre de résultats. Par prudence, l'optimiseur choisit généralement d'effectuer une analyse de table au lieu d'une analyse de la plage d'index.

Pour que l'optimiseur choisisse un plan d'accès plus efficace, vous pouvez fournir des statistiques supplémentaires sous la forme d'histogrammes.

Par défaut, Oracle crée des histogrammes dans le cadre de la génération de statistiques. Vous pouvez vérifier si une colonne contient des histogrammes en exécutant la commande suivante :

   select table_name,column_name,histogram 
   from user_tab_columns
   
   TABLE_NAME COLUMN_NAME HISTOGRAM
   YFS_ORDER_LINE CHAINED_FROM_ORDER_LINE_KEY NONE
   YFS_ORDER_LINE CHAINED_FROM_ORDER_HEADER_KEY NONE
   YFS_ORDER_LINE DERIVED_FROM_ORDER_LINE_KEY FREQUENCY
   YFS_ORDER_LINE DERIVED_FROM_ORDER_HEADER_KEY FREQUENCY
   

Dans l'exemple ci-dessus, Oracle a créé des histogrammes pour les deux colonnes DERIVED_FROM, mais pas pour les colonnes CHAINED_FROM. Pour créer manuellement les histogrammes pour les colonnes CHAINED_FROM, exécutez les commandes suivantes :

   exec dbms_stats.gather_table_stats (ownname => 'YANTRA', -
   tabname=>'YFS_ORDER_LINE', -
   estimate_percent => dbms_stats.auto_sample_size, -
   method_opt=>'for columns size auto CHAINED_FROM_ORDER_LINE_KEY, 
CHAINED_FROM_ORDER_HEADER_KEY');
   

Lorsque vous réexécutez la requête d'histogramme, vous devez obtenir la sortie suivante :

   
   TABLE_NAME COLUMN_NAME HISTOGRAM
   YFS_ORDER_LINE CHAINED_FROM_ORDER_LINE_KEY FREQUENCY
   YFS_ORDER_LINE CHAINED_FROM_ORDER_HEADER_KEY FREQUENCY
   YFS_ORDER_LINE DERIVED_FROM_ORDER_LINE_KEY FREQUENCY
   YFS_ORDER_LINE DERIVED_FROM_ORDER_HEADER_KEY FREQUENCY
   

Dans l'exemple ci-dessus, la commande method_opt avec le paramètre auto laisse Oracle décider si les histogrammes doivent être créés en fonction de la répartition des données de la colonne et de quelle façon les colonnes doivent être utilisées par l'application.

Identification des colonnes avec défaut d'alignement

La requête suivante permet d'identifier les colonnes ayant une répartition des données non uniforme :

   select ui.table_name,ui.index_name, column_name, column_position, num_rows, 
distinct_keys as dist_keys
   from user_indexes ui, user_ind_columns uic
   where ui.table_name = uic.table_name and 
       ui.index_name = uic.index_name and 
       ui.num_rows > 0 and 
       ui.distinct_keys/ui.num_rows < 0.1
   order by table_name, index_name, column_position
   
TABLE_NAME INDEX_NAME COLUMN_NAME NUM_ DIST
                                                                  ROWS _KEYS
YFS_ORDER_LINE YFS_ORDER_LINE_I3 CHAINED_FROM_ORDER_HEADER_KEY 6552586 1
YFS_ORDER_LINE YFS_ORDER_LINE_I4 DERIVED_FROM_ORDER_HEADER_KEY 6357590 1
YFS_ORDER_LINE YFS_ORDER_LINE_I5 DERIVED_FROM_ORDER_LINE_KEY 6624191 1
YFS_ORDER_LINE YFS_ORDER_LINE_I6 CHAINED_FROM_ORDER_LINE_KEY 6534969 1
YFS_ORDER_LINE YFS_ORDER_LINE_I7 DEPENDENT_ON_LINE_KEY 6457481 1

Dans l'exemple ci-dessus, le client n'utilise pas de commandes dérivées ou chaînées.

Si l'absence d'histogrammes conduit Oracle à choisir un plan inefficace, vous devez vous assurer que les histogrammes sont ajoutés aux colonnes indexées.