RE: Advice needed please

Lee - Just some reactions, few answers.

• Generally a process like this will be disk-bound, not CPU-bound, so idle CPU time is to be expected unless your disk is REALLY fast.
• Multiple simultaneous full-table scans may not be any faster because the disk heads may need to flit to and fro in order to satisfy each process' request. Sometimes a single full table scan is as fast is it gets for a mechanical device like a disk. RAID will be faster, of course, but ultimately the RAID is composed of disks.
• Trying for something faster than select * is a real challenge. To perform a full table scan, Oracle must read each data block. The alternative is index scanning, but this means reading an index block, fetching a data block, etc. Not faster if you're going to eventually read all data blocks anyway.
• If select * isn't fast enough, you should consider using table partitioning. That way each process can separately scan a separate partition and separately write to your output files. Hopefully someone else will think of a bright idea I've missed. Dennis Williams DBA Lifetouch, Inc. dwilliams_at_lifetouch.com

-----Original Message-----
Sent: Thursday, May 30, 2002 6:48 AM
To: Multiple recipients of list ORACLE-L

Oracle 8.0.5

Tru64 4.0f

One of our developers here is writing a utility to provide fast unloads of tables (to replace fastunloader as it happens)

His problem is as follows. Start from the bottom and work your way up. I would be really grateful if anyone can offer up some alternatives for us.

Regards

Lee

> -----Original Message-----
> From: Dudley Dave - ddudle
> Sent: 29 May 2002 16:04
> To: Robertson Lee - lerobe
> Subject: RE: Do you still have that SQL Expert?
>
>
> No, you miss the point. I'm explicitly NOT using PQ (or at least not
> explicitly using it).
>
> Using a parallel hint on huge table unloads - with the
> single-threaded version of the code (i.e. pipdynsql.v2.0.0) didn't seem to
> make much difference at all. I didn't do the tests directly though, poeple
> on the account did. So it may be that the tables already had a degree of
> parallelism built in, in which case I'd guess the hint would be redundant.
>
> What I mean is that even if you use PQ for the server to extract the
> data in parallel you still have the bottle neck of a single client to send
> it all back to. That's what I was trying to get around. Assuming that
> we're not generally using the full network bandwidth, I'd assume that
> multiple clients ought to be able to dump out separate sections of a table
> at the same time, at roughly the same speed at a single client would
> unload a single table - i.e double the throughput.
>
> But I can't find anything on the web to tell me the best / most
> efficient way to actually do this. (By the way, I've tried the NO_PARALLEL
> hint too, to stop the server setting off too many conflicting slaves on
> its side. Again no better as far as I could tell.)
>
> N.B. Not sure if you'd suggest it, but before you do: most of the
> tables we'd really want to use this for are massive, and so are already
> partitioned. So where I say "table" I mean either that or a partition
> thereof. Besides, need a generic solution that doesn't rely on having to
> partition your table to unload it quickly.
>
> By the way, I'm specifically testing speed of my original code (e.g.
> pipdynsql.v2.0.0 user/pass "select * from table") against the new
> multithreaded development code - i.e. regardless of the machine load at
> the time, I want to see if multiple simultaneous unloads can be quicker
> than a single unload client (at the expense of using more machine / Oracle
> resource obviously).
>
> Is this making sense?
>
> Dave
>
> -----Original Message-----
> From: Robertson Lee - lerobe
> Sent: 29 May 2002 15:14
> To: Dudley Dave - ddudle
> Subject: RE: Do you still have that SQL Expert?
>
>
> How are you using PQ, is it just a hint ??. Which tables are you
> testing against.
>
>
> -----Original Message-----
> From: Dudley Dave - ddudle
> Sent: 29 May 2002 14:27
> To: Robertson Lee - lerobe
> Subject: RE: Do you still have that SQL Expert?
>
> OK Clever-Trousers,
>
> As you're so hot on table/index disk striping...
>
> I've written the program "pipdynsql", which as you may have
> heard (this lunchtime if not before), people want to use to replace
> FastUnloader.
>
> I've been playing about with a new multithreaded version to
> try to download a table in sections to multiple client threads which then
> write back out to a single file (either ordered, or for max speed in
> random/undefined order).
>
> Can you think of any quick ways to do this, or "tricks" to
> try?
>
> I've tried ranges of rowids (as I'm told that's hold
> parrallel query works) but the ROWID (tab) hint does not seem to go
> through the table in rowid order. And it's a massive overhead to order by
> rowid to work out non-overlapping ranges. And even if you do, you have to
> say WHERE rowid >= xxx AND rowid <= xxx (as I say, can't force it to go in
> ROWID "sort" order) so this tends to be slower than nect opt...
>
> tried assuming there's a unique index and giving start
> points to each slave thread, which then selects a set number of rows. This
> is prety quick, but even this seems slower than a simple SELECT * FROM
> table (for the same number of records).
>
> tried loading temp "rowid" tables with sets of rowids and
> each slave does a full table scan of its rowid set table, with where
> clause connection to the data table.
>
>
> Can't find anything better than my original method which
> selects a unique key from an index with a "master" thread, for every x'th
> rownum. Then hands these out to the slave threads to select * from table
> where unique key >= given key for specified number of records. e.g. master
> pulls out every 100,000th key with a modulus and each slave dumps out
> 100,000 rows at a time, starting at the key its given.
>
> The above uses a temp view for the rownum bit to work. Also
> tried without a master slave where the unique key is numeric, as you can
> do the modulud directly on this. Tends to be slower - I guess due to MOD
> func overhead? Tried adding CACHE(table) to this to try and make sure all
> threads will get the majority of selected data from cache, rather than
> each going to disk. But no better.
>
> Tried many, many other combinations of hints, but can't seem
> to get more than "almost" double speed of a SELECT * from TABLE single
> process approach - using about 5 threads. Even when there's loads of free
> oracle/CPU resource available.
>
> Any ideas?!?!
>
> Dave
>

The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged. If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please re-send this communication to the sender and delete the original message or any copy of it from your computer system.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Robertson Lee - lerobe
  INET: lerobe_at_acxiom.co.uk

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: DENNIS WILLIAMS
  INET: DWILLIAMS_at_LIFETOUCH.COM

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).