Re: Scalable sequence session prefix size

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