| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Disadvantage of Unlimited extent?
Excellent points from the Master of all Oracle masters, Mr. Kevin Loney (if it is indeed you; I love your books), I'll throw in my $.02:
One place I find MAXEXTENTS UNLIMITED very useful is for Temporary tablespaces. It can be very useful for a database which has been essentially tuned with respect to its temporary tablespace need/usage, but then a new, unexpected (or terribly ineffecient) long-running sorted query gets run while you're on vacation, which might have reached maxextents otherwise but runs to completion instead. As long as you do not have autoextend turned on for your temporary tablespace, you don't have to worry about filling up a disk, since the worst that can happen is to fill the Temporary tablespace where the temp segments are being created.
What is your opinion on this Mr. Loney? Sound idea? It has worked for me so far, but, like a pilot, a good Oracle DBA is always learning!
Kevin Loney wrote in message ...
>Ng TC wrote in message <6uev7o$rg8$1_at_newton2.pacific.net.sg>...
>>Hi,
>>
>>Can anyone tell me what is the disadvantage of setting table extents to
>>unlimited?
>
>
>Sure there are disadvantages:
>1. Dropping tables with many many extents takes a long time. The Oracle
>tables that track used and free extents, UET$ and FET$, are not indexed in
a
>way that helps the performance of drops. If your table has 2000 extents,
>then you're essentially scanning a table with at least 2000 rows, and
you're
>doing that 2000 times. Consider how that changes when you go to 10,000
>extents. I did it on 64,000 extents and it took almost a day to drop the
>table on a dedicated 12-way server.
>
>2. Setting extents to unlimited implies that you are not controlling the
>extent allocation, and hence the space usage, in your database. I prefer
to
>control that. Heck, I'd rather set maxextents to 10,000 then set it to
>unlimited.
>
>My bottom line philosophy on this: if you have unlimited physical resources
>and unlimited time for recovery, then unlimited extents makes sense for
you.
>If not, then it does not.
>
>Just an opinion.
>
>Hope this helps.
>Kevin.
>
>http://www.kevinloney.com
>
>
Received on Sat Sep 26 1998 - 00:00:00 CDT
 |
 |