Introduction
Vous savez qu'un ORDER BY dans une requête SQL va générer au niveau d'Oracle un SORT des données. Cette opération de tri est très consommatrice en terme de ressources mais, sur le coût total du plan d'exécution, ce coût peut être annulé en gagnant sur d'autres opérations. Nous allons voir qu'une requête avec un ORDER BY peut être aussi rapide voir même plus rapide qu'une requête sans le ORDER BY.



Points d'attention
Réfléchissez bien avant de faire un ORDER BY car cette opération est très lourde pour Oracle et dans certains cas, cela alourdit considérablement le temps d'exécution.



Base de tests
N'importe quelle base Oracle.



Exemples
============================================================================================
Création de notre base de test
============================================================================================
Pour vérifier notre hypothèse, il nous faut une table avec un nombre d'enregistrements assez important. Nous allons prendre des données de la table DBA_OBJECTS et les dupliquer dans notre table de test jusqu'à obtenir plus de 600 000 lignes.
          SQL> create table test_obj01 as select OWNER, OBJECT_NAME, SUBOBJECT_NAME from dba_objects;
          SQL> insert into test_obj01 select * from test_obj01;
          78417 rows created.

          SQL> /
          ...
          ...

          SQL> select count(*) from test_obj01;
          COUNT(*)
          ----------
          627352

On crée un ID dans la table avec la pseudo colonne ROWNUM puis on génère les stats.
          SQL> update test_obj01 set OWNER = OWNER || to_char(rownum);
          627352 rows updated.

          SQL> commit;
          SQL> exec dbms_stats.gather_schema_stats('HR');


============================================================================================
SELECT sans le ORDER BY
============================================================================================

Première exécution du SELECT sans ORDER BY : lecture des données sur disque dur, temps total 04 secondes 36 centièmes pour un coût de 1168.
          SQL> set autotrace traceonly explain statistics
          SQL> SET TIMING ON

         
SQL> select owner from test_obj01;
          627352 rows selected.

          Elapsed: 00:00:04.36

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2410895595

          ---------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                          | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT       |                      |   627K|  7351K|  1168     (1)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL     | TEST_OBJ01  |   627K|  7351K|  1168     (1)| 00:00:01 |
          --------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          27  recursive calls
          0  db block gets
          45886  consistent gets
          4285  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          5  sorts (memory)
          0  sorts (disk)
          627352  rows processed
    

Deuxième exécution du SELECT sans ORDER BY : les données sont lues uniquement en mémoire, temps total 04 secondes 13 centièmes pour un coût de 1168. Les physical reads sont à zéro, les consistent gets restent vers 46 000.
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.13

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2410895595

          -------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                      | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
          -------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT   |                       |   627K|  7351K|  1168     (1)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL | TEST_OBJ01  |   627K|  7351K|  1168     (1)| 00:00:01 |
          -----------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          45841  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          627352  rows processed
    

Troisième exécution du SELECT sans ORDER BY pour valider les données du SELECT précédent : temps total 04 secondes 19 centièmes pour un coût de 1168. Les physical reads sont à zéro, les consistent gets restent vers 46 000.    
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.19

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2410895595

          ---------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
          ---------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                        |   627K|  7351K|  1168     (1)| 00:00:01 |
          |   1 |  TABLE ACCESS FULL   | TEST_OBJ01   |   627K|  7351K|  1168     (1)| 00:00:01 |
          --------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          45841  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          627352  rows processed
    

============================================================================================
SELECT avec le ORDER BY
============================================================================================

On va maintenant exécuter le même ordre SQL mais en ajoutant un ORDER BY; Oracle ajoute une opération de SORT avec un coût de 4029, soit près de quatre fois le coût de lecture de toutes les données de la table.
          SQL> set autotrace traceonly explain statistics
          SQL> SET TIMING ON

          SQL> select owner from test_obj01 order by owner;
          627352 rows selected.

          Elapsed: 00:00:03.96

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2720153981

          -----------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                          | Name            | Rows    | Bytes |TempSpc| Cost (%CPU)| Time   |
          -----------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT       |                      |   627K|  7351K|                |  4029   (1)| 00:00:01 |
          |   1 |  SORT ORDER BY            |                      |   627K|  7351K|    12M      |  4029   (1)| 00:00:01 |
          |   2 |   TABLE ACCESS FULL    | TEST_OBJ01  |   627K|  7351K|                |  1168   (1)| 00:00:01 |
          -----------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          27  recursive calls
          0  db block gets
          4328  consistent gets
          4285  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
          627352  rows processed

On réexécute le SELECT, pour ne lire les données qu'en SGA.
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.10

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2720153981

          ----------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name            | Rows    | Bytes |TempSpc| Cost (%CPU)| Time    |
          ----------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                      |   627K   |  7351K|            |  4029   (1)| 00:00:01   |
          |   1 |  SORT ORDER BY          |                      |   627K   |  7351K|    12M  |  4029   (1)| 00:00:01   |
          |   2 |   TABLE ACCESS FULL  | TEST_OBJ01 |   627K    |  7351K|            |  1168   (1)| 00:00:01   |
          ----------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4283  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          627352  rows processed

Troisième exécution.
          SQL> /
          627352 rows selected.

          Elapsed: 00:00:04.09

          Execution Plan
          ----------------------------------------------------------
          Plan hash value: 2720153981

          ------------------------------------------------------------------------------------------------------------------------------------------------
          | Id  | Operation                        | Name            | Rows    | Bytes |TempSpc  | Cost (%CPU)| Time    |
          ------------------------------------------------------------------------------------------------------------------------------------------------
          |   0 | SELECT STATEMENT     |                      |   627K|  7351K|                  |  4029   (1)| 00:00:01  |
          |   1 |  SORT ORDER BY          |                      |   627K|  7351K|    12M        |  4029   (1)| 00:00:01  |
          |   2 |   TABLE ACCESS FULL  | TEST_OBJ01  |   627K|  7351K|                  |  1168   (1)| 00:00:01  |
          ------------------------------------------------------------------------------------------------------------------------------------------------

          Statistics
          ----------------------------------------------------------
          0  recursive calls
          0  db block gets
          4283  consistent gets
          0  physical reads
          0  redo size
          15681662  bytes sent via SQL*Net to client
          460660  bytes received via SQL*Net from client
          41825  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          627352  rows processed


============================================================================================
Comparaison entre les deux SELECT : le ORDER BY ne ralentit pas le SELECT
============================================================================================

Nous n'allons comparer que les temps d'exécution après le premier SELECT puisque, lors du premier, les données sont sur disque dur et pas lors des SELECT suivants.
Sans ORDER BY
          Temps d'exécution : 04 secondes 13 et 04 secondes 19
          Consistent gets : 45 841 et 45 841

Avec ORDER BY
          Temps d'exécution : 04 secondes 10 et 04 secondes 09
          Consistent gets : 4 283 et 4 283
Opération en plus : SORT ORDER BY pour un coût de 4029


Comment expliquer qu'une opération de coût 4029 induite par le ORDER BY, soit quatre fois plus que le Full Table Scan de la table, ne ralentisse pas du tout la requête et même, on croit rêver, s'offre le luxe d'avoir une requête plus rapide de quelques dixièmes de secondes?

L'explication m'a été donné par Franck PACHOT, un DBA certifié Oracle Master 12c : sans ORDER BY, Oracle va lire N fois le même bloc dans le Database Buffer Cache, N variant selon ARRAYSIZE, pour récupérer toutes les données du bloc. MAIS, attention, Oracle fait à chaque lecture un Consistent get, ce qui a un certain coût. Avec le ORDER BY, Oracle déplace les blocs de données du Database Buffer Cache de la SGA vers la zone "SQL Work Area" de la PGA du process serveur. L'explication finale est que Oracle ne lit pas les blocs de la même façon dans la PGA que dans la SGA puisque le nombre de Consistents gets est divisé par 10. SGA signifie System Global Area mais avant cela SGA signifiait Shared Global Area, la zone partagée! Comme c'est une zone partagée par toutes les sessions, Oracle gére chaque bloc en SGA avec des latches/mutex/pins pour éviter des problèmes d'accès concurrents et c'est cette gestion qui est très coûteuse en SGA alors qu'en PGA il n'y a aucun accès concurrent puisque cette zone est réservée au process serveur.

Pour lire la même quantité de données, Oracle fait dans un cas 45 841 Consistents gets et dans l'autre cas seulement 4 283. C'est donc sur cette action, la façon de lire les blocs de données, que le ORDER BY est plus performant et permet d'annuler le surcoût de l'opération du SORT.