Discussion :

[punkoff]

Bonjour,

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

La requête est de ce type :

Code :

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

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 ?

[Pomalaix]

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.

[punkoff]

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

Code :

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

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;
/

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

Code :

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

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 :

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

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 :

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

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 :

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

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.

[Pomalaix]

Vous êtes en Exadata ??

[punkoff]

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

[Waldar]

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 :

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

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.

Vous êtes en Exadata ??

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

[mnitu]

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).

[Mohamed.Houri]

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 :

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

1
2
TABLE ACCESS STORAGE FULL FIRST ROWS| T_MESSAGE

[punkoff]

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

Code :

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

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.

[Pomalaix]

C'est la clause STORAGE...

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

[pacmann]

Salut,

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

Code :

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

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 :

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

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

[mnitu]

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...

[pacmann]

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 ?

[Mohamed.Houri]

Très intéressante question

Code :

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

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?

[punkoff]

En désactivant les index :

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
 
-------------------------------------------------------------------------------------------------------------                                                                                                                                                                                               
| 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')

[mnitu]

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

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

[Waldar]

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.

[Mohamed.Houri]

Bonjour punkoff

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
 
-------------------------------------------------------------------------------------------------------------                                                                                                                                                                                               
| 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

[punkoff]

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 :

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

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')

[pachot]

Bonjour,

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 :

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

 _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.