从PL/SQL中向临时表GTT插入数据非常缓慢

Question

我有一个查询，在SQL中执行时表现非常好。

这是一个表和查询之间的连接。这两个表都有接近400万条记录。在doc表上有位图索引，我正在尝试提供提示。从toad中查看，解释计划确实显示它们对连接有很大帮助。

我已经提供了其他2个提示，以查看它们是否有所帮助。一个是用于直接路径APPEND，另一个是利用现有的pda BTree索引。

当在SQL中运行此查询以替换变量时，结果是即时的，但同样的查询在过程内运行需要8秒或更长时间。

除了DBA尚未产生的过程计划外，您认为还有哪些重要的问题吗？先感谢您。

     INSERT                                                           /*+ APPEND */
          INTO  tmp_search_gross_docs (document_id,
                                       last_name,
                                       first_name,
                                       person_doc_association_id,
                                       association_date)
       SELECT                 /*+INDEX(pda IDX_DOC_PDOC_DOCID ) USE_NL(pda doc) */
             pda.document_id,
              last_name,
              first_name,
              person_doc_association_id,
              association_date
         FROM   pda,
              (SELECT /*+INDEX_COMBINE(attr IDX_BMP_SEARCH_FN,IDX_BMP_SEARCH_LN)*/
                      document_id, last_name, first_name
                 FROM doc attr
                WHERE first_name LIKE l_first_name OR last_name LIKE l_last_name) doc
        WHERE pda.document_id = doc.document_id;
                         ) doc
                 WHERE pda.document_id = doc.document_id;

 

解释计划（来自绑定变量的Toad）

插入语句ALL_ROWSCost：1,086,010字节：15,309,420基数：364,510
11个负载，如选择TMP_SEARCH_GROSS_DOCS
10个表访问按索引ROWID表PDA成本：3字节：20基数：1
9个嵌套循环成本：1,086,010字节：15,309,420基数：364,510
7个表访问按索引ROWID表ATTR成本：23,893字节：8,019,220基数：364,510
6个位图转换为ROWIDS
5个位图或
2个位图合并
1个位图索引范围扫描索引（位图）IDX_BMP_SEARCH_FN 4个位图合并
3个位图索引范围扫描索引（位图）IDX_BMP_SEARCH_LN 8个索引范围扫描索引IDX_PDA_EXP_DOC成本：2基数：1

基数364,510似乎不正确，因为该表包含3738562行，对于WHERE子句中的列的替代值，计数仅为8892。

然而，这个方案至少告诉我正确的索引被使用，并且从Toad编辑器运行非常快。

PL/SQL实际计划仍然不可用。

不确定这是否添加了一些有价值的信息。但还是想编辑一下。谢谢。

- user2275460

---

在那个过程中你还做了什么？ - user330315

不确定这是否有帮助，但最终它会建立一个记录集，然后由应用程序读取。我应该指出，我尝试过在相同的SQL上使用BULK COLLECT和集合，性能也是一样的。不知何故，当从编辑器执行时，SELECT语句似乎没有采取最优路径。 - user2275460

Answer 1

首先，我不认为使用“append”方式向全局临时表插入数据有任何逻辑。虽然我可能错了，但据我所知，“append”方式绕过了缓冲区高速缓存并直接写入文件，它写在高水位线以上，并且不允许查询，直到进行提交操作。 GTT不是常规数据文件——它在临时文件上，并且在提交操作（默认设置）时被截断。

如果您在查询后不需要操作数据，请考虑向应用程序返回ref cursor。这基本上是相同的事情——很多DAL层都是这样实现的。

如果您仍然需要使用GTT，则应检查您的临时文件分配，包括大小和实际磁盘位置——您的DBA可能将它们放在不同的设备上。

- haki

---

+1 你对使用带有append的GTT持怀疑态度是正确的。GTT的优点在于它应该始终被缓存；第一次写入时绕过缓存可能不会有帮助。但如果GTT是会话特定而不是事务特定的，它仍然可以工作。 - Jon Heller

Answer 2

以下是你可以考虑的几件事。

1. 对于GTT表不相关。像你说的，使用nologging创建表，在插入时使用insert append提示进行直接路径加载。

2. 此外，如果你从Toad中看到结果，可能会对查询速度产生误解。像Toad这样的软件会自动添加提示以获取前50/100/200行，因此查询可能会运行得更快。你尝试过去查看最后一条记录（网格中的“>”图标），看看获取最后一行需要多长时间吗？

3. 没有能够查看计划就很难调整查询或编写良好的代码。如果这是一个较大的过程，可以查看DBMS_PROFILER等内容。如果你确定这是导致问题的语句，可以从SQL跟踪或解释计划开始。

- Rajesh Chamarthi

---

在第二点，是的，查询返回了600多行与我比较性能相关的参数。我已经查看了结果中的最后一条记录。在第三点，是的，我确保这就是导致延迟的语句。独立SQL的计划看起来很好，这也是我进行调整并将其带到过程中的方式。没有运行Trace的权限，也没有可用的实际执行计划。感谢您再次重申这些要点。 - user2275460

Answer 3

自适应光标共享不适用于LIKE谓词。

如果您的过程首先使用%等参数执行，则Oracle无法为该谓词使用某些索引访问方法。 Oracle无法遵守您的提示，并构建了一个对于%非常好但对其他所有内容都很糟糕的计划。

一种可能的解决方案是强制Oracle始终硬解析查询。 我已经使用了这个解决方案，如Dion Cho在this OTN thread中所解释的那样：

dbms_stats.set_table_stats('schema','DOC',num_rows=>null,no_invalidate=>false);

硬解析会使用额外的CPU资源，但是希望通过更好的计划来弥补差异。这种解决方案会导致其他无关的查询需要进行硬解析。您可能希望查看参考线程中提到的其他解决方案。

此外，您的INDEX_COMBINE提示可能不正确。索引之间不应该有逗号。然而，提示语法文档不完善，并且提示解析器通常会“部分”工作。您的提示可能被评估为等同于/*+INDEX_COMBINE(attr)*/，这样可能或可能不按照您的要求工作。没有计划，您永远不会知道。

避免使用提示的理由有很多，尤其是如果您没有方便地访问解释计划。

- Jon Heller

---

我确认提示确实会使索引被使用，因此语法肯定是正确的。我仍在尝试研究您关于启用硬解析的其他评论。如果有必要，如何关闭它？鉴于该帖子提到了“库缓存争用”的危险。 - user2275460

你可能不需要担心库缓存。如果查询需要8秒才能运行，那么它可能并不经常运行。运行select count(*) from gv$sql;并将其与您的过程始终进行硬解析时产生的不同查询数量进行比较。此外，我仍然怀疑提示是否有效。我认为这是一个情况，在这种情况下，您看到的解释计划与实际执行计划不同。我不确定为什么您在获取实际计划方面遇到问题，这应该很容易获得。 - Jon Heller