Oracle, SQL*Net или ORDER BY экономит сетевые ресурсы…

Все справедливо считают, что конструкция ORDER BY расходует ресурсы на проведение сортировки результата и в итоге мы должны получить результат несколько позже. Всегда ли это так?..

Давайте представим простой тривиальный запрос:

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

Вроде всё просто:

1. делается выборка из таблицы
2. результат выгоняется в файл
3. результат на терминал не выводится
4. в конце запроса отображается время и статистика

Теперь взглянем на статистику:

Затрач.время: 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

А теперь представим, что данные нам надо упорядочить. Вопрос — что будет со временем? Первое мнение — сортировка займёт некое время и результат придёт позже. Что-ж выполняем:

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

Теперь взглянем на статистику:

Затрач.время: 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

Оказывается, что в случае использования order by результат мы получаем быстрее. В статистике имеем только два отличия — время, затраченное на операцию, и количество информации, передаваемой по SQL*Net.

Напрашивается один вывод — операция сортировка проходит быстрее на 33000 строк, чем пересылка по существующему каналу 992 kb данных.

Но вот откуда взялась разница?..
А всё дело в том, что данные, пересылаемые по sql*net, сжимаются и сжимаются буферами. На это влияют размер SDU в TNS описании SQL*Net, а так же размер буфера, настраиваемого в SQL*Plus посредством параметра ARRAYSIZE, который по-умолчанию равен 15. Если данные отсортированы, то в буфере больше одинаковых данных и процент сжатия выше. Т.о. передаётся меньше данных по SQL*Net.

Давайте проэксперементируем, а именно — внесём небольшое изменение во второй скрипт:

SET autot ON stat
SET arraysize 5000
spool s1.txt

Теперь мы увеличили размер буфера до 5000 (это максимум) и выполнили запрос с сортировкой. Взглянем на результат:

Затрач.время: 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

• Мы сократили кол-во информации, передаваемое на клиента ещё на 1.8Mb
• Мы сократили кол-во информации, передаваемой от клиента в 200 раз (на 235кб)
• Мы сократили количество roundtrip (запросов между клиентом и сервером SQL*Net) в 300 раз (с 22012 до 68).

Итого: благодаря увеличению буфера мы сокращаем кол-во roundtrip при передаче данных и это практически всегда положительно скажется на больших запросах. Но, что интересно, на медленных каналах связи (например 1 м/бит и медленнее) даже обычная сортировка данных может положительным образом сказаться на результате доставки запроса.

Да, и по уровню сжатия. Пусть у вас данные подготовлены следующим образом:

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;

Теперь сравним статистику для ARRAYSIZE 5000 по запросам:

SELECT object_name
FROM tbl2;

SELECT object_name
FROM tbl2
ORDER BY 1;

получаем следующую статистику:

Статистика
----------------------------------------------------------
          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

Как мы видим, при ARRAYSIZE 5000 все 1.2 миллиона строк перекачиваются за одинаковое количество roundtrip-ов, т.е. влияние задержек SQL*Net на запрос/ответ будет приблизительно одинакова, зато объем информации по отсортированным данным 7.3 мб против 32.5 мб для не отсортированных. Т.о. при предварительной сортировке повторяющихся данных мы сократили объем трафика по сети в 4.5 раза, что очень существенно на медленных каналах связи.