SQL*Net compression

Here’s a little demonstration I’ve been meaning to write about for the last few years – it’s very simple: create a table, then query it a couple of times.

rem
rem     Script:         sqlnet_compression.sql
rem     Author:         Jonathan Lewis
rem     Dated:          December 2006
rem

execute dbms_random.seed(0)

create table t1
as
with generator as (
        select  --+ materialize
                rownum  id
        from    all_objects
        where   rownum <= 1e3    -- > comment to avoid wordpress format issue
)
select
        lpad(dbms_random.string('U',2),40,'X')  v1
from
        generator       v1,
        generator       v2
where
        rownum <= 1e4    -- > comment to avoid wordpress format issue
;

break on report
compute sum of number_of_groups on report

select
        group_size, count(*)    number_of_groups
from
        (
        select
                v1, count(*) group_size
        from
                t1
        group by
                v1
        )
group by
        group_size
order by
        group_size
;

The data is a single column, basically a randomly generated two-character string, padded up to 40 characters on the left with lots of ‘X’s. The first first query gives you an indication of the data distribution, which is as follows:

GROUP_SIZE NUMBER_OF_GROUPS
---------- ----------------
         5                2
         6                5
         7                6
         8               15
         9               27
        10               30
        11               51
        12               55
        13               63
        14               59
        15               78
        16               65
        17               63
        18               54
        19               21
        20               35
        21               19
        22               14
        23                8
        24                5
        26                1
           ----------------
sum                     676

The way I’ve written this query you can see that there are two pairs of characters that appear five times each, one pair of characters that appears 26 times, and 78 pairs of characters that appear 15 times each; so there’s a fair bit of repetition, and a total of 676 different pairs of characters (which isn’t too surprising since I’ve stuck to the upper case alphabet, sampled 10,000 times, and 26 * 26 = 676).

So let’s try to query all the data from SQL*Plus:

set arraysize 1000
set autotrace traceonly statistics

select  v1
from    t1
;

select  v1
from    t1
order by
        v1
;

set autotrace off

Since I’ve set autotrace to display only the trace output, and only the statistics for the trace, the output is very limited. Here are the results for the first query:

10000 rows selected.

Elapsed: 00:00:00.07

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        143  consistent gets
          0  physical reads
          0  redo size
     425479  bytes sent via SQL*Net to client
        494  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
      10000  rows processed

We see we have 10,000 rows processed, 11 SQL*Net round-trips, and 420KB of data travelling across SQL*Net to the client. At 10,000 rows with about 40 characters per rows that last figure is obviously in the right ballpark for the volume of data moved. But what are the stats for the second query (the one where I’ve added an “order by” clause) going to look like:

10000 rows selected.

Elapsed: 00:00:00.04

Statistics
----------------------------------------------------------
          4  recursive calls
          0  db block gets
        133  consistent gets
          0  physical reads
          0  redo size
      79287  bytes sent via SQL*Net to client
        494  bytes received via SQL*Net from client
         11  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
      10000  rows processed

It’s interesting to note that the second query completed faster despite the work done in sorting the data (though it’s not really sensible to make a big thing of comparing 0.07 seconds with 0.04 seconds) but what’s really interesting is that the volume of data moved across the network is only about 75KB – nearly one-sixth of what you might expect.

There are two questions here: how come we received 10,000 rows of about 40 bytes each in only 79KB of data transmission and, for the very observant, how come the number of SQL*Net round-trips hasn’t dropped. (I’ll leave the second question for another posting – there’s too much to say in just one note.)

Addressing the first question: SQL*Net has been able to do compression (or rather “de-duplication”) for years, at least since 8.0. If a single session data unit (SDU) contains data with a suitable pattern of duplicates SQL*Net can remove duplicates before transmission (and put them back on receipt, of course).

The critical pattern is (or seems to be) described by the question: “does this row have the same value in column X as the last row, if so than I don’t have to send column X again.” So the de-duplication is from row to row, not a comparison across columns, or across the entire bulk of the session data unit. This means that if we sort our rows appropriately we can maximise the volume of data where “the columns in this row” are often identifcal to “the corresponding columns in the preceding row”.

In my special case, of course, my “order by” clause ensured that I would always be sending at least five consecutive rows that were identical, and up to 26 rows that were identical – thereby achieving lots of eliminations of the 40-character column value.

Footnote:

I can say with some confidence that this de-duplication worked from at least Oracle 8 (or rather, SQL*Net 8 ) because of a discussion I had about “object-relational” technology just after 8.0 was launched. Someone tried to persuade me that it was kinder on the network to send “parent object with embedded multiple children” rather than sending “parent joined to child”. Their argument was that the object approach reduced the network traffic because the join repeated the required columns of the parent with each child while the object method didn’t. But as I was able to point out, the SQL may have repeated the columns but SQL*Net eliminated the repetitions. (In fact in some cases the object approach used to increase the network traffic because of the extra messages from the client asking the server for the definition of the object type – I don’t know if this is still the case, though.)

Footnote 2

I’ve re-run the experiment for several  versions of Oracle since the original posting, and the effect appears to be the same all the up to 21.3 so far.