Re: ST-enqueue problem

In article <383D02B4.6DA21A55_at_gelrevision.nl>,   "akolk - gelrevision.nl" <akolk_at_gelrevision.nl> wrote:
> I am sorry to say that chaning enqueue_resources is not going to help
the
> ST contention problem.

I never said it would; The ST lock is one of the causes of enqueue resource contention. I suggested that the original poster check his enqueue respurces because I have encountered a couple of systems that hd ST lock problems and also had enqueue problems.

 The best thing to do from 7.3 upwards is to use
> TEMPORARY tablespace (not TEMP) for sorting. They will only get the
ST way
> less than sorting in TEMP tablespaces.
Wasn't that the first item mentioned in my post!

Most of the rest of your post deals with item that affect fragmentation, but re-reading the original post along with mine pointed me at the probable cause of the problem. Since this is an OPS system I suggest adding events to the init.ora of one instance to stop coalesing from one node.
# 10061 = stop coalesce temp segs; 10269 = stop coalesce event="10061 trace name context forever, level 10" event="10269 trace name context forever, level 10"

The Oracle white paper on ST locks that my post was based on mentioned these. Since it was written in the days of ver 7 it may be wise to contact support about using these events.
>>>>>>>>>>>>>>>>

 Another thing that you can do is:
> 1) set pct_increase to 0 for the tablespaces
This stops smon from coalesing free space. This may help prevent ST locks on systems with applications

> 2) initial and next extent should have the same size
> 3) sort should fit in one extent
>
> Anjo.
>
> markp7832_at_my-deja.com wrote:
>
> > In article <80r8tj$6tf$2_at_news2.kornet.net>,
> > "Cho, Hyon-Hyok" <cho2h_at_kt.co.kr> wrote:
> > > Hi !!
> > > I am running oracle8.0.1 OPS on IBM SP Machine...
> > > On my system, st-enqueue problem is very serious.
> > > so I'm very careful whenver I create any objects.
> > > I can bear it, too small initial or next size can cause
> > > st-enqueue contention.
> > >
> > > But I can't understand why st-enqueue arose when
> > > I rebuild index!!!!(alter index ~ rebuild ;)
> > > My index-rebuild script performe rebulding across the
> > > whole nodes, and each node performe rebulding
> > > on its own tablespaces.
> > > But when I start rebulding, I found that every command
> > > running across all nodes got a contentition problem.
> > >
> > > Is there any parameter that can avoid st-enqueue
> > > contention problem?
> > >
> > > Thanks, have a good time!!!!
> > >
> > > ===================================================
> > > At the edge of the chaos, You will find the new rule.....
> > >
> > One of the things that can cause an ST lock problem is having a non-
> > temporary temp tablespace.
> >
> > You can also run into ST enqueues when you have an environment
where a
> > lot of extent allocation (and deallocation) takes place because
these
> > operations contend on sys.uet$ and fet$ which are the real tables
under
> > sys.dba_extents and dba_free_space.
> >
> > You should verify that you have enough enqueue resources to support
> > your environment. Oracle calculates this number but at least in ver
> > 7.3 and below you could need more than it calculated. See init.ora
> > parameter enqueue_resources.
> >
> > --
> > 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.
>
>

--
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. Received on Thu Nov 25 1999 - 19:34:30 CST