Re: SQL*Net waits in a 10046 trace

A late entry to this post, but following up Stefan's comment about memory and buffering, here's a highlight from SQL*Plus "Text_content" is a CLOB of about 120,000 bytes. I've deleted some of the lines from the output

SQL> set autotrace traceonly statistics
SQL> set timing on
SQL> set longchunksize 131072
SQL> select text_content from test_lobs where id = 1;

Elapsed: 00:00:00.00

Statistics

         25  consistent gets
         16  physical reads
     120945  bytes sent via SQL*Net to client
        792  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          1  rows processed

SQL> set longchunksize 8192
SQL> select text_content from test_lobs where id = 1;

Statistics

         71  consistent gets
         29  physical reads
     123400  bytes sent via SQL*Net to client
       2625  bytes received via SQL*Net from client
         18  SQL*Net roundtrips to/from client
          1  rows processed

SQL> set longchunksize 80
SQL> select text_content from test_lobs where id = 1;

Statistics

       5181  consistent gets
       1508  physical reads
     402477  bytes sent via SQL*Net to client
     211164  bytes received via SQL*Net from client
       1497  SQL*Net roundtrips to/from client
          1  rows processed

The number of roundtrips is affected by the buffer size set of the lob (which is controlled by the longchunksize parameter in SQL*Plus). The impact on buffer gets is also highly visible. The effect on the physical reds is dependent on whether the LOB is declared cache or nocache.

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message -----
  > The query that I am working on retrieves 67 rows each of which has one
  > blob column. It takes a total of 11 seconds.
  >
  > - Peter Schauss
  

--
http://www.freelists.org/webpage/oracle-l