AW: Scalable sequence session prefix size

Hi,  

IMHO it should be avoided to use sequences in parallel direct load. One technique I use to stabilize PL / SQL code is to serially generate the IDs in a mapping stage table and use it as a join in the INSERT statement. This has proven itself in terms of performance and also against some oddities in the implementation of sequences  

Best regards
Ahmed      

-----Original-Nachricht-----
Betreff: Re: Scalable sequence session prefix size Datum: 2021-04-30T18:57:20+0200
Von: "Jonathan Lewis" <jlewisoracle_at_gmail.com> An: "Ghassan Salem" <salem.ghassan_at_gmail.com>      

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
<mailto: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   <mailto: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.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     <mailto: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
      <mailto: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