Oracle-L: Re: Problem with sequence and row cache lock

From: Thomas Day <tday6_at_csc.com>
Date: Thu, 11 Jul 2002 08:23:34 -0800
Message-ID: <F001.004950D7.20020711082334@fatcity.com>

ORDER or NOORDER? If the sequence is not cached then Oracle has to find the last number generated and generate the next one. If the sequence is ordered (default) then Oracle must satisfy the first request for NEXTVAL before it can go on to the next request. If order is not important try NOORDER and see if the locks don't go away.

                                                                                           
                    Thomas Jeff                                                            
                    <ThomasJe            To:     Multiple recipients of list ORACLE-L      
                    @tce.com>            <ORACLE-L_at_fatcity.com>                            
                    Sent by: root        cc:                                               
                                         Subject:     Problem with sequence and row cache  
                                         lock                                              
                    07/11/2002                                                             
                    11:43 AM                                                               
                    Please                                                                 
                    respond to                                                             
                    ORACLE-L

I noticed some statements hung with respect to selecting from a sequence. The sequence is not cached nor pinned. At any rate, these processes appear to be piling up, waiting to acquire a data dictionary lock.

Why would this be the case?

Username SPID O/S User Process Sid, Ser# Logon Idle
------------ -------- --------------- ---------- ------------ --------------- ----------

ECM          37872    vignette        20427       211,22415    07-10-02
11:37  20:26:58
ECM          36428    vignette        21387       181,29256    07-10-02
11:56  20:23:45
ECM          28044    vignette        29813       17,4904      07-11-02
08:05  0:0:0
ECM          53278    vignette        28240       44,8865      07-11-02
07:53  0:0:0
ECM          83916    vignette        21164       228,857      07-10-02
11:52  20:25:49
ECM          32162    vignette        22132       54,54828     07-10-02
12:08  20:23:1

SID HASH_VALUE SQL_TEXT

O/S User

----- ----------
----------------------------------------------------------------
---------------

   44 4074667660 SELECT TNE_SPAREPART_LIST_TEMP_SEQ.NEXTVAL   FROM DUAL vignette
   54 4074667660 SELECT TNE_SPAREPART_LIST_TEMP_SEQ.NEXTVAL   FROM DUAL vignette
108 4074667660 SELECT TNE_SPAREPART_LIST_TEMP_SEQ.NEXTVAL   FROM DUAL
vignette
181 4074667660 SELECT TNE_SPAREPART_LIST_TEMP_SEQ.NEXTVAL   FROM DUAL
vignette
211 4074667660 SELECT TNE_SPAREPART_LIST_TEMP_SEQ.NEXTVAL   FROM DUAL
vignette
228 4074667660 SELECT TNE_SPAREPART_LIST_TEMP_SEQ.NEXTVAL   FROM DUAL
vignette

SID EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3

----- --------------- ------------------- --------- ------------ ----------

44 row cache lock cache id 13 mode 0 request 5

   54                 cache id            13 mode            0 request

5
108                 cache id            13 mode            0 request
5
228                 cache id            13 mode            0 request
5
211                 cache id            13 mode            0 request
5
181                 cache id            13 mode            0 request
5

SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ----- -- ---------- ---------- ---------- ---------- ---------- ----------

   54 TX     262215     292550          6          0      74738          0
   54 TM      50215          0          3          0      74746          0


  181 TX     262203     292536          6          0      74750          0

  181 TM      50215          0          3          0      74755          0

  181 TO      38012          1          3          0      77372          0

  181 TO      38013          1          3          0      77372          0

  181 TO      38011          1          3          0      77290          0

  211 TX     589862     289307          6          0      74982          0

  211 TM      50215          0          3          0      74982          0

  211 TO      38013          1          3          0      78470          0

  211 TO      38011          1          3          0      78459          0

  211 TO      38012          1          3          0      78470          0

  228 TX     327747     290803          6          0      74885          0

  228 TM      50215          0          3          0      74887          0

  228 TO      38012          1          3          0      77463          0

228 TO 38013 1 3 0 77463 0
228 TO 38011 1 3 0 77464 0

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas Day
  INET: tday6_at_csc.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 Thu Jul 11 2002 - 11:23:34 CDT