[11g] Mise en place automatique des statistiques

[tomk41]

Bonjour à tous,

Je travaille actuellement sur l'optimisation des performances d'une application Siebel couplée à un moteur Oracle 11g. L'éditeur préconise un certain nombre de choses pour la mise en place des statistiques qui ne paraissent pas évidentes pour un dba en herbe comme moi. Je m'explique :

Les statistiques doivent être calculées sur la majorité des tables avec la méthode 'FOR ALL INDEXED COLUMNS SIZE AUTO'.

Pour trois des tables de l'application (S_POSTN_CON, S_ORG_BU et S_ORG_GROUP) les statistiques doivent être calculées avec la méthode 'FOR ALL COLUMNS SIZE 254'.

Pour finir, les statistiques ne doivent pas être calculées pour les tables de moins de 15 enregistrements (en "lockant" les statistiques sur ces tables j'imagine ?).

Savez-vous s'il est possible d'implémenter une telle chose dans oracle et si possible de façon automatique (toutes les semaines voir toutes les nuits) ?

D'avance merci pour votre aide !

Thomas

[Waldar]

Sur les dernières versions, Oracle sait quelles sont les stats à jour ou pas à jour.
Il y a plein d'options pour que celles-ci soit rafraîchies de manière semi automatique.

Si vous lisez l'anglais, suivez ce lien :
http://asktom.oracle.com/pls/asktom/...88300346992024

[tomk41]

Bonjour Waldar,

J'ai déjà eu l'occasion de parcourir la doc que tu as cité. J'ai bien conscience qu'avec la 11g, le calcul des stats peut se faire de façon automatique. Seulement, j'ai l'impression que c'est tout ou rien. Non ?

Mon plus gros problème et de savoir comment exécuter le calcul de façons différentes en fonction du nombre d'enregistrements ou du nom de la table.

Une idée ?

Encore merci.

[pifor]

Vous pouvez utilisez le verrouillage des statistiques pour personnaliser le calcul des statistiques de la façon suivante:

• verrouiller les stats que vous voulez personnaliser
• garder le calcul général de stats que propose Oracle
• écrire un job DBMS_SCHEDULER pour les objets dont les stats doivent être personnalisées qui va:

1. déverrouiller les stats
2. calculer les stats
3. verrouiller les stats

[Pomalaix]

Depuis Oracle 10, par défaut, les statistiques sont collectées automatiquement toutes les nuits, avec des options par défaut.
Et puisque vous êtes en V11, autant profiter des apports de cette version !

Pour connaître les paramètres généraux en vigueur, on a DBMS_STATS.GET_PREFS.
Le paramètre qui nous intéresse ici est METHOD_OPT.

Code :

1
2
3
4
5
6
col METHODE format A40
SELECT dbms_stats.get_prefs('METHOD_OPT') METHODE FROM dual;
 
METHODE
------------------------------
FOR ALL COLUMNS SIZE AUTO

Donc il faut modifier cette valeur par défaut :

Code :

exec dbms_stats.set_global_prefs('METHOD_OPT', 'FOR ALL INDEXED COLUMNS SIZE AUTO');

Ensuite, on peut désormais définir des préférences individuelles pour des tables particulières.

Code :

1
2
3
4
5
6
7
8
9
begin
dbms_stats.set_table_prefs(
	ownname => 'SCOTT', 
	tabname => 'EMP', 
	pname => 'METHOD_OPT',
	pvalue => 'FOR ALL COLUMNS SIZE 254'
	);
end;
/

La dernière exigence (pas de recalcul pour les tables de moins de 15 lignes) est pour moi satisfaite par défaut, et il n'y a rien de particulier à faire.
En effet, depuis Oracle 10, les tables sont surveillées automatiquement (attribut MONITORING implicite), et leurs modifications sont tracées dans DBA_TAB_MODIFICATIONS. Cette vue est prise en compte lors du calcul des stats : si une table a subi moins de n % de modifications depuis le dernier calcul, elle est ignorée et on passe à la suivante.
Ainsi, on ne recalcule pas inutilement des stats sur une table qui n'aurait pas bougé, et ce indépendamment de son nombre de lignes.

Ce seuil de n % est défini automatiquement par défaut.
On peut le connaître table par table en consultant SAMPLE_SIZE (nombre de lignes lues pour calculer les stats) et NUM_ROWS (nombre de lignes estimées) dans DBA_TABLES.

Il est aussi paramétrable, globalement ou individuellement :

Code :

exec dbms_stats.set_global_prefs('ESTIMATE_PERCENT', 10);

[Alderick]

Bonjour à tous,

Je me permet de ré-ouvrir ce sujet c'est celui que j'ai trouvé qui se rapproche le plus de mes interrogations.

On sait qu'en 10g, le calcul automatique des statistiques est initié par le job GATHER_STATS_JOB.
On retrouve par ailleurs une historisation de son exécution dans la vue dba_scheduler_job_log.

Ce job semble avoir disparu en 11g au profit d'une gestion plus native ?!?

Ma question est donc la suivante :
Quel mécanisme utilise précisément une 11g pour lancer le calcul des stats.
Y a t'il, au même titre que dba_scheduler_job_log en 10g une vue permettant de vérifier l'heure et la bonne exécution de ce processus ?

Merci d'avance

[jkofr]

Hello,

Tu as le job de stats globales

Code :

1
2
3
4
 
JOB_NAME         SCHEDULE_NAME            SCHEDULE_TYPE  ENABLED
---------------- ------------------------ -------------- -------
GATHER_STATS_JOB MAINTENANCE_WINDOW_GROUP WINDOW_GROUP   TRUE

Il tourne entre 22h et 2h tous les jours

Tu as le job pour la "moving window" qui tourne tous les samedis il me semble, BSLN_MAINTAIN_STATS_JOB c'est un module externe qui calcule les stats, aucune utilisation de dbms_Stat.

Je crois que il y en a un 3eme qui tourne tous les mois.


Juste une remarque, je te conseille de ne pas calculer les histogrammes pour toutes tes colonnes.

Idéalement

Code :

1
2
 
dbms_stats.set_global_prefs('method_opt','FOR ALL COLUMNS SIZE 1');

A toi par la suite de définir au niveaux des objets pour lesquels tu est sur que les histogrammes vont être bénéfiques.

Code :

1
2
 
dbms_stats.set_table_prefs('"USER"', '"TABLE"', 'METHOD_OPT', 'FOR ALL COLUMNS SIZE 56');

Je préfère pour toutes les colonnes car l'optimiseur de la 11g est très friand d'informations. Plus elle est pertinente meilleure sera le plan d’exécution calculé.

Voila
jko

[Alderick]

Merci jkofr pour ta réponse rapide et précise.

J'ai en effet bien retrouvé le JOB BSLN_MAINTAIN_STATS_JOB sur mes 11g.

J'avoue que dans mon environnement de travail ces jobs ne me semblent pas satisfaisants. D'une part parce que le créneau de 22h-0h est déjà utilisé pour certains batch et d'autre part parce que la fonction dbms_space.auto_space_advisor_job_proc ne me semble pas aisément manipulable (obligation de changer les paramètres par défaut).
Je vais sans doute opter pour la désactivation de ces jobs et passer par un ordonnanceur (déjà en place) pour faire appel à la fonction DBMS_STATS.gather_database_stats en lui passant des paramètres différents en semaine et le Week end.