Discussions
Categories
- 196.9K All Categories
- 2.2K Data
- 239 Big Data Appliance
- 1.9K Data Science
- 450.3K Databases
- 221.7K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 550 MySQL Community Space
- 478 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3K ORDS, SODA & JSON in the Database
- 545 SQLcl
- 4K SQL Developer Data Modeler
- 187K SQL & PL/SQL
- 21.3K SQL Developer
- 295.9K Development
- 17 Developer Projects
- 138 Programming Languages
- 292.6K Development Tools
- 107 DevOps
- 3.1K QA/Testing
- 646K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 155 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.1K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 18 Java Essentials
- 160 Java 8 Questions
- 86K Java Programming
- 80 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 440 LiveLabs
- 38 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 171 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 232 Portuguese
Please help: DB link related query

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
Best 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,003set 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 96In 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
Answers
-
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.
-
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,003set 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 96In 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 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.
-
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