ORDER BY et Consistents gets réduits par 10 : un ORDER BY ne ralentit pas toujours une requête! - ORDER BY and Consistents gets
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 les blocs de données sur le disque dur et les écrire dans la SGA. Il lira ensuite N fois le même bloc dans cette SGA (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 d'une ligne un Consistent get, ce qui a un certain coût. Avec le ORDER BY, Oracle écrit les blocs de données dans la zone "SQL Work Area" de la PGA du process serveur et non pas dans la SGA et là il ferait un Consistent get par bloc; en tout cas pas un Consistent get par ligne.
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; a contrario, en PGA il n'y a aucun accès concurrent puisque cette zone est réservée au process serveur. Pour rappel, la PGA veut dire Program Global Area mais certains l'appellent Private Global Area...
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.
Dernier point : quand Oracle exécute un SELECT sans ORDER BY, les blocs sont lus du disque dur, écris en SGA puis, dans la SGA, Oracle récupère les colonnes voulues par le SELECT et, c'est important, copies les données dans la PGA. Cette étape est indispensable car qui retourne les données au process client? C'est le process serveur or celui-ci utilise la PGA pour stocker les données à transférer (sauf erreur de ma part!). A l'inverse, si on a un SELECT avec un ORDER BY, les blocs sont lus du disque dur, écris en PGA et traités directement dans celle-ci avant envoi au client;on économise donc une copie de données de la SGA vers la PGA, ce qui a forcément un coût.
Un autre test, pour valider tout ceci, serait de regarder à chaque SELECT quels sont les Wait Events générés mais ce sera pour un autre article!
[EDIT 01/11/2018]
Un complément d'informations sur le fameux site "Ask Tom" de questions/réponses d'Oracle.
En résumé, un Consistent Get aurait lieu pour une ou N lignes stockées en SGA alors qu'un Consistent Get a lieu par bloc en PGA; donc un Consistent get traite plus de lignes en PGA qu'en SGA.
https://asktom.oracle.com/pls/apex/f?p=100:11:::NO::P11_QUESTION_ID:9539194300346037888
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)