Discussion :

[grouhan]

Bonjour,

comment puis-je analyser le comportement de cette requête qui a un comportement aléatoire en terme de durée et de buffer gets ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT  ...
FROM  zcptesl0
WHERE
  cptesleta = :etab
  AND cpteslpla = :plan
  AND cpteslref = rpad(:refer, 26, ' ')
  AND cpteslobl = rpad(:coobl, 10, ' ')
  AND key_mbr = 'ZCPTESL0'
  AND cpteslage = :agence
  AND cptesldev = :devise
ORDER BY
  cptesleta, cpteslpla, cpteslobl, cptesldev, cpteslage, cpteslcom

[vanagreg]

Bonjour,

Pas super exploitable les infos. C'est quoi le plan d'exécution de la requête?

[grouhan]

Bonjour,

le plan d'exécution est le suivant

[Ikebukuro]

Ton plan d'exécution est peu utilisable, je ne sais pas comment tu l'as généré mais je te conseille de lire ceci :
http://dbaoraclesql.canalblog.com/ar.../39496756.html
http://dbaoraclesql.canalblog.com/ar.../39509234.html
http://dbaoraclesql.canalblog.com/ar.../39513680.html
http://dbaoraclesql.canalblog.com/ar.../39539467.html


Pourquoi avoir mis optimizer_index_cost_adj à 10? Si ce paramètre vaut moins de 100, tu veux favoriser les index; pourquoi ne pas laisser l'optimiseur décider du plan?
Le e-rows est à 1 : il nous faut le plan des deux exécutions, celle du 08/08 à 10h00 ou 11h00 posant pb et celle du 09/08 où c'est OK.
On voit que le nb de Buffer Gets explose le 08/08 mais pas pour 12h00 et 13h00 : très curieux car le Elapsed Time, lui, ne change pas...

Sais-tu si la volumétrie de ta table a fortement changé entre ces deux journées?
Est-ce que les paramètres influençant l'optimiseur ont changé entre le 08/08 et le 09/08?

[grouhan]

concernant optimizer_index_cost_adj à 10, je ne sais pas pourquoi il a été positionné avec cette valeur.

Je vais récupérer les plans d'exécutions avec la via DBMS_XPLAN.DISPLAY_CURSOR.

La table contient deux valeurs distinctes pour la colonne cptesleta. Cette table est vidée puis remplie lors de chaque exécution. Les stats ont été calculées table pleine mais avec la valeur cptesleta = 2 uniquement.

Dans les binds variables du plan, il indique 1 comme valeur de la colonne cptesleta , donc à priori la première requête exécutée est la requête avec la valorisation de cptesleta à 1 (aucunes données dans la table correspondante au moment de l'exécution de la requête)

Le pb est sur la requête avec cptesleta = 2. Peut-être qu'un autre plan serait plus optimale mais n'utilise t'il pas le plan calculé lors de la première exécution de la requête ? Ce plan est peut-être optimale pour cptesleta = 1 mais pas pour cptesleta = 2 ?

[Ikebukuro]

concernant optimizer_index_cost_adj à 10, je ne sais pas pourquoi il a été positionné avec cette valeur.

Mets ce paramètre à 100, c'est la valeur par défaut et voit si c'est mieux.

La table contient deux valeurs distinctes pour la colonne cptesleta. Cette table est vidée puis remplie lors de chaque exécution. Les stats ont été calculées table pleine mais avec la valeur cptesleta = 2 uniquement.
Le pb est sur la requête avec cptesleta = 2. Peut-être qu'un autre plan serait plus optimale mais n'utilise t'il pas le plan calculé lors de la première exécution de la requête ? Ce plan est peut-être optimale pour cptesleta = 1 mais pas pour cptesleta = 2 ?

Tu as TOUT compris, pour cptesleta = 1, Oracle génère un plan d'exécution Plan1, et pour cptesleta = 2, Oracle peut (ou non, selon l'histogramme sur cptesleta), réutiliser le même plan Plan1 ou bien en générer un autre, de nom Plan2, plus performant.
Il y a un histogramme sur la colonne cptesleta de cette table?

Ce qui est important, c'est quand tu dis "Cette table est vidée puis remplie lors de chaque exécution." : c'est normal que cette table soit vidée? Cela sert à quoi?
Je te conseille TRES TRES fortement, au cas où la volumétrie ou la sélectivité sur toutes les colonnes de ta clause WHERE (notamment cptesleta) change fortement.
Donc après avoir vidé et remplit ta table, appelle dbms_stats.gather_table_stats avec, si possible (si cela ne prend pas trop de temps), le paramètre estimate_percent à 90% ou 100%.

[grouhan]

Oui il y a bien un histogramme sur la colonne cptesleta de type FREQUENCE.

C'est une table de travail qui est alimentée avec les données du jour qui sont exploitée et la table est ensuite vidée.

Les stats ont été figées table pleine sur un jeu de données représentatif de la cible.

Oracle n'est-il pas censé utilisé un plan d'exécution différent si nécessaire via la fonctionnalité Active Cursor Sharing ?

[Ikebukuro]

C'est une table de travail qui est alimentée avec les données du jour qui sont exploitée et la table est ensuite vidée.
Les stats ont été figées table pleine sur un jeu de données représentatif de la cible.

C'est une bonne façon de traiter les tables très volatiles. Mais je te laisse vérifier ton affirmation "un jeu de données représentatif".

Oracle n'est-il pas censé utilisé un plan d'exécution différent si nécessaire via la fonctionnalité Active Cursor Sharing ?

Oui, mais, comme tu le dis, uniquement "si nécessaire". Si le CBO estime, d'après ses entrées (stats, paramètres, contraintes d'intégrité...), que le plan d'exécution en mémoire est OK, il n'a pas besoin d'en calculer un nouveau.

Et quid du paramètre optimizer_index_cost_adj ?
Tu devrais essayer de le mettre à 100.

[grouhan]

OK, merci,

Pas pu avoir le résultat avec le paramètre optimizer_index_cost_adj = 100

Je vous tiens informé

[Mohamed.Houri]

Bonjour,

Oracle n'est-il pas censé utilisé un plan d'exécution différent si nécessaire via la fonctionnalité Active Cursor Sharing ?

La notion d' Active Cursor Sharing n'existe pas. Par contre, il existe un ''feature'' appelé Adaptive(Extended) Cursor Sharing : ACS

Si vous voulez comprendre ce "feature" je vous invite à lire ce que j'ai écrit à ce sujet (beaucoup d'articles)

https://hourim.wordpress.com/?s=Adaptive+Cursor+sharing

Pour votre cas, il y a un histogram du type FREQUENCY sur votre requête: pour que cette requête soit candidate à ACS il faut d'abord que votre cursor soit bind sensitive

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
col object_status format a20
col end_of_fetch_count format 999
SELECT
     p.sql_id
	,p.child_number
    --,p.plan_hash_value
	,p.is_bind_sensitive bsens
	,p.is_bind_aware baware 
    --,p.first_load_time	
	--,p.last_load_time
    ,p.executions
	,p.end_of_fetch_count end_fetch
	,p.invalidations
	,p.object_status
 FROM   
    gv$sql p
where
    p.sql_id = '&sql_id'
and    
  p.is_shareable ='Y';

Je vous repondrai pourquoi votre requête ne change pas de plan automatiquement lorsque j'aurai le résultat de la requête précédente

Bien à vous
Mohamed Houri

[grouhan]

Bonjour,

l'ajout d'un index composée sur l'ensemble des critères de la clause where a permis de revenir à une situation plus stable.