RE: Less bytes when transferring table
- From: Yong Huang <yong321@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 3 Apr 2009 09:33:36 -0700 (PDT)
> From: "Tanel Poder" <tanel@xxxxxxxxxx>
>
> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
