Please help: DB link related query

Rajneesh S-Oracle

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

Jonathan Lewis

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

John Thorton

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

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

Jonathan Lewis

John Thorton wrote: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.

No doubt you find that behaving like a boring little pedant is much easier than applying any intelligence; but if you wish to be a boring little pedant please get your pedantry right.

a) "Oracle" does not "contain" anything, Oracle is a piece of software.  You could say that an Oracle database contains various things - including rows, but if you want to say that "Oracle contains rows" you need to recognise that "Oracle" is also capable of "containing" records - check the PL/SQL reference manual 5.11.2 RECORD types.

b) That's a reasonable statement for some interpretations of "process". Of course it does demonstrate your spiteful intent to be as unhelpful and unintelligent as possible about the OP's request for information.

c) A DBLINK is not a "glorified pointer" to a remote object, it's a pointer to a service or instance. If want to claim that it's nothing but a pointer to some type of object you need to remember that it's a pointing through a (local) tnsnames entry.

d) How does a DBLINK "do what ever code has been implemented" when it is only a "glorified pointer" - pointers don't "do" anything. And if you're going to talk about things that "code" does, make sure you make clear whether you mean end-user code or Oracle's internal code.

Two incorrect claims, one case of contradicting yourself and one piece of petty vindictiveness - and yet you seem to think every naive user of the forum should be able to phrase perfect questions. Grow up.

jaramill

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