Re: Rollback segment problem?

In article <8ni64h$hqk$1_at_news.hk.linkage.net>,   "FAI" <leemingf_at_pacific.net.hk> wrote:
> i've got the following message in the alert log :
>
> Fri Aug 18 08:10:25 2000
> Thread 1 advanced to log sequence 154789
> Current log# 1 seq# 154789 mem#

 0: /ufs6/dbdata/oracle/kmbrp/kmbrp_redo11
> Current log# 1 seq# 154789 mem#

1. /ufs7/dbdata/oracle/kmbrp/kmbrp_redo12
   > Fri Aug 18 08:10:44 2000
   > Errors in file /oracle/app/db/admin/kmbrp/bdump/kmbrp_smon_17630.trc:
   > ORA-01595: error freeing extent (0) of rollback segment (7))
   > ORA-01575: timeout waiting for space management resource
   > Fri Aug 18 08:10:48 2000
   > Thread 1 advanced to log sequence 154790
   > Current log# 2 seq# 154790 mem#
   
2. /ufs6/dbdata/oracle/kmbrp/kmbrp_redo21
   > Current log# 2 seq# 154790 mem#
   
3. /ufs7/dbdata/oracle/kmbrp/kmbrp_redo22
   >
   > Can anyone tell me what's the problem is?
   >
   >
   First, there is an Oracle white paper available from support on this subject.

The timeout waiting for space management resources is something I have had to live with since 1995 in one form or another. There are several possible causes but basically the problem revolves around obtaining the ST lock.

Every process that has to allocate or deallocate an extent must obtain the one ST lock in the database. Systems that are experiencing extreme high level of dynamic allocation can get this error.

Some possible solutions/work arounds.

For 7.3+, If your temporary tablespace is of type permanent make it temporary. This will end the need for sorts to obtain the ST lock.

If prior to 7.3 or you do not want to use a temporay tablespace make sure the initial = next and pctincrease = 0 for temporary tablespace.

Modify you tablespace space management policy to limit all objects in a tablespace to using one extent size so coalesing is unnecessary after truncates, rebuilds, or drops. Then set tablespace pctincrease to 0.

If your application is fairly new take a look at your object extents and for objects that have taken a lot of extents quickly alter the next extent parameter to increase the time between extents. Then when time permits consider re-organizing the objects to fit the item above.

If you are OPS stop coalescing from all but one instance via an init.ora event. Check with support for the current events/parameters to use for your version of Oracle. I think these were given to us back in 7.2 days so they may be obsolete.

# 10061 = stop coalesce temp segs; 10269 = stop coalesce event="10061 trace name context forever, level 10" event="10269 trace name context forever, level 10"

I hope this helps.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.