ORDER BY et déduplication SQLNet : un ORDER BY ne ralentit pas toujours une requête - ORDER BY and SQLNet deduplication
Introduction
Dans mon précédent article "ORDER BY et Consistents gets réduits par 10 : un ORDER BY ne ralentit pas toujours une requête!", je parlais du fait qu'un ORDER BY ne ralentit pas toujours un SELECT, voir même peut l'accélérer en divisant considérablement le nombre de Consistents gets. Dans cet article nous allons voir que le ORDER BY peut aussi diminuer la taille des données échangées entre le client et le serveur via de la déduplication des données identiques et que cela contribue à rendre le SELECT plus rapide.
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
============================================================================================
La base de test est un extract de quelques colonnes de DBA_OBJECTS. Je recopie à l'identique ces données jusqu'à avoir 630 000 enregistrements dans ma table.
SQL> create table test_obj02 as select OWNER, OBJECT_NAME, SUBOBJECT_NAME from dba_objects;
SQL> insert into test_obj02 select * from test_obj02;
78417 rows created.
SQL> /
...
...
SQL> select count(*) from test_obj02;
COUNT(*)
----------
628280
============================================================================================
SELECT sans le ORDER BY
============================================================================================
On génère les stats du schéma de la table.
SQL> exec dbms_stats.gather_schema_stats('HR');
SQL> set autotrace traceonly explain statistics
SQL> set timing on
Première exécution du SELECT sans ORDER BY : lecture des données sur disque dur, temps total 04 secondes 52 centièmes pour un coût de 1066 et 32,2 mégas octets échangés sur le réseau.
SQL> select OWNER, OBJECT_NAME from test_obj02;
628280 rows selected.
Elapsed: 00:00:04.52
Execution Plan
----------------------------------------------------------
Plan hash value: 3527827752
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628K| 23M| 1066 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL | TEST_OBJ02| 628K| 23M| 1066 (1)| 00:00:01 |
----------------------------------------------------------------------------
Statistics
----------------------------------------------------------
29 recursive calls
0 db block gets
45568 consistent gets
3894 physical reads
0 redo size
32283093 bytes sent via SQL*Net to client
461343 bytes received via SQL*Net from client
41887 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
628280 rows processed
Deuxième exécution du SELECT sans ORDER BY : les données sont lues uniquement en mémoire, temps total 04 secondes 34 centièmes pour un coût de 1066. Les physical reads sont à zéro et 32,2 mégas octets échangés sur le réseau..
SQL> /
628280 rows selected.
Elapsed: 00:00:04.34
Execution Plan
----------------------------------------------------------
Plan hash value: 3527827752
---------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628K| 23M| 1066 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL | TEST_OBJ02 | 628K| 23M| 1066 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
45517 consistent gets
0 physical reads
0 redo size
32283093 bytes sent via SQL*Net to client
461343 bytes received via SQL*Net from client
41887 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
628280 rows processed
Troisième exécution du SELECT sans ORDER BY : les données sont lues uniquement en mémoire, temps total 04 secondes 07 centièmes pour un coût de 1066. Les physical reads sont à zéro et 32,2 mégas octets échangés sur le réseau..
SQL> /
628280 rows selected.
Elapsed: 00:00:04.07
Execution Plan
----------------------------------------------------------
Plan hash value: 3527827752
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628K | 23M| 1066 (1)| 00:00:01 |
| 1 | TABLE ACCESS FULL | TEST_OBJ02 | 628K| 23M| 1066 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
45517 consistent gets
0 physical reads
0 redo size
32283093 bytes sent via SQL*Net to client
461343 bytes received via SQL*Net from client
41887 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
628280 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 7491, soit plus de sept fois le coût de lecture de toutes les données de la table. Cependant la quantité de données échangées serveur/client est divisée par deux avec seulement 14 mégas octets. On remarque que les physicals reads sont à 0 pour la première exécution car les données sont déjà en mémoire suite aux précédents SELECTs.
SQL> select OWNER, OBJECT_NAME from test_obj02 order by owner, object_name;
628280 rows selected.
Elapsed: 00:00:03.95
Execution Plan
----------------------------------------------------------
Plan hash value: 3934302124
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628K| 23M| | 7491 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 628K| 23M| 28M | 7491 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST_OBJ02| 628K| 23M| | 1066 (1)| 00:00:01 |
--------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3893 consistent gets
0 physical reads
0 redo size
13772046 bytes sent via SQL*Net to client
461343 bytes received via SQL*Net from client
41887 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628280 rows processed
Deuxième exécution.
SQL> /
628280 rows selected.
Elapsed: 00:00:03.92
Execution Plan
----------------------------------------------------------
Plan hash value: 3934302124
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628K| 23M| | 7491 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 628K| 23M| 28M | 7491 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST_OBJ02 | 628K| 23M| | 1066 (1)| 00:00:01 |
------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3893 consistent gets
0 physical reads
0 redo size
13772046 bytes sent via SQL*Net to client
461343 bytes received via SQL*Net from client
41887 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628280 rows processed
Troisième exécution.
SQL> /
628280 rows selected.
Elapsed: 00:00:03.95
Execution Plan
----------------------------------------------------------
Plan hash value: 3934302124
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 628K| 23M| | 7491 (1)| 00:00:01 |
| 1 | SORT ORDER BY | | 628K| 23M| 28M | 7491 (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL | TEST_OBJ02 | 628K| 23M| | 1066 (1)| 00:00:01 |
--------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
3893 consistent gets
0 physical reads
0 redo size
13772046 bytes sent via SQL*Net to client
461343 bytes received via SQL*Net from client
41887 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
628280 rows processed
============================================================================================
Comparaison entre les deux SELECT : le ORDER BY ne ralentit pas le SELECT
============================================================================================
Nous allons maintenant comparer 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,34 secondes et 04,07 secondes
Consistent gets : 45517 et 45517
Octets échangés : 32,28 mégas octets et 32,28 mégas octets
Avec ORDER BY
Temps d'exécution : 03,92 secondes et 03,95 secondes
Consistent gets : 3893 et 3893
Octets échangés : 13,77 mégas octets et 13,77 mégas octets
Opération en plus : SORT ORDER BY pour un coût de 7491
Nous constatons que le SELECT avec le ORDER BY est plus rapide de presque 10% que le SELECT sans ORDER BY alors qu'il y a une opération de SORT qui représente en coût plus de 7 fois le coût d'un FULL TABLE SCAN de la table de tests!
Comment l'expliquer? De deux façons :
- le nombre de Consistents gets est dix fois moins importants, chose détaillée dans cet article "ORDER BY et Consistents gets réduits par 10 : un ORDER BY ne ralentit pas toujours une requête!"
- la quantité de données échangée entre le serveur et le client est divisé par plus de deux : 13,77 Mo contre 32,28 Mo alors que dans mon article ci-dessus ce phénomène n'existait pas. L'explication est simple : le protocole réseau SQLNet fait par défaut de la déduplication de données, c'est à dire que si deux enregistrements qui se suivent sont identiques alors Oracle en supprime un. Et c'est justement ce que le ORDER BY provoque dans un SELECT, les enregistrements identiques se suivent et donc Oracle peut supprimer les doublons, triplés... A quelle hauteur? 32,28 - 13,77 = 18,51 mégas octets soit plus de la moitié des données sélectionnées!
Attention, cette fonctionnalité étant gratuite, ne vous attendez pas à des performances extraordinaires! Il faut que les enregistrements soient absolument identiques sinon il n'y a aucun bénéfice, c'est pourquoi on parle de déduplication et pas de compression. Dans mon article précédent (voir lien ci-dessus), le contenu de ma table TEST_OBJ01 est différent de TEST_OBJ02 car j'avais ajouté un ID basé sur ROWNUM dans la colonne OWNER, ce qui fait que chaque enregistrement différait par cette colonne, comme on le voit ici
SQL> select OWNER, OBJECT_NAME from test_obj01 where rownum < 11;
OWNER OBJECT_NAME
-------------------- --------------------
SYS1 I_FILE#_BLOCK#
SYS2 I_OBJ3
SYS3 I_TS1
SYS4 I_CON1
SYS5 IND$
Voyons maintenant comment Oracle retourne les dernières données selon les deux SELECTs.
Sans ORDER BY, aucun enregistrement consécutifs identiques.
SQL> select OWNER, OBJECT_NAME from TEST_OBJ02;
OWNER OBJECT_NAME
-------------------- --------------------
...
SYS jdk/internal/dynalink/linker/TypeBasedGuardingDynamicLinker
SYS jdk/internal/dynalink/beans/DynamicMethod
SYS jdk/internal/dynalink/beans/BeanIntrospector
SYS jdk/internal/dynalink/beans/BeanLinker$Binder
SYS jdk/internal/dynalink/beans/BeanLinker$1
SYS jdk/internal/dynalink/beans/BeanLinker$CollectionType
SYS jdk/internal/dynalink/beans/BeanLinker
SYS jdk/internal/dynalink/beans/ClassLinker
SYS jdk/internal/dynalink/beans/DynamicMethodLinker
SYS jdk/internal/dynalink/beans/BeansLinker$1
Avec ORDER BY, de nombreux enregistrements consécutifs identiques sur lesquels la déduplication d'SQLNet va pouvoir agir.
SQL> select OWNER, OBJECT_NAME from TEST_OBJ02 order by OWNER, OBJECT_NAME;
OWNER OBJECT_NAME
-------------------- --------------------
...
XFILES XMLSCHEMA_LIST
XFILES XMLSCHEMA_LIST
XFILES XMLSCHEMA_LIST
XFILES XMLSCHEMA_LIST
XFILES XMLSCHEMA_LIST
XFILES XMLSCHEMA_LIST_DML
XFILES XMLSCHEMA_LIST_DML
XFILES XMLSCHEMA_LIST_DML
XFILES XMLSCHEMA_LIST_DML
XFILES XMLSCHEMA_LIST_DML
XFILES XMLSCHEMA_LIST_DML
XFILES XMLSCHEMA_LIST_DML
XFILES XMLSCHEMA_LIST_DML
XFILES XMLSCHEMA_OBJECT_LIST
XFILES XMLSCHEMA_OBJECT_LIST
XFILES XMLSCHEMA_OBJECT_LIST
XFILES XMLSCHEMA_OBJECT_LIST
Continuons notre analyse. La table a 628280 enregistrements mais, si on groupe par OWNER et OBJECT_NAME, on tombe à 76200 soit une moyenne de 8,24 occurrences pour chaque enregistrement. Avec un tel taux de lignes dupliqués, on s'attendrait à avoir une taille divisée par 8 soit 4 mégas au lieu de 32 mais ce n'est pas le cas; pas d'explication pour le moment, peut-être dans un prochain article :-)
SQL> select count(*), OWNER, OBJECT_NAME from TEST_OBJ02 group by OWNER, OBJECT_NAME order by count(*), owner, object_name;
COUNT(*) OWNER OBJECT_NAME
---------- -------------------- --------------------
8 APEX_050100 APEX
8 APEX_050100 APEX$ARCHIVE_CONTENTS
8 APEX_050100 APEX$ARCHIVE_CONTENTS_IDX1
8 APEX_050100 APEX$ARCHIVE_HEADER
...
72 SYS WRI$_OPTSTAT_HISTGRM_HISTORY
80 SYS SYS_IL0000014347C00016$$
80 SYS SYS_LOB0000014347C00016$$
80 SYS WRI$_OPTSTAT_HISTHEAD_HISTORY
272 SYS WRI$_OPTSTAT_SYNOPSIS$
808 SYS AQ$_SUBSCRIBER_LWM
76200 rows selected.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)