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:

    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:
    1. a selection is made from the table
    2. the result is driven to a file
    3. the result is not displayed on the terminal
    4. 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.

    Also popular now: