Re: create index nologging does not improve performance

The redo log DOES contain an image of the index, in a form that is close to the actual index blocks. I think you're thinking of an export file containing just the "create index" command.

The small change in the time between logging and nologging tends to suggest that the sort phase is taking most of the time - which further suggests that the sort_area_size is small enough to force two or three merge passes. The excess time on the parallel version tends to confirm this - if read/write is the bottleneck serially, and if the sort_area_size is so small that sharing the job between two slaves still leaves multiple merges, the I/O contention is likely to get much worse.

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

Author of:
Practical Oracle 8i: Building Efficient Databases

Next Seminar - Australia - July/August
http://www.jlcomp.demon.co.uk/seminar.html

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



Nuno Souto wrote in message <3cb8dd83.3008764_at_news-vip.optusnet.com.au>...


>Mike F doodled thusly, after I reduced the x-posting:

>

>>create unique index  ........ logging

>>Elapsed: 00:03:230.90

>>

>>create unique index  ........ nologging

>>Elapsed: 00:03:218.41

>

>

>Hmmm, you just saved the writing in the redo log of the CREATE INDEX

>statement and possibly a few dictionary updates/inserts.  Exactly what

>were you expecting?  The redo logs don't store the ENTIRE index!  Just

>the create statement and little else.

>

>>

>>create unique index .........nologging  parallel (degree 2);

>>Elapsed:  00:04:282.39

>>

>

>This is what you should be worried about.  Was the original no

>parallel?  IOW, what is the setting of parallel in the table?  How

>many CPU's do you have?  Are you getting lots of wait on I/O when the

>read phase of the create index executes?  This should take less time,

>not more.  It is the real problem that you have to address.

>

>

>Cheers

>Nuno Souto

>nsouto_at_optushome.com.au.nospam