sequence caches and synonyms

[Patrice sur GMail]

Strange situation here,

We noticed that if we ask a package to return the next value which would be
used for a table entry (done using a sequence), we get a number.

If we go through a local synonym to ask the same package to answer the same
question, we receive a slightly different answer.

Is it possible that there are two caches of sequences values, depending on
how the package which refers to the sequence is executed? I suppose if two
users are entering records concurrently in the same table, they each have
their own set of cached sequence values (?).

We don't think this happened in 11g, it only seems to be happening in our
12.2 databases.

I will log an SR as well, just thought I might ask this list first.

-- Patrice
My profiles: [image: Facebook]
<http://www.facebook.com/home.php?#!/profile.php?id=100000206805521>[image:
LinkedIn] <http://ca.linkedin.com/pub/patrice-boivin/a/933/5a9>[image:
Twitter] <http://www.twitter.com/PatriceBoivin>

[Andy Sayer]

There is one cache for the sequence per instance. All sessions within one
instance share that same cache. If the cache size is 20 and the increment
size is 1: I do nextVal and get 1, another session in the same instance
does nextVal and gets 2. This has always been the case AFAIK.

Synonyms shouldn't have any effect on this - If I refer to the sequence
directly or via a synonym that points to it, I will get the same value. If
I do nextVal using the direct sequence name and currVal from the synonym, I
will get the same value. If I call the synonym nextVal, I will increment
the sequence in exactly the same way I would if I referenced the sequence
directly.

From what you've described, it sounds like you're just doing nextVal which
will give an incremented value for each call - so you obviously will get
slightly different values.

What exactly is the code in your package doing? Can you copy and paste
your test case so we can see what you're confused about?

Hope this helps,
Andy

Post by Patrice sur GMail
Strange situation here,
We noticed that if we ask a package to return the next value which would
be used for a table entry (done using a sequence), we get a number.
If we go through a local synonym to ask the same package to answer the
same question, we receive a slightly different answer.
Is it possible that there are two caches of sequences values, depending on
how the package which refers to the sequence is executed? I suppose if two
users are entering records concurrently in the same table, they each have
their own set of cached sequence values (?).
We don't think this happened in 11g, it only seems to be happening in our
12.2 databases.
I will log an SR as well, just thought I might ask this list first.
-- Patrice
My profiles: [image: Facebook]
Twitter] <http://www.twitter.com/PatriceBoivin>