Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Large initial extent after import
<snip>
>From 8 hours to 4 minutes? And you belive this improvement was achived
>exclusivelly by reducing the number of extents? Well, don't belive it!
This is not an exercise in faith. I didn't want to believe it either. But those are the numbers.
>Although 11.000 extents is realy an overkill I am 100% certain the
>poor performance was caused by many other factors, not exclusivelly by
>the large number of extents. Like wrongly chosen storage parameters of
>the extents as opposed to the init parameters db_block_size and
>db_file_multiblock_read_count. Like *much too many* migrated/chained
>rows in the table. Like wrongly chosen PCTFREE/PCTUSED on havily
>inserted/updated/deleted table.
Wrong storage parameters (initial and next) are why we had so many extents.
The init parameters were not changed at any time. PCTFREE is 0 as these
tables
are a warehouse data load - insert only once (15 million rows), no update
(hence
no chained rows), no delete. PCTUSED has no effect in this case.
>With correctly chosen parameters the number of extents per segment can
>have practicaly *no impact* on performance. "Single extent per segment
>is a must for good performance" is nothing but an old DBA mith that
>has nothing in common with reality!
OK - your view is your view - call me a dinosaur.
>The only impact a large number of extents per segment can have on
>performance is when you are dropping the segment - in that case you
>realy will have to wait for a long time if you have a few thousand of
>extents instead of 20 or 30.
...neil {actually: neil [dot] hulin [at] litech [dot] freeserve [dot] co [dot] uk} Received on Sat Nov 14 1998 - 07:20:21 CST