| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: coalesce tablespace
Do you have the same parameters in init.ora file?
Parameters like sort_are_size might be important for performace of such a
query.
What are waits during processing?
Ivo
-----Original Message-----
Sent: Wednesday, September 26, 2001 02:30 PM
To: Multiple recipients of list ORACLE-L
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)
-----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=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: Libal, Ivo INET: ivo.libal_at_knapp-systems.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 Wed Sep 26 2001 - 07:24:40 CDT
 |
 |