RE: Query to predict failure on second extent of segment

OK, once again, this isn't what I'm looking for. Comparing NEXT_EXTENT*2 to the largest free space will only work in some cases. Consider that a segment has NEXT_EXTENT of 30M and there are two 40M free spaces in the TS. The segment clearly has enough room to extend twice, but comparing NEXT_EXTENT*2 to the largest free space may not see this.

I've changed the subject back to reflect the original thread that I started.

Thanks,
Rich

Rich Jesse                           System/Database Administrator
Rich.Jesse_at_qtiworld.com              Quad/Tech International, Sussex, WI USA

> -----Original Message-----
> From: Ron Rogers [mailto:RROGERS_at_galottery.org]
> Sent: Monday, November 18, 2002 6:49 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: How to identify objects that will fail to extend?
>
>
> Rich,
> How about using the next_extent*2 compared with the select max(bytes)
> from dba_free_space where tablespace_name ='tablespace name in
> question';
> That will show you if there is enough free space for two extents or
> more.
> I display sum(bytes) and max(bytes) to give a complete picture of the
> free space and then manually increasing the datafile(if possible) when
> there are multiple datafiles to allow for the needed expantion.
> Ron
>
> >>> Govind.Arumugam_at_alltel.com 11/15/02 06:04PM >>>
> Okay. Let me try this!
>
> The largest column will have the biggest extent size that the
> tablespace can accommodate next time. You might save this information
> in a temp. table and have the other query to check against this.
>
>
> select substr(a.tablespace_name,1,20) tablespace,
> round(sum(a.total1)/1024/1024, 1) Total,
> round(sum(a.total1)/1024/1024, 1)-round(sum(a.sum1)/1024/1024, 1)
> used,
> round(sum(a.sum1)/1024/1024, 1) free,
> round(sum(a.sum1)/1024/1024, 1)*100/round(sum(a.total1)/1024/1024, 1)
> pct_free,
> round(sum(a.maxb)/1024/1024, 1) largest,
> max(a.cnt) fragments
> from
> (select tablespace_name, 0 total1, sum(bytes) sum1,
> max(bytes) MAXB,
> count(bytes) cnt
> from dba_free_space
> group by tablespace_name
> union
> select tablespace_name, sum(bytes) total1, 0, 0, 0 from dba_data_files
> group by tablespace_name) a
> group by a.tablespace_name
>
>
> -----Original Message-----
> Sent: Friday, November 15, 2002 4:54 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Nope. If a segment has a NEXT EXTENT of 20M and the two largest
> contiguous
> free spaces in it's TS are 30M and 15M, the second extent (i.e. two
> extends
> to that segment) would fail, but would not show up in the query.
> That's
> what spawned the complexity of my SQL.
>
> Rich
>
>
> Rich Jesse System/Database Administrator
> Rich.Jesse_at_qtiworld.com Quad/Tech International, Sussex,
> WI USA
>
> > -----Original Message-----
> > From: Govind.Arumugam_at_alltel.com [mailto:Govind.Arumugam_at_alltel.com]
>
> > Sent: Friday, November 15, 2002 1:44 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: How to identify objects that will fail to extend?
> >
> >
> > If PCT_INCREASE is set to 0, then can't we simply compare
> > next_extent*2 > ( sub-query )?
> >
> >
> > -----Original Message-----
> > Sent: Friday, November 15, 2002 12:40 PM
> > To: Multiple recipients of list ORACLE-L
> >
> >
> > Thanks, but the next extent is the easy one. As I mentioned,
> > I'm already
> > running a similar query hourly.
> >
> > Rich
> >
> >
> > Rich Jesse System/Database Administrator
> > Rich.Jesse_at_qtiworld.com Quad/Tech International,
> > Sussex, WI USA
> >
> > > -----Original Message-----
> > > From: Govind.Arumugam_at_alltel.com
> [mailto:Govind.Arumugam_at_alltel.com]
> > > Sent: Friday, November 15, 2002 10:14 AM
> > > To: Multiple recipients of list ORACLE-L
> > > Subject: How to identify objects that will fail to extend?
> > >
> > >
> > > List,
> > >
> > > There was a question as to how to identify objects that will
> > > fail to extend?
> > >
> > > This is what we do.
> > >
> > > SELECT owner, tablespace_name, segment_name, next_extent
> > > FROM dba_segments ds
> > > WHERE tablespace_name != 'TEMP'
> > > AND next_extent > ( SELECT max(bytes)
> > > FROM dba_free_space
> > > WHERE tablespace_name=ds.tablespace_name)
> > > ORDER BY 1, 2;
> > >
> > > -----Original Message-----
> > > Sent: Thursday, November 14, 2002 4:54 PM
> > > To: Multiple recipients of list ORACLE-L
> > >
> > >
> > > Hi all,
> > >
> > > Until a whole mass of astrological confluences happen, I'm
> > stuck with
> > > dictionary-managed tablespaces on 8.1.7 on HP/UX 11.0. And
> > > we're having
> > > some space/growth issues right now that I want (need!) to be more
> > > proactive
> > > with. So, based on several factors -- most political -- I
> > > want to run a
> > > daily report that tells me when a segment will not be able to
> extend
> > > twice.
> > > (We're already running the single extent failure hourly.)
> > >
> > > After looking on the net, I found some queries to do this,
> > > but all I saw
> > > were severely flawed. So, I rolled my own. The only problem
> > > I can see
> > > with
> > > it for dictionary TSs is when the RANK() has multiple matches
> > > for first
> > > and
> > > second (e.g. TS "MY_BIG_TS" has it's largest contiguous
> > free spaces of
> > > 40M,
> > > 10M, and 10M). Unfortunately, I'm stumped as to how to
> > prevent this.
> > >
> > > Anyone care to comment on this load of SQueaL? Thx! :)
> > >
> > > Rich
> > >
> > > Rich Jesse System/Database Administrator
> > > Rich.Jesse_at_qtiworld.com Quad/Tech International,
> > > Sussex, WI
> > > USA
> > >
> > >
> > >
> > > SELECT ds.owner, ds.segment_name, ds.segment_type,
> > ds.tablespace_name,
> > > ds.next_extent/1024 "Next ext", fs2.max_free/1024 "Max Free",
> > > fs2.min_free/1024 "2nd Max Free", fs2.free_spaces
> > > FROM dba_segments ds,
> > > (
> > > SELECT tablespace_name, MAX(bytes) max_free, MIN(bytes)
> > > min_free,
> > > count(*) free_spaces
> > > FROM
> > > (
> > > SELECT tablespace_name, bytes,
> > > RANK() OVER (PARTITION BY tablespace_name
> > > ORDER BY tablespace_name, bytes DESC)
> > > byte_rank
> > > FROM dba_free_space
> > > )
> > > WHERE byte_rank < 3
> > > GROUP BY tablespace_name
> > > ) fs2
> > > WHERE ds.segment_type IN ('INDEX','TABLE')
> > > AND fs2.tablespace_name = ds.tablespace_name
> > > AND (
> > > ((ds.next_extent > fs2.min_free OR fs2.free_spaces < 2)
> > > AND ds.next_extent*2 > fs2.max_free)
> > > OR ds.next_extent > fs2.max_free
> > > )
> --
>

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jesse, Rich
  INET: Rich.Jesse_at_qtiworld.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).