Re: Performance of Oracle on read-intensive operations

Yes, it's pretty normal.

Your earlier post describes 100 byte rows, and 0.5Gb file. That's about 5,000,000 row are copied.

This post says the total elapsed time (is that including the pipe through compress) is 36 mins, of which the shadow took 12 minutes CPU and SQL*Plus took 15 minutes, leaving at most 9 minutes for compress file waits.

Remember that

1. Oracle shadow has to make blocks read consistent, unpack blocks, convert data to external formats, and pack data into the pipeline
2. SQL*Plus has to unpack the pipeline, convert data into output format, and write it to file in small pieces.
3. The pipeline is usually very small (ca. 2k or 4K) and only one of (a) and (b) can occur at any time, the dialogue is synchronous, and a typical round- trip time is likely to be around 0.5 to 1 millisecond.

By comparison, cp just jumps to a point in a file, streams a great slice into memory, and slams it down somewhere else. Particularly effective if the file system is a Veritas file system.

--

Jonathan Lewis
Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk

Vsevolod Afanassiev wrote in message
<7s6etf$g8m5_at_xlprod01.westpac.com.au>...
>Mario,
>Thanks, I'll try export (not sure whether 7.3.2 has direct export option,
>I'll look in Utilities Guide).
>However, I am not displaying the output in the window. I am either using
>spool file
>with SET TERMOUT OFF or redirect standard output using UNIX pipe
>
>sqlplus / @script.sql | compress -c > file.dat.Z
>
>Im my last test (which took 36 min) sqlplus consumed 15 min 17 sec CPU time
>and oracle shadow
>process (oracleSID) consumed 11 min 59 sec. So the problem is not in the
>client process (sqlplus),
>the problem is with Oracle itself. My question is: Is it normal?
>
>I checked the table for chained rows: it does not have any (because the
>table is never updated:
>rows are inserted and then truncated).
>
>Regards,
>Sev
>
>
>Mario Bucsics wrote in message
><0d24a28c.a9e5d98c_at_usw-ex0102-010.remarq.com>...
>>
>>This is because oracle needs to display the result to your
>>sql-plus window. if you use exp with the direct path
>>option ( exp user/pass table=asdfasdf direct=y file=asd )
>>then you will see that this method takes only a few minutes.
>>
>>
>> Mario Bucsics
>>
>>* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network
>*
>>The fastest and easiest way to search and participate in Usenet - Free!
>>
>
>
Received on Tue Sep 21 1999 - 02:59:00 CDT