来自 Oracle 中 dblink 等待事件的 SQL*Net 消息
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11126777/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL*Net message from dblink wait event in Oracle
提问by Paul
I have an INSERT query in Oracle 10g that is getting stuck on a "SQL*Net message from dblink" event. It looks like:
我在 Oracle 10g 中有一个 INSERT 查询，它卡在“来自 dblink 的 SQL*Net 消息”事件上。看起来像：
INSERT INTO my_table (A, B, C, ...)
SELECT A, B, C, ... FROM link_table@other_system;
I do not see any locks on my_tablebesides the one from the INSERT I'm trying to do. The SELECT query on link_table@other_systemcompletes without any trouble when run on its own. I only get this issue when I try to do the INSERT.
my_table除了我正在尝试执行的 INSERT 中的锁定之外，我没有看到任何锁定。SELECT 查询在link_table@other_system单独运行时可以毫无问题地完成。我只有在尝试执行 INSERT 时才会遇到这个问题。
Does anyone know what could be going on here?
有谁知道这里会发生什么？
UPDATEThe SELECT returns 4857 rows in ~1.5 mins when run alone. The INSERT was running over an hour with this wait message before I decided to kill it.
更新SELECT 单独运行时在约 1.5 分钟内返回 4857 行。在我决定终止它之前，INSERT 运行了一个多小时并显示此等待消息。
UPDATEI found an error in my methods. I was using a date range to limit the results. The date range I used when testing the SELECT only was before the last OraStats run on the link_table, but the date range that I used when testing the INSERT was after the last OraStats run on the link_table. So, that mislead me to believe there was a problem with the INSERT. Not very scientific of me to do this; my mistake.
更新我在我的方法中发现了一个错误。我使用日期范围来限制结果。我仅在测试 SELECT 时使用的日期范围是在最后一次在 link_table 上运行的 OraStats 之前，但我在测试 INSERT 时使用的日期范围是在最后一次在 link_table 上运行的 OraStats 之后。所以，这误导了我相信 INSERT 存在问题。我这样做不是很科学；我的错。
采纳答案by Jon Heller
Are you using a /*+ driving_site(link_table) */hint to make Oracle perform the joins on the remote server?
您是否使用/*+ driving_site(link_table) */提示让 Oracle 在远程服务器上执行连接？
If so, that hint will notwork with DML, as explained by Jonathan Lewis on this page.
如果是这样，该提示将不适用于 DML，正如 Jonathan Lewis 在本页上所解释的那样。
This may be a rare case where running the query just as a SELECTuses a very different plan than running the query as part of an INSERT. (You will definitely want to learn how to generate explain plans in your environment. Most tools have a button to do this.)
这可能是一种罕见的情况，在这种情况下，SELECT将查询作为INSERT. （您肯定想学习如何在您的环境中生成解释计划。大多数工具都有一个按钮来执行此操作。）
As Andras Gabor recommended in the link, you may want to use PL/SQL BULK COLLECTto improve performance. This may be a rare case where PL/SQL will work faster than SQL.
正如 Andras Gabor 在链接中推荐的那样，您可能希望使用 PL/SQLBULK COLLECT来提高性能。这可能是 PL/SQL 比 SQL 运行得更快的罕见情况。
回答by Justin Cave
SQL*Net message from dblinkgenerally means that your local system is waiting on the network to transfer the data across the network. It's a very normal wait event for this sort of query.
SQL*Net message from dblink通常意味着您的本地系统正在等待网络通过网络传输数据。对于此类查询，这是一个非常正常的等待事件。
How many rows does the SELECTstatement return? How much data (in MB/ GB) does that represent?
SELECT语句返回多少行？这代表多少数据（以 MB/GB 为单位）？
When you say that it "completes without any trouble on its own", are you actually fetching all the data? If you're using something like TOAD or SQL Developer, the GUI will generally fetch the first N rows and return to you. That can be very quick but it doesn't imply that the database is done executing the query-- it may take much more time to finish producing all the rows your query is going to return. It's pretty common for people to measure the time required to fetch the first N rows rather than the time to fetch the last row-- your INSERT statement, obviously, can't return until all the rows have been fetched from the remote table.
当您说它“自行完成而没有任何麻烦”时，您实际上是在获取所有数据吗？如果您使用的是 TOAD 或 SQL Developer 之类的东西，GUI 通常会获取前 N 行并返回给您。这可能非常快，但并不意味着数据库已完成执行查询——完成生成查询将要返回的所有行可能需要更多时间。人们通常会测量获取前 N 行所需的时间而不是获取最后一行的时间——很明显，您的 INSERT 语句在从远程表中获取所有行之前无法返回。
