Re: Excessive waits on 'SQL*Net message from client'

From: DA Morgan <damorgan_at_x.washington.edu>
Date: Tue, 10 May 2005 00:34:25 -0700
Message-ID: <1115710211.729638@yasure>

Vsevolod Afanassiev wrote:

> I am trying to extract all data from a relatively small table into a
> flat file and extract runs for too long. When looking at wait events I
> see 46 seconds wait on 'SQL*Net message from client'
> and 3 seconds wait on 'db file scattered read', all other waits are
> less than 1 second.
>
> Basically I start SQL*Plus and specify SQL file on the same line, and
> SQL file contains EXIT at the end, so where all these waits on 'SQL*Net
> message from client' are coming from?
>
>

>>sqlplus / @select.sql

>
>
> where file select.sql looks like that
> -----------------------------
> select col1, col2, col3,col4 from table;
> exit
> -----------------------------
>
> Note that I run the extract on the same server where the database is
> running and connect through Bequeath, bypassing the listener.
> Background information:
> - Oracle 9.2.0.4.0 (32-bit) on Sun SPARC Solaris 8
> - The table contains 400,000 records, segment size 47 MB, 45 extents
> - The table does not have any LONG, CLOB, etc columns - only VARCHAR2,
> DATE, and NUMBER
> - The table does not have any chained rows (checked)
> - The query does not have any WHERE, ORDER BY, GROUP BY, etc
> - Extract size is 17 MB, it takes approx. 1 second to copy extract file
> on the local disk of the Sun server, so slow disks are not the problem
> - The server is not overloaded, all filesystems, swap, memory etc are
> fine
> - Connecting to Oracle and disconnecting takes less than a second

Well you definitely have some i/o problems but from what you've posted anything else would be a guess.

Also, from what you've posted, I don't see any specific issue other than the fact that you don't like waiting for your system to write out 17MB of data.

What is the issue? "Too long" has no context.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)

Received on Tue May 10 2005 - 02:34:25 CDT