Re: insert nologging parallel/noparallel and archiving

From: Jack Silvey <jack_silvey_at_yahoo.com>
Date: Fri, 05 Jul 2002 11:23:21 -0800
Message-ID: <F001.004906CA.20020705112321@fatcity.com>

Gene,

This sounds right.

Standard insert as append does freelist block checking and generates redo. Parallel DML generates minimial redo, just for the new temp segments being created, not for the datablock updates. This is one reason it is so fast.

Index changes are always logged, even in PDML and append modes.

One way to consider would be to alter indexes to unusable and rebuild with nologging and compute statistics clauses. This way, your load is fast, you generate minimal redo, and your indexes are balanaced and have good stats afterwards.

A space consideration with PDML and dictionary managed tablespaces - each PQ process in your PDML is going to have its own extent. This extent will begin life with a size=initial, but will have the empty space trimmed off at the end of the write, leaving an odd size extent. Since you are doing PDML, you might have these extents close together, and when the trim occurs, it will leave odd sized holes in between and throw off your space managment strategy. I don't believe that LMT tablespaces have this issue.

hth,

jack

• Gurelei <gurelei_at_yahoo.com> wrote:
  > Hi all.
  >
  > I've been trying to copy a 20mil rows table between
  > two databases via insert as select statement. I
  > have dropped all the indices and ran in nologging
  > mode
  > with hints forcing parallel execution of both ISERT
  > and SELECT. While checking the archive directory I
  > have noticed that the archives have been created
  > every
  > 2 minutes. I have cancelled the load and noticed
  > that
  > I forgot to alter system enable parallel dml so in
  > effect the load was going in sequential mode. Then I
  > reran my script having added the alter system
  > statement and there we no archive created while it
  > ran.
  > So it looks like even without the indices a
  > sequential
  > insert as select nologging still creates the
  > archives
  > wihle the parallel - doesn't. Does it sound right?
  > If
  > so, why. I thought the nollogging and with no
  > indices
  > shouldn't produce any log info regardless of the
  > mode.
  >
  > TIA
  >
  > Gene
  >
  > __________________________________________________
  > Do You Yahoo!?
  > Sign up for SBC Yahoo! Dial - First Month Free
  > http://sbc.yahoo.com
  > --
  > Please see the official ORACLE-L FAQ:
  > http://www.orafaq.com
  > --
  > Author: Gurelei
  > INET: gurelei_at_yahoo.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).
  

  ---

Do You Yahoo!?
Sign up for SBC Yahoo! Dial - First Month Free http://sbc.yahoo.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jack Silvey
  INET: jack_silvey_at_yahoo.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).

Received on Fri Jul 05 2002 - 14:23:21 CDT