| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Large initial extent after import
On Fri, 13 Nov 1998 10:25:49 -0000, "Neil Hulin"
<nospam_at_litech.freeserve.co.uk> wrote:
>
>Jan Dolman wrote in message <364b52d3.360440_at_news.a2000.nl>...
>>When importing an export file that was exported using COMPRESS=Y,
>>Oracle creates an inital extent for each table large enough to hold
>>the entire table. For some tables, this initial extent can be quite
>>big.
>>Questions:
>>Is there a good reason to want this large extent?
>
>Yes, do not underestimate the performance hit if you have a table or index
>with many (say, more than 10) extents.
>Read the Oracle documentation and get a copy of Corrigan and Gurry.
>
>I have experience of 13GB tables and decided on 13 x 1GB extents to reduce
>waste space to an average of 500Mb per table. Previously these tables were
>up to 11,000 extents and operations would take in excess of 8 hours on a Sun
>ES10000 with 38 processors, 26GB RAM and 1.5TB mirrored EMC frames. After
>the reorg to reduce the number of extents we had most operations down to
>four minutes.
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!
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.
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!
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.
>>Is there a good reason NOT to want this large extent?
>
>Yes, when the extent size is greater than the maximum file size for the
>system you are hosted on. This is typically 2GB for most breeds of UNIX, so
>a 30GB table won't fit in a single extent.
>
>...neil {actually: neil [dot] hulin [at] litech [dot] freeserve [dot] co
>[dot] uk}
Regards,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)
 |
 |