From: Hameed, Amir <>
Date: Mon, 9 Sep 2013 13:25:40 +0000
Message-ID: <>

Our custom programs get transaction IDs from standard Oracle tables (MTL_MATERIAL_TRANSACTIONS, etc.) that are generated from sequences and use those TX ids to see when was the last time a custom job was run and are noticing large gaps. Oracle sequences are based on CACHE+NOORDER. We know the root cause of the issue and would like to use V$_SEQUENCES.NEXTVALUE to fix it. At this point, I am only interested in finding out if this view is protected by latches, etc., to ensure that before we go down this path, we know if there are any performance implications. RDBMS version is

-----Original Message-----
From: Mark W. Farnham [] Sent: Friday, September 06, 2013 12:09 PM To: Hameed, Amir; 'ORACLE-L'
Subject: RE: Using V$_SEQUENCES

  1. You said RAC, so you probably need to consider whether you want gv$_sequences and to include inst_id in your diagnostics, or whether v$_sequences, which includes the filter to the current inst_id.
  2. If you do pull from gv$_sequences, it's going to have to check with all the instances you've got running, so in that case you might put some pressure on your network transport and latches or mutexes. I'm trying to remember whether Arup posted one of his classically easy to follow examples of how this works or not. Nope, can't remember. I won't attempt it off the top of my head, 'cause I'd probably get some detail wrong and regret it.
  3. If you want to minimize possible hilarity I suppose you could create a custom read only view limiting the values selected to relevant schema owners and/or sequence names and including a dummy zero row select union all view so no one can even attempt to lock it. (See Tanel Poder's blog which I *think* is still relevant

Do you have a problem description of the issue? Are you attempting to use cached, ordered sequences in RAC to get a monotonically gapless sequence of numbers? (Forgive me for suspecting without real evidence that there may be an analytical cure for the application problem, qualitatively like: Well that only "worked" [or the problem escaped detection] by luck in non-RAC and
"luck" is sparse in RAC.)

What release?

That's all that immediately comes to mind.


-----Original Message-----
From: [] On Behalf Of Hameed, Amir
Sent: Friday, September 06, 2013 10:10 AM To: ORACLE-L
Subject: Using V$_SEQUENCES

Hi Folks,
We have a need to use V$_SEQUENCES.NEXTVALUE column in our custom programs to try and solve an issue that was introduced when we implemented RAC and is impacting our custom programs. Since this view is only available to the SYS schema, I am not sure what the implications are (performance, etc.) in exposing it to our custom schema. Are there any suggestions on whether it should be exposed to a non-SYS account?


Received on Mon Sep 09 2013 - 15:25:40 CEST

Original text of this message