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: Skurský Michal <skursky_at_brn.pvt.cz>
Date: Wed, 26 Sep 2001 04:17:40 -0700
Message-ID: <F001.00398CA7.20010926043022@fatcity.com>

Thanks for reply, especialy to Barbara and Bruce. I made some experiments:
I think, fragmentation plays no role (PERCENT_BLOCKS_COALESCED is mostly 100). Execution plan is the same on Win NT and Open VMS. Optimizer methods choose and rule gives almost the same results. Select from table FET$ ( on this table the views dba_free_space_coalesced_tmp1, ...tmp2 are defined) is almost instantaneous. Then I created XXX view with the same definition as dba_free_space_coalesced_tmp1 view.
create or replace view XXX as
select ts#, count(*) extents_coalesced, sum(length) blocks_coalesced from sys.fet$ a
where not exists (
  select * from sys.fet$ b

  where b.ts#=a.ts# and
        b.file#=a.file# and
        a.block#=b.block#+b.length)

group by ts# ;
Select * from xxx takes 17 minutes on OpenVMS. The problem is perhaps lack of physical memory ?. Can you give me any suggestion how to verify this assumption? Thanks Michal

-----Původní zpráva-----
Od: Reardon, Bruce (CALBBAY)
[mailto:Bruce.Reardon_at_comalco.riotinto.com.au] Odesláno: 26. září 2001 5:55
Komu: Multiple recipients of list ORACLE-L Předmět: RE: coalesce tablespace

Michal,

It might not be related, but there have been a number of known bugs when selecting against data dictionary tables whilst using the CBO.

So, if you are using the CBO on VMS, then try doing "alter session set optimizer_goal=rule;" and then repeating the dba_free_space_coalesced query.

You could also try doing the query as sys with autotrace on, to compare the execution plan on the 2 systems.

Regards,
Bruce Reardon

-----Original Message-----
Sent: Tuesday, 25 September 2001 10:40

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

-----Puvodní zpráva-----
Od: Baker, Barbara [mailto:bakerb_at_rockymountainnews.com] Odesláno: 25. zárí 2001 1:17

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]
> Sent: Monday, September 24, 2001 4:07 PM
>
> 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
>
> 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
>
> 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
>
> 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
>
> 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: Reardon, Bruce (CALBBAY)
  INET: Bruce.Reardon_at_comalco.riotinto.com.au

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).
Received on Wed Sep 26 2001 - 06:17:40 CDT

Original text of this message

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