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: coalesce tablespace

RE: coalesce tablespace

From: Baker, Barbara <bakerb_at_rockymountainnews.com>
Date: Tue, 25 Sep 2001 10:11:55 -0700
Message-ID: <F001.00397DEE.20010925080033@fatcity.com>

Michal:
Something is really wacky here . . . It should not take 10+ minutes just to select rows from dba_free_space_coalesced. I kinda doubt that it's a fragmentation problem, but I'm not sure what it might be. Does this slowness occur with any other selects??

Here's a table fragmentation script. You can run it and see what kind of fragmentation you have . . .
HTH
Barb

(this script is looking for extents within a range of maxextents, i.e., am I about to run out of extents.
You might want to change it a bit for your needs.)

column owner format a8

column segment_type    format a6    heading "Seg|Type"
column ext             format 9,999 heading "Ext|Used"
column maxext          format 9999  heading "Max|Ext"
COLUMN REMAINING       FORMAT 9999  HEADING "Ext|Left"
column tablespace_name format a8    heading "Tblspace|Name"


ttitle Center 'Segments With Extents Used Within &&1 Extents of Maximum' skip 2

SELECT

                owner,
                segment_name,
                segment_type,
                tablespace_name,
                sum(extents) ext,
                SUM(MAX_EXTENTS) maxext,
                SUM(MAX_EXTENTS) - SUM(EXTENTS) REMAINING,
                bytes/1024 k
FROM            dba_segments
WHERE           segment_type IN ('TABLE','INDEX')
GROUP BY        owner,
                segment_type,
                tablespace_name,
                segment_name,
                bytes/1024
HAVING          (SUM(MAX_EXTENTS) - SUM(EXTENTS)) < &&1
/

> ----------
> From: Skurský Michal[SMTP:skursky_at_brn.pvt.cz]
> Reply To: ORACLE-L_at_fatcity.com
> Sent: Tuesday, September 25, 2001 6:40 AM
> To: Multiple recipients of list ORACLE-L
> Subject: RE: coalesce tablespace
>
> Thanks for your suggestion.
> Timing in my case is (for select * from dba_free_space_coalesced)
> 8 seconds .... for Win NT 2x400MHz PII 512 MB RAM, testing database 8.0.5
> with 7 tablespaces each about 500 MB.
> 10 minutes 24 seconds for Open VMS 7.1 Alpha 2100 275 MHz, 512 MB RAM,
> testing database 8.0.5 with 17 tablespaces each about 100 MB. SQLPlus
> process which executes this select is consuming nearly 100% of cpu all the
> time.
> I am just wondering about the reason for such a big difference. Could it
> be
> caused by fragmentation? Is there useful script for determine
> fragmentation?
> I can exclude the bug while on production database (VMS on rather stronger
> machine) this select statement takes several seconds as on Win NT.
> Michal
>
>
>
>
> -----Původní zpráva-----
> Od: Baker, Barbara [mailto:bakerb_at_rockymountainnews.com]
> Odesláno: 25. září 2001 1:17
> Komu: Multiple recipients of list ORACLE-L
> Předmět: RE: coalesce tablespace
>
>
> Michal: You mention the select for the view dba_free_space_coalesced.
> I'm
> not sure what you're actually doing. However, if I "set time on" and
> issue
> the command
> select * from dba_free_space_coalesced
>
> here's elapsed time on my VMS system
> start: 16:07:44
> end: 16:07:59
>
> here's elapsed time on my Solaris system
> start: 16:05:21
> end 16:05:29
>
> The 2 databases are sized comparably. (The Solaris box has more power)
>
> Just selecting from the view should be almost instantaneous. Sounds like
> something else is going on on the VMS box.
>
> HTH.
> Barb
>
>
> > ----------
> > From: Mohammad Rafiq[SMTP:rafiq9857_at_hotmail.com]
> > Reply To: ORACLE-L_at_fatcity.com
> > Sent: Monday, September 24, 2001 4:07 PM
> > To: Multiple recipients of list ORACLE-L
> > Subject: RE: coalesce tablespace
> >
> > Is it working or not? Have you done coalesing or not?
> > As regard timings, it depends on system and number of objects on a
> > database?
> >
> > Regards
> >
> > MOHAMMAD RAFIQ
> >
> >
> >
> > Reply-To: ORACLE-L_at_fatcity.com
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Date: Mon, 24 Sep 2001 06:35:26 -0800
> >
> > Hi,
> > how is defined view dba_free_space_coalesced?
> > What reason can be, that SELECT response on this view takes me in
> databese
> > on Win NT several seconds while in database on OpenVMS it takes several
> > minutes (databases are similar).
> > Thanks for suggestions
> > Michal
> >
> >
> >
> >
> >
> >
> > -----Puvodní zpráva-----
> > Od: Mohammad Rafiq [mailto:rafiq9857_at_hotmail.com]
> > Odesláno: 20. zárí 2001 22:51
> > Komu: Multiple recipients of list ORACLE-L
> > Predmet: RE: coalesce tablespace
> >
> >
> > Following script may be used to check whether coalesing is required or
> > not.
> > If lasr column not 100% then coalesce that tbs....
> >
> > select substr(tablespace_name,1,10)TS_NAME,total_extents
> > "Total_Extnts",extents_coalesced,round(percent_extents_coalesced,0)
> > from dba_free_space_coalesced
> > /
> >
> >
> > MOHAMMAD RAFIQ
> >
> >
> >
> > Reply-To: ORACLE-L_at_fatcity.com
> > To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> > Date: Thu, 20 Sep 2001 12:21:21 -0800
> >
> > It would take contiguous free extents and make them larger extents,
> which
> > would be more likely to reuse. Especially if there are many smaller
> ones,
> > this moot if using LMT.
> >
> > It is a very quick procedure and good to do occasional, you can check in
> > dba_data_files_coalesced to see if the number is far from 100%, if it is
> > less than 75% or so, just throw a coalesce on the tablespace.
> >
> > "Do not criticize someone until you walked a mile in their shoes, that
> way
> > when you criticize them, you are a mile a way and have their shoes."
> >
> > Christopher R. Spence
> > Oracle DBA
> > Phone: (978) 322-5744
> > Fax: (707) 885-2275
> >
> > Fuelspot
> > 73 Princeton Street
> > North, Chelmsford 01863
> >
> >
> >
> > -----Original Message-----
> > Sent: Thursday, September 20, 2001 3:40 PM
> > To: Multiple recipients of list ORACLE-L
> >
> > We have tablespaces in acceptance and production that are being resized
> > for
> > growth. Pctincrease is set at 0. Would it also help to coalesce the
> > tablespace? What are the benefits of this command?
> > Thanks,
> > Sandi
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author:
> > INET: Sandi.McClary_at_flyingj.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Christopher Spence
> > INET: cspence_at_FuelSpot.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
> >
> >
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Mohammad Rafiq
> > INET: rafiq9857_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: =?ISO-8859-2?Q?Skurský_Michal? INET: skursky_at_brn.pvt.cz
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
> >
> >
> > _________________________________________________________________
> > Get your FREE download of MSN Explorer at
> http://explorer.msn.com/intl.asp
> >
> > --
> > Please see the official ORACLE-L FAQ: http://www.orafaq.com
> > --
> > Author: Mohammad Rafiq
> > INET: rafiq9857_at_hotmail.com
> >
> > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> > San Diego, California -- Public Internet access / Mailing Lists
> > --------------------------------------------------------------------
> > 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).
> >
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Baker, Barbara
> INET: bakerb_at_rockymountainnews.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: =?iso-8859-1?Q?Skursk=FD_Michal? INET: skursky_at_brn.pvt.cz
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> 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).
>

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Baker, Barbara
  INET: bakerb_at_rockymountainnews.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
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 Tue Sep 25 2001 - 12:11:55 CDT

Original text of this message

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