Re: Scalable sequence session prefix size

Ghassan,

Thanks for the warning and information about possible patches

Regards
Jonathan Lewis

On Fri, 30 Apr 2021 at 17:15, Ghassan Salem <salem.ghassan_at_gmail.com> wrote:

> Thomas,
> As Jonathan said, the nextval should be different in any case, even if the
> instance&session ids are the same. The problem is that in some PDML cases
> (like yours), the bug makes for duplicate nextval. IT happens that i your
> case the instance+session parts are the same for some slaves, this can
> happen.
> If this cannot wait till you upgrade to 19.11, then ask for a backport of
> bug 31423645 (hidden).
>
> regards
>
> On Fri, Apr 30, 2021 at 5:55 PM Thomas Ranchon <thomas_at_ranchon.org> wrote:
>
>>
>> Oracle version is 18.6 in extended RAC 2 nodes
>> OS is AIX 7.2
>> Insert is done in PDML 16 local to the first node
>> Migration to 19c is planned in the next few months.
>>
>> I've generated
>> [image: image.png]
>> As you can see there are only 15 different sequences prefixes
>> Each prefix have ~5 millions rows except 101742 which have ~10 millions
>> rows, sequence suffix is ~5 millions for each sequence prefix including
>> 101742, almost all sequence number generated for this prefix are duplicated.
>>
>> Checking in dba_hist_active_sess_history for this query of the 16
>> sessions doing the insert there was session_id 742 and 2742 which share the
>> sequence prefix 101742.
>>
>> Sequence is creating with this command :
>> CREATE SEQUENCE load_seq MINVALUE 1 INCREMENT BY 1 MAXVALUE 999999999
>> CYCLE CACHE 1000 NOORDER SCALE EXTEND;
>>
>> The insert is like this one (table names and columns names changed) :
>>
>>> INSERT /*+ APPEND */
>>>
>>> INTO table_dst(id_load, id_1, id_2, id_3, id_4, id_5, dt)
>>>
>>> SELECT id_load.NEXTVAL, id_1, id_2, id_3, id_4, id_5, dt
>>>
>>> FROM (SELECT id_1, id_2, id_3, id_4, id_5, dt
>>>
>>> FROM table_src_1
>>>
>>> UNION
>>>
>>> SELECT id_1, id_2, id_3, id_4, id_5, dt
>>>
>>> FROM table_src_2
>>>
>>> UNION
>>>
>>> SELECT id_1, id_2, id_3, id_4, id_5, dt
>>>
>>> FROM table_src_3
>>>
>>> UNION
>>>
>>> SELECT id_1, id_2, id_3, id_4, id_5, dt
>>>
>>> FROM table_src_4
>>>
>>> UNIONid_1, id_2, id_3, id_4, id_5, dt
>>>
>>> FROM (SELECT id_1, id_2, id_3, id_4, id_5, start_date, end_date
>>>
>>> FROM table_src_5
>>>
>>> UNION
>>>
>>> SELECT id_1, id_2, id_3, id_4, id_5, start_date, end_date
>>>
>>> FROM table_src_6
>>>
>>> UNION
>>>
>>> SELECT id_1, id_2, id_3, id_4, id_5, start_date, end_date
>>>
>>> FROM table_src_7)
>>>
>>> UNPIVO(dt FOR id_type_date IN ( start_date AS 0, end_date AS
>>>> 0)));
>>>
>>>
>>>>
>>>
>> Regards,
>> Thomas
>>
>> Le ven. 30 avr. 2021 à 16:52, Jonathan Lewis <jlewisoracle_at_gmail.com> a
>> écrit :
>>
>>> That shouldn't give you duplicate IDs - it is likely to give you two
>>> sessions generating values that belong in the same leaf block if you're
>>> using the sequence to generate a unique/primary key, but the lower N digits
>>> of the sequence number ought to be different.
>>>
>>> Regards
>>> Jonathan Lewis
>>>
>>>
>>> On Fri, 30 Apr 2021 at 14:59, Thomas Ranchon <thomas_at_ranchon.org> wrote:
>>>
>>>> Hi all,
>>>>
>>>> I have an insert statement that is using a scalable sequence to
>>>> generate an id.
>>>> This insert statement is running in PARALLEL(16) mode.
>>>> Sometimes duplicate ids are generated because 2 of the 16 sessions
>>>> doing the insert share the same (session_id%1000) that is used by the
>>>> scalable sequence to make the session prefix of the sequence.
>>>>
>>>> Is there a way to increase the size of the session prefix in the
>>>> sequence that would avoid those duplicates ids ?
>>>>
>>>> Thank you and best regards
>>>> Thomas Ranchon
>>>>
>>>

--
http://www.freelists.org/webpage/oracle-l




image/png attachment: image.png