performance - 让 Oracle 9i 使用索引

Question

在客户端的 9i 服务器中部署在 10g XE 上开发的应用程序时，我遇到了性能问题。根据服务器的不同，相同的查询会产生完全不同的查询计划:

SELECT DISTINCT FOO.FOO_ID               AS C0,
    GEE.GEE_CODE                         AS C1,
    TO_CHAR(FOO.SOME_DATE, 'DD/MM/YYYY') AS C2,
    TMP_FOO.SORT_ORDER                   AS SORT_ORDER_
FROM TMP_FOO
INNER JOIN FOO ON TMP_FOO.FOO_ID=FOO.FOO_ID
LEFT JOIN BAR ON FOO.FOO_ID=BAR.FOO_ID
LEFT JOIN GEE ON FOO.GEE_ID=GEE.GEE_ID
ORDER BY SORT_ORDER_;

Oracle 数据库 10g 快捷版 10.2.0.1.0 版 - 生产:

-------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |     1 |    67 |    10  (30)| 00:00:01 |
|   1 |  SORT UNIQUE                    |         |     1 |    67 |     9  (23)| 00:00:01 |
|   2 |   NESTED LOOPS OUTER            |         |     1 |    67 |     8  (13)| 00:00:01 |
|*  3 |    HASH JOIN OUTER              |         |     1 |    48 |     7  (15)| 00:00:01 |
|   4 |     NESTED LOOPS                |         |     1 |    44 |     3   (0)| 00:00:01 |
|   5 |      TABLE ACCESS FULL          | TMP_FOO |     1 |    26 |     2   (0)| 00:00:01 |
|   6 |      TABLE ACCESS BY INDEX ROWID| FOO     |     1 |    18 |     1   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | FOO_PK  |     1 |       |     0   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL           | BAR     |     1 |     4 |     3   (0)| 00:00:01 |
|   9 |    TABLE ACCESS BY INDEX ROWID  | GEE     |     1 |    19 |     1   (0)| 00:00:01 |
|* 10 |     INDEX UNIQUE SCAN           | GEE_PK  |     1 |       |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------

Oracle9i 版本 9.2.0.1.0 - 64 位生产:

----------------------------------------------------------------------------
| Id  | Operation               |  Name    | Rows  | Bytes |TempSpc| Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |    98M|  6546M|       |  3382K|
|   1 |  SORT UNIQUE            |          |    98M|  6546M|    14G|  1692K|
|*  2 |   HASH JOIN OUTER       |          |    98M|  6546M|   137M|  2874 |
|   3 |    VIEW                 |          |  2401K|   109M|       |   677 |
|*  4 |     HASH JOIN OUTER     |          |  2401K|   169M|    40M|   677 |
|   5 |      VIEW               |          |   587K|    34M|       |    24 |
|*  6 |       HASH JOIN         |          |   587K|    34M|       |    24 |
|   7 |        TABLE ACCESS FULL| TMP_FOO  |  8168 |   207K|       |    10 |
|   8 |        TABLE ACCESS FULL| FOO      |  7188 |   245K|       |     9 |
|   9 |      TABLE ACCESS FULL  | BAR      |   409 |  5317 |       |     1 |
|  10 |    TABLE ACCESS FULL    | GEE      |  4084 | 89848 |       |     5 |
----------------------------------------------------------------------------

据我所知，索引存在并且是正确的。我有哪些选项可以让 Oracle 9i 使用它们？

更新 #1: TMP_FOO 是一个临时表，在此测试中没有任何行。 FOO 是我本地 XE 中包含 13,035 行的常规表；不确定为什么查询计划显示 1，也许它意识到针对空表的 INNER JOIN 不需要全表扫描:-?

更新#2:我花了几周的时间尝试所有但没有提供真正的增强:查询重写、优化器提示，数据库设计的变化，摆脱临时表......最后，我得到了客户拥有的相同 9.2.0.1.0 未打补丁的 Oracle 版本的副本(具有明显的体系结构差异)，将其安装在我的站点并......惊喜!在我的 9i 中，所有执行计划都是立即执行的，查询需要 1 到 10 秒才能完成。

此时，我几乎确信客户存在严重的配置错误问题。

Answer 1

看起来您的 10g express 数据库中没有数据，或者您的统计信息没有正确收集。无论哪种情况，在 Oracle 看来，行数都不多，因此索引范围扫描是合适的。

在您的 9i 数据库中，统计信息看起来像是正确收集的，并且 Oracle 看到一个包含很多行的 4 表连接，没有 where 子句。在这种情况下，由于您没有提供提示，Oracle 会使用默认的 ALL_ROWS 优化器行为构建一个解释计划:Oracle 将找到性能最高的计划，将 所有 行返回到最后。在这种情况下，具有全表扫描的 HASH JOIN 非常高效，它将比使用索引 NESTED LOOP 连接更快地返回大量行。

也许您想使用索引，因为您只对查询的前几行感兴趣。在这种情况下，使用提示 /*+ FIRST_ROWS*/ 将帮助 Oracle 了解您对第一行响应时间比总查询时间更感兴趣。

也许您想使用索引，因为您认为这会导致更快的总查询时间。您可以通过使用像 USE_NL 这样的提示来强制执行解释计划。和 USE_HASH但大多数时候您会发现，如果统计信息是最新的，优化器会选择最有效的计划。

---

更新:我看到您关于 TMP_FOO 是一个没有行的临时表的更新。临时表的问题是它们没有统计信息，所以我上面的回答并不完全适用于临时表。由于临时表没有统计信息，Oracle 必须进行猜测(这里它选择了相当任意的 8168 行)，这导致了一个低效的计划。

在这种情况下，使用提示可能是合适的。您有多种选择:

• LEADING 的组合、USE_NL 和 USE_HASH 提示可以强制执行特定计划(LEADING 设置连接顺序，USE* 设置连接方法)。
• 您可以使用未记录的 CARDINALITY 提示为优化器提供额外信息，如 AskTom article 中所述。 .虽然提示没有记录，但可以说是 safe to use .注意:在 10g+ 上，DYNAMIC_SAMPLING 可能是记录在案的替代方案。
• 你也可以预先用DBMS_STATS.set_table_stats设置临时表的统计数据。程序。最后一个选项非常激进，因为它可能会修改针对此临时表的所有查询的计划。