Oracle优化提示XMLAGG函数

Question

我有一个函数，会调用几个表格/视图等数据，并使用一些xmlagg进行处理。

奇怪的是，当我添加额外信息时，即使这些信息不在代码的其他部分中使用（例如索引关键值），性能也有所提高。

我已经在快慢两个查询上运行了tkprof，并发现了一些问题- 首先是缓慢的查询在解析和执行过程中存在未命中情况，而快速的查询则没有。

我的主要问题是，进一步查看后，我发现其中一个视图的成本非常高 - 快速查询正在使用底层表格的3个索引，而缓慢的查询没有使用任何索引。

我尝试插入了一个提示:

SELECT /*+ index(view_alias,table1_index, table2_index, table3_index) */     
XMLCONCAT (...

然而，它仍在进行全表扫描。我是把优化器提示放错了位置还是使用了错误的语法？

编辑 - 我进行了更多的调查，发现这可能是 Oracle 执行哈希连接而不是嵌套循环所导致的，但我的选择来自几个表 - 我可以强制所有三个使用 USE_NL 吗？我如何知道 PL / SQL 的哪个区域导致这种情况，因为它被多次调用。

更新 28/08 - 添加赏金，请让我知道是否需要任何额外的内容。

更新 01/09 -

> SELECT XMLCONCAT (  XMLELEMENT (  "1",  (SELECT XMLCONCAT(  XMLELEMENT
> (  "2",  XMLELEMENT (  "3",  XMLFOREST (  )),  CASE  WHEN   THEN    
> XMLELEMENT (  "3",  XMLFOREST (  ))  END),  /*   (SELECT XMLELEMENT ( 
> "4",  XMLAGG (XMLELEMENT ("5")))  FROM TABLE t1,  t2  WHERE t1.col1 =
> t2.col2)  ,*/    CASE  WHEN   THEN  (SELECT XMLAGG (  XMLELEMENT ( 
> "5", */(SELECT col1  FROM TABLE t1,  t2  WHERE t1.col1 = t2.col2),*/ 
> XMLFOREST ( ....

有两个被注释掉的选择器，只要其中一个取消注释就可以使查询速度更快。在查询中，t1和t2都没有被使用。

更新01/09: 执行计划如下: 快速执行 http://pastebin.com/pbJMSxrB 慢速执行 http://pastebin.com/zt3eUYNd

我想纠正第86行的高代价操作。这可能是由于全面扫描或更上层的连接导致的。

- bob dylan

---

你能否发布包含索引的表格以及你的查询语句？ - Nick

嗨，我本来要发布PL/SQL代码的，但它相当长（超过1000行）- 实际上格式是xmlconcat（xmlagg（xmlagg（case（xmlagg select value1,2,3 from views 1 2 3 where view1 value1 = view2 value2 and view1 value1 = view3 value3）..... 我引用的视图是同一个x3表，只是分成不同的类别，例如视图=类别1 + 类别2 + 类别3的聚合。如果还有不清楚的地方，请谅解。 - bob dylan

你可以将其作为文件发布。我认为语法可能是个问题。你能具体说明 'index(view_alias.table1_alias table1_index) index(view_alias.table2_alias table2_index)' 吗？ - Brainhash

这个提示应该放在第一个select语句还是在紧随其后的'from'语句之后？或者它应该放在from语句之后，例如FROM /*+ index(view,table1index) index(view,table2index) index(view,table3index) */ schema.view_to_use view .... - bob dylan

t1和t2 - 它们是视图吗？如果是，那么视图定义是什么？ - David Aldridge

Answer 1

查询中的一个小变化可能会对查询的一个非常不同的部分产生影响。在这里，这种变化影响了视图VIP_CODES_VW的连接（它在两个地方进行了连接，但第二个连接对性能的影响更大）：在快速查询中，使用NESTED LOOPS进行连接（第79行），而在慢查询中-使用HASH JOIN（第75行）。要告诉优化器使用NESTED LOOPS，可以在查询VIP_CODES_VW的SELECT之后添加提示/*+ USE_NL(VIP_CODES_VW) */。

- mik

---

嗨，接下来做什么？我已经对所有(SELECT进行了替换，使用(SELECT /+ USE_NL(VIP_CODES_VW)（只是为了确保我已经获取了所有实例）。 (SELECT /+ USE_NL(VIP_CODES_VW) /XMLCONCAT ( CASE ..... (SELECT /+ USE_NL(VIP_CODES_VW) */MAX ( .... 等等，但性能相同。问题在于函数中存在多个嵌套语句和对此视图的使用。 - bob dylan

视图位于另一个模式（tvic）上，因此通常您需要在提示中添加前缀，但是您也可以使用别名（vip），这更简单。请尝试此操作：pastebin.com/Hrsji1sM。 - mik

抱歉，能否更新一下这个 pastebin 的位置？我尝试过了，但不确定它是否在正确的位置。 - bob dylan

这是链接：http://pastebin.com/erxyKB5w。查询有三种变量，我不确定哪个实际使用，因此我对所有变量添加提示（dbms_output应该显示它）。 - mik

它使用的部分取决于之前的基础表。这并没有完全解决问题，但至少我的“在哪里”放置这些提示的问题已经得到了回答。我将向您授予赏金（如果可以的话，也会给Florin），但我的问题仍未解决。由于我的问题的关键非常具体，我认为我们不会能够在没有更多来回交流的情况下解决它。 - bob dylan

Answer 2

不使用索引的原因是理论上存在空值的可能性。由于空值不被索引，因此如果您的查询需要/认为可能存在空值，则不能通过索引访问表格。

另外，您的提示必须与从表格读取的级别相同：

select /*+parallel(table_a)*/ ...
from (
      select ...
      from table_a
      ...
      )
...

"won't work"

"但是"

select  ...
from (
      select /*+parallel(table_a)*/ ...
      from table_a
      ...
      )
...

将会工作。

- Florin Ghita

---

嗨，我已经尝试在几乎所有地方放置提示，但它仍然无效。我遇到的问题是我有一个选择器，它运行缓慢，但当我插入另一个使用不同表的选择器时，它就可以正常运行，没有缓冲失败/哈希连接。请参见更新以获取更多详细信息。 - bob dylan

你尝试在视图中插入提示了吗？这就是我在答案中所说的：将提示放在引用表的位置。 - Florin Ghita

/+ index(t1 t1_index_name) index(t2 t2_index_name) use_nl(t1 t2)/ 不行。 - Florin Ghita

我将这个添加到视图中吗？我是否将它们全部放在每个选择行内（请记住此视图中有3个表）CREATE VIEW... SELECT /+ index(tvic.vip_codes TVIC.VIP_CODES_IDX) index(tvic.vip_lcv_codes TVIC.VIP_LCV_CODES_IDX) use_nl(tvic.vip_codes tvic.vip_lcv_codes)/ cols from table1...从T2中选择（另一个提示？）SELECT...从T3中选择（另一个提示？）？ - bob dylan

@bobdylan 我已经分析了你给 mik 的代码。你应该用 /*+use_nl(vip pat pc)*/ 编辑第 292 行。我保存了修改 http://pastebin.com/zFHVp0vz。也许不需要 pc。你应该把你需要使用嵌套循环的视图别名放在那里。 - Florin Ghita