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 раза, что очень существенно на медленных каналах связи.
    Nexign
    Компания

    Комментарии 13

      +3
      И это нормальная ситуация одним запросом гонять на клиент такие объемы данных? Для чего? Почему обработку нельзя произвести на стороне сервера?
        +11
        Думаю, Вы «невкурили». Статья о том, что до ее прочтения Вы (по крайней мере я уж точно) не смогли бы объяснить даже потенциальную возможность ускорения выборки от order, и свели бы это к неточностям измерения, кэшу и т.д.
        Ан нет, есть еще одна не тривиальная причина. Автору — спасибо!

        Ну и, если уж быть занудным, причин передать 30 тыс строк с БД я могу придумать десятки.
          +4
          Таки да — нормально. Есть системы, где в принципе не предусмотрена обработка данных на стороне сервера. Возможны ситуации, когда данные пакетно обрабатываются через определённые интервалы — выгружается некий объём и обсчитывается внешней по отношению к серверу подсистемой. Есть системы многомерного анализа и т.д.
          Да и обычные OLTP системы могут создать сложности в плане избыточной нагрузки. Сократив количество roundtrip-ов можно убрать bottleneck с транзакционного ограничения канала, а сократив объем — с пропускного.
          Когда система упирается в нехватку некоторого ресурса, то это — как правило — говорит о том, что мы подбираемся к границам возможностей системы (ну или просчитались где-то в плане выделения ресурса), но редко бывает, что заканчивается всё и сразу и при возникновении «bottleneck» в качестве первой помощи мы — как правило — пытаемся заткнуть нехватку одного ресурса избыточностью другого. Например — нехватку памяти свопом на диск, нехватку CPU выделением большего объёма памяти и т.д. Не всегда такие «решения» высокоэффективны в плане базовых, но могут быть исключительно полезны в частных задачах.
          Если вы упираетесь в пропускную способность канала, который доставляет SQL*Net пакеты, то можете попробовать увеличить её, заплатив дополнительной нагрузкой на CPU и память сервера базы данных (хоть ресурс и дорогой, но если он избыточен, то почему бы и нет).
          0
          Спасибо, очень интересный «фокус», но можно уточнить? Строчки дублируются или просто похожи? Т.е. большая часть это
          1, a1, 2014.12.30, 2014.12.31
          1, a1, 2014.12.30, 2014.12.31
          1, a1, 2014.12.30, 2014.12.31

          или все-таки вида
          1, a1, 2014.12.30, 2014.12.31
          1, a1, 2014.05.21, 2014.05.22

          Просто есть подозрение что данная магия хорошо работает только если строчки полностью дублируются и есть подозрение что group by по всем полям или DISTINCT сработали бы ещё более эффективно, так как мне сложно представить необходимость получать сто тысяч одинаковых значений в поле имени без других параметров.

          Но все равно, спасибо за интересный «трюк», надо будет попробовать и на других базах данных.
            0
            Ну, у себя я обнаружил это на обычных данных по абонентам. В среднем на абонента приходилось 7-14 записей, строчки ~ 1200 байт, в строке выборки находились данные по id абонента и имени клиента + несколько низкоселективных атрибутов. Запрос был аналитический типа STAR с групировками (там всякие агрегаты были). Базово сортировало не по абоненту, записей — около 48млн. При сортировке по клиенту, id абонента,… ужалось порядка 35%. Как повлиял ARRAYSIZE уже не скажу — на тот момент я его уже увеличил.

            По поводу вашего вопроса — использовались реальные имена клиентов в истории, даты — естественно — были разные (
            т.е. ближе ко второму вашему примеру).
            +1
            Отличная статья. Я только надеюсь, что никто не воспринял ее как руководство юзать ORDER BY для ускорения получения результатов любых запросов :)
              0
              Насколько всё это правдиво при использовании JDBC/ODBC? Они ведь поверх SQL*Net-а работают, значит всё тоже самое должно сработать и там или есть какие-то подводные камни?
                0
                Ну, если вы используете ODBC или JDBC*OCI, то все вызовы проходят через те же Oracle Call Interface функции и результат будет такой же (только ARRAYSIZE надо определять через header соединения). Касательно JDBC*Thin думаю, что тоже будет всё ОК, но это надо проверять. Там реализован тот же SQL*Net протокол, но средствами Java Native и — естественно — некоторые аспекты могли быть опущены.
                  0
                  Нашел, что этой опцией протокола можно управлять с помощью *Statement.setFetchSize(int rows) в JDBC.
                    0
                    Если на уровне Statement, то да, но есть ещё на уровне соединения: класс — OracleConnection, метод — setDefaultRowPrefetch(...).

                    Да, и коль скоро мы заговорили про ARRAYSIZE,- есть такой интересный момент:
                    если мы, например, открываем курсор и тащим из него %fetchSize% строк, а последние строки попали из блока базы данных NNN, то после .next() нам будет передана следующая порция строк и если блок NNN был вычитан не полностью и там ещё есть нужные нам строки, то будет произведена операция повторного логического чтения данного блока. Таким образом, можно предположить, что если размер данных от %fetchSize% строк будет достаточно мал в сравнении с размером блока базы данных, то количество логических чтений будет выше реально необходимого (может даже в несколько раз).
                  0
                  Если я правильно понимаю, сжатие реализовано даже на уровне таких протоколов как ssh, http, то есть в теории это может сработать, даже если просто пересылать более структурированные данные по сети от бека веб.клиенту. Впрочем, я так понимать, все будет зависеть от алгоритмов архиватора протокола и в некоторых случаях никакой пользы от такой оптимизации может и не быть.
                    0
                    Да, при некоторых задачах успеха может и не быть по объёму трафика, но сокращение roundtrip тоже дает эффект.
                    Касательно сокращения трафика между бэком и веб-клиентом — это вопрос не данной темы. Здесь рассматривается взаимодействие между бэком и сервером базы данных либо между клиентом и сервером базы данных, а точнее — работа с СУБД Oracle посредством SQL*Net протокола.
                    0
                    Кстати, категорически советую блог Джонатана Льюиса — это прямо must read для oracle-специалистов.
                    Он про это еще 5 лет назад писал: jonathanlewis.wordpress.com/2010/05/07/sqlnet-compression/

                    Как-то мимо меня проскочил этот топик… Как бы подписаться на hub/oracle чтобы письма приходили, когда новый топик появляется?

                    Только полноправные пользователи могут оставлять комментарии. Войдите, пожалуйста.

                    Самое читаемое