Discussion :

[joel90]

Bonjour,

Je suis actuellement chez un client qui utilise une base Oracle 8.1.7 en mode RULE.
Sur un système de production, l’ensemble des requêtes ont un comportement tout à fait normal .
Sur des requêtes que j’ai besoin d’exécuter sur un requêteur (BO pour ne pas le citer), je rencontre des soucis de perfs. Evidemment, je croise des tables non prévues dans leurs systèmes de production avec des index qui n’existent pas forcément.
J’ai donc fait un analyze table sur les tables , et j’utilise un HINTS o un alter session optimizer mode=’CHOOSE’ pour suivre mon plan d’exécution. Et là, bien évidemment, les mêmes requêtes qui prenaient du temps à s’exécuter, s’exécute normalement .
Jusqu’ici, tout va bien, je n’ai même pas eu besoin de toucher à la SGA, ou à d’autres paramètres.
Ce qui me gêne, c’est que mon client à des procédures chaque fin de mois qui prennent environ 1H00, et comme par hasard depuis l’analyze des tables, elles ne passent plus .
De mon côté, je n’ai pas les requêtes exécutées lors des traitements, mais mon client à simplement fait un delete des Stats, et depuis son traitement fonctionne.
Ma question est la suivante :
Sachant que dans le INIT.ORA, la base est en mode RULE, pourquoi les stats ont une influence sur des traitements qui se font forcément en mode RULE ? (et dans ce cas qui n’utilise pas les tables de stats).
Et pourquoi en supprimant les stats, le traitement passe correctement (toujours en mode RULE) ?

[lalystar]

Quand on est en mode CHOOSE, le CBO est utilisé dès lors qu'une des tables engagées dans la requête à des stats. Dans ton cas, le pb de perfs que tu rencontres est du au fait, que le CBO se base sur les stats pour générer le plan d'exécution ad hoc mais il n'a pas toutes les informations en main pour pouvoir le faire.

A partir du moment où certaines tables sont analysées, je crois qu'il faut pousser la logique jusqu'au bout et analyser entièrement la table.

EDIT:


J'ai lu un peu vite, je croyais que tu avais OPTIMIZER_MODE=CHOOSE au niveau du init.ora

Peut être utilises-tu l'une des fonctionalités suivantes qui nécessitent le CBO qui se retrouve dépourvu de stats :

Features that Require the CBO

The following features require use of the CBO:

Partitioned tables and indexes
Index-organized tables
Reverse key indexes
Function-based indexes
SAMPLE clauses in a SELECT statement
Parallel query and parallel DML
Star transformations and star joins
Extensible optimizer
Query rewrite with materialized views
Enterprise Manager progress meter
Hash joins
Bitmap indexes and bitmap join indexes
Index skip scans

Laly.

[joel90]

Features that Require the CBO

The following features require use of the CBO:

Partitioned tables and indexes
Index-organized tables
Reverse key indexes
Function-based indexes
SAMPLE clauses in a SELECT statement
Parallel query and parallel DML
Star transformations and star joins
Extensible optimizer
Query rewrite with materialized views
Enterprise Manager progress meter
Hash joins
Bitmap indexes and bitmap join indexes
Index skip scans

C-a-d, comment je peux voir ce type d'information ?
Mon client a tjs utilisé le mode RULE, sans stats sur la base, et il n'a jamais eu de soucis de perfs. Il m'a suffit de faire des analyze des tables pour tout casser !!!! ......bon heureusement facilement réparable, mais bon, je n'avais pas prévu le coup....

[13thFloor]

Il se peut que le user oracle utilisé ait un trigger qui positionne le CBO en choose. Vérifie.
Pour ne pas perturber les requêtes cbo vs rbo, récupère le plan d'exécution en rbo (index scan avec nested loops je présume) et applique quelques modifications en mode cbo :
- use_nl et index(table index) au niveau de la requête
- db_file_multiblock_read_count plus petit pour favoriser les index scans

Il se peut également qu'en cbo, tes requêtes fassent des hash join, consommatrices de mémoire. Augmente le paramètre hash_area_size (et vérifie que hash_join_enabled est à true) afin de limiter les io.

[joel90]

En ce qui concerne :
hash_area_size : 2 097 152
hash_join_enabled : TRUE.

La solution apportée serait finalement dans le script :
* lancer les stats sur les tables des différents From
* mettre un alter session optimizer mode= CHOOSE, et lancer les requêtes,
* Faire un delete des stats à la fin du traitement.

Bon, ok , ma solution ne m'explique pas pourquoi : les traitements en mode rule ne fonctionnait +, mais bon..... De plus, je n'ai pas la possibilité de voir les traitements lancé par l'exploitation en mode RULE (par rapport aux trigger qui positionne le CBO en CHOOSE) , et encore moins le User qui lance les traitements.