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:[EMAIL PROTECTED]]
> Reply To: [EMAIL PROTECTED]
> 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:[EMAIL PROTECTED]]
> 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:[EMAIL PROTECTED]]
> > Reply To: [EMAIL PROTECTED]
> > 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: [EMAIL PROTECTED]
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > 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:[EMAIL PROTECTED]]
> > 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: [EMAIL PROTECTED]
> > To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> > 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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
> >
> > 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
>
> 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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
