最近，我遇到了在oracle中进行大型查询的功能，其中更改一件事会导致查询耗时10分钟，耗时3个小时。

简而言之，我在数据库中存储了很多坐标，每个坐标都有一个概率。然后，我想将这些坐标"装箱"成50米的箱柜(基本上是将坐标向下舍入到最接近的50米)并求和。

为此，查询的一部分是"从....中选择x，y，sum(probability)。按x，y分组"

最初，我以0.1的概率存储了大量的点，并且查询运行正常，每个查询大约需要10分钟。

然后我请求更改概率的计算方式以调整分布，因此它们不是所有值都是0.1，而是不同的值(例如0.03、0.06、0.12、0.3、0.12、0.06、0.03)。运行完全相同的查询将导致大约3个小时的查询。

改回所有0.1后，查询又回到了10分钟。

查看查询计划和系统性能，看起来问题出在"散列组"功能上，该功能旨在加快oracle中的分组速度。我猜想它正在为每个唯一的x，y，probability值创建哈希条目，然后为每个唯一的x，y值求和。

谁能更好地解释这种行为？

添加信息

多亏了答案。他们允许我验证发生了什么。我当前正在运行查询，并且来自v $ sql_workarea_active的tempseg_size当前为7502561280，并且正在迅速增长。

鉴于我正在运行的开发服务器只有8gb的ram，看起来查询需要使用临时表。

我现在设法通过更改查询的类型并预先计算了一些信息来解决此问题。

解决方案

哈希组(和哈希联接以及其他操作(例如排序等))可以使用最佳方法(即内存中)，一次通过或者多次通过。后两种方法使用TEMP存储，因此速度较慢。

通过增加可能的项目数量，我们可能已经超出了为此类操作保留的内存中可以容纳的项目数量。

尝试在查询运行时查看v $ sql_workarea_active，以查看是否是这种情况。或者查看v $ sql_workarea以获取历史信息。它还会告诉我们该操作需要多少内存和/或者临时空间。

如果发现是实际问题，请尝试增加pga_aggregate_target初始化参数(如果可能)。可用于最佳哈希/排序操作的内存量通常约为pga_aggregate_target的5％。

有关更多详细信息，请参见《性能调整指南》。

"正在猜测它正在为每个唯一的x，y，probability值创建哈希条目，然后为每个唯一的x，y值求和。"-几乎可以肯定的是，因为这是查询所需要的。

我们可以使用解释计划来检查查询是否需要临时dfisk空间以完成排序或者分组依据(等)的可能性。

explain plan for
select x,y,sum(probability) from .... group by x,y
/

select * from table(dbms_xplan.display)
/

如果优化程序可以从统计信息中正确推断出x和y的近似唯一数目，那么很有可能在第二个查询的输出的TempSpc列中将显示多少磁盘空间(如果有)需要完成查询(无列=不需要磁盘空间)。

太多信息在这里：http：//download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_xplan.htm#i999234

如果临时空间使用率很高，那么正如CaptP所说，可能是时候进行一些内存调整了。在执行大量排序和聚合的数据库上，通常指定比SGA目标更高的PGA目标。

PGA_AGGREGATE_TARGET是否有机会设置为零？可能不是由HASH GROUPBY单独导致问题的，它可能在问题发生之前或者之后。将OPTIMIZER_FEATURES_ENABLE降级到10.1.0.4并重新运行查询，我们将看到，现在我们将获得SORT GROUPBY，它应该总是比HASH GROUPBY更好，除非PGA大小设置为MANUAL并且哈希工作区域为矮小。