RE: Using V$_SEQUENCES
Date: Mon, 9 Sep 2013 13:25:40 +0000
Message-ID: <AF02C941134B1A4AB5F61A726D08DCED0DE12CDD_at_USA7109MB012.na.xerox.net>
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 11.2.0.3.x
-----Original Message-----
From: Mark W. Farnham [mailto:mwf_at_rsiz.com]
Sent: Friday, September 06, 2013 12:09 PM
To: Hameed, Amir; 'ORACLE-L'
Subject: RE: Using V$_SEQUENCES
- 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.
- 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.
- 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
"oracle-security-part-2-your-read-only-accounts-arent-that-read-only")
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.
mwf
-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org]
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?
Thanks,
Amir
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Mon Sep 09 2013 - 15:25:40 CEST