Path: news.easynews.com!easynews!sn-xit-02!supernews.com!postnews1.google.com!not-for-mail From: afilonov@yahoo.com (Alex Filonov) Newsgroups: comp.databases.oracle.server,comp.databases.oracle.tools Subject: Re: create index nologging does not improve performance Date: 12 Apr 2002 14:17:23 -0700 Organization: http://groups.google.com/ Lines: 34 Message-ID: <336da121.0204121317.2ddf8bad@posting.google.com> References: NNTP-Posting-Host: 205.215.216.202 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 8bit X-Trace: posting.google.com 1018646243 2908 127.0.0.1 (12 Apr 2002 21:17:23 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: 12 Apr 2002 21:17:23 GMT Xref: easynews comp.databases.oracle.server:142963 comp.databases.oracle.tools:50625 X-Received-Date: Fri, 12 Apr 2002 14:15:05 MST (news.easynews.com) u518615722@spawnkill.ip-mobilphone.net (Mike F) wrote in message news:... They say it's not polite to answer with question, but why do you expect performance improvement here? I mean, nologging works a little bit faster than logging, so you have performance improvement. But you can't improve performance much because most of the time is spent by oracle doing full table scan on 7m rows table and sorting. The second question might be why parallel reduces performance. Possible answers: 1. Parallel execution is not allowed in init.ora. Check parameters parallel_max_servers and optimizer_percent_parallel. 2. You don't have enough hash_area_size. Oracle recommends 8M at least. 3. You don't have enough CPUs. 4. Your sort_area_size is too small and sorts are mostly done in temp tablespace. 5. Tablespace is located on one disk and parallel execution is slower because the disk headers are being repositioned all the time. There might be a lot of other reasons. > We have a table which has 7m rows > > create unique index ........ logging > Elapsed: 00:03:230.90 > > create unique index ........ nologging > Elapsed: 00:03:218.41 > > create unique index .........nologging parallel (degree 2); > Elapsed: 00:04:282.39 > > Could somebody tell me why? > > Thanks