Re: object # of extents

From: Ben <balvey_at_comcast.net>
Date: 29 Jun 2006 06:31:44 -0700
Message-ID: <1151587904.649746.184470@i40g2000cwc.googlegroups.com>

Brian Peasland wrote:
> 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

There in lies the problem. We are still using DMTs. If should go to reorg the tables that have over 4 or 8000 extents, would it be best to exp/truncate/imp or alter table move? Received on Thu Jun 29 2006 - 08:31:44 CDT