RE: Less bytes when transferring table
Date: Fri, 3 Apr 2009 09:33:36 -0700 (PDT)
> From: "Tanel Poder" <tanel_at_poderc.com>
> Yep, Oracle Net does simple compression - when there are lots of rows with
> same consecutive column values sent over sqlnet then Oracle can send the
> column only once plus a count. Anjo Kolk wrote about it in his old blog but
> I couldn't find his article anymore.
> Check the simple example (look into difference in bytes sent via SQL*Net
> even though the result data is the same):
> select owner from dba_source order by dbms_random.random;
> select owner from dba_source order by owner;
With Tanel's excellent demo, I searched on Google and Metalink. It's hard to come up with good keywords. But I think ORA_OCI_NO_OPTIMIZED_FETCH environment variable may be controlling it. When I don't set it, "bytes sent via SQL*Net to client" is 980126 and 1428722, for "order by owner" and "order by dbms_random.random", respectively. When I set it to 0 or 1, both numbers are 1116122.
My test is on Oracle 10.2.0.1, Windows XP. "set autot trace stat" is run before the SQLs. I don't know what value for the environment variable is equivalent to not setting it. If you need to unset, just type "set ORA_OCI_NO_OPTIMIZED_FETCH=" in DOS.
Yong HuangReceived on Fri Apr 03 2009 - 11:33:36 CDT