Re: Scalable sequence session prefix size

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