Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: object # of extents

Re: object # of extents

From: Brian Peasland <oracle_dba_at_nospam.peasland.net>
Date: Thu, 29 Jun 2006 00:29:56 GMT
Message-ID: <J1LIpy.HMq@igsrsparc2.er.usgs.gov>


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?

>
> I was trying to find out if it IS a problem. I'm fairly new to being an
> Oracle DBA and this is something that I ran across, so I didn't know if
> it could possibly cause some problems if there are tons of extents. I
> would imagine if the datafiles for a tablespace are spread across
> several drives and a select had to query data from very many of those
> small extents it could possibly cause an I/O issue, couldn't it?
>

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" - Unknown
Received on Wed Jun 28 2006 - 19:29:56 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US