Oracle Sql Query 需要一天时间才能使用 dblink 返回结果
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4614107/
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
Oracle Sql Query taking a day long to return results using dblink
提问by Dead Programmer
Guys i have the following oracle sql query that gives me the monthwise report between the dates.Basically for nov month i want sum of values between the dates 01nov to 30 nov. The table that is being queried is residing in another database and accesssed using dblink. The DT columns is of NUMBER type (for ex 20101201).
伙计们，我有以下 oracle sql 查询，它为我提供了日期之间的月度报告。基本上对于 11 月，我想要 01nov 到 30 nov 之间的值的总和。正在查询的表驻留在另一个数据库中并使用 dblink 访问。DT 列是 NUMBER 类型（例如 20101201）。
SELECT /*+ PARALLEL (A 8) */ /*+ DRIVING_STATE(A) */
TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')- 1,'MM'),'MONYYYY') "MONTH",
TYPE AS "TYPE", COLUMN, COUNT (DISTINCT A) AS "A_COUNT",
COUNT (COLUMN) AS NO_OF_COLS, SUM (DURATION) AS "SUM_DURATION",
SUM (COST) AS "COST" FROM **A@LN_PROD A**
WHERE DT >= TO_NUMBER(TO_CHAR(add_months(SYSDATE,-1),'YYYYMM"01"'))
AND DT < TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMM"01"'))
GROUP BY TYPE, COLUMN
The execution of the query is taking a day long and not completed. kindly suggest me , if their is any optimisation that can be suggested to my DBA on the dblink, or any tuning that can be done on the query , or rewriting the same.
查询的执行需要一天时间并且未完成。请建议我，如果他们是可以向我的 DBA 在 dblink 上建议的任何优化，或者可以对查询进行的任何调整，或者重写相同的。
UPDATES ON THE TABLE
表上的更新
The table is partiontioned on the date column and almost 1 billion records.
该表按日期列和近 10 亿条记录进行分区。
Below i have given the EXPLAIN PLANfrom TOAD
下面，我已经给出了解释计划从蟾蜍
**Plan**
SELECT STATEMENT REMOTE ALL_ROWSCost: 1,208,299 Bytes: 34,760 Cardinality: 790
12 PX COORDINATOR
11 PX SEND QC (RANDOM) SYS.:TQ10002 Cost: 1,208,299 Bytes: 34,760 Cardinality: 790
10 SORT GROUP BY Cost: 1,208,299 Bytes: 34,760 Cardinality: 790
9 PX RECEIVE Cost: 1,208,299 Bytes: 34,760 Cardinality: 790
8 PX SEND HASH SYS.:TQ10001 Cost: 1,208,299 Bytes: 34,760 Cardinality: 790
7 SORT GROUP BY Cost: 1,208,299 Bytes: 34,760 Cardinality: 790
6 PX RECEIVE Cost: 1,208,299 Bytes: 34,760 Cardinality: 790
5 PX SEND HASH SYS.:TQ10000 Cost: 1,208,299 Bytes: 34,760 Cardinality: 790
4 SORT GROUP BY Cost: 1,208,299 Bytes: 34,760 Cardinality: 790
3 FILTER
2 PX BLOCK ITERATOR Cost: 1,203,067 Bytes: 15,066,833,144 Cardinality: 342,428,026 Partition #: 11 Partitions accessed #1 - #5
1 TABLE ACCESS FULL TABLE CDRR.FRD_CDF_DATA_INTL_IN_P Cost: 1,203,067 Bytes: 15,066,833,144 Cardinality: 342,428,026 Partition #: 11
The following things i am going to do today ,any additional tips would be helpful.
我今天要做的以下事情，任何额外的提示都会有所帮助。
- I am going to gather the tablewise statistics for this table, which may give optimal execution plan.
- Check whether an local index is created for the partition .
- using BETWEEN instead of >= and <.
- 我将收集该表的 tablewise 统计信息，这可能会提供最佳执行计划。
- 检查是否为分区创建了本地索引。
- 使用 BETWEEN 而不是 >= 和 <。
回答by UVM
It may be because several issues: 1.Network speed because the database may be residing on different hardware. However you can refer this link http://www.experts-exchange.com/Database/Oracle/Q_21799513.html. There is a similar issue.
可能是因为几个问题： 1.网络速度，因为数据库可能驻留在不同的硬件上。但是，您可以参考此链接 http://www.experts-exchange.com/Database/Oracle/Q_21799513.html。有一个类似的问题。
回答by Ronnis
Impossible to answer without knowing the table structure, constraints, indexes, data volume, resultset size, network speed, level of concurrency, execution plans etcetera.
如果不知道表结构、约束、索引、数据量、结果集大小、网络速度、并发级别、执行计划等，就无法回答。
Some things I would investigate:
我会调查的一些事情：
If the table is partitioned, does statistics exist for the partition the query is hitting? A common problem is that statistics are gathered on an empty partition before data has been inserted. Then when you query it (before the statistics are refreshed) Oracle chooses an index scan, when in fact it should use an FTS on that partition.
如果表已分区，是否存在查询命中的分区的统计信息？一个常见的问题是在插入数据之前在空分区上收集统计信息。然后当您查询它时（在刷新统计信息之前）Oracle 选择索引扫描，而实际上它应该在该分区上使用 FTS。
Also related to statistics: Make sure that
还与统计相关：确保
WHERE DT >=TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')-1,'MM'),'YYYYMMDD'))
AND DT < TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM'),'MM'),'YYYYMMDD'))
generates the same execution plan as:
生成与以下相同的执行计划：
WHERE DT >= 20101201
AND DT < 20110101
UpdatedWhat version of Oracle are you on? The reason I'm asking is that on Oracle 10g and later, there is another implementation of group by that should have been selected in this case (hashing rather than sorting). It looks like you are basically sorting the 342 million rows returned from the date filter (14 gigabytes). Do you have the RAM to back that up? Otherwise you will be doing a multipass sort, spilling to disk. This is likely what is happening.
更新您使用的是哪个版本的 Oracle？我问的原因是在 Oracle 10g 及更高版本上，在这种情况下应该选择 group by 的另一个实现（散列而不是排序）。看起来您基本上是在对从日期过滤器（14 GB）返回的 3.42 亿行进行排序。你有内存来备份吗？否则，您将进行多遍排序，溢出到磁盘。这很可能就是正在发生的事情。
According to the plan, about 790 rows will be returned. Is that in the right ballpark? If so, you can rule out network issues :)
按照计划，将返回约790行。这是在正确的球场吗？如果是这样，您可以排除网络问题:)
Also, I'm not entirely familiar with the format on that plan. Is the table sub partitioned? Otherwise I don't get the partition #11 reference.
另外，我并不完全熟悉该计划的格式。表子分区了吗？否则我不会得到分区 #11 的参考。
回答by Mike Meyers
As usual for this type of question, an explain plan would be useful. It would help us work out what is actually going on in the database.
像往常一样，对于此类问题，解释计划会很有用。它将帮助我们弄清楚数据库中实际发生了什么。
Ideally you want to make sure the query is running on the remote database the sending the result set back, rather than sending the data across the link and running the query locally. This ensures that less data is sent across the link. The DRIVING_SITEhint can help with this, although Oracle is usually fairly smart about it so it might not help at all.
理想情况下，您希望确保查询在远程数据库上运行并将结果集发回，而不是通过链接发送数据并在本地运行查询。这可确保通过链接发送的数据更少。该DRIVING_SITE提示可以帮助这一点，尽管甲骨文通常是相当聪明一点，所以可能不是在所有的帮助。
Oracle seems to have got better at running remote queries but there still can be problems.
Oracle 似乎在运行远程查询方面做得更好，但仍然存在问题。
Also, it might pay to simplify some of your date conversions.
此外，简化一些日期转换可能是值得的。
For example, replace this:
例如，替换这个：
TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')- 1,'MM'),'MONYYYY')
with this:
有了这个：
TO_CHAR(add_months(TRUNC(SYSDATE,'MM'), -1),'MONYYYY')
It is probably slightly more efficient but also is easier to read.
它可能稍微更有效，但也更容易阅读。
Likewise replace this:
同样替换这个：
WHERE DT >=TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM')-1,'MM'),'YYYYMMDD'))
AND DT < TO_NUMBER(TO_CHAR(TRUNC(TRUNC(SYSDATE,'MM'),'MM'),'YYYYMMDD'))
with
和
WHERE DT >=TO_NUMBER(TO_CHAR(add_months(TRUNC(SYSDATE,'MM'), -1),'YYYYMMDD'))
AND DT < TO_NUMBER(TO_CHAR(TRUNC(SYSDATE,'MM'),'YYYYMMDD'))
or even
甚至
WHERE DT >=TO_NUMBER(TO_CHAR(add_months(SYSDATE,-1),'YYYYMM"01"'))
AND DT < TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMM"01"'))
