Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Problem with sequence and row cache lock

RE: Problem with sequence and row cache lock

From: Thomas Jeff <ThomasJe_at_tce.com>
Date: Thu, 11 Jul 2002 09:18:41 -0800
Message-ID: <F001.00495394.20020711091841@fatcity.com>


Thanks for the reply, but it seems to be the problem you're describing is exactly
what sequences are there for - to prevent serialization and eliminate this type
of waiting.

The sequences is ORDERED, BTW.

-----Original Message-----
Sent: Thursday, July 11, 2002 11:24 AM
To: Multiple recipients of list ORACLE-L

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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Thomas Jeff
  INET: ThomasJe_at_tce.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 - 12:18:41 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US