Re: ST-enqueue problem

I am sorry to say that chaning enqueue_resources is not going to help the ST contention problem. 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. Another thing that you can do is:

1) set pct_increase to 0 for the tablespaces
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.
Received on Thu Nov 25 1999 - 03:34:44 CST