les stats sont-elles nécessaires? à quel cout?

**j6m** · 05/06/2006, 22h36

Je travaille sur des bases de données Oracle assez hétérogènes question systèmes (Unix, Linux, windows), niveau de noyau SGBD (8 9 et 10) et quant à leur raison d'être : OLAP OLTP tests etc.
Je cherche des infos sur les statistiques analyze et DBmS_STATS, sur le site on trouve comment les activer, et comment installer statspack, mais quel est le bénéfice de ces stats pour l'optimiseur? (statspack aucun je suppose

quelle est la fréquence nécessaire pour un evironnment décisonnel qui calcule une fois par jour, mais des données qui sont ensuite servies à plusieurs dizaines d'utilisateurs (qui construisent leurs requêtes librement => la SGA est en constant renouvellement), et quel en est le coût : quelle place ça prend, est-ce que c'est fonction du volume du schéma analysé?
Pendant que les stats tournent est-ce que ça dégrade les temps pour les utilisateurs?
Récemment j'ai demandé des stats (estimate) sur une seule table d'un de mes schémas. ça n'a eu aucune incidence sur le temps de calcul du lendemain, et je suis trop éloigné de mes utilisateurs pour leur demander - je ne suis pas sûr de savoir quelle question leur poser. est-ce parce qu'une seule table c'est pas assez, ou estimate 30% => pas suffisant.
Pendant que j'y suis, comment géreriez-vous un chef de projet récalcitrant à sortir du périmètre fonctionnel qu'il s'est défini de lui-même, au point de refuser de versionner les développements qu'il chapeaute?

**dyvim** · 05/06/2006, 23h35

Envoyé par j6m

... mais quel est le bénéfice de ces stats pour l'optimiseur?

Les stats vont permettre à ton optimiseur de travailler correctement... De choisir d'utiliser ou pas tel ou tel index... Bref de faire moins (pas?) d'erreurs dans le calcul du cout de tes requetes.

quelle est la fréquence nécessaire pour un evironnment décisonnel qui calcule une fois par jour, mais des données qui sont ensuite servies à plusieurs dizaines d'utilisateurs (qui construisent leurs requêtes librement => la SGA est en constant renouvellement)

La comme ca je dirais une fois par jour entre ton load de données et le travail des utilisateurs... Mais si le volume ne bouge pas (ou peu) il peut être utile de fixer tes stats definitivement.

et quel en est le coût : quelle place ça prend, est-ce que c'est fonction du volume du schéma analysé?

Je crois que tu réponds à la question.. mais ca depend aussi beaucoup de la precision souhaitée et de ton nombre de tables. Mais ca reste bcp moins important en volume que tes données.

Pendant que les stats tournent est-ce que ça dégrade les temps pour les utilisateurs?

A ton avis???
La réponse est oui bien entendu. Cela peut être interessant de les programmer la nuit.

Récemment j'ai demandé des stats (estimate) sur une seule table d'un de mes schémas. ça n'a eu aucune incidence sur le temps de calcul du lendemain, et je suis trop éloigné de mes utilisateurs pour leur demander - je ne suis pas sûr de savoir quelle question leur poser. est-ce parce qu'une seule table c'est pas assez, ou estimate 30% => pas suffisant.

Peut être ou peut être aussi que l'optimiseur travaille déjà pour le mieux...
Il faut vérifier le plan d'exécution pour ca...

Pendant que j'y suis, comment géreriez-vous un chef de projet récalcitrant à sortir du périmètre fonctionnel qu'il s'est défini de lui-même, au point de refuser de versionner les développements qu'il chapeaute?

Alors là ca dépasse mes compétences Oracle

Sinon je dirais que le versionnement (CVS ?) permet de gagner une sécurité au niveau des dev (comme ca on n'efface pas nos modifs les uns les autres).
Voilà c'est l'argument sécurité à faire valoir (et gratuité puisque les outils de versionning sont pour la plupart gratuits)

**orafrance** · 05/06/2006, 23h54

Envoyé par j6m

Je cherche des infos sur les statistiques analyze et DBmS_STATS, sur le site on trouve comment les activer, et comment installer statspack, mais quel est le bénéfice de ces stats pour l'optimiseur? (statspack aucun je suppose

Oracle utilise (jusqu'à la 9i) 2 optimiseurs : le RBO (Rule Based Optimizer) et le CBO (Cost Based Optimizer). Le premier utilise un certain nombre de régles strictes pour trouver le plan d'exécution d'un requête alors que le second utilise les statistiques calculées sur les objets. Ainsi, plutôt que de prendre "bêtement" un index il vérifie qu'il est réellement intéressant. Tu l'auras compris, le CBO est bien plus efficace dans des systémes qui bougent. Si les stats ne sont pas calculées alors c'est le RBO qui est utilisé.

Envoyé par j6m

quelle est la fréquence nécessaire pour un evironnment décisonnel qui calcule une fois par jour, mais des données qui sont ensuite servies à plusieurs dizaines d'utilisateurs (qui construisent leurs requêtes librement => la SGA est en constant renouvellement), et quel en est le coût : quelle place ça prend, est-ce que c'est fonction du volume du schéma analysé?

Une fois par semaine c'est pas mal déjà mais si tu as vraiment beaucoup de fluctuation dans les données (gros DELETE ou INSERT) il faut augmenter la fréquence. Ca ne remplit que les colonnes statistiques de DBA_SEGMENTS, DBA_TABLES, etc... donc ça ne prend pas de place. Les stats sont calculées sur les objets et en effet tu peux calculer les stats sur un schéma seulement. Ce qui peut être obligatoire dans le cas d'application qui sont spécifiquement prévu pour le RBO seulement.

Envoyé par j6m

Pendant que les stats tournent est-ce que ça dégrade les temps pour les utilisateurs?

Oui, les stats font de compte de nombre de ligne, des SUM, des AVG, bref, pas mal de calcul sur toutes les colonnes.

Envoyé par j6m

Récemment j'ai demandé des stats (estimate) sur une seule table d'un de mes schémas. ça n'a eu aucune incidence sur le temps de calcul du lendemain, et je suis trop éloigné de mes utilisateurs pour leur demander - je ne suis pas sûr de savoir quelle question leur poser. est-ce parce qu'une seule table c'est pas assez, ou estimate 30% => pas suffisant.

Il me semble que si une des tables de la requêtes n'a pas de statistique, c'est le RBO qui est utilisé... donc, il faut tout calculer ou rien

Envoyé par j6m

Pendant que j'y suis, comment géreriez-vous un chef de projet récalcitrant à sortir du périmètre fonctionnel qu'il s'est défini de lui-même, au point de refuser de versionner les développements qu'il chapeaute?

C'est à lui de gérer ce type de problème... toi tu dois faire fonctionner la base au mieux et éventuellement conseiller les développeurs (en livrant un guide de bonne pratique par exemple). Si le chef de projet n'est pas capable de définir un périmétre et de pérenniser les dévs c'est de sa responsabilité.

**dyvim** · 06/06/2006, 09h24

Envoyé par Fred_D

Il me semble que si une des tables de la requêtes n'a pas de statistique, c'est le RBO qui est utilisé... donc, il faut tout calculer ou rien

Pas d'accord... Le CBO s'il est défini comme optimiseur ne sera pas remplacé par le RBO en cas d'absence de stats...
Ceci dit s'il manque d'info, il aura peut être tendance à se servir des index. Mais il se peut surtout qu'il rate son calcul de cout car il peut croire qu'une table fait 2000 lignes alors qu'elle en fait 3 millions. Ou que la repartition des données dans une colonne est uniforme (autant de valeurs à 1 qu'à 2 et ainsi de suite) alors qu'elle ne l'est pas du tout et qu'une de ces valeurs est rencontrée beaucoup plus souvent que les autres. (interet des histogrammes)

**aline** · 06/06/2006, 09h58

Envoyé par dyvim

Pas d'accord... Le CBO s'il est défini comme optimiseur ne sera pas remplacé par le RBO en cas d'absence de stats...
Ceci dit s'il manque d'info, il aura peut être tendance à se servir des index. Mais il se peut surtout qu'il rate son calcul de cout car il peut croire qu'une table fait 2000 lignes alors qu'elle en fait 3 millions. Ou que la repartition des données dans une colonne est uniforme (autant de valeurs à 1 qu'à 2 et ainsi de suite) alors qu'elle ne l'est pas du tout et qu'une de ces valeurs est rencontrée beaucoup plus souvent que les autres. (interet des histogrammes)

Pas d'accord non plus.
J'ai une troisième version.
quand seulement une partie des stats est calculé, Oracle calcule le reste mais ne sauve pas l'information dans ces tables.
bref, vous avez tout perdu.
en tout les cas, ce qui est sur, c'est qu'il faut que toutes les tables et tout les indexes soient analysés!
Et la nous eserons touts d'accord

**dyvim** · 06/06/2006, 11h02

Envoyé par aline

Quand seulement une partie des stats est calculé, Oracle calcule le reste mais ne sauve pas l'information dans ces tables.

Là je ne comprends pas Aline. S'il les calcule lui même à quoi sert-il de lui fournir des stats?
De plus vu le temps que prend le calcul de stat, je le vois mal prendre le temps de le faire pour chaque SELECT..
Et surtout s'il les calcule quand elles manquent, pourquoi se trompe-t-il quelquefois?
A moins qu'il ne se trompe que quand les statistiques existent mais ne sont pas à jour?
Ce qui voudrait dire qu'une absence totale de stat serait "préférable" à des stats pas à jour?

En tout les cas, ce qui est sur, c'est qu'il faut que toutes les tables et tout les indexes soient analysés!
Et la nous serons touts d'accord

+1

**Jaouad** · 06/06/2006, 12h05

Bonjour ,
Concernant le choix de l'optimiseur , Oracle se base sur le principe suivant :

Mode Rule : Toutes les opérations ( sauf alter session ou Hint ) se font dans ce mode là

Mode Cost : Toutes les opérations ( sauf alter session ou Hint ) se font dans le mode Cout. S'il n'y a pas de stat Oracle fait de l'échantillognage qu'il ne stocke pas ( Cf explication d'Aline) Il peut dans ce cas être amené à se tromper facilement.

Mode Choose : Si toutes les tables ont des stats alors Oracle va choisir le mode COST, si une table n'a pas de stat alors dans ce cas là Oracle va prendre le mode RULE. C'est pourquoi Oracle conseille en régle génèrale ce mode.

Lorsqu'on est en mode CHOOSE , pour savoir qu'elle est le mode choisit il suffit sous Sql*Plus de voir si les valeurs pour card et Bytes sont renseignés ( COST ) ou pas ( RULE )

un exemple simpliste :

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
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
SQL>  create table dvp ( a varchar2(10), b number ) ; 
 
Table créée.
 
SQL>  
SQL>  declare 
  2   i number ; 
  3   begin 
  4   for i in 0..100000 
  5   loop
  6   insert into dvp  values  ( 'Oracle', i) ; 
  7   end loop ; 
  8   commit ;
  9   end ; 
 10  /
 
Procédure PL/SQL terminée avec succès.
 
SQL> create index dvp_ind on dvp ( b) ;  
 
Index créé.
 
SQL> set autot trace exp 
SQL> select * from dvp where b= 2536 ;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DVP'
   2    1     INDEX (RANGE SCAN) OF 'DVP_IND' (NON-UNIQUE)
 
 
 
SQL> analyze table dvp compute statistics ; 
 
Table analysée.
 
SQL> analyze index dvp_ind  compute statistics ; 
 
Index analysé.
 
SQL> select * from dvp where b= 2536 ;
 
Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=1 Bytes=10)
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'DVP' (Cost=2 Card=1 Byte
          s=10)
 
   2    1     INDEX (RANGE SCAN) OF 'DVP_IND' (NON-UNIQUE) (Cost=1 Car
          d=1)