Forum Stats

  • 3,728,023 Users
  • 2,245,521 Discussions
  • 7,853,249 Comments

Discussions

Please help: DB link related query

Rajneesh S-Oracle
Rajneesh S-Oracle Posts: 402 Employee
edited February 2020 in SQL & PL/SQL

Hi All,

I am curious to know how DB link process records, is it row by row processing or it is in bulk ?

Thanks,

Rajneesh

Andris Perkons-OracleRajneesh S-OracleUser_M5BJOSven W.Dom Brooks

Best Answer

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited June 2019 Accepted Answer

    If the local session is fetching rows from a remote table in some way then the process is essentially array based.

    The local and remote sessions will negotiate the "session data unit" (SDU) size based on the sqlnet.ora setting and then each call across the DB link will send back an array of rows based on that size.  If the local array fetch size is larger than the number of rows that will fit the SDU size then a single local fetch will turn into multiple DB link round trips, showing one "SQL*Net message from dblink" wait followed by one or more "SQL*Net more data from dblink".  (There's also a little overhead of negotiation, opening and closing cursors.

    For example - from SQL*Plus

    set arraysize 20

    select * from [email protected]_db where rownum <= 20000;

    Event                                             Waits
    -----                                             -----   
    SQL*Net message from client                       1,004
    SQL*Net message to client                         1,004
    SQL*Net message to dblink                         1,003
    SQL*Net message from dblink                       1,003

    set arraysize 2000

    select * from [email protected]_db where rownum <= 20000;

    Event                                             Waits    
    -----                                             -----   
    SQL*Net message from client                          14
    SQL*Net message to client                            14
    SQL*Net more data to client                          96
    SQL*Net message to dblink                            13
    SQL*Net message from dblink                          13
    SQL*Net more data from dblink                        96

    In the first case the requirement for each client fetch (20 rows) fits in one SDU, so turns into exactly one "SQL*Net message to/from dblink".

    In the second case the requirement for each client fetch (2,000 rows) is too big for an SDU, so each client fetch turns into one message to/from dblink plus 7 or 8 "more data from dblink".

    (In passing, the session activity statistics "SQL*Net roundtrips to/from client", "SQL*Net roundtrips to/from dblink" count the message to/from, a "more data" event does not count as a separate roundtrip.

    Regards

    Jonathan Lewis

    Rajneesh S-OracleUser_M5BJO

Answers

  • John Thorton
    John Thorton Member Posts: 14,493 Silver Crown
    edited June 2019
    3988035 wrote:Hi All,I am curious to know how DB link process records, is it row by row processing or it is in bulk ?

    Oracle does NOT contain records; only rows.

    DBLINK does not "process records" or rows.

    DBLINK is a glorified pointer to remote object (package, table, etc.).

    DBLINK does not know or care if processing is done row by row or in bulk & will do what ever code has been implemented.

  • Jonathan Lewis
    Jonathan Lewis Member Posts: 9,579 Gold Crown
    edited June 2019 Accepted Answer

    If the local session is fetching rows from a remote table in some way then the process is essentially array based.

    The local and remote sessions will negotiate the "session data unit" (SDU) size based on the sqlnet.ora setting and then each call across the DB link will send back an array of rows based on that size.  If the local array fetch size is larger than the number of rows that will fit the SDU size then a single local fetch will turn into multiple DB link round trips, showing one "SQL*Net message from dblink" wait followed by one or more "SQL*Net more data from dblink".  (There's also a little overhead of negotiation, opening and closing cursors.

    For example - from SQL*Plus

    set arraysize 20

    select * from [email protected]_db where rownum <= 20000;

    Event                                             Waits
    -----                                             -----   
    SQL*Net message from client                       1,004
    SQL*Net message to client                         1,004
    SQL*Net message to dblink                         1,003
    SQL*Net message from dblink                       1,003

    set arraysize 2000

    select * from [email protected]_db where rownum <= 20000;

    Event                                             Waits    
    -----                                             -----   
    SQL*Net message from client                          14
    SQL*Net message to client                            14
    SQL*Net more data to client                          96
    SQL*Net message to dblink                            13
    SQL*Net message from dblink                          13
    SQL*Net more data from dblink                        96

    In the first case the requirement for each client fetch (20 rows) fits in one SDU, so turns into exactly one "SQL*Net message to/from dblink".

    In the second case the requirement for each client fetch (2,000 rows) is too big for an SDU, so each client fetch turns into one message to/from dblink plus 7 or 8 "more data from dblink".

    (In passing, the session activity statistics "SQL*Net roundtrips to/from client", "SQL*Net roundtrips to/from dblink" count the message to/from, a "more data" event does not count as a separate roundtrip.

    Regards

    Jonathan Lewis

    Rajneesh S-OracleUser_M5BJO
  • jaramill
    jaramill Member Posts: 4,299 Gold Trophy
    edited July 2019
    3988035 wrote:Hi All,I am curious to know how DB link process records, is it row by row processing or it is in bulk ?Thanks,Rajneesh

    From the FAQ (Frequently Asked Questions) link on --> , please update the subject title of your thread based on #2

    2) Thread Subject lineGive your thread a meaningful subject, not just "help please", "Query help" or "SQL". This is the SQL and PL/SQL forum. We know your question is going to be about those things, make it meaningful to the type of question so that people with the right sort of knowledge can pick it up and those without can ignore it. Never, EVER, mark your subject as "URGENT" or "ASAP".  This forum is manned by volunteers giving their own time to help and your question is never urgent or more important than their own work or than other people's questions. It may be urgent to you, but that's not forum members issue.  By marking your question as urgent you are actually less likely to get a good response, as members will choose to ignore such questions or respond unfavorably.

    But to answer your question, it processes records "as-if" the table were in your "local" schema.  Row-by-row or bulk processing has nothing to do with database links.

    You're confusing that with PL/SQL CURSOR FOR-LOOPS, and BULK COLLECT into arrays.

    Here's the link to the documentation for the BULK COLLECT clause --> https://docs.oracle.com/database/121/LNPLS/tuning.htm#LNPLS891

    Here's the link to the documentation on Database Links --> https://docs.oracle.com/database/121/SQLRF/statements_5006.htm#SQLRF01205

    User_M5BJO
Sign In or Register to comment.