RE: Less bytes when transferring table

From: Yong Huang <yong321_at_yahoo.com>
Date: Fri, 3 Apr 2009 09:33:36 -0700 (PDT)
Message-ID: <660650.62722.qm_at_web80606.mail.mud.yahoo.com>


> 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 Huang       

--
http://www.freelists.org/webpage/oracle-l
Received on Fri Apr 03 2009 - 11:33:36 CDT

Original text of this message