Discussion :

[doc malkovich]

Hello,

J'ai un souci avec le calcul des stats sur Oracle, en version 9i et 11g.
Que ce soit avec un ANALYZE TABLE ou un DBMS_STATS.GATHER_TABLE_STATS, que ce soit en estimate ou en compute Oracle ne choisit pas le bon chemin, et je dois forcer via un hint le passage ou non par un index.
En regardant de plus près les cardinalités estimées sont fausses, ce qui explique le chemin choisi. Mais bon, les requêtes ne sont pas si compliquées, je m'étonne du résultat. Et puis je n'aime pas forcer via un hint; d'un environnement à un autre la situation pourrait être différente.
Aussi y a t-il d'autres méthodes pour "améliorer" les stats et le choix de l'optimiseur ?

ps : J'avoue utiliser la syntaxe basique ( et peu de paramètres ) lors du calcul

[mnitu]

Donnez nous plus des informations: requête, indexes, volumétrie, cardinalités estimés et réelles, paramètrage d'optimiseur, temps de réponses, ...

[doc malkovich]

Par exemple sur une requête ...

Ci joint les explain plan "anonymisés" ko ( choisi par Oracle ) et ok ( avec un hint FULL ).
La requête renvoie 121716 lignes.
Le hint évite de passer par un index - car d'après ce que je comprends Oracle s'attend en fait à n'avoir qu'environ 2000 lignes ... c'est ça ?

[mnitu]

Je ne vois pas d’accès via index. Je vois que du FULL. De plus je vois qu’il s’agit d’une requête distribué et que la différence entre les deux plans est lié à la méthode de jointure: Hash Join vers Nested Loop

[doc malkovich]

Le Nested Loop se fait sur du remote dans le cas de l'explain plan ko. On ne voit pas l'index mais je m'en doute car il n'y a pas de FULL, la cardinalité est à 1 et il y a un index bien placé sur la table ( pk ).

Et j'ai oublié de dire que la requête avec le nested loop met plus de 3h, celle sans met 4'.

[Mohamed.Houri]

Le Nested Loop se fait sur du remote dans le cas de l'explain plan ko. On ne voit pas l'index mais je m'en doute car il n'y a pas de FULL, la cardinalité est à 1 et il y a un index bien placé sur la table ( pk ).

Et j'ai oublié de dire que la requête avec le nested loop met plus de 3h, celle sans met 4'.

Comme l'a remarqué Marius, il y a un accès remote (dblink) et il y a aussi des tables partitionnées. Je remarque aussi que les deux explains plans montrent une cardinalité différente du nombre d'enregistrements que la requête est sensés retourné : 121716 lignes.

Savez-vous aussi qu’un explain plan amputé de sa partie ‘’Predicate’’ est un explain plan incomplet car ladite partie comprend des informations parfois cruciales (comme les conversions implicites qui empêchent l’utilisation des indexes). Ceci dit, pourquoi vous n’utilisez pas

Code :

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

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

Ceci vous montrera les estimations que le CBO est en train de faire par rapport au résultat final (E-Rows versus A-Rows) et donc éventuellement un problème de statistiques. Regardez aussi combien vaut la valeur du paramètre suivant

Code :

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

1
2
 
SHOW PARAMETER OPTIMIZER_INDEX_COST_ADJ

Car ceci peut avoir une influence sur le choix de l’index. La valeur par défaut est 100. Une valeur au-delà favorise les fulls table scan au lieu et place des accès via index. Pensez aussi, au cas où vous n’avez pas trouvé de solution, au hint /*+ driving_site */ mais uniquement si vous faite des selects.

[doc malkovich]

Merci de votre réponse.

Hélas ici je n'ai pas tous les droits, le

Code :

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

SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST')) ;

nécessite l'accès à V$_SESSION.

Bizarrement même le

SHOW PARAMETER OPTIMIZER_INDEX_COST_ADJ

ne marche pas.

[mnitu]

Pour le parameter ça peut être contourné

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
 
SQL> show parameter optimizer
ORA-00942: Table ou vue inexistante
 
 
SQL> variable r number
variable intval number
variable strval varchar2(30)
Begin
  :r := dbms_utility.get_parameter_value('OPTIMIZER_INDEX_COST_ADJ', :intval, :strval);
End;
/SQL> SQL> SQL>   2    3    4
 
Procédure PL/SQL terminée avec succès.
 
SQL> print intval
 
    INTVAL
----------
       100

[mnitu]

Il y a une différence importante dans les cardinalités sur la deuxième table accensée en REMOTE: 1 vers 167070. Si vous trouvez la réponse à la question que est-ce qu'il explique cette différence, vous avez la solution à votre problème.

[Mohamed.Houri]

Merci de votre réponse.

Hélas ici je n'ai pas tous les droits, le

Code :

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

SELECT * FROM TABLE(dbms_xplan.display_cursor(NULL,NULL,'ALLSTATS LAST')) ;

nécessite l'accès à V$_SESSION.

Pour pouvoir executer le package dbms_xplan.display_cursor() il faut avoir des droits d'accès aux vues dynamiques suivantes:

1. v$session
2. v$sql
3. v$sql_plan
4. v$sql_plan_statistics_all