Discussion :

[fatsora]

c'est qoui le resultat de
sho parameter optimizer_mode

Sinon

optimizer_index_caching 0
optimizer_index_cost_adj 100

==> favorise les FULL TABLE SCAN

[bouyao]

Le paramètrage est normal. Oracle devrait choisir le bon index, mais dans cette situation il préfère le FTS.

Essaye de reconstruire l'index sur un autre tablespace dedié aux indexes.

[neo.51]

c'est qoui le resultat de
show parameter optimizer_mode

les résultat est : ALL_ROWS

j'ai l'impression qu'on approche une piste là

optimizer_index_caching 0
optimizer_index_cost_adj 100

==> favorise les FULL TABLE SCAN

que ça favorise les full table scan je veux bien mais là quand même j'ai un index sur un champ et une requete avec une clause sur ce même champ !!! Je comprend pas qu'est-ce que j'ai de mal paramètrè dans oracle pour qu'il prèfère un full scan à l'utilisation de l'index !!!!

C'est la taille trop grosse de l'index qui lui ferait préférer un full scan ?!

Quel paramétrage me conseillez vous ? Mon paramètrage oracle il est simple : tout par défaut

[fatsora]

BOn OK

dans les base OLTP

ptet bien
all_rows a changer en first_rows

alter session set optimizer_mode=first_rows

mais dans la mesure ou la requete between utlise l'idex ....

fait voir le pour et le contre

entre
changer un parametre c'est pour toute la base ...
et
changer la requete

[neo.51]

Ce que j'aimerais bien comprendre c'est pourquoi il réagit comme ça (oui je sais je suis chiant j'aime bien comprendre )

Je vais essayer de changer l'index de tablespace mais je comprend pas comment oracle en arrive à préferer un full scan à un index sur une table de 15 million de rows !!!

[13thFloor]

-Pourquoi ça marche avec le between et pas avec ">" "<" ?
C'est une question de sélectivité.
Avec un between, l'optimiseur estime qu'il va ramener 0,25% des lignes de la table => index recherché
Avec > ou <, la sélectivité n'est que de 5% (je ne rentre pas dans les détails).

[13thFloor]

...changer un parametre c'est pour toute la base ...

A partir de la 10g release 2, le hint opt_param permet de positionner un paramètre au niveau requête.
Voir note Metralink 377333.1

De toute façon, le changement de code est préférable.

[neo.51]

changement de tablespace de l'index et aucun changement sur le plan d'exécution de ma requête !!!

C'est pas tant le problème de changer ma requête avec un between qui me chagrinne c'est le fait que je trouve qu'oracle ne réagit pas "normalement" avec une requête ultra simple... et je me dis que comprendre et résoudre ce problème m'aidera surement pour la suite...

[neo.51]

ok 13thFloor je comprends mieux. Je chercherais plus de documentation sur le sujet car ça m'intéresse et j'ai l'impression que je passe à coté de quelque chose dans ma compréhension d'oracle...

En tous cas merci à tous ceux qui se sont décarcassés pour me trouver une solution

et si vous avez des article sur le sujet n'hésitez pas

[13thFloor]

Ce que j'aimerais bien comprendre c'est pourquoi il réagit comme ça (oui je sais je suis chiant j'aime bien comprendre )

Je vais essayer de changer l'index de tablespace mais je comprend pas comment oracle en arrive à préferer un full scan à un index sur une table de 15 million de rows !!!

2 raisons :
1) le plan d'exécution est réutilisé. L'optimiseur n'analyse plus la requête puisqu'il l'a déjà fait SAUF si tu recalcules les statistiques sur la table avec invalidation des curseurs contenant cette table (ou si tu vides la zone mémoire contenat les plans d'exécution : alter system flush shared_pool
2) le coût (lecture de l'index+lignes de la table) est supérieur à celui de la lecture complète de la table.

[bouyao]

2 raisons :
1) le plan d'exécution est réutilisé. L'optimiseur n'analyse plus la requête puisqu'il l'a déjà fait SAUF si tu recalcules les statistiques sur la table avec invalidation des curseurs contenant cette table (ou si tu vides la zone mémoire contenat les plans d'exécution : alter system flush shared_pool
2) le coût (lecture de l'index+lignes de la table) est supérieur à celui de la lecture complète de la table.

Je suis d'accord avec toi, ca peut venir de ça.

[bouyao]

-Pourquoi ça marche avec le between et pas avec ">" "<" ?
C'est une question de sélectivité.
Avec un between, l'optimiseur estime qu'il va ramener 0,25% des lignes de la table => index recherché
Avec > ou <, la sélectivité n'est que de 5% (je ne rentre pas dans les détails).

Par contre la je ne suis pas sûre. Internalement oracle transforme le BETWEEN en > AND <, ce qui revient au même. Même le fait d'indiquer > AND < lui évite de faire cette transaformation.

Si le plan d'execution a été changé en modifiant > AND < en BETWEEN c'est par ce que le plan d'execution de la première requête était deja dans la memoire.

[neo.51]

Bon je comprend plus bien ce qu'il se passe là. Mes différent test sont bizzares :

->Si je fais un count en utilisant between ou > < pour retourner 0,4% des rows il utilise l'index.(57833/12257445).
->Si je fais un betwenn ou > < pour retourner 0,9% des rows il me fait encore un full scan !!! (119918/12257445).

Bref je sais pas comment il fait son compte mais en tous cas de mon coté l'utilisation de l'index divise les temps de réponse par 10 !!!

édit :

Par contre la je ne suis pas sûre. Internalement oracle transforme le BETWEEN en > AND <, ce qui revient au même. Même le fait d'indiquer > AND < lui évite de faire cette transaformation.

Si le plan d'execution a été changé en modifiant > AND < en BETWEEN c'est par ce que le plan d'execution de la première requête était deja dans la memoire.

Je confirme, j'ai changé les valeurs pour ne pas utiliser le plan d'exécution.

[bouyao]

Si tu veut vraiement favoriser l'index, alors dans ce cas il faut modifier le paramètre : optimizer_index_cost_adj par exemple 70 au lieu de 100 pour commencer.

[neo.51]

70 ça suffit pas 50 c'est bon. quels pourrait-être les incovénients de la modification de ce paramètre ?

[13thFloor]

Si tu veut vraiement favoriser l'index, alors dans ce cas il faut modifier le paramètre : optimizer_index_cost_adj par exemple 70 au lieu de 100 pour commencer.

Oula, j'en connais un qui va réagir, n'est-ce pas Orafrance ?

Pour inciter encore à utiliser l'index sans altérer les paramètres qui ont un impact sur les performances et l'indexation, un histogramme peut être nécessaire.

Code :

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

1
2
3
4
5
6
7
8
9
exec dbms_stats.gather_table_stats( 
ownname=> 'schéma', 
tabname=> 'POINTGPS , 
estimate_percent=> DBMS_STATS.AUTO_SAMPLE_SIZE, 
cascade=> DBMS_STATS.AUTO_CASCADE, 
degree=> null, 
no_invalidate=> DBMS_STATS.AUTO_INVALIDATE, 
granularity=> 'AUTO', 
method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

[bouyao]

On peut vérifier le bon paramètrage de optimizer_index_cost_adj en lançant ce script.

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
21
22
 
col c1 heading 'Average Waits for|Full Scan Read I/O'        format 9999.999
col c2 heading 'Average Waits for|Index Read I/O'            format 9999.999
col c3 heading 'Percent of| I/O Waits|for Full Scans'        format 9.99
col c4 heading 'Percent of| I/O Waits|for Index Scans'       format 9.99
col c5 heading 'Starting|Value|for|optimizer|index|cost|adj' format 999
 
 
select
   a.average_wait                                 c1,
   b.average_wait                                  c2,
   a.total_waits /(a.total_waits + b.total_waits)  c3,
   b.total_waits /(a.total_waits + b.total_waits)  c4,
   (b.average_wait / a.average_wait)*100           c5
from
   v$system_event  a,
   v$system_event  b
where
   a.event = 'db file scattered read'
and
   b.event = 'db file sequential read'
;

Je n'incite pas a changer ce paramètre qui influence totalement l'optimiseur.
Attention sur certains version 10g une valeur très bas peut mener à un bug qui oblige l'optimiseur a choisir FTS au lieu de l'Index Full Scan.
Sinon, tu peut diminuer le db_file_multiblock_read_count à 8 au lieu de 16, mais tous ça dépend de l'utilisation de la base.

[neo.51]

Donc la valeur C5 serait la valeur optimale pour mon paramètre optimizer_index_cost_adj ?