Re: create index nologging does not improve performance

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 12 Apr 2002 14:17:23 -0700
Message-ID: <336da121.0204121317.2ddf8bad_at_posting.google.com>

u518615722_at_spawnkill.ip-mobilphone.net (Mike F) wrote in message news:<l.1018637208.1782989501@[64.94.198.252]>...

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
Received on Fri Apr 12 2002 - 23:17:23 CEST