Automatiser un ANALYZE

Bonjour

Voici mon problème. Sur un serveur Oracle 10G, nous avons installé un schéma d'un fournisseur.
Le fournisseur recommande d'exécuter un ANALYZE sur une table qui recence plus d'un million d'entrée.
C'est vrai, exécuter un ANALYZE TABLE truc COMPUTE STATISTICS aide pas mal.
Mais je voudrais automatiser cette tâche.
Est-ce possible? J'ai essayé mais mettre ceci dans un bloc PL/SQL n'est pas accepté.
Par ailleurs, est-il utile d'invoquer DBMS_STATS ?

Merci d'avance de vos réponses.

@++

Bonjour,

En quelle version d'oracle êtes-vous ?
Avez-vous désactivé le calcul automatique des statistiques ?

En 11g (et 10g), Oracle recalcule automatiquement les statistiques si la table a plus de n% (10% par défaut je crois) de changement.

Voici quelques docs utiles :
http://docs.oracle.com/cd/E11882_01/...t.htm#PFGRF027
http://docs.oracle.com/cd/E11882_01/...htm#REFRN10214
http://docs.oracle.com/cd/E11882_01/...s.htm#ARPLS059

Cordialement,

Arkhena

Soit votre fournisseur a dit ANALYZE par réflexe de langage, mais veut juste dire "recalculer les statistiques", ce qui se fait depuis plusieurs versions par DBMS_STATS, soit il a quelques métros de retard...

Comme l'indique Arkena, depuis Oracle 10g, les statistiques sont par défaut rafraîchies chaque nuit, à moins que vous ayez désactivé ce mécanisme.

Bonjour

Merci pour vos réponses.
Je suis sous Oracle 10g.
En réalité, avec le fournisseur, on avait essayé d'invoquer le paquet DBMS_STATS mais au final, on y gagnait pas en performances en faisant:

Code:

---

EXEC dbms_stats.gather_table_stats('MON_SCHEMA', 'TRUC', cascade=>TRUE);

---

D'où ce retour vers ANALYZE.
Bon, maintenant 2 choses:
1) J'ai fait un 'script' Java qui permet d'automatiser les ANALYZE
2) J'avoue que le serveur est localisé ailleurs (Belgique) et le DBA est en Angleterre, y a t'il une commande pour savoir si les stats sont calculées automatiquement ?

Merci encore de vos réponses.

@++

De mémoire il est fortement déconseillé de mélanger analyze et dbms_stats sur une même base de données, le CBO se planterais alors royalement dans ses analyses produisant des plans "surprise".

Le mieux à faire est de voire quelles requêtes foirent (à l'aide d'AWR ou de statspack) puis de comprendre pourquoi elles foirent (là SQLT est votre ami)

Citation:

---

Envoyé par GLDavid

En réalité, avec le fournisseur, on avait essayé d'invoquer le paquet DBMS_STATS mais au final, on y gagnait pas en performances en faisant:

Code:

---

EXEC dbms_stats.gather_table_stats('MON_SCHEMA', 'TRUC', cascade=>TRUE);

---

---

Rajoutez quelques options, par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10

begin dbms_stats.gather_table_stats ( ownname => 'MON_SCHEMA' , tabname => 'MA_TABLE' , cascade => TRUE , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , degree => 4 ); end; /

---

À adapter selon votre besoin bien entendu :
http://docs.oracle.com/cd/B19306_01/...s.htm#i1036461

Citation:

---

Envoyé par GLDavid

y a t'il une commande pour savoir si les stats sont calculées automatiquement ?

---

Bonjour,

Les statistiques automatiques sont gérées par le scheduler. La requête suivante devrait vous donner les infos sur le job des statistiques par défaut :

Code:

---

1 2 3

SELECT * FROM dba_autotask_client WHERE client_name='auto optimizer stats collection';

---

Cette requête-ci devrait vous donner des infos sur la (ou les) fenêtre d'exécution de ce job :

Code:

---

1 2 3 4 5

SELECT window_group_name, window_name FROM dba_scheduler_wingroup_members WHERE window_group_name IN (SELECT window_group FROM dba_autotask_client WHERE client_name='auto optimizer stats collection');

---

Cette dernière requête vous permet de trouver à quels jour et heure ces fenêtres d'exécution correspondent :

Code:

---

1 2 3 4 5 6 7

SELECT window_name, repeat_interval, duration FROM dba_scheduler_windows WHERE window_name IN (SELECT window_name FROM dba_scheduler_wingroup_members WHERE window_group_name IN (SELECT window_group FROM dba_autotask_client WHERE client_name='auto optimizer stats collection'));

---

J'allais oublier la doc afférente aux vues dba utilisées :
dba_autotask_client : http://docs.oracle.com/cd/E11882_01/...htm#REFRN23591
dba_scheduler_wingroup_members : http://docs.oracle.com/cd/E11882_01/...htm#REFRN23370
dba_scheduler_windows : http://docs.oracle.com/cd/E11882_01/...htm#REFRN23352

Cordialement,

Arkhena

Citation:

---

Envoyé par GLDavid

...
Bon, maintenant 2 choses:
...
2) J'avoue que le serveur est localisé ailleurs (Belgique) et le DBA est en Angleterre, y a t'il une commande pour savoir si les stats sont calculées automatiquement ?

---

Code:

---

1 2 3

select * from dba_scheduler_jobs Where job_name = 'GATHER_STATS_JOB'

---

Faitez pas de (mauvaises) scripts Java pour ce que Oracle sait mieux faire.

Ne faite pas confiance à des éditeurs qui recomande d'utiliser Analyze pour améliorer les performances pour une base 10g.

Bonjour à tous

Avant tout, un grand merci pour vos réponses complètes.
Je vais mettre en stand-by mon programme Java.
J'ai réimplémenté un job à l'aide du code de Waldar.
On verra bien demain matin si ça tourne mieux. Croisons les doigts !

@++

Citation:

---

Envoyé par Arkhena

...

---

Attention, tout ce que vous venez de dire s'applique à partir d'Oracle 11.1.
Or notre ami est en V10...

Citation:

---

Envoyé par GLDavid

Bonjour à tous

Avant tout, un grand merci pour vos réponses complètes.
Je vais mettre en stand-by mon programme Java.
J'ai réimplémenté un job à l'aide du code de Waldar.
On verra bien demain matin si ça tourne mieux. Croisons les doigts !

@++

---

Lorsque vous déciderez (et je vous conseille de le faire) de passer de l’utilisation de ANALYZE à l'utilisation du package DBMS_STATS pour le calcul de vos statistiques il va falloir alors que vous vous assuriez des trois points suivants :

1) Il y a une différence dans la méthode de calcul des statistiques par analyse et par dbms_stats qui provoquera certainement quelques changements de plan d’exécution à surveiller. Par exemple la commande analyze ne calcule pas très bien avg_col_length information utilisée lors du calcul du coût des sort et hash join. Aussi le calcul des statistiques des tables partitionnées par la commande analyze n’est pas précis quant à la valeur du num_distinct

2) que ce changement de méthode de calcul des statistiques, ne vas pas produire des statistiques différentes. Particulièrement, il faut faire attention aux valeurs par défaut du package DBMS_STATS qui diffèrent de ceux de la commande analyze table.

Par exemple dans ceci

Code:

---

1 2 3 4 5 6 7 8 9 10 11

begin dbms_stats.gather_table_stats ( ownname => 'MON_SCHEMA' , tabname => 'MA_TABLE' , cascade => TRUE , estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE , degree => 4 ); end; /

---

la valeur par défaut du paramètre methot_opt est "for all columns size auto" qui veut dire qu'Oracle va lui même trouver les colonnes qui nécessitent un histogram et va les calculer. Et comme vous le savez peut-être, bind variable et histogram ne font pas un bon ménage et sont souvent à l’origine de problèmes de performance aléatoires dus aux changements des plans d’exécution. Il Faudrait donc savoir si vous être en OLTP ou en DWH afin d’adapter le calcul des statistiques en conséquence.


3) soyez au courant enfin qu’il est for possible que le dbms_stats prenne plus de temps que la commande analyze

De plus, vous allez trouver en 10g qu'il existe un job automatique de calcul des statistiques qui va peut-être vous surprendre quelques fois. Il faudra aussi faire attention au calcul des statistiques des indexes.

Conclusion : ne changez pas de méthode de calcul des statisques sans tests intensifs

Bonjour

Ce que je comprend pas c'est pourquoi alors que je lance les commandes avec DBMS_STATS, je me retrouve alors alors une query qui prend du temps alors qu'avec analyze, elle ne prend plus de temps.
C'est pour cela que je suis dubitatif avec DBMS_STATS.

@++

Les statistiques sont la pour donner un ordre d'idee a l'optimiseur. Si les modifications sur les tables ne sont aps tres importantes, c'est pas forcement la peine de le recalculer toutes les nuits.:?

Donc à évaluer...

Ok, je vais voir pour le moment.
Merci encore pour vos réponses.

@++

Citation:

---

Envoyé par GLDavid

...y a t'il une commande pour savoir si les stats sont calculées automatiquement ?

---

Vous pouvez regarder de quand datent les dernières statistiques :

Code:

---

1 2 3 4 5

set linesize 100 pagesize 100 select owner, table_name, to_char(last_analyzed, 'DD/MM/YYYY HH24:MI:SS') AS date_stats from dba_tables where owner='&SCHEMA' order by 1, 2;

---

Si tout va bien, vous devriez avoir une date récente pour chaque table ayant une proportion significative (plus de 10 %) de lignes modifiées depuis le dernier calcul des statistiques.
Ça vous indiquera si les statistiques sont à jour, mais pas qui les a calculées.

Pour vérifier si le mécanisme automatique est activé :

Code:

---

1 2 3 4 5 6 7 8

select job_name, enabled AS actif from dba_scheduler_jobs where job_name='GATHER_STATS_JOB';     JOB_NAME ACTIF ------------------------------ ----- GATHER_STATS_JOB TRUE

---

Citation:

---

Envoyé par GLDavid

Ce que je comprend pas c'est pourquoi alors que je lance les commandes avec DBMS_STATS, je me retrouve alors alors une query qui prend du temps alors qu'avec analyze, elle ne prend plus de temps.

---

DBMS_STATS propose plusieurs options notamment METHOD_OPT.

Quelle est la commande ANALYZE exacte qui est passée?
Il est possible que la commande DBMS_STATS utilisée ne soit pas strictement équivaltente à celle d'ANALYSE notamment sur la création d'histogramme.

Il sera intéressant d'examiner aussi les paramètres de l'optimiseur

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

SQL> show parameter optimizer SQL> show parameter optimizer   NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ optimizer_dynamic_sampling integer 2 optimizer_features_enable string 10.2.0.4 optimizer_index_caching integer 0 optimizer_index_cost_adj integer 100 optimizer_mode string all_rows optimizer_secure_view_merging boolean TRUE

---

bonsoir,

la table en question est-elle partitionnée?
l'approche d'ojo77 est bonne à mon (humble) avis. la requête prend plus de temps selon la méthode de collecte de stats? alors voir quels sont les différents plans d'exécution de celle-ci avec une petite requête, une fois le sql_id repéré :

Code:

---

select * from table (dbms_xplan.display_awr('sql_id', null, null, 'ALL'));

---

L'optimizer se plante dans le cost sur l'un des plan_hash_value, il faut voir pourquoi et où.
dbms_stats est un bon outil de collecte mais il vaut mieux ne pas le laisser tout choisir, notamment lui fixer un estimate_percent plutôt que le laisser choisir son échantillon (auto_sample_size ne donne pas toujours des stats précises). la commande analyze va dériver les stats globales des niveaux les plus bas (si la table est partitionnée) alors que dbms_stats va les calculer.
juste par curiosité, si vous passez une commande dbms_stats sur la table avec l'option method_opt=>'FOR ALL COLUMNS SIZE 1' (si vous avez un environnement de test bien sûr...), ça donne quoi sur le temps de réponse?