Re: Problem with sequence and row cache lock

No, it seems to me that's what CACHEd sequences are there for. If your requirements insist on NOCACHE, serialization is inevitable as the data dictionary must be updated with the last value.

Cheers,

John Thomas

In article <F001.00495388.20020711091841_at_fatcity.com>, Thomas Jeff <ThomasJe_at_tce.com> writes
>
> 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-----
> From: Thomas Day [mailto:tday6_at_csc.com]
> Sent: Thursday, July 11, 2002 11:24 AM
> To: Multiple recipients of list ORACLE-L
> Subject: Re: Problem with sequence and row cache lock
>
> 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
>

-- 
John Thomas
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: John Thomas
  INET: john_at_toronto.demon.co.uk

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