Oracle, SQL * Net or ORDER BY saves network resources ...
Everyone rightly believes that the ORDER BY construct spends resources on sorting the result, and in the end we should get the result a little later. Is this always the case? ..
Let's introduce a simple trivial query:
Everything seems to be simple:
Now take a look at the statistics:
Now imagine that we need to streamline the data. Question - what will happen with time? The first opinion is that sorting will take some time and the result will come later. Well done:
Now take a look at the statistics:
It turns out that in the case of 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 over SQL * Net.
One conclusion begs - the sorting operation is faster by 33,000 lines than sending over the existing channel 992 kb of data.
But where did the difference come from? ..
And the thing is that the data sent via sql * net are compressed and compressed by buffers. This is affected by the size of the SDU in the TNS SQL * Net description, as well as the size of the buffer configured in SQL * Plus using the ARRAYSIZE parameter, which is 15 by default. If the data is sorted, then the buffer has more identical data and the compression ratio is higher. T.O. less data is transferred over SQL * Net.
Let's experiment, namely, make a small change to the second script:
Now we have increased the size of the buffer to 5000 (this is the maximum) and fulfilled the request with sorting. Take a look at the result:
Total: due to the increase in the buffer, we reduce the number of roundtrip during data transfer and this will almost always have a positive effect on large requests. But, interestingly, on slow communication channels (for example, 1 m / bit and slower), even the usual sorting of data can positively affect the result of the request delivery.
Yes, and in terms of compression. Let your data be prepared as follows:
Now compare the statistics for ARRAYSIZE 5000 with the queries:
we get the following statistics:
As we can see, with ARRAYSIZE 5000 all 1.2 million lines 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 sorted data is 7.3 mb versus 32.5 mb for non-sorted data. T.O. In the preliminary sorting of repeated data, we reduced the amount of traffic over the network by 4.5 times, which is very significant on slow communication channels.
Let's introduce 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 to be simple:
- a selection is made from the table
- the result is driven to a file
- the result is not displayed on the terminal
- at the end of the request, 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 streamline the data. Question - what will happen with time? The first opinion is that sorting will take some time and the result will come later. Well done:
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 in the case of 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 over SQL * Net.
One conclusion begs - the sorting operation is faster by 33,000 lines than sending over the existing channel 992 kb of data.
But where did the difference come from? ..
And the thing is that the data sent via sql * net are compressed and compressed by buffers. This is affected by the size of the SDU in the TNS SQL * Net description, as well as the size of the buffer configured in SQL * Plus using the ARRAYSIZE parameter, which is 15 by default. If the data is sorted, then the buffer has more identical data and the compression ratio is higher. T.O. less data is transferred 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 size of the buffer to 5000 (this is the maximum) and fulfilled the request with sorting. Take a 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 amount of information transmitted to the client by another 1.8Mb
- We have reduced the amount 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: due to the increase in the buffer, we reduce the number of roundtrip during data transfer and this will almost always have a positive effect on large requests. But, interestingly, on slow communication channels (for example, 1 m / bit and slower), even the usual sorting of data can positively affect the result of the request delivery.
Yes, and in terms 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 with the 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 can see, with ARRAYSIZE 5000 all 1.2 million lines 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 sorted data is 7.3 mb versus 32.5 mb for non-sorted data. T.O. In the preliminary sorting of repeated data, we reduced the amount of traffic over the network by 4.5 times, which is very significant on slow communication channels.