Une question sur le forum montre une mauvaise performance de la requête suivante:
SELECT * FROM ( SELECT ... FROM ... WHERE condition_booleene = 'N' -- prédicat très selectif ORDER BY ... ) WHERE rownum < :variable
Exadata choisit un Smart Scan mais c’est long. Un index range scan est plus rapide même si l’index ne correspond pas à l’ordre de l’ORDER BY.
C’est bien sûr étonnant car cest le cas idéal du SmartScan: besoin de lire toute la table mais prédicat très selectif pour predicate offloading.
Lorsqu’on utilise rownum < :variable l’optimiseur passe en mode FIRST_ROWS, ce qui est logique puisqu’on ne va chercher que les premières lignes.
Mais Exadata fait une optimisation lorsqu’il sait qu’il va chercher peu de lignes: il ne démarre pas tout de suite en SmartScan car l’établissement d’une session SmartScan a un certain overhead et n’est pas justifié pour lire quelques lignes seulement. Il ne passe en SmartScan que s’il n’a pas trouvé rapidement ce qu’il cherchait.
Mais dans le cas présenté ici, avec l’ORDER BY, ça n’a pas de sens. Comme on fait un FULL SCAN, qui ne renvoit les lignes sans aucun ordre, il faudra lire toutes les lignes et les trier avant de pouvoir sortir les quelques premières. Et le tri ne se fait pas sur le Storage Cell. tout doit être renvoyé à la base. En bref, FIRST ROWS est un effet de bord du rownum, mais il n’est pas bon ici puisque il empêche le SmartScan de démarrer tout de suite. C’est probablement pourquoi l’accès par index est plus rapide. Si on ne fait pas de SmartScan, alors autant bénéficier d’un index, du buffer cache, et de flash cache…
Pour répondre à cette question du forum, j’ai fait un test sur une table de 32MB (lorsqu’elle est plus grosse, l’effet se voit moins) en faisant un:
select /*+ gather_plan_statistics */ * from ( select * from TEST order by n ) where rownum<=10
en désactivant cette fonctionnalité ‘FIRST ROWS’ avec le paramètre _kcfis_fast_response_enabled dont la description est ‘Enable smart scan optimization for fast response (first rows)’, puis avec l’environnement par défaut.
avec _kcfis_fast_response_enabled=false, les 4000 blocs de ma table sont lus:
------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.20 | 4019 | 4017 | |* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.20 | 4019 | 4017 | | 2 | VIEW | | 1 | 1000K| 10 |00:00:00.20 | 4019 | 4017 | |* 3 | SORT ORDER BY STOPKEY | | 1 | 1000K| 10 |00:00:00.20 | 4019 | 4017 | |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| TEST | 1 | 1000K| 1000K|00:00:00.13 | 4019 | 4017 | -------------------------------------------------------------------------------------------------------------------
avec _kcfis_fast_response_enabled=true (défaut) seulement la moitié:
------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 10 |00:00:00.21 | 2137 | 2135 | |* 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.21 | 2137 | 2135 | | 2 | VIEW | | 1 | 1000K| 10 |00:00:00.21 | 2137 | 2135 | |* 3 | SORT ORDER BY STOPKEY | | 1 | 1000K| 10 |00:00:00.21 | 2137 | 2135 | |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| TEST | 1 | 1000K| 1000K|00:00:00.12 | 2137 | 2135 | -------------------------------------------------------------------------------------------------------------------
Ça semble formidable … mais c’est impossible: on sait bien qu’il faut tout lire avant de pouvoir donner une réponse correcte.
En fait, c’est juste que Oracle a oublié de compter les blocs qu’il a lu avant de faire le SmartScan… ceux qui ont été lus de manière beaucoup moins efficace…
On comprend mieux sur les stats suivantes.
avec _kcfis_fast_response_enabled=false:
NAME VALUE ---------------------------------------------------------------- ------------------------ cell IO uncompressed bytes 33,120,256 cell blocks processed by cache layer 5,913 cell blocks processed by data layer 4,043 cell blocks processed by txn layer 5,913 cell physical IO bytes eligible for predicate offload 32,915,456 cell physical IO interconnect bytes 29,833,624 cell physical IO interconnect bytes returned by smart scan 29,825,432 cell scans 2 physical read bytes 32,923,648 physical read total bytes 32,923,648
On a 32MB à lire, et c’est ce qu’on fait. Dans mon cas, pas de prédicat ni projection, donc presque tout est renvoyé par SmartScan.
avec _kcfis_fast_response_enabled=true (défaut):
NAME VALUE ---------------------------------------------------------------- ------------------------ cell IO uncompressed bytes 17,596,416 cell blocks processed by cache layer 3,329 cell blocks processed by data layer 2,148 cell blocks processed by txn layer 3,329 cell num fast response sessions 1 cell num fast response sessions continuing to smart scan 1 cell physical IO bytes eligible for predicate offload 17,498,112 cell physical IO interconnect bytes 31,273,680 cell physical IO interconnect bytes returned by smart scan 15,848,144 cell scans 2 physical read bytes 17,506,304 physical read total bytes 32,923,648
On a seulement 17MB éligibles au SmartScan. Le reste a été lu sans SmartScan, au début de l’exécution, à cause du mode FIRST_ROWS (cell num fast response sessions), avant de passer en SmartScan (cell num fast response sessions continuing to smart scan).
Et malheureusement, les premières lectures ne sont pas comptées là où il faut: elles devraient être inclues dans ‘physical read bytes‘ et bien sûr dans les stats du plan d’exécution.
D’après les wait events, ces premières lectures étaient mono-bloc: ‘cell single block physical read’. C’est la raison pour laquelle elles sont plus lentes (c’est l’equivalent Exadata de db file sequential read).