RE: Rollback Segment Problem

From: Mohammad Rafiq <rafiq9857_at_hotmail.com>
Date: Tue, 12 Feb 2002 14:21:43 -0800
Message-ID: <F001.0040D522.20020212135322@fatcity.com>

Jerry,
Thanks for sharing script. We might have better systems as timing was never be a issue. I just tested my script on 2 systems and it ran between 34.5 mseconds to 4.65 seconds...and on the basis of it is results we coalesce all such tablespaces. I prefer to keep pctincrease of tablespaces as 0(zero) and manually coalesing as and when it is required....

So have you ran your deletion job after coalesing or not?

Regards
Rafiq

To: <ORACLE-L_at_fatcity.com>
CC: <rafiq9857_at_hotmail.com>
Date: Tue, 12 Feb 2002 15:28:41 -0600

Rafiq,

While your script provides a lot of good information, it sure is slow on my system. It took 1:44 minutes while my script below took 420 mseconds. I was really surprised as my script has a self-join. Then I checked out dba_free_space_coalesced. It sure is complicated as it calls on two other views and a table.

select a.tablespace_name, count(a.tablespace_name) "ContinguousFreeBlocks" from dba_free_space a, dba_free_space b
where a.tablespace_name = b.tablespace_name and a.file_id = b.file_id
and a.block_id = b.block_id + b.Blocks
group by a.tablespace_name;

If ContinguousFreeBlocks is > 10, I coalesce.

Jerry Whittle
ACIFICS DBA
NCI Information Systems Inc.
jerome.whittle_at_scott.af.mil
618-622-4145

> -----Original Message-----
> From: Mohammad Rafiq [SMTP:rafiq9857_at_hotmail.com]
>
> Is your tablespace fragmented as it is not finding contingous extent ..
> TRy to coalesce your subject tablespace and try...
>
> you can use following script to check whether coalesing is required or
not..
> If percent is < 100 then coalesce it..
>
> select substr(tablespace_name,1,10)TS_NAME,total_extents
> "Total_Extnts",extents_coalesced,round(percent_extents_coalesced,0)
> from dba_free_space_coalesced
> order by tablespace_name
> /
>
> HTH
> Regards
> Rafiq
>

MOHAMMAD RAFIQ

---

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

Received on Tue Feb 12 2002 - 16:21:43 CST