Non utilisation d'un index (optimiseur)

Bonjour,

J'ai un problème avec une requête.

La requête est de ce type :

Code:

---

1 2 3 4 5 6 7

select * from ( select col_a, col_b, ..... from ma_table where condition_booleene = 'N' order by col_date ASC ) where rownum < :variable_bindée

---

Il existe un index de ce type : condition_booleene, col_date asc

la table fait en gros 2,5 millions de lignes et la condition : condition_booleene = 'N' retourne en gros moins de 100 lignes.


J'ai bien calculer les stats avec toad avant de faire un explain, mais celui-ci force le scannage de table sur ma_table au lieu de partir de l'index.

Il en découle que la requête met 3 plombes à s’exécuter, alors que si je force l'utilisation de l'index celle-ci s’exécute instantanément (ou presque).


Bref, comment puis-je orienter mes recherches afin que l'optimiseur me sorte un plan potable pour cette requête sans passer par un hint oracle ?

Vu votre problème, il vous manque probablement des histogrammes dans vos statistiques.

Mais mine de rien, certains éléments de votre présentation sont très imprécis.

1) "Calculer les stats", si on ne sait pas avec quelles options, ça ne renseigne pas sur ce que vous avez vraiment fait.

2) "Faire un explain" n'est pas clair non plus si on ne sait pas par quelle commande vous le faites.
Si vous faites appel à la commande EXPLAIN PLAN, ou AUTOTRACE, vous générez un plan d'exécution théorique, mais qui n'est pas forcément celui réellement utilisé.
De plus, cette méthode se marie mal avec les variables de liaison.

Pour obtenir le plan réel, il faut passer par DBMS_XPLAN.DISPLAY_CURSOR.

Citation:

---

Envoyé par Pomalaix

1) "Calculer les stats", si on ne sait pas avec quelles options, ça ne renseigne pas sur ce que vous avez vraiment fait.

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12

BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS ( OwnName => 'XXXX' ,TabName => 'T_MESSAGE' ,Estimate_Percent => 10 ,Method_Opt => 'FOR ALL COLUMNS SIZE 1' ,Degree => 4 ,Cascade => FALSE ,No_Invalidate => FALSE); END; /

---

Citation:

---

2)
Pour obtenir le plan réel, il faut passer par DBMS_XPLAN.DISPLAY_CURSOR.

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11

select * from ( select * from XXXX.t_message where sync = 'N' order by hor_cli asc) where rownum < 100;   set linesize 10000   select * from table(dbms_xplan.display_cursor);

---

Code:

---

1 2 3 4 5 6 7 8 9 10

------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 306K(100)| | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 820K| 1663M| | 306K (1)| 01:01:21 | |* 3 | SORT ORDER BY STOPKEY | | 820K| 889M| 1068M| 306K (1)| 01:01:21 | |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| T_MESSAGE | 820K| 889M| | 111K (1)| 00:22:15 | -------------------------------------------------------------------------------------------------------------

---

Temps exec : ~15 sec (temps moyen dans le rapport AWR : 12sec)

-----------------------------
Avec l'hint :

Code:

---

1 2 3 4 5 6 7 8 9 10 11

select * from ( select /*+ index(t_message, idx_sync) */ * from XXXX.t_message where sync = 'N' order by hor_cli asc) where rownum < 100;   set linesize 10000   select * from table(dbms_xplan.display_cursor);

---

Code:

---

1 2 3 4 5 6 7 8 9 10 11

---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 680K(100)| | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 820K| 1663M| | 680K (1)| 02:16:07 | |* 3 | SORT ORDER BY STOPKEY | | 820K| 889M| 1068M| 680K (1)| 02:16:07 | | 4 | TABLE ACCESS BY INDEX ROWID| T_MESSAGE | 820K| 889M| | 485K (1)| 01:37:01 | |* 5 | INDEX RANGE SCAN | IDX_SYNC | 820K| | | 2365 (1)| 00:00:29 | ----------------------------------------------------------------------------------------------------

---

Temps exec : ~250ms

cette table est constament alimenté.
Chaque nouvelle ligne qui est insérée possède la condition spécifiée : SYNC = 'N'

Un batch scrut toutes les minutes cette table et met à 'O' la colonne SYNC des lignes traitées.

En pic on a moins de 1000 lignes avec la condition SYNC = 'N'.

Pour rappel cette table dispose d'environ 2,5 millions de lignes, et une partie de la table est purgée chaque soir.


=> problème de stat mais mes compétences en oracle sont insufisante pour savoir quoi faire.

Vous êtes en Exadata ??

Citation:

---

Envoyé par Pomalaix

Vous êtes en Exadata ??

---

oui, est-ce problématique ?

sinon je cherche dans ce sens au vu de vos commentaires : http://www.aide-oracle.net/2010/03/l...togrammes.html

Avec la méthode FOR ALL COLUMNS SIZE 1, vous n'avez pas construit d'histogramme.

Il faut vérifier dans user_tab_col_statistics sa présence.

Repassez ensuite les stats comme ceci :

Code:

---

1 2 3 4 5 6 7 8 9

BEGIN SYS.DBMS_STATS.GATHER_TABLE_STATS ( OwnName => 'XXXX' , TabName => 'T_MESSAGE' , Estimate_Percent => 100 , Degree => 4 , Cascade => TRUE); END; /

---

Et vérifiez s'il y a la présence d'un histogramme.

Citation:

---

Envoyé par Pomalaix

Vous êtes en Exadata ??

---

Qu'est-ce qui l'indique dans les messages de punkoff, j'ai beau chercher je ne trouve pas.

L’optimiseur n’utilise pas le plan avec l’index parce-que il le trouve plus cher (regardez le coût). La raison pour laquelle c’est plus cher est due au manque des informations sur la distribution non-uniforme des valeurs de la colonne sync. Pour cela il a besoin des histogrammes mais la valeur du paramètre method_opt il lui dit de ne pas les calculer. Essayez de recalculer les statistiques pour cette table avec la valeur SIZE AUTO (à ce que j'ai compris exadata est un peu spécial).

Citation:

---

Envoyé par Waldar

Avec la méthode FOR ALL COLUMNS SIZE 1, vous n'avez pas construit d'histogramme.

Qu'est-ce qui l'indique dans les messages de punkoff, j'ai beau chercher je ne trouve pas.

---

C'est la clause STORAGE

Code:

---

1 2	TABLE ACCESS STORAGE FULL FIRST ROWS| T_MESSAGE

---

Merci, bien compris. (pas encore dans tous les détails mais globalement)

Code:

---

1 2 3 4 5 6 7 8 9 10 11

-------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 102 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 160 | 332K| 102 (1)| 00:00:02 | |* 3 | SORT ORDER BY STOPKEY | | 160 | 177K| 102 (1)| 00:00:02 | | 4 | TABLE ACCESS BY INDEX ROWID| T_MESSAGE | 160 | 177K| 101 (0)| 00:00:02 | |* 5 | INDEX RANGE SCAN | IDX_SYNC | 160 | | 3 (0)| 00:00:01 | --------------------------------------------------------------------------------------------

---

Au niveau de la table dba_histogramms j'ai bien 3 entrées pour les trois valeurs possible de la colonne.

Citation:

---

Envoyé par Mohamed.Houri

C'est la clause STORAGE...

---

Mince, vous n'avez pas laissé le mystère planer longtemps ! :cry:

Salut,

Tiens juste au passage, dans ce genre de situation, j'aime bien créer un function based index un peu spécial :

Code:

---

CREATE INDEX xxx ON t_message (nullif(sync, 'Y',))

---

L'index est alors tout petit, puisqu'il ne stocke pas les 2,5 millions de rowid du 'Y'.

Le filtre doit être alors réécrit pour matcher le fbi :

Code:

---

1 2	WHERE nullif(sync, 'Y') = 'N'

---

Citation:

---

Envoyé par pacmann

Tiens juste au passage, dans ce genre de situation, j'aime bien créer un function based index un peu spécial ...

---

Salut pacmann,

Je ne vois pas d'avantage donc si t'as des détails à apporter...

Ben ça te fait un index tout petit, c'est toujours mieux (stockage, b tree level et tout ça)... et au moins t'es sûr qu'il ne va jamais te prendre l'index pour la valeur présente 2.5 millions de fois.

Tu trouves pas ça sympa ? :)

Très intéressante question

Code:

---

1 2 3 4 5 6 7 8 9 10

------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 306K(100)| | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 820K| 1663M| | 306K (1)| 01:01:21 | |* 3 | SORT ORDER BY STOPKEY | | 820K| 889M| 1068M| 306K (1)| 01:01:21 | |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| T_MESSAGE | 820K| 889M| | 111K (1)| 00:22:15 | -------------------------------------------------------------------------------------------------------------

---

Normalement l'inverse aurait été plus logique. Cela va plus vite en full table scan qu'en index range scan. Vous êtes en Exadata et votre ''mauvais'' plan montre déjà qu'un ''smart scan'' est possible car on voit la clause storage dans le plan d'exécution. Ce qui manque c'est la partie prédicats. Il faut toujours poster la partie prédicat et la version d'Oracle. Ici on sait que c'est du 11gR2 car c'est de l'Exadata.

Si dans la partie prédicats il n'y a pas la clause storage alors le ''smart scan'' qui représente la force d'exadata n'a pas eu lieu. Et dans ce cas, c'est comme si on a basculé dans une base de données normale ou les accès via indexes sont plus performants (pour les petits volumes) que via les tables full scan.

Est-ce que vous pouvez poster la partie prédicats?

En désactivant les index :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 314K(100)| | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 844K| 1713M| | 314K (1)| 01:02:58 | |* 3 | SORT ORDER BY STOPKEY | | 844K| 916M| 1100M| 314K (1)| 01:02:58 | |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| T_MESSAGE | 844K| 916M| | 113K (1)| 00:22:42 | -------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter(ROWNUM<100)   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 3 - filter(ROWNUM<100) 4 - storage("SYNC"='N') filter("SYNC"='N')

---

Citation:

---

Envoyé par pacmann

Ben ça te fait un index tout petit,...

---

Ok je vois mieux maintenant l'idée.
Merci.

Le "problème" de l'index de fonction c'est qu'Oracle rajoute une colonne cachée à la table et que tu dois modifier ton SQL derrière pour en profiter.

C'est une fonctionnalité qui existe chez SQL-Server, les index filtrés.
Pas d'équivalent chez les rouges ?

Edit : apparemment non, par contre on peut rajouter une colonne virtuelle et l'indexer directement. Il faut toujours un peu réécrire le code mais c'est plus transparent que nullif.

Bonjour punkoff

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21

------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | 314K(100)| | |* 1 | COUNT STOPKEY | | | | | | | | 2 | VIEW | | 844K| 1713M| | 314K (1)| 01:02:58 | |* 3 | SORT ORDER BY STOPKEY | | 844K| 916M| 1100M| 314K (1)| 01:02:58 | |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| T_MESSAGE | 844K| 916M| | 113K (1)| 00:22:42 | -------------------------------------------------------------------------------------------------------------   Predicate Information (IDENTIFIED BY operation id): ---------------------------------------------------   1 - filter(ROWNUM<100)   PLAN_TABLE_OUTPUT ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ 3 - filter(ROWNUM<100) 4 - storage("SYNC"='N') filter("SYNC"='N')

---

J'espère que vous allez repasser par là et répondre à ma question. Ce sujet m'interpelle car je n'ai pas encore tout saisi. Exadata étant fait pour accélérer les lectures physique grâce au smart scan et au predicate offloading, il est donc forcément plus avantageux dans cette machine d'avoir des full tables scans. Dans votre cas, c'est un index range scan qui est plus rapide (même en l'absence d'agrégation dans le select).

A partir des réponses que vous avez publiées, il s'avère que 2 conditions sur 3 sont remplies pour un smart scan à savoir

(1) TABLE ACCESS STORAGE FULL FIRST ROWS
(2) 4 - storage("SYNC"='N')
filter("SYNC"='N')

Il manque une troisième condition importante : c'est que le full table scan a été fait via un direct path read.

Pourriez-vous s'il vous plaît voir avec quel 'event' le full table scan a lieu?

En plus, ce qui est intriguant ici c'est l'apparition du terme FIRST ROWS qui est certainement lié à l'utilisation du rownum <100

Merci d'avance pour votre réponse

Citation:

---

Envoyé par Mohamed.Houri

Pourriez-vous s'il vous plaît voir avec quel 'event' le full table scan a lieu?

---

Bonjour,

je ne comprend pas ce que vous voulez.


concernant le FIRST ROW si j'enleve le row num on obtient ca :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

---------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 120K(100)| | | 1 | SORT ORDER BY | | 451 | 501K| 120K (1)| 00:24:01 | |* 2 | TABLE ACCESS STORAGE FULL| T_MESSAGE | 451 | 501K| 120K (1)| 00:24:01 | ----------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------   2 - storage("SYNC"='N') filter("SYNC"='N')

---

Bonjour,

Citation:

---

Envoyé par Mohamed.Houri

En plus, ce qui intriguant ici c'est l'apparition du terme FIRST ROWS qui est certainement lié à l'utilisation du rownum <100

---

J'ai été intrigué aussi ;)
Il me semble que le STORAGE FULL FIRST ROWS est une 'optimisation' qui est plutôt néfaste dans ce cas, parce qu'il y a un ORDER BY.

J'ai détaillé cette idée dans mon blog

Dans mon exemple il n'y a pas de grosse différence de temps car je lis toutes les lignes et toutes les colonnes, donc le SmartScan n'est pas si efficace. Mais dans l'exemple de punkoff où la condition est très sélective, la différence peut être importante. Pas étonnant que l'accès par index soit plus rapide.

Ce serait intéressant d'essayer (pas en prod) avec:

Code:

---

_kcfis_fast_response_enabled=false

---

afin de désactiver cette fonctionnalité afin que le SmartScan démarre tout de suite. Et peut-être d'ouvrir un bug auprès du support Oracle.

Cordialement,
Franck.