Non utilisation d'un index (optimiseur)

**Mohamed.Houri** · 17/10/2013, 08h58

je ne comprend pas ce que vous voulez.

Comme je l’ai signalé dans mon précédent poste, j’essaie d’être sûr que le smart scan et le predicate offloading n’ont pas eu lieu malgré la présence de deux indices (storage full et storage()). Pour cela il faut savoir si votre select en FULL table scan a été fait via db file scattered read ou via direct path read. Dans votre cas, vous dites que cela prend 15 secondes. La requête a donc été monitorée. Le plus simple serait donc de prendre le sql_id de cette requête et faire ceci

Code :

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

1
2
3
4
5
6
7
8
 
SPOOL c:\exadata_first.htm
SELECT DBMS_SQLTUNE.report_sql_monitor(
  sql_id       => 'afwx9adju2rsp', -- votre sql_id ici
  type         => 'HTML',
  report_level => 'ALL') AS report
FROM dual;
SPOOL OFF;

Et voir par quel moyen le FULL table scan a été fait (allez avec la souris sur la partie Wait Activity pour voir apparaître un hint indiquant le temps d’attente correspondant).

Vous pouvez aussi exécuter la requête suivante:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
SELECT 
    ROUND(physical_read_bytes/1048576) phyrd_mb 
  , ROUND(io_cell_offload_eligible_bytes/1048576) elig_mb 
  , ROUND(io_interconnect_bytes/1048576) ret_mb 
  , (1-(io_interconnect_bytes/NULLIF(physical_read_bytes,0)))*100 "SAVING%" 
  FROM 
v$sql 
  WHERE 
     sql_id = '9n2fg7abbcfyx' -- votre sql_id
 AND child_number = 0;        -- le child_number correspondant (0 probablement)

Dans le cas où phyrd_mb = ret_mb, ceci signifierait alors que le smart scan n’a pas été capable de réduire le trafic entre les cellservs et le serveur DB.

Enfin, comme l’a signalé Franck, il y a un bug ou un mauvais fonctionnement dans les DB non exadata lorsqu’un ORDER BY est combiné avec le mode FIRST_ROWS (where ROWNUM <=1) qu’il faudrait évaluer ici grâce à votre cas bien que cela ne me semble pas tout à fait correspondre car dans les DB non exadata on voit un FULL INDEX SCAN alors qu’ici on voit un INDEX RANGE SCAN.

En résumé, pourriez-vous faire ce qui est ci-dessus et aussi évaluer les performances sans hint mais avec ceci:

1) Sans rownum
2) Sans order by mais avec rownum
3) Sans order by et sans rownum

**punkoff** · 17/10/2013, 09h56

Envoyé par Mohamed.Houri

Dans le cas où phyrd_mb = ret_mb, ceci signifierait alors que le smart scan n’a pas été capable de réduire le trafic entre les cellservs et le serveur DB.

Bonjour,

C'est effectivement le cas.

Le calcul de ELIG_MB vaux 0 et j'ai bien phyrd_mb = ret_mb

Concernant ceci :

1) Sans rownum
2) Sans order by mais avec rownum
3) Sans order by et sans rownum

Même si le plan change, le temps d’exécution est identique dans les 4 cas possibles.

Sinon pour la méthode DBMS_SQLTUNE.report_sql_monitor, le repport généré est assez illisible :/
Je n'ai rien dans les cases sous les titres "Wait event Time".

**punkoff** · 17/10/2013, 10h36

bon,

Du coup j'ai cherché dans v$sql des plan pour lesquels il y avait des données supérieur à 0 dans la colonne io_cell_offload_eligible_bytes.

J'ai trouvé une requete assez similaire à celle utilisé pour cet exemple, qui est executée sur une table assez similaire en taille (2.2go pour 2millions ligne) / structure.

Ce coup-ci le smartscan est activé.

requete avec 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
22
23
24
 
1456 rows selected.
Elapsed: 00:00:01.11
 
PLAN_TABLE_OUTPUT  
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  bd78ahf9cpf4c, child number 0                                                                                                                                                                                                                                                                       
-------------------------------------                                                                                                                                                                                                                                                                       
select * from YYY.T_MAP_GPS_WRK where traite = 'N'                                                                                                                                                                                                                                                       
 
Plan hash value: 1029201127                                                                                                                                                                                                                                                                                 
 
---------------------------------------------------------------------------------------------                                                                                                                                                                                                               
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                               
---------------------------------------------------------------------------------------------                                                                                                                                                                                                               
|   0 | SELECT STATEMENT            |               |       |       |    19 (100)|          |                                                                                                                                                                                                               
|   1 |  TABLE ACCESS BY INDEX ROWID| T_MAP_GPS_WRK |  1370 | 90420 |    19   (0)| 00:00:01 |                                                                                                                                                                                                               
|*  2 |   INDEX RANGE SCAN          | IDX_TST       |  1370 |       |     5   (0)| 00:00:01 |                                                                                                                                                                                                               
---------------------------------------------------------------------------------------------                                                                                                                                                                                                               
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
 
   2 - access("TRAITE"='N')

Requete sans 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
22
23
24
25
 
1456 rows selected.
Elapsed: 00:00:01.18
 
PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                           
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  g801z8ybfq28k, child number 0                                                                                                                                                                                                                                                                       
-------------------------------------                                                                                                                                                                                                                                                                       
select /*+ full(T_MAP_GPS_WRK)*/ * from YYY.T_MAP_GPS_WRK where                                                                                                                                                                                                                                          
traite = 'N'                                                                                                                                                                                                                                                                                                
 
Plan hash value: 544008852                                                                                                                                                                                                                                                                                  
 
-------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
| Id  | Operation                 | Name          | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                 
-------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
|   0 | SELECT STATEMENT          |               |       |       | 78612 (100)|          |                                                                                                                                                                                                                 
|*  1 |  TABLE ACCESS STORAGE FULL| T_MAP_GPS_WRK |  1370 | 90420 | 78612   (2)| 00:15:44 |                                                                                                                                                                                                                 
-------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
 
   1 - storage("TRAITE"='N')                                                                                                                                                                                                                                                                                
       filter("TRAITE"='N')

Résultat de la requete sur v$sql pour connaitre l'utilité du smartscan :

Code :

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

1
2
3
4
 
phyrd_mb  elig_mb  ret_mb  SAVING%
-------------------------------------------
2202	2202	0	99,9949039061502

Cette requête est executer sur la même base (schéma différent).

Du coup je ne comprend pas bien pourquoi el smartscan ne c'est pas effectué sur la 1ere requete étudiée dans ce poste.

Y a-t-il des options à mettre au niveau de la création de la table ?
des options au niveau de la session ?

**Mohamed.Houri** · 17/10/2013, 10h59

Dans ce cas(phyrd_mb = ret_mb) c'est clair que le smart scan offlading n'a pas eu lieu ce qui tend fortement à signifier que la lecture s'est faite via buffer cache (db file scattered read).

Si vous êtes en test (et je précise bien en test) vous pouvez jouer avec le paramètre _small_table_threshhold en lui attribuant une valeur très petite pour tromper le moteur SQL en lui faisant croire que votre table est énorme ce qui va peut être le conduire à faire un direct path read. Et là dites nous si la performance change ou pas?

**punkoff** · 17/10/2013, 11h35

cf ce lien : http://www.centroid.com/knowledgebas...hold-important

J'ai essayé avec un alter session, rien n'a changé.
(j'ai aussi pour le coup essayé l'option _serial_direct_read, vu que pour lui ca avait fonctionné)

Par contre, par rapport à mon post précédent :
- T_MESSAGE : pas de smartscan => 442 368 blocks
- T_MAP_GPS_WRK : smartscan actif => 286 720 blocks

Mon buffer cache fait : 2go384

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

Rien n'a changé non plus.

**Mohamed.Houri** · 17/10/2013, 11h52

Par contre, par rapport à mon post précédent :
- T_MESSAGE : pas de smartscan => 442 368 blocks
- T_MAP_GPS_WRK : smartscan actif => 286 720 blocks

Mon buffer cache fait : 2go384

Une remarque importante que vous avez signalée au début de votre poste et que j'ai négligée s'avère peut-être cruciale ici à savoir:

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.

Est-ce que l'autre table T_MAP_GPS_WRK bouge elle aussi?

Il se peut (c'est même certain) que le select sur la première table (T_MESSAGE) se fait avec une lecture consistante (read consistency) qui doit donc utiliser les undos et le buffer cache pour appliquer ces undos à des copies de block de votre table avant de renvoyer une image consistante; ceci à mon sens ne peut pas se faire au niveau cellservs. Ce qui expliquerait pourquoi le smart scan n'a pas eu lieu.

Essayer de stopper les updates sur cette tables et voyez les conséquences d'un nouveau select sur une table stable.

**punkoff** · 17/10/2013, 12h03

Envoyé par Mohamed.Houri

Est-ce que l'autre table T_MAP_GPS_WRK bouge elle aussi?

Les deux tables fonctionnent de la même maniere.

Elles sont associés à des process qui inject des données en continues dedans et pose un flag à "non traité".

Puis un job dépile ces lignes et passe le flag à "traité".

Sinon j'ai ramené les schémas dans une autre base afin de pouvoir faire des tests.

La table T_MESSAGE n'est plus alimentée du tout, mais j'observe exactement le même résultat qu'en production (pas de smartscan sur cette table, par contre smartscan actif sur T_MSG_GPS_WRK)

**punkoff** · 17/10/2013, 14h01

ah j'ai trouvé.

Ce lien m'a mis sur la piste : http://www.acehints.com/2011/12/what...r-exadata.html

In below mentioned situations the exadata predicate evaluation will not offloaded :

A LOB or LONG column is being used in the query

la table T_MESSAGE possède une colonne de type CLOB.

Si à la place du SELECT *, je sélectionne toutes les colonnes sauf le CLOB, le smartscan se déclenche.

edit: ceci étant dit, les perfs avec l'index sont quand même meilleur pour ce cas, du coup je ne penses pas l’enlever.

**Mohamed.Houri** · 17/10/2013, 15h33

merci pour le suivi.

**pacmann** · 22/10/2013, 15h34

Envoyé par 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.

Sisi, c'est bien une colonne cachée qui est créée pour la FBI

Du coup j'ai posté une petite illustration sur mon blog pour immortaliser ton intervention :
http://pacmann.over-blog.com/article...120725858.html

Non utilisation d'un index (optimiseur) [11gR2]

SQL Oracle

Discussions similaires

Partager

Partager