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 Вроде всё просто:
делается выборка из таблицы результат выгоняется в файл результат на терминал не выводится в конце запроса отображается время и статистика Теперь взглянем на статистику:
Затрач.время: 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 раза, что очень существенно на медленных каналах связи.
