Excessive waits on 'SQL*Net message from client'

From: Vsevolod Afanassiev <vafanassiev_at_yahoo.com>
Date: 9 May 2005 23:02:23 -0700
Message-ID: <1115704943.108483.104760@o13g2000cwo.googlegroups.com>

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

Received on Tue May 10 2005 - 01:02:23 CDT