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

I assume that the total amount of time that the extract takes is about 50 seconds to one minute (given your 49 seconds of time reported as the significant portion)? Without any additional information, I would guess that most of your time is being spent by SQL*Plus formatting the data on the client end of things - the "wait on message from client" time is time reported by Oracle can be interpreted as all time spent between fetch calls.

You didn't mention how big your row size is, but 17MB/400,000 rows = about 45 bytes per row, so that doesn't seem excessively long, so it doesn't seem like you're writing out unnecessary bytes (this can happen if you do a SET LINESIZE to some large number).

How long does this extract compare to doing a command like:

create table foo as select col1, col2, col3, col4 from table;

If the above statement runs quickly, you can be certain that it is sqlplus that is slowing you down. If it runs as slow or slower (unlikely, given your numbers), concentrate on the server side of things

One thing to try: issue a

SET ARRAYSIZE 100 or some larger number (up to 5000) to reduce the number of round-trips to the sqlplus client. Another trick may be to convert all your data in your select to character fields (with the to_char function) before sending it to sqlplus to reduce processing overhead, but I'm doubtful that will be a win.

-Steve Received on Tue May 10 2005 - 11:13:58 CDT