Re: SQL*Net waits in a 10046 trace
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sat, 26 May 2007 11:56:49 +0100
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
