Quand change le plan d'execution? [Résolu]

[kervoaz]

Bonjour,

je vous présente les données de ma question:
une table toute bête avec par exemple 2 colonnes: col1 et col2
la table comporte x rows et pour 1% des lignes col1 = Y
et 99% des lignes col1 = N
on met un index classique, donc dans notre cas de mauvaise sélectivité, sur col1.
Et on requête avec une clause where sur col1 avec une variable liée(col1=:var)
Vous m'arrêtez si je dis des bêtises mais dans ces conditions l'optimizer va sûrement préférer un full plutôt que prendre l'index (pour :var =Y ou N). Donc lorsque :var=Y on peut difficilement avoir un plan plus merdique.

Maintenant On rajoute un histogramme sur col1, tout à fait indiqué dans mon cas.
En admettant que la requete soit en memoire et que son plan actuel soit un full scan, si je fais ma query col1=:var avec :var=Y
QUE VA T'IL SE PASSER?
Le sql va t'il etre reparsé? le plan va t'il changé? si le sql n'est jamais dechargé de la memoire, le plan du full sera toujours choisit?

Merci pour votre aide

[orafrance]

oui, ça va changer parce que les stats sont recalculées

[kervoaz]

Bonjour,

ok donc maintenant je suis avec un plan d'exécution avec un index.
Si rien ne change par ailleurs(requête toujours en shared pool, pas de recalcul de stat,..),
Est ce que je peux me retrouver avec un full scan suivant la valeur de la bind variable?

Merci

[orafrance]

oui puisque tu as calculer les histogrammes. Si tu as un index déséquilibré il n'est pas impossible de faire un FULL SCAN. Mais attention, un FTS n'est pas forcément mauvais. Si une valeur retourne 90% (chiffre pris arbitrairement ) des données de la table, l'accés par index pourra être plus couteux.

[DAB.cz]

J'ai testé ça (10.1.0.4):

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
CREATE TABLE tb (col1 number, col2 char (1));
 
DELETE FROM tb;
INSERT INTO tb (col1, col2)
  SELECT level, decode (mod (level, 100), 1, 'Y', 'N')
    FROM dual
    connect BY level <= 5000000;
commit;
 
CREATE INDEX tbii ON tb (col2);
 
exec dbms_stats.gather_table_stats ('TEST', 'TB')
 
variable c char (1)
exec :c := 'N'
SELECT count (1) c
  FROM tb WHERE col2 = :c;
 
variable c char (1)
exec :c := 'Y'
SELECT count (1) c
  FROM tb WHERE col2 = :c;

Oracle choisit bon chemin (FTS ou index) d'après le premier ordre, mais ne la change pour une autre valeur.

DAB

[kervoaz]

Merci dab.cz,

c'est un peu là où je voulais en arriver.
A part de réécrire le sql sans bind variable, y a un gros problème.

De plus je n'ai rien trouvé pour forcer une requête à être reparsée ou déchargée de la mémoire.

Vous faites comment vous?

Merci

[DAB.cz]

Une manière brutale:

Code :

ALTER system FLUSH shared_pool;

Dans les cases spécial, je n'utiliserais pas de variable liée:

Code :

1
2
3
4
5
6
7
IF xxx then
  SELECT count (1) c INTO yyy
    FROM tb WHERE col2 = 'Y';
else
  SELECT count (1) c INTO yyy
    FROM tb WHERE col2 = 'N';
end IF;

DAB

[orafrance]

DAB.cz tu peux essayer en calculant les histogrammes sur la colonne et en déséquilibrant l'arbre... parce que ton test ne me semble pas juste

kervoaz quel est ton problème exactement, pourquoi tu veux parser systématiquement ta requête ?

[orafrance]

Envoyé par DAB.cz

Une manière brutale:

Code :

ALTER system FLUSH shared_pool;

tu parles d'un conseil... le paramètre cursor_sharing me parait plus indiqué

[kervoaz]

Merci pour vos réponses.

j'avais exclus l'alter volontairement

Mon problème est que l'on va avoir 1 chance sur 2 d'avoir un très mauvais plan.
Pour l'utilisateur le problème devient "aléatoire".
et Pour moi le problème est difficile à deceler et à corriger.

Cdt

[orafrance]

il suffit de tracer les sessions et comprendre pourquoi le plan change, encore une fois, je mets une petite piéce sur les histogrammes et la répartition des données dans la table

[kervoaz]

orafrance
dans mes hypothèses de départ il y a effectivement un histogramme sur la colonne(ce qui n'est pas reflété dans le code DAB.cz)

Cependant même avec les histogrammes, si le SQL n'est pas reparsé pourquoi le plan changerait-il? (qui est ma principale question).

[orafrance]

parce que le but de l'histogramme c'est justement de fournir au CBO les infos sur la répartition des données... pour moi, avec histogramme c'est quasiment toujours reparsé d'ailleurs si tes utilisateurs ont des résultats plus ou moins bon c'est bien que l'explain plan change non ?

[kervoaz]

Merci pour votre aide.
Il faut que je refasses une verif pour ne pas vous dire de bêtises.

Je vous dit ça rapidement

Merci

[DAB.cz]

voilà: 11g feature

DAB

[kervoaz]

ben voilà.
Les explications d'un pro sont bien plus claires que les miennes.

C'est exactement le problème que je rencontre en 9 et 10 avec une solution en 11.
Un grand merci

[13thFloor]

Il me semble que le paramètre _optim_peek_user_binds mis à false évite ce genre de situation (mauvais plan d'exec avec bind variable).
Il est à positionner en environnement SAP.

[LeoAnderson]

STOP : il s'agit d'un paramèter non documenté qui ne doit être activé que sur demande express du support (Oracle ou SAP par exemple).