Hibernate索引查询速度慢

Question

我的问题类似于此线程中提出的问题：如何避免这个使应用程序变慢的非常重的查询？

我们检查了外键缺失索引并找到了一些。添加缺失的索引实际上产生了相反的效果，使查询速度更慢了。一个重要的信息是，我们的客户在单个Oracle安装上复制了我们的模式21次。每个模式中有将近1,000个表。我们是否向Oracle请求太多，因为有这么多表格（当然还有索引）？我不知道他们的硬件是什么，但我的问题是，这种方法是否合理，或者将用户分配到不同的SIDs是否更好？

以下是Hibernate执行的查询。客户告诉我们，执行此查询时它会消耗大约45％的处理器（尽管我不知道持续多长时间）。

欢迎提供建议， 史蒂夫

SELECT NULL AS table_cat,
       owner AS table_schem,
       table_name,
       0 AS non_unique,
       NULL AS index_qualifier,
       NULL AS index_name,
       0 AS TYPE,
       0 AS ordinal_position,
       NULL AS column_name,
       NULL AS asc_or_desc,
       num_rows AS CARDINALITY,
       blocks AS pages,
       NULL AS filter_condition
  FROM all_tables
 WHERE table_name = 'BOOKING'
       AND owner = 'FORWARD_TN'
UNION
SELECT NULL AS table_cat,
       i.owner AS table_schem,
       i.table_name,
       DECODE (i.uniqueness, 'UNIQUE', 0, 1),
       NULL AS index_qualifier,
       i.index_name,
       1 AS TYPE,
       c.column_position AS ordinal_position,
       c.column_name,
       NULL AS asc_or_desc,
       i.distinct_keys AS CARDINALITY,
       i.leaf_blocks AS pages,
       NULL AS filter_condition
  FROM all_indexes i,
       all_ind_columns c
 WHERE     i.table_name = 'BOOKING'
       AND i.owner = 'FORWARD_TN'
       AND i.index_name = c.index_name
       AND i.table_owner = c.table_owner
       AND i.table_name = c.table_name
       AND i.owner = c.index_owner
ORDER BY non_unique,
         TYPE,
         index_name,
         ordinal_position

- Stephen Seltzer

---

在Oracle服务器上进行处理器操作，还是在应用服务器上？ - skaffman

我应该指明一下。Oracle服务器是占用处理器的那个。 - Stephen Seltzer

Answer 1

您在处理1000个表时不会遇到任何容量问题。在Oracle世界中，这仍然相对较小。几乎所有占用大量CPU的查询都是执行计划问题。以下是一些需要注意的事项：

您是否已经收集了优化器统计信息？如果没有，优化器可能会做出非常糟糕的决策来执行查询。

下一步是查看执行计划本身。如果您正在运行企业管理器，则可能将该查询放在资源消耗的前台页面上。您只需单击它并查看其操作即可。如果没有，您必须使用sql_trace或explain plan来查看发生了什么。

- JOTN

---

感谢您的建议。每个架构有1,000张表，21个架构约为21,000张表。我并不认为这对于Oracle来说有多少，但是感谢您的确认。我们会检查您的建议，看看我们能找到什么。 - Stephen Seltzer

Answer 2

如果你的所有语句都针对 22,000 张表使用略有不同的 SQL（即没有绑定变量），那么在 Oracle 服务器上可能会很容易地遭受过度解析时间的打击。如果是这种情况，只需切换到绑定变量，CPU 消耗应该会大大降低。

您的客户能否知道 CPU 时间在哪个 Oracle 函数中消耗了？Oracle 提供了必要的统计信息。由于 CPU 消耗被报告为整个实例消耗的重要部分，您的客户可以运行 statspack 报告（或者如果他已经获得了诊断包许可，则可以运行 ASH）。这应该显示 CPU 时间究竟花在了哪里。

- Peter G.

Answer 3

我们的客户审核了他们的Oracle配置，并将配置更改为以下值： optimizer_index_caching=90 optimizer_index_cost_adj=15 optimizer_mode='CHOOSE'

他们报告说，这似乎解决了他们的速度问题。

- Stephen Seltzer