Re: Extent Numbers (Was Re: Interview question)

"Terry Steyaert" <steyaert_at_my-deja.com> wrote in message news:8lkce0$oon$1_at_nnrp1.deja.com...
> I have a related question, although this is mostly with my experience
> with Oracle 6.0.3.
>
> Back when we were using Oracle 6.0, we would run into problems of
> running out of space and needing to optimize. Since Oracle 6 didn't
> have abilities to shrink things, and to be honest, we didn't know what
> we were doing, we would export the entire dataspace, delete the entire
> database, recreate the entire table and re-import. That seemed to be
> the only way to clean up the database.
>
> We found out at that time that "Compress" was a VERY BAD THING. If you
> took one of our heavily used tables (approximatly 40% of our database)
> and compressed the table, it went from something like 25-60 extents,
> depending on system, to 2. Our ability to process data was cut in a
> quarter because database access with the table was just SO SLOW.....
> From that point on, Compress has been a four letter word around here.
>
> We never did find out what compress did that killed our database I/O,
> but it was definitely something drastic. In my opinion, that means
> that having few extents is not a good thing (with the exception of some
> fixed-size tables that we set the size handle all the data.)
>
> Has that changed in the last 11 years? I hear people on both sides of
> the "you want to have few extents" but no real "answers". Do you want
> to compress when you export/import?

Compress=Y *should* mean that if your segment starts off with 60 extents in the source database, on subsequent import into the new destination database, it will be created with a single extent, sized to be whatever the previous 60 added up to. Ie, you'd end up with a single 60Mb extent instead of 60 1Mb extents.

It therefore saves you precisely zero space. Worse, if you'd been doing a lot of deleting on the source table, most of that 60Mb would turn out to be completely fresh air (though, if you knew you were going to re-populate the thing later, perhaps with a bulk load, that's not necessarily a bad thing).

This in itself is not a bad thing. Fewer extents (or 1 extent) certainly don't stuff up performance. However, you have to remember that the table created in the destination database still has its original "Next" extent setting -so you are at risk of introducing odd-sized extents into the tablespace, and that could lead to fragmentation. But since that is a waste-of-space issue rather than a performance one, I can't see that that could have been the cause of the problem.

Not being well-versed in Oracle 6, I couldn't really explain the nature of this problem -but I'd bet a small sum that it wasn't compression per se that caused it. I myself wouldn't compress a table I knew to be due for extensive DML in the future -though it's good for tables which are, or have become, fairly static (archives etc).

However, in general, fewer extents are good for your data dictionary. 'Nuff said!

How about taking a quick trip to
http://www.ixora.com.au/tips/creation/extents.htm for that full discussion of the issues you were after? If anyone can contradict Steve's findings, I haven't seen them do it.

Regards
HJR
>
> Terry Steyaert
> steyaert_at_my-deja.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Wed Jul 26 2000 - 00:00:00 CDT