Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: object # of extents
Ben wrote:
> gazzag wrote:
>> Ben wrote: >>>> Frank van Bortel wrote: >>>>> Ben schreef: >>>>>> Running Oracle 9.2.0.5 Ent Ed, DMTs, UNIX AIX5L, compatible parameter = >>>>>> 8.1.0 >>>>>> >>>>> On 9i, release 2, you should really get used to locally managed >>>>> tablespaces with auto segment space management. >>>>> In that case, you will probably never even *have* 1500 extents! >>>>> >>>>> And I start worrying at about 4000 (four thousand) extents. And >>>>> even then, I doubt it would be "a concern". >>>>> >>>>> -- >>>>> Regards, >>>>> Frank van Bortel >>>>> >>>>> Top-posting is one way to shut me up... >>> well, how about 8000 then? >> Other than a perceived excessive extent usage, what "problem" are you >> attempting to solve?
In one of my databases, I have the following segments with more than 4,000 extents:
SQL> select owner,segment_name,segment_type,extents
2> from dba_segments where extents > 4000 order by 4;
OWNER SEGMENT_NAME SEGMENT_TYPE EXTENTS ---------- --------------------------- ------------------ ---------- WEBMAP SDE_BLK_8487 TABLE 4335 WEBMAP SDE_BLK_7082 TABLE 5045 WEBMAP SDE_BLK_7077 TABLE 5119
In another database, I have the following:
SQL> select owner,segment_name,segment_type,extents
2 from dba_segments where extents > 4000 order by 4;
OWNER SEGMENT_NAME SEGMENT_TYPE EXTENTS ---------- --------------------------- ------------------ ---------- NAIP SDE_BLK_954 TABLE 4166 NAIP SDE_BLK_755 TABLE 4472 NAIP SDE_BLK_758 TABLE 5342
I do not have any performance issues with any of these tables.
It has been proven, time and time again, that the number of extents does not cause performance issues, provided you are using Locally Managed Tablespaces. If you are using Dictionary Managed Tablespaces, then extent allocation and deallocation operations can be slow due to the interactions with SYS.UET$ and SYS.FET$. But LMTs do not exhibit this same behavior.
-- =================================================================== Brian Peasland oracle_dba_at_nospam.peasland.net http://www.peasland.net Remove the "nospam." from the email address to email me. "I can give it to you cheap, quick, and good. Now pick two out of the three" - UnknownReceived on Wed Jun 28 2006 - 19:29:56 CDT