Discussion :

[shaun_the_sheep]

Bonjour,

Je m'interroge sur mon installation de ma base Oracle dont la version est :

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

Je m'explique, j'ai une requête qui interroge une vue un peu complexe (la vue est composée une requete et sous requete).

Ma vue réponse bien en terme d'accès.. Elle a été conçu en oracle 10G il y a quelques temps et elle répondait déjà bien en terme d'accès.

J'ai une requete simple qui interroge cette vue par des critères dans ma clause Where.

Anciennement en 10G j'avais aucun soucis.

Depuis que nous sommes passé en 11G (version précisée au dessus), à la première exécution de la requête les temps de réponse sont parfait (30s, le volume de donnée étant assez conséquent).

Aux exécutions suivantes la même requête ne répond plus dans des délais raisonnable on passe les 20 min voir plus.

Si je change le critère elle répond de suite, puis nouvelle exécution sur le critère précédent et le nouveau critère, la requête ne répond plus.

J'avoue ne pas comprendre, est-ce que cela peut venir d'une mauvaise config de la base de données ?

Merci de votre aide.

[mnitu]

Comme toujours dans ce cas, commencez par analyser ce qui se passe au niveau du plan d'exécution en suivant les conseils déjà donnés sur ce type de sujet: trace sql étendue, autotrace ou (privilégié) hint gather_plan_statistics. Est-ce que le plan est le même ?

Sinon peut être que vous tapez d'une mauvais manière dans le "nouveau" mécanisme "adaptive cursor sharing" de la 11g.

Probablement que vos statistiques ne sont pas à jour.

[shaun_the_sheep]

Merci de votre aide.

Est il possible de me préciser ce point : "nouveau" mécanisme "adaptive cursor sharing" de la 11g.

Est ce lié à la configuration de ma base ?

Mes requêtes / vues ont été crée alors que j’étais en 10G , je n'avais pas de soucis.

Depuis la migration en 11G je rencontre ce soucis.

je vais regarder l'aspect plan d’exécution.

[Mohamed.Houri]

La première chose qui me vient à l'esprit dans ce genre de situation est ce qu'on appelle l'effet de la cardinality feedback. Je suppose que la deuxième execution est entièrement identique à la première y compris en variable de liaison (bind variables)

Dans ce cas postez s'il vous plait le plan d'exécution (même non fini) en utilisant

Code :

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

1
2
 
select * from table (dbms_xplan.display_cursor('sql_id', null, null));

Si c'est le cas il y aura dans la partie Note du plan d'exécution une information concernant l'utilisation de la cardinality feedback

Sinon, si vous êtes pressé exécutez alors votre requête en lui ajoutant le hint suivant

Code :

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

1
2
 
/*+ opt_param('_optimizer_use_feedback','false') */

[shaun_the_sheep]

bonjour,

merci , par contre je ne suis pas très au faite de ce type d'opératon.

sql_id correspond à un id de requete ?
comment l'obtient on ?

mercià vous

[shaun_the_sheep]

Bonjour,

j'ai déja essayé cela

Code :

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

1
2
 
/*+ opt_param('_optimizer_use_feedback','false') */

Et effectivement à tout les coups la requete répond instantanément.

etant pas très calé sur le sujet ca veut dire quoi ?

est ce une option de paramétrage de la base de données ?

est ce lié :

Cardinality feedback. This feature, enabled by default in 11.2, is intended to improve plans for repeated executions. See Support note 1344937.1 and documentation for additional info. You can be disable cardinality feedback with an underscore parameter which can be set at session or instance level. Usual caveats on setting underscore parameters apply:
alter session set "_OPTIMIZER_USE_FEEDBACK"=FALSE;
This can also be done with a hint, therefore at statement level, using the opt_param syntax: /*+ opt_param('_OPTIMIZER_USE_FEEDBACK','FALSE') */

merci à vous.

[Mohamed.Houri]

Lorsque le CBO produit un plan d'exécution qu'il pense être le meilleur il se base alors sur les statistiques dont il a à sa disposition. Mais lorsque la requête est exécutée, le CBO, sous certaines conditions, utilise la technique de cardinality feedback pour s'auto informer sur les mauvaises estimations qu'il a faites. Il le sait car il vient d'exécuter la requête et a à sa disposition les Estimations (E-Rows) et les Actuals (A-Rows). Lors de la deuxième exécution il essaie alors d'utiliser ces informations pour générer un nouveau plan. Malheureusement cette histoire de cardinality feedback cause plus de problèmes qu'elle n'en résout.

Aussi dans votre cas, moi je ferai ceci

(a) re-calculer correctement les statistiques
(b) annuler l'utilisation de la cardinality feedback soit en utilisant un hint(local comme indiqué plus haut) soit au niveau session ou système(alter session set...).

[shaun_the_sheep]

Merci de votre aide

Quand vous dites :
(b) annuler l'utilisation de la cardinality feedback soit en utilisant un hint(local comme indiqué plus haut) soit au niveau session ou système(alter session set...).

Au niveau système cela veut dire un paramètre de config au niveau de la base et cela sera valable pour tout le monde ?

Que nous faudrait il faire dans la config ?

[Mohamed.Houri]

Faudrait voir cela avec le support Oracle. C'est un paramètre caché que vous ne devez changer qu'avec l'accord du support Oracle. Mais avant d'en arriver là, calculer bien vos statistiques et vous aurez des chances à ce que cette cardinality feedback n'ait pas lieu du tout.

Pour voir quelles tables nécessitent un calcul des stats utilisez le plan d'exécution de votre requête et analyser les E-Rows et A-Rows de ce plan.

Il y a dans ce forum plusieurs exemples qui montrent comment avoir ce genre de plan d'exécution

[devkais]

Lorsque le CBO produit un plan d'exécution qu'il pense être le meilleur il se base alors sur les statistiques dont il a à sa disposition. Mais lorsque la requête est exécutée, le CBO, sous certaines conditions, utilise la technique de cardinality feedback pour s'auto informer sur les mauvaises estimations qu'il a faites. Il le sait car il vient d'exécuter la requête et a à sa disposition les Estimations (E-Rows) et les Actuals (A-Rows). Lors de la deuxième exécution il essaie alors d'utiliser ces informations pour générer un nouveau plan. Malheureusement cette histoire de cardinality feedback cause plus de problèmes qu'elle n'en résout.

Aussi dans votre cas, moi je ferai ceci

(a) re-calculer correctement les statistiques
(b) annuler l'utilisation de la cardinality feedback soit en utilisant un hint(local comme indiqué plus haut) soit au niveau session ou système(alter session set...).

Bonjour ,
Sous quelles conditions arrive ce phénomène vicieux dans ce cas ?

[shaun_the_sheep]

bonjour,

merci pour vos conseilles.

Je vois avec mon DBA pour les statistiques. Je vous tiens informé si cela change ou non quelque chose.

[Mohamed.Houri]

Bonjour ,
Sous quelles conditions arrive ce phénomène vicieux dans ce cas ?

https://blogs.oracle.com/optimizer/e...ality_feedback

[shaun_the_sheep]

Bonjour

Merci pour votre aide, j'ai transmis les éléments à mon DBA , on a recalculé les stats (sans effet) donc on a déactivé cette option pour le moment.

Ma requête retrouve ainsi des perfs d’exécution normales même après plusieurs exécutions simultanées.