Discussion :

[cquilgars]

Bonjour à tous,

Après un changement de version d'Oracle de la 10g à la 11g, je constate que l'optimizer ne prend plus en compte l'activation du paramètre "star_transformation_enabled=true" dans le spfile.

Il faut que j'ajoute explicitement le hint pour que les plans d'execution soient comparables.

Code :

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

 /*+ STAR_TRANSFORMATION */

De plus, je constate que le parametre est bien positionné via la requête suivante :

Code :

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

 select * from v$parameter where name like '%star_transformation%'

De plus, la commande

Code :

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

ALTER SESSION set star_transformation_enabled=true;

ne résout pas mon problème

Nous avons trouvé une vague référence à un paramètre _always_star_transformation activable par

Code :

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

alter session set "_always_star_transformation"=true;

sans résultat

En vous remerciant par avance de votre intérêt à ma cause

[ojo77]

Il y a 3 paramètres disponibles pour gérer les 'star transformations' :

1. _always_star_transformation (par defaut FALSE)
2. star_transformation_enabled (par defaut FALSE)
3. _optimizer_use_cbqt_star_transformation (par defaut TRUE)

L'algorithme a été réécris pour utiliser des règles de coût pour la réécriture plutôt que de simples règles de logique booléenne.

Pour repasser à l'ancien algorithme il faut
passer star_transformation_enabled à TRUE et _optimizer_use_cbqt_star_transformation à FALSE. Ces paramètres sont modifiables au niveau session.

[Waldar]

Les paramètres qui sont préfixés par un _ ne sont pas censés être modifiés qu'à la demande du support Oracle ?

[ojo77]

Non seulement ils sont sensés ne pas être modifiés sauf si le support le conseille, mais ils sont sensés aussi ne pas être connus. Pourtant ils trainent un peu partout sur le net.

Bref difficile de savoir quoi faire avec ces paramètres.

La démarche officielle reste cependant bien d'ouvrir une SR sur supporthtml.oracle.com (ou support.oracle.com pour les fans) et de voir la réponse apportée. Ca peut être long pour un problème de changement de plan.

[cquilgars]

Pour repasser à l'ancien algorithme il faut
passer star_transformation_enabled à TRUE et _optimizer_use_cbqt_star_transformation à FALSE. Ces paramètres sont modifiables au niveau session.

Merci à tous pour vos réponses

Malheureusement, j'ai passé les commandes suivantes sans résultat :

Code :

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

1
2
alter session set star_transformation_enabled=true;
alter session set "_optimizer_use_cbqt_star_transformation"=false;

Dans mon cas, le 1er ALTER SESSION ne devrait pas être utile car déjà positionné dans mon spfile (la valeur dans v$parameter à la connexion est bonne)

J'ai tenté ma chance avec la commande ci-dessous sans plus de résultat

Code :

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

alter session set "_always_star_transformation "=true;

[PpPool]

quels sont les changements que tu as observé au niveau explain plan ?
As-tu un exemple concret ?

as-tu réalisé des évolutions entre la 10g et la 11g au niveau :
- de ton modéle en étoile
- de l'indexation bitmap
- de la collecte des statistiques

Quelle est la valeur des paramètres suivants :
DB_BLOCK_SIZE
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_MODE
BITMAP_MERGE_AREA_SIZE
WORKAREA_SIZE_POLICY

As tu implémentée la MEMORY_TARGET ?

[cquilgars]

Bonjour PpPool

quels sont les changements que tu as observé au niveau explain plan ?
As-tu un exemple concret ?

as-tu réalisé des évolutions entre la 10g et la 11g au niveau :
- de ton modéle en étoile
- de l'indexation bitmap
- de la collecte des statistiques

Tu trouveras ci-joint 2 explain plans. Le 1er est sans le hint STAR_TRANSFORMATION. Dès que ce hint est posé, nous obtenons le même plan que la 10g. Pour la requête témoin, nous passons de 60s à moins d'une seconde.
Le modèle en étoile, la stratégie d'indexation par BITMAP, la stratégie de stockage par compression et la collecte des stats n'ont pas évolué. Nous avons changé de serveur physique et OS (SunOS ==> RH 5.3) avec 8 CPU à 1.8Ghz et 8Go RAM.
La migration de données a été réalisée par export/import avec recalcul systématique des stats partition par partition (et sous-partition éventuelle) avec reprise intégrale des paramètres de la 10g (acceptés par la 11g)

Quelle est la valeur des paramètres suivants :
DB_BLOCK_SIZE
DB_FILE_MULTIBLOCK_READ_COUNT
OPTIMIZER_MODE
BITMAP_MERGE_AREA_SIZE
WORKAREA_SIZE_POLICY

As tu implémentée la MEMORY_TARGET ?

Je ne connais pas le concept autour du paramètre MEMORY_TARGET...

Code :

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

1
2
3
4
5
DB_BLOCK_SIZE=8192
DB_FILE_MULTIBLOCK_READ_COUNT=128
OPTIMIZER_MODE=ALL_ROWS
BITMAP_MERGE_AREA_SIZE=1048576
WORKAREA_SIZE_POLICY=AUTO

[cquilgars]

Bonjour à tous,

Pendant la migration de 10g vers 11g, nous n'avons pas embarqué un paramètre déjà positionné en 10g (hérité d'une migration douloureuse de 9i vers 10g)

La commande suivante résout nos problèmes :

Code :

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

ALTER SESSION SET optimizer_features_enable='9.2.0';

Comme vous l'aurez compris, lors du passage à la 10g (il y a maintenant 5 ans), nous avions rencontré la même problématique de régression des plans d'exécution retenus avec la version supérieure.
A défaut, nous avons demandé à l'optimiseur de conserver la logique de la 9.2.0...

Nous sommes dans le même contexte en 11g. Le situation devient difficilement justifiable : faire un "V-2" sur l'optimiseur pour obtenir des temps satisfaisants...

Donc je considère que la discussion est résolue mais pas optimale

Je vais donc travailler cette entrée ci-dessous (merci à son auteur)

As tu implémentée la MEMORY_TARGET ?

Je reste donc ouvert à toute suggestion !

Merci à tous

[cquilgars]

Un post supplémentaire...

A priori, nous venons de résoudre une bonne partie de nos problèmes après avoir comparé le contenu des tables user_tab_statistics et user_ind_statistics (du propriétaire du schéma) entre la 10g et 11g.

En 11g, ces tables contenaient de nombreuses lignes avec la colonne stale_stats valuée à 'YES' au niveau table, partition et sous-partition.

Par acquis de conscience, nous avons rejoué le calcul des statistiques (déjà exécuté lors de la migration des données par export/import). Cette colonne est passée à 'NO'.

A partir de ce moment, les requêtes "témoins" s'exécutent dans les temps attendus...