Discussion :

[carreau]

sans vouloir amoindrir le niveau d'expertise de chacun, j'essaye de faire une synthèse de vos apports :

je retiens surtout
(et précise mon contexte entre parenthèses) :

- le conseil de mnitu par lequel il invite à analyser l'activité globale de son instance de base de données
(par des traces mises en forme dans un statpack par exemple)

- ainsi que le rappel de mohammed.houri sur la manière dont évolue une table et la pertinence d'un index par rapport au CBO
(pour moi une attention particulière à améliorer la performance des insert très (trop) nombreux dans une table qui grossie à vue d'oeil, jamais de delete ou d'update, des select rares mais catastrophiques en terme de performance à se demander si ils ne provoquent pas ponctuellement des engorgements sur l'activité)

- et la méthode d'analyse de pachot
(dont je me servirai certainement à d'autres occasions)

Ce que je retiens des digressions relatives aux triggers et contraintes :

- dangereux à être utilisés pour créer des règles de gestion car peuvent être trop complexes à exprimer

- doivent rester simples pour ne pas risquer d'alourdir l'activité d'oracle et engendrer des effets de bord sur les performances ou des dégradations dans le temps

Je n'ai pas bien compris ce qu'est le clustring_factor dans le calcul du CBO

Merci à tous
JC

[mnitu]

...
Je n'ai pas bien compris ce qu'est le clustring_factor dans le calcul du CBO

On Clustering Factor and Validating Keys

[mnitu]

Je vois pas en quoi faire un trigger qui effectue un contrôle et provoque une erreur si le contrôle n'est pas vérifié ne pourrait pas marcher ! Vous dites n'importe quoi mnitu !

Peut être qu'en lisant un peu la doc et faisant quelques tests pourrait vous aider à mieux comprendre pourquoi votre solution ne marche pas.

[Mohamed.Houri]

Je n'ai pas bien compris ce qu'est le clustring_factor dans le calcul du CBO

Merci à tous
JC

Commencez par lire le Chapitre 5 (Clustering Factor) du livre de Jonathan Lewis "Cost Based Oracle Fundamentals". Et bonus, vous avez ce chapitre gratuit à télécharger et que j'ai eu le plaisir de traduire dans une langue que vous allez apprécier.

[Mohamed.Houri]

Je vois pas en quoi faire un trigger qui effectue un contrôle et provoque une erreur si le contrôle n'est pas vérifié ne pourrait pas marcher ! Vous dites n'importe quoi mnitu !

A chaque fois, dans ce genre de situation, je rappelle un principe fondamental que tout utilisateur de la base de données doit avoir à l’esprit à savoir : vous n’êtes pas tout seul à utiliser la base de données. Pensez aux accès concurrents, pensez à deux utilisateurs différents lançant le même trigger au même moment et faisant des vérifications simultanées. L’un peut ne pas voir les changements de l’autre et vice versa.

De plus, pensez un instant à l’avantage que peut apporter un index ou une contrainte au CBO par rapport au trigger ; non seulement la contrainte accomplie bien son rôle de vérification en mode multiutilisateurs mais en plus elle fourni, d’importantes informations au CBO lui permettant de choisir un plan d’exécution adéquat (moyennant d’autres informations bien sûr telles que des statistiques à jour et représentatives, etc…)

Trigger, vérifications et accès concurrents ne font pas bon ménage.

[mnitu]

Bref, le clustering factor indique dans quelle mesure les données de la table se trouvent dans le même ordre que les données de l’index. Si l’adéquation est complète alors le clustering facteur aura une valeur proce de nombre des blocs de la table. Si l’adéquation est nulle la valeur sera proche du nombre des enregistrements de la table.

Imaginez-vous deux hypermarchés : un ou les fromages sont dans le même rayon et un autre ou les fromages se trouvent éparpillées un peu par tout : le camembert est dans un coin, l’emmenthal dans le coin opposé, le roquefort au milieu, etc. Vous avez à acheter plusieurs variétés de fromage. Confronté à l’organisation du deuxième hypermarché vous allez décider que vous allez perdre moins de temps pour avoir vos fromages en balayant l’ensemble des gondoles de l’hypermarché là où dans le premier cas vous aller tout droit au rayon fromage.

[Edit]
Corrigé suite à la juste remarque de Pomalaix
[/Edit]

[Mohamed.Houri]

cammembert et clustering factor

Juste un complément d'information par rapport à l'exemple de l'hypermarché. Dans cet exemple vous pouvez organiser vos rayons par produit ce qui vous donnera une répartition (clustering) parfaite par produit. Malheureusement ceci n'est pas possible, on ne peut pas avoir plusieurs indexes d'une même table avec un bon clustering factor pour tous les indexes.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
SQL> drop table t1 purge;
 
Table dropped.
 
SQL> create table t1 as select * from user_tables order by table_name;
 
Table created.
 
SQL> create index t1_i1 on t1(table_name);
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(user, 't1', cascade => true, method_opt => 'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT t.table_name
  2        ,i.index_name
  3        ,t.blocks
  4        , t.num_rows
  5        , i.clustering_factor
  6   FROM user_tables t, user_indexes i
  7   WHERE t.table_name = i.table_name
  8   AND i.index_name='T1_I1';
 
TABLE_NAME                     INDEX_NAME                         BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
T1                             T1_I1                                  13        324                10

Très bon clustering factor car sa valeur est très proche du nombre de blocks que du nombre d’enregistrements.

Par contre, créons un autre index avec la colonne directrice (la première) différente de celle par laquelle la table est ordonnée (table_name).

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
SQL> create index t1_i2 on t1(blocks, table_name);
 
Index created.
 
SQL> exec dbms_stats.gather_table_stats(user, 't1', cascade => true, method_opt => 'FOR ALL COLUMNS SIZE 1');
 
PL/SQL procedure successfully completed.
 
SQL> SELECT t.table_name
  2        ,i.index_name
  3        ,t.blocks
  4        , t.num_rows
  5        , i.clustering_factor
  6   FROM user_tables t, user_indexes i
  7   WHERE t.table_name = i.table_name
  8   AND i.index_name='T1_I2';
 
TABLE_NAME                     INDEX_NAME                         BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------------------------ ---------- ---------- -----------------
T1                             T1_I2                                  13        324               109

Et voilà que pour ce deuxième index, le clustering factor n’est pas bon car il est plus proche du nombre d’enregistrement de la table qu’il ne l’est du nombre de blocks de cette table.

Une table ne peut pas être ordonnée de plusieurs manières différentes. Il est donc clair que certaines colonnes ont un Clustering Factor meilleur que d’autres. C’est pour cette raison que lors de la création d’un index composé ind(a,b) par exemple et que, pour des raisons que je ne vais pas reprendre ici, nous avons le choix de créer cet index comme (a,b) ou (b,a) je regarde les deux points qui suivent pour le choix de la première colonne:

1.Celle qui a un meilleur clustering factor (pour la désirabilité de l’index)
2.Celle qui est la plus répétitive (pour une bonne compression lorsque cette option est choisie)

J'aime beaucoup les analogies que quelqu'un peut faire entre Oracle et le monde dans lequel nous vivons. Pour l'anecdote, Marius, vous vous rappelez de la comparaison que j’ai faite entre le CBO-plan d’exécution et le chauffeur de taxi ? Vous n’étiez pas d’accord à l’époque. Il est donc venu le temps où vous-même vous avez utilisé le camembert-hypermarché pour expliquer la relation index-clustering factor.

Never say always never say never, I always say

[mnitu]

...Pour l'anecdote, Marius, vous vous rappelez de la comparaison que j’ai faite entre le CBO-plan d’exécution et le chauffeur de taxi ? Vous n’étiez pas d’accord à l’époque. Il est donc venu le temps où vous-même vous avez utilisé le camembert-hypermarché pour expliquer la relation index-clustering factor.

J'y avais pensé!

Juste un complément d'information par rapport à l'exemple de l'hypermarché. Dans cet exemple vous pouvez organiser vos rayons par produit ce qui vous donnera une répartition (clustering) parfaite par produit. Malheureusement ceci n'est pas possible, on ne peut pas avoir plusieurs indexes d'une même table avec un bon clustering factor pour tous les indexes

Si, je fais des partitions par produit.

@Mohamed, il y a des limites dans les modèles du chauffeur et du camembert pour expliquer les divers concepts d’Oracle.

[Pomalaix]

Bref, le clustering factor indique dans quelle mesure les données de la table se trouvent dans le même ordre que les données de l’index.

Tout à fait d'accord, et je trouve la formulation très bonne.

Si l’adéquation est complète alors l’index contiendra autant de feuils que la table a des blocs. Si l’adéquation est nulle l’index contiendra autant de feuils que la table a des lignes.

Pour moi, ce paragraphe est complètement faux. A ma connaissance, le "clustering factor" n'a aucun lien avec le taux de remplissage des blocs d'index. La seule chose qui compte, c'est l'emplacement des valeurs dans les blocs de la table. Que l'index soit compact (blocs très pleins) ou pas n'a pas d'incidence dans ce calcul.

[mnitu]

@Pomalaix
Vous avez raison. Pour plus de clarté je cite l'article indiqué

So if a table and an index key are in about the same order, the clustering factor will be near the number of blocks in the table and the index will be useful for very large index range scans and for retrieving numerous rows from the table. On the other hand, if the data is randomly scattered, the clustering factor will be near the number of rows in the table, and given that the number of rows in a table is usually at least an order of magnitude more than the number of blocks, the index will be less efficient for returning numerous rows.

[Edit]
J'avais lu à travers
[/Edit]