Re: Why do I see multiple ST locks?

yong321_at_yahoo.com (Yong Huang) wrote in message news:<b3cb12d6.0106151315.5c2034b1_at_posting.google.com>...
> Oracle 8.0.5.0.0 Enterprise on Sparc Solaris 2.6. TEMP tablespace is
> type PERMANENT (which should be changed soon). At the moment of high
> transaction rate on this data warehouse, a SQLPlus screen shot (I did
> some editing only for cleaner display; SID 15 is SMON):
>
> SQL> select * from v$lock where type = 'ST';
>
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
>
> 791D1BAC 791D1BBC 15 ST 0 0 6 0 22 1
> 791D20D4 791D20E4 68 ST 0 0 0 6 22 0
>
> And after a while:
>
> ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
>
> 791D1BAC 791D1BBC 15 ST 0 0 6 0 14 1
> 791D1A08 791D1A18 27 ST 0 0 0 6 9 0
> 791D18A0 791D18B0 68 ST 0 0 0 6 12 0
> 791D1990 791D19A0 76 ST 0 0 0 6 9 0
> 791D1A44 791D1A54 159 ST 0 0 0 6 12 0
>
> I thought there should only be one ST enqueue in the entire database.
> So what's wrong?
>
> Yong Huang
> yong321_at_yahoo.com

Once you change your temp tablespace to type "temporary" you won't see this again. If you use permenant tablespace for sort, all sort segments are de-allocated when the session ends. The next sorting session has to re-allocate its own extents, thus causing ST enqueue wait.

Using a temporary tablespace for sort, all sort segments are re-used. The de-allocation only occurs at shutdown. This avoids ST enqueue in sorting.

-Bass Chorng Received on Wed Jun 20 2001 - 11:54:56 CDT