如何避免这个非常沉重的查询会降低应用程序的速度？ [英] How to avoid this very heavy query that slows down the application?

问题描述

我们有一个在生产环境中运行的web应用程序，并且在某个时候，客户抱怨应用程序有多慢。

当我们检查发生了什么应用程序和数据库，我们发现这个珍贵的查询是由多个用户同时执行的（因此会给数据库服务器造成极高的负载）：

  SELECT NULL AS table_cat，
 o.owner AS table_schem，
 o.object_name AS table_name，
 o.object_type AS table_type，
 NULL AS备注
 FROM all_objects o 
 WHERE o.owner LIKE：1 ESCAPE：SYS_B_0AND 
 o.object_name LIKE：2 ESCAPE：SYS_B_1AND 
 o.object_type IN （：SYS_B_2，：SYS_B_3）
 ORDER BY table_type，table_schem，table_name 
  

我们的应用程序不执行这个查询，我相信它是一个Hibernate内部查询。我发现很少有关于为什么Hibernate能够处理这个非常重要的查询，所以在如何避免它的任何帮助时都非常感谢！

生产环境信息：Red Hat Enterprise Linux 5.3（Tikanga），JDK 1.5，Web容器OC4J（惠普Oracle应用服务器），Oracle数据库10.1.0.4，JDBC驱动程序JDK 1.2和1.3，Hibernate 3.2.6.ga，连接池库C3P0 0.9.1版本。

UPDATE ：感谢@BalusC的claryfing，确实是Hibernate执行查询，现在我拥有关于发生了什么的更好的想法。我将解释我们处理hibernate会话的方式（这是非常基本的是，如果你有更好的处理方法，那么他们不仅仅是欢迎！）

我们有一个过滤器（实现javax.servlet.Filter），当它启动时（init方法），它构造会话工厂（这只会发生一次）。然后，每个进入应用程序的HttpRequest都会通过过滤器并获得一个新会话并启动一个事务。当进程结束时，它通过过滤器返回，提交事务，杀死 hibernate会话，然后继续前进页面（我们不会将Hibernate会话存储在Http因为它在我们的测试中从未运行良好）。

现在来看看我认为问题所在的部分。在我们的开发环境中，我们将应用程序部署在Tomcat 5.5中，当我们启动服务时，所有过滤器立即启动并仅启动一次。在OC4J的生产环境中似乎没有这样的工作方式。我们部署应用程序，并且只有当第一个请求到达时，OC4J实例化过滤器。

这让我认为OC4J在每个请求（或至少多次，这仍然是错误的），从而在每个请求上创建一个会话工厂，执行％&％＃％$＃查询，这会导致我的问题！

现在，这是否正确？有没有办法让我配置OC4J让它只实例化一次过滤器？

非常感谢大家花时间回应这个问题！

发生的情况是Oracle Forms应用程序在数据库，因此几乎所有的数据库工作都非常缓慢（包括我喜欢的Hibernate查询）。

锁的原因是没有任何外键的Oracle Forms应用程序已编入索引。因此，当我的老板向我解释（他发现原因），当用户在Oracle Form应用程序中编辑主从关系的主记录时，数据库会锁定整个详细信息表没有索引它的外键。这是因为Oracle Forms的工作方式，它会更新主记录的所有字段，包括主键的字段，外键引用的字段。

总之，请不要留下没有索引的外键。我们遭受了很多痛苦。

感谢所有花时间帮忙的人。

We have a web application running in a production enviroment and at some point the client complained about how slow the application got.

When we checked what was going on with the application and the database we discover this "precious" query that was being executed by several users at the same time (thus inflicting an extremely high load on the database server):

SELECT   NULL AS table_cat,
         o.owner AS table_schem,
         o.object_name AS table_name,
         o.object_type AS table_type,
         NULL AS remarks
FROM     all_objects o
WHERE    o.owner LIKE :1 ESCAPE :"SYS_B_0" AND
         o.object_name LIKE :2 ESCAPE :"SYS_B_1" AND
         o.object_type IN(:"SYS_B_2", :"SYS_B_3")
ORDER BY table_type, table_schem, table_name

Our application does not execute this query, I believe it is an Hibernate internal query. I've found little information on why Hibernate does this extremely heavy query, so any help in how to avoid it very much appreciated!

The production enviroment information: Red Hat Enterprise Linux 5.3 (Tikanga), JDK 1.5, web container OC4J (whitin Oracle Application Server), Oracle Database 10.1.0.4, JDBC Driver for JDK 1.2 and 1.3, Hibernate version 3.2.6.ga, connection pool library C3P0 version 0.9.1.

UPDATE: Thanks to @BalusC for claryfing that indeed it is Hibernate that executes the query, now I have a better idea about what's going on. I'll explain the way we handle the hibernate session (it's very rudimentary yes, if you have suggestions about how to handle it better they are more than welcome!)

We have a filter (implements javax.servlet.Filter) that when it's starts (init method) it constructs the session factory (supossedly this happens only once). Then every HttpRequest that goes to the application goes through the filter and obtains a new session and it starts a transaction. When the process it's over, it comes back through the filter, makes the commit of the transaction, kills the hibernate session, then continue to the forward page (we don't store the hibernate session in the Http session because it never worked well in our tests).

Now here comes the part where I think the problem is. In our development enviroment we deploy our apps in Tomcat 5.5, and when we start the service all filters start inmediately and only once. In the production enviroment with OC4J doesn't seem to work that way. We deploy the application and only when the first request arrives, OC4J instantiates the filters.

This leads me to think that OC4J instantiates the filters on every request (or at least multiple times, which is still wrong), thus creating a session factory on every request, wich executes that %&%#%$# query, which leads to my problem!

Now, is that correct? It's there a way for me to configure the OC4J for it to instantiate filters only once?

Thanks very much to all of you for taking the time to respond this!

解决方案

All right, after months of looking at the thing, it turns out that the problem wasn't my web application. The problem was the other Oracle Forms applications that use the same instance (different user) of the database.

What was happening was that the Oracle Forms applications were locking records on the database, therefore making pretty much all of the work of the database extremely slow (including my beloved Hibernate query).

The reason of the locks was that none of the foreign keys of the Oracle Forms apps were indexed. So as my boss explained it to me (he discovered the reason) when a user is editing the master record of a master-detail relationship in a Oracle Form application, the database locks the entire detail table if there is no index for its foreign key. That is because the way the Oracle Forms works, that it updates all af the fields of the master record, including the ones of the primary key, wich are the fields that the foreign key references.

In short, please NEVER leave your foreign keys without indexes. We suffered a lot with this.

Thanks to all of you who took the time to help.

这篇关于如何避免这个非常沉重的查询会降低应用程序的速度？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！