Oracle, SQL * Net or ORDER BY saves network resources ...

Everyone rightly believes that the ORDER BY construction spends resources on sorting the result and as a result we should get the result a little later. Is it always like this? ..

Let's present a simple trivial query:

SET echo OFF SET linesize 192 SET pagesize 0 SET TRIM ON SET trims ON SET feedback OFF SET heading OFF SET term OFF SET TIME ON SET timing ON SET autot ON stat spool s.txt SELECT clnt_clnt_id, name, start_date, end_date FROM client_histories; spool OFF exit 

Everything seems simple:

1. the selection is made from the table
2. the result is expelled to the file
3. the result is not displayed on the terminal
4. At the end of the request, the time and statistics are displayed.

')
Now take a look at the statistics:

 .: 00:00:17.97  ---------------------------------------------------------- 0 recursive calls 0 db block gets 6515 consistent gets 0 physical reads 0 redo size 14182576 bytes sent via SQL*Net to client 242558 bytes received via SQL*Net from client 22012 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 330154 rows processed 

Now imagine that we need to organize the data. The question is - what will happen with time? The first opinion - sorting will take some time and the result will come later. What do we do:

 SET echo OFF SET linesize 192 SET pagesize 0 SET TRIM ON SET trims ON SET feedback OFF SET heading OFF SET term OFF SET time ON SET timing ON SET autot ON stat spool s1.txt SELECT clnt_clnt_id , name , start_date , end_date FROM client_histories ORDER BY 1, 2; spool OFF exit 

Now take a look at the statistics:

 .: 00:00:16.92  ---------------------------------------------------------- 0 recursive calls 0 db block gets 6115 consistent gets 0 physical reads 0 redo size 13166047 bytes sent via SQL*Net to client 242558 bytes received via SQL*Net from client 22012 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 330154 rows processed 

It turns out that when using order by, we get the result faster. In statistics, we have only two differences - the time spent on the operation, and the amount of information transmitted via SQL * Net.

One conclusion suggests itself - the sorting operation is faster by 33,000 lines than sending 992 kb data through the existing channel.

But where did the difference come from? ..
And the thing is that the data sent by sql * net is compressed and compressed by buffers. This is affected by the size of the SDU in TNS SQL * Net description, as well as the size of the buffer configured in SQL * Plus using the ARRAYSIZE parameter, which defaults to 15. If the data is sorted, then the buffer contains more identical data and the compression percentage is higher. So less data is passed over SQL * Net.

Let's experiment, namely make a small change to the second script:

 SET autot ON stat SET arraysize 5000 spool s1.txt 

Now we have increased the buffer size to 5000 (this is the maximum) and executed the query with sorting. Let's look at the result:

 .: 00:00:06.47  ---------------------------------------------------------- 0 recursive calls 0 db block gets 6115 consistent gets 0 physical reads 0 redo size 11278863 bytes sent via SQL*Net to client 1174 bytes received via SQL*Net from client 68 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 330154 rows processed 

• We have reduced the number of information transmitted to the client by another 1.8Mb
• We have reduced the number of information transmitted from the client 200 times (by 235kb)
• We reduced the number of roundtrip (queries between the client and the SQL * Net server) by 300 times (from 22012 to 68).

Total: thanks to the increase in the buffer, we reduce the number of roundtrips during data transfer, and this will almost always have a positive effect on large queries. But, interestingly, on slower communication channels (for example, 1 m / bit and slower), even regular data sorting can have a positive effect on the result of the request delivery.

Yes, and the level of compression. Let your data be prepared as follows:

 CREATE TABLE tbl0 AS SELECT object_name, object_id, min(object_id) over (partition BY object_name) AS min_object_id FROM dba_objects; CREATE TABLE tbl1 AS SELECT DISTINCT object_name, object_id FROM tbl0 WHERE object_id = min_object_id; CREATE TABLE tbl2 AS SELECT object_name FROM tbl1 WHERE rownum < 1; BEGIN FOR i IN 1..20 LOOP INSERT INTO tbl2 SELECT object_name FROM tbl1 ORDER BY reverse(object_id||object_name); END LOOP; COMMIT; END; 

Now compare the statistics for ARRAYSIZE 5000 by queries:

 SELECT object_name FROM tbl2; SELECT object_name FROM tbl2 ORDER BY 1; 

we get the following statistics:

  ---------------------------------------------------------- 0 recursive calls 0 db block gets 4992 consistent gets 0 physical reads 0 redo size 34152895 bytes sent via SQL*Net to client 3088 bytes received via SQL*Net from client 250 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1242280 rows processed  ---------------------------------------------------------- 167 recursive calls 16 db block gets 5211 consistent gets 16377 physical reads 0 redo size 7629058 bytes sent via SQL*Net to client 3088 bytes received via SQL*Net from client 250 SQL*Net roundtrips to/from client 21 sorts (memory) 4 sorts (disk) 1242280 rows processed 

As we see, with ARRAYSIZE 5000, all 1.2 million rows are pumped for the same number of roundtrip, i.e. The effect of SQL * Net delays on the request / response will be approximately the same, but the amount of information on the sorted data is 7.3 mb versus 32.5 mb for non-sorted data. So when pre-sorting duplicate data, we reduced the amount of traffic over the network by 4.5 times, which is very significant on slow communication channels.