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ý_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).