RE: LOCALLY MANAGED EXTENT PERFORMANCE

From: Dogan, Ibrahim - Ibrahim <Ibrahim.Dogan_at_Lowes.com>
Date: Mon, 25 Apr 2005 16:40:51 -0400
Message-ID: <61C900F558E4184DBD8E177CC9D51F67035D76CC@msexdb06.lowes.com>

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:

that can NOT extend because there is no free extent to support its next_extent size
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_at_freelists.org=20
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
> Sent: Monday, April 25, 2005 3:22 PM
> To: oracle-l_at_freelists.org
> 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

Received on Mon Apr 25 2005 - 16:45:48 CDT