RE: LOCALLY MANAGED EXTENT PERFORMANCE

Just forgot to ask..=20

Do you mind if you share your "alert" with us ?

I was wondering how long would it take to run that alert which
"computes" next N extents if you have PCT_INCREASE set for a table ...

I'm impatiently waiting ..

The one I have for my alert uses the following SQL.. It can only check
the next extent not next N extents..

TITLE: 'OBJECTS THAT MAY NOT BE ABLE TO EXTEND'

-- 'D'=3D database batch 'O'=3D OS script/program
PARAMETER TYPE: 'D'

<DESCRIPTION>
Generates an alert if it finds a table or index:
 a) that can NOT extend because there is no free extent to support
    its next_extent size
 b) that has max 5 extents to reach its MAXEXTENTS.
<DESCRIPTION>

<SUGGESTED RECOVERY>
This alert helps DBA identifiy the space requirements in future.
Possible solutions:
Consider changing next extent size.
Consider defragmenting tablespace.
Consider adding space to the tablespace.
<SUGGESTED RECOVERY>

<MONITORING CODE>
BEGIN
 DBA_DHC.CHECKIN ('CANNOTEXTEND',
SP_GENERIC_REPORT(
'SELECT s.segment_type, s.owner || ''.'' || s.segment_name,
s.tablespace_name,      '               ||
'       ROUND(s.bytes / (1024. * 1024.), 0) AS sizemb, '
||
'       ROUND(s.next_extent * (1. + s.pct_increase/100) / 1024., 0) AS
next_extent, '          ||
'       extents, max_extents, '
||
'       ''no free extent left'' AS reason '
||
'FROM   dba_segments s, '
||
'      (SELECT tablespace_name, MAX(bytes) AS bytes '
||
'       FROM   dba_free_space '
||
'       GROUP BY tablespace_name) f '
||
'WHERE  s.tablespace_name =3D f.tablespace_name AND     s.next_extent >
0 AND '                 ||
'       f.bytes <  s.next_extent * (1. + s.pct_increase/100) AND '
||
'       s.segment_type NOT IN (''TEMPORARY'', ''TYPE2 UNDO'', ''UNDO'',
''CACHE'', ''ROLLBACK'') '      ||
'  UNION ALL '
||
'SELECT s.segment_type, s.owner || ''.'' || s.segment_name,
s.tablespace_name, '                    ||
'       ROUND(s.bytes / (1024. * 1024.), 0) AS sizemb,  '
||
'       ROUND(s.next_extent / 1024., 0) AS next_extent, '
||
'       extents, max_extents, '
||
'       (s.max_extents - s.extents) || '' extents left to MAXEXTENTS''
AS reason '             ||
'FROM   dba_segments s '
||
'WHERE  s.segment_type NOT IN (''TEMPORARY'', ''TYPE2 UNDO'', ''UNDO'',
''CACHE'', ''ROLLBACK'') AND '  ||
'       s.max_extents - s.extents <=3D 5 '
||
'ORDER BY 1, 2' ,
1, 'TYPE', 10, 'OBJECT NAME', 35, 'TABLE SPACE', 17, 'SIZE(MB)', 8,
'NEXT EXT.(KB)', 13, 'EXTENTS', 8, 'MAX EXTENTS', 11, 'CANNOT EXTENT
REASON', 25, p_textwrap =3D> 'Y')
);

END;
/
<MONITORING CODE>

Thanks,
=20
Ibrahim DOGAN
Sr. Sybase/Oracle DBA
www.lowes.com


> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx=20
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
> Sent: Monday, April 25, 2005 3:22 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE
>=20
>=20
> From a production-support perspective...
>=20
> For years, we've been using a script that would alert if any=20
> segments in a tablespace are going to run out of space within=20
> "N" extents (i.e. 5, 10, whatever).  How do you do this for=20
> autoallocate tablespaces?
>=20
> I know that some folks have reverse-engineered the sizing=20
> algorithm for autoallocate, but I don't think it is yet=20
> documented anywhere.  Which means that Oracle can feel free=20
> to "tweak" it whenever they wish...
>=20
> ...which means lots of pages in the middle of the night...
>=20
>=20
>=20
--
http://www.freelists.org/webpage/oracle-l

Other related posts: