Re: update 30mil rows - thanks

Larry,

You're right - I'm out of date.

Oracle 8.1.7.4 (which is what I've been
fiddling with for the last couple of hours) seems to be doing something a lot smarter than earlier versions.

I'm still trying to figure out what happens when - but I'm having a lot of trouble because the 10046 level 8 traces are not reporting anything like the correct wait states.

In the test case I have, which generates about 30 extents in the finished table - the QC is handing out 13 blocks at a time to the PX slaves. Each slave is building its own mini-table for most of the time.

However, somewhere near the end of the process, I think the QC starts tell slaves to start filling holes in each other's closing extents (this may be the meaning of a 'hwn_brokering' hint I have found in the executable). I have one set of trace files which shows 3 out of 4 PX slaves all contributing to a single extent near the end of the final table.

My problem, though, is the PX slave traces are only showing about 25% of the writes that they should be showing, and they are missing about 30% of the reads that should be showing, and repeating reads about 20% of the time. This doesn't make it easy to figure out what happens when. The upshot, though, is that I keep seeing every extent filled except the last one when doing:

    create table t2 nologging parallel (degree 4)     as
    select /*+ parallel(t1,4) */ * from t1;

Thanks for passing giving me the heads-up on this one.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon one-day tutorials:
Cost Based Optimisation
Trouble-shooting and Tuning
Indexing Strategies
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd

Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message ----- To: <jonathan_at_jlcomp.demon.co.uk> Sent: 26 February 2003 17:53

> When doing a parallel insert like that, doesn't he keep all that in
temp
> segments in the table's TS and then merge all those as opposed to
leaving a
> lot of half-empty extents? Yes, he will try to allocate the initial
and next
> for each slave. But you would only end up with a single potentially
> partially populated extent? Maybe I'm thinking wrong but I'm pretty
sure
> that's what we see.
>
> Regards,
>
> Larry G. Elkins
> elkinsl_at_flash.net
> 214.954.1781
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
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).