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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Query to predict failure on second extent of segment

RE: Query to predict failure on second extent of segment

From: Jesse, Rich <Rich.Jesse_at_qtiworld.com>
Date: Mon, 18 Nov 2002 11:46:08 -0800
Message-ID: <F001.00505D52.20021118114608@fatcity.com>


Yes, this is a nice query, but it doesn't address my original topic.

I've changed the subject to reflect the thread I orginally started.

Thanks,
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 5:05 PM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: How to identify objects that will fail to extend?
>
>
> 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).
Received on Mon Nov 18 2002 - 13:46:08 CST

Original text of this message

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