| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Locally Managed Tablespace Uniform Extent
My understanding was that the main reason to keep the number of extents down
was in case you needed to drop or truncate the table it would take Oracle a
long time to clean up the fet$ table.
I think, and I emphasize that I am not certain of this, that this is no
longer a problem with locally managed tablespaces.
I'm sure someone will correct me if I'm wrong...
Jay Miller
-----Original Message-----
Sent: Wednesday, March 14, 2001 4:59 PM
To: Multiple recipients of list ORACLE-L
Jim,
I'm probably a bit extreme here, but, with all due respect to Steve Adams (because I really do), I wouldn't worry terribly much about numbers of extents.
Our 8.1.6 production db on Win2k has 8KB block size and uniform extent size of 1MB in all tablespaces. Our largest segment stores the out-of-line CLOBs of a partition of our largest table - it has over 22,000 extents. Another partition has a CLOB segment of over 18,000 extents. Since we hit those segments by RowID during InterMedia Text index queries, we've had absolutely no performance problems - we get 1 to 5 second response times. Actually, the InterMedia Text index segments have over 1,000 extents.
A bunch of our tables with non-LOB data have hundreds of extents as well, which probably puts them in line with Steve's guidelines, but I wouldn't be worried if they got up into the thousands of extents.
I'm sure there are numerous situations where different extent sizes in different tablespaces makes sense. Maybe I'm just too lazy. We don't have that many small tables where 1MB extents waste a lot of space and, like I said, I don't worry about too many extents, though if extents approached the hundreds of thousands, I might create a tablespace or two with large extents for those segments. I've just not seen really convincing arguments that large (but not huge) numbers of extents cause significant performance problems, especially compared to the really BAD SQL that Duhvelopers seem so fond of writing. ;-)
A big advantage is that I can't even remember the last time I worried about coelescing and fragmentation!
Jack
-----Original Message-----
Jim Walski
Sent: Wednesday, March 14, 2001 2:10 PM
To: Multiple recipients of list ORACLE-L
I have to move a 7.3.4 database to 8i and I want to use the
uniform extent
size to reduce fragmentation. I read the article "stop
defragmenting and
start living" and it indicates to have 3 extent sizes -
128K, 4M, 128M.
I also read an article on Steve Adams site that indicates to
keep the number
of extents under (db_block_size/16) - 7 ( which in my case
8k block) would
be 505 extents. )
In the database I am moving there are some segments that are
currently 1GB
in size, if i was to put those in the 4M tablespace it would
already have
over 250 extents starting off. Are there any other
performance type issues
to consider?
Should I create another extent size between 4M and the 128M?
Maybe 64M
increment?
Thanks,
Jim
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Jack C. Applewhite
INET: japplewhite_at_inetprofit.com
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Apr 03 2001 - 15:40:58 CDT
 |
 |